Daily Dose of Excel

List all folders in a Microsoft Outlook account

Daily Dose of Excel — Thu, 03 Nov 2011 14:00:00 GMT

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

Daily Dose of Excel — Thu, 03 Nov 2011 02:36:32 GMT

$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

Daily Dose of Excel — Wed, 02 Nov 2011 05:45:33 GMT

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

Daily Dose of Excel — Mon, 31 Oct 2011 06:50:46 GMT

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

Daily Dose of Excel — Tue, 25 Oct 2011 00:52:03 GMT

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?

Daily Dose of Excel — Mon, 24 Oct 2011 02:42:23 GMT

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

Daily Dose of Excel — Wed, 19 Oct 2011 00:57:31 GMT

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?

Daily Dose of Excel — Tue, 18 Oct 2011 18:11:25 GMT

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

Daily Dose of Excel — Tue, 18 Oct 2011 00:40:20 GMT

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

Daily Dose of Excel — Sat, 15 Oct 2011 01:15:37 GMT

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

Daily Dose of Excel — Wed, 12 Oct 2011 01:53:52 GMT

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

Daily Dose of Excel — Mon, 10 Oct 2011 13:00:46 GMT

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

Daily Dose of Excel — Sun, 09 Oct 2011 16:01:00 GMT

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

Daily Dose of Excel — Sat, 08 Oct 2011 15:05:06 GMT

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

Daily Dose of Excel — Sat, 08 Oct 2011 01:33:16 GMT

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)