Daily Dose of Excel

List all folders in a Microsoft Outlook account

2011-11-03T14:00:00Z

Recently, I wanted to create a list of all the folders in my MS Outlook PST file together with the size of each folder. Outlook provides that information through the user interface. Unfortunately, it shows the result in a modal dialog with no way to save the information elsewhere. So, I decided [...] Read More......(read more)

How Much to Charge for Freelance VBA Development

2011-11-03T02:36:32Z

$150 per hour. $120 per hour if the project is big enough that you won’t have to look for work for a while. $100 per hour if you’re really hungry. I’m sick of reading blog posts about how much to charge. They don’t tell me anything I don’t already know. And they [...] Read More......(read more)

Protect a global variable in another VB project

2011-11-02T05:45:33Z

Developers who have done any kind of programming with the Office 2007 (and later) Ribbon architecture have encountered almost certainly a scenario that resulted in the loss of their pointer to the ribbon. This happens because the ribbon object has to be stored in a global variable and any kind of unhandled error leads [...] Read More......(read more)

Sorting a Custom Collection Class

2011-10-31T06:50:46Z

I’ve been following Dick’s VBHelpers Build series (1, 2, 3) and his last post reminded me that, from time to time, I need to sort a collection of items in-memory. I don’t have to sort all that often, so my approach has changed over time. I’ve kind of settled on the following. Let’s say I have a [...] Read More......(read more)

VBHelpers Build 3

2011-10-25T00:52:03Z

In this iteration, I Create a generator for a FillFromRange method of the parent class Get rid of those unisightly blank lines when converting Public variables to Property statements Change FindBy to ChildBy (thanks Steve J) If I have a range of records that I want to put in a class, which happens a lot, I wanted to create [...] Read More......(read more)

In a class module, why use an unrestricted property?

2011-10-24T02:42:23Z

Over the years, I have followed the “best practice” of always using a property get and let/set combination rather than just declaring a public variable. But, over the last few months I’ve started questioning this dictum. Now, before people start jumping up and down, I am aware of the many very, very good reasons why [...] Read More......(read more)

More Class Module Automation

2011-10-19T00:57:31Z

How was that video? Pretty super-awesome, huh? You can download VBHelpers.zip It’s rough, to say the least, but if you want to mess around with it, have at it. Here’s some more information on what was happening in that video: 0:00 First I insert a class module. Inserting modules is one of those activities that [...] Read More......(read more)

Is it Standard Time yet?

2011-10-18T18:11:25Z

Part of my part-time job schedules world-wide PC-chat conferences weekly, and I announce the time referenced to the East Coast. As daylight savings time is about to end, I wanted an algorithm that knew whether standard time or daylight savings time was in effect. As I’m not worried about the 2:00AM change over, I can [...] Read More......(read more)

Automating Class Creation

2011-10-18T00:40:20Z

Happy Spreadsheet Day. This year, it’s something about helping students. I don’t care about students. They don’t have any money and they spend all their time on the Facebook and the Twitter. Instead of helping students, I made a video about how I create custom class modules. If you use class module [...] Read More......(read more)

Posting Code to this Blog

2011-10-15T01:15:37Z

For years I’ve been typing <code> tags and pasting code between them. But no more! I wrote a small utility that puts the code tags around my code and pops into the clipboard. Think of the seconds that I’ll save. There are three situations that I wanted to cover with this code; no [...] Read More......(read more)

CFB Stats Correlation

2011-10-12T01:53:52Z

Disclaimer: I’m not a statistician. I aced Business Statistics, but that was 20 years ago and I forgot everything the day after the final. My premise is that Points-per-Yard on offense and Yards-per-Point on Defense are predictive of the final score of a game. I went back to week five of the college football [...] Read More......(read more)

Wrap Sheets Hotkey

2011-10-10T13:00:46Z

I use Control+PageUp/PageDown to navigate between sheets. Sometimes I need to get from the first sheet to the last sheet and I don’t want to hit the hotkey seven or eight times to get there. I recently added some code to my UIHelpers addin. First, I set up and destroy the hotkeys [...] Read More......(read more)

CFB Stats Refactor

2011-10-09T16:01:00Z

I had some redundant code in my previous post that I’m going to fix up. First, this Public Function IsAway(hRow As HTMLTableRow) As Boolean Dim bReturn As Boolean bReturn = Left$(hRow.Cells(1).innerText, 1) = "@" [...] Read More......(read more)

CFB Stats Part II

2011-10-08T15:05:06Z

In College Football Statistics, I created my Team and Game objects, filled the Teams collection, and wrote a FindByName property for Teams. Now the fun part; filling the Game objects. This is a long one and I’m not going to sit here and tell you it doesn’t need some refactoring. But it [...] Read More......(read more)

College Football Statistics

2011-10-08T01:33:16Z

Who wants to talk about college football, class modules and XMLHttp requests? Anyone? Well, for the two of you left, here’s what I got. The problem with comparing team statistics, especially early in the season, is cupcakes. Cupcakes are teams that aren’t very good at football. Teams like to schedule [...] Read More......(read more)