Daily Dose of Excel

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 People collection that contains Person items.
In my Person class I’ve written a method (Function) called CompareTo. It works a lot like VBA’s StrComp, returning -1, 0 or +1 depending on whether Item 1 is less than or greater than Item 2.
I’d use it against two person items:

In my People collection class, I’ve created a method called Sort that returns itself in sorted order.
It’s an Insertion Sort that I converted from Wikipedia’s article into VBA. Notice how it uses the CompareTo method for deciding on item placement.

Now I get to use the above in my main code routines:

The code above is available for download. It’s an extension of the code I posted a year ago on the same topic (links 1, 2). It also includes the Enum enhancements suggested by Andy Pope way back then.

You can download SortClass.zip

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 a quick way to generate the code to fill the class. It’s not particularly tricky code to write, but I get sick of typing all the Offset statements. Most of the code is string concatenation and isn’t very interesting. The meat of the procedure is identifying which properties to create lines for.

In this code fragment, I loop through all the code lines looking for lines that are LIKE this constant:

. I started out looking for Property Let statements, but ran into trouble. When I create a class that I know will come from a Range, I generally type in the Public variables in the order they appear in the Range. I can control that order, but I have less control over the order of the Property Let statements and frankly I don’t want to control it. I also can’t be sure there wasn’t a Property Let written later that isn’t a simple getter/setter.

So I look for the private variables, then look for the corresponding Property Let statement. If I find it, I grab the ProcOfLine, which gives me the string I would call using the class in a procedure; msModel becomes just Model. I ignore any property that ends in ID and the Parent property because I set those in the Add method. However, it should be noted that while I want this code to compile right out of the gate, I don’t have any problems with editing it when necessary. If I want to assign the ID property explicitly, I can still do it with an edit and most of the rest of the code is still generated for me.

The sCode variable is extended to include the new line, which is in a With block accounting for it starting with the period. The convention is that the private variables in the child class are in the same order as the data appears in the range. Again, if that’s not right it’s still easier to edit than to write from scratch.

If I have a CCar class with Make, Model, and Year properties, it generates a method like this:

I added a couple of functions to find 1) the first Property statement and 2) the first blank line after the first Property statement. If I add properties later, it would put the newly created Property statements one line away (or at the bottom). I didn’t like it, so I keep them all together and get rid of the blank lines.

Finally, Steve commented that I shouldn’t be using verbs in Property Get procedures, and he’s absolutely right. I changed the FindBy statement to ChildBy. So instead of FindByMakeAndModel As CCar, it generates CarByMakeAndModel As CCar.

Next up on my todo list for this addin:

• Refactor the code so I can do all this stuff at once as well as one at a time
• Automatically create a class from an Access table
• Generate a ChildrenBy method so I can return a filtered parent class. clsCars.CarsByMake(“Toyota”) would return a CCars instance with only Toyotas in it.
• Generate inter-class relationships. If I have CInvoices and CInvoiceLines, I want to generate the relationship between them automagically.

You can download VBHelpersBuild3.zip

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 one should use properties rather than public variables. This comment is *limited* to the case where the property provides unrestricted access to the underlying value.

For those who want a clarification of what I am writing about, in a class module, one could have either

or

As far as a consumer of this class goes, the code would be identical irrespective of which of the above methods the developer of the class used.

One could argue that at some point, the developer may want to enforce a check on R (e.g., enforce that R > 0). Or one might want to provide a property that is read-only or write-once-read-many or one of many other scenarios where a property Get / Let would be required. But, until that happens, what’s the difference whether the class developer uses an unrestricted property or a public variable?

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 bugs me. I do it enough that the extra few steps get on my nerves. Normally, I Alt+I+M/C/U. Then I F4 to open the properties box and change the name. The utility that I use now does a few things. It prompts for a module name. Based on the first letter of the module name, it creates the appropriate module.

M = Standard Module and adds a private constant to the module called msMODULE with the module’s name. This is for the error handling stuff that I copied out of Professional Excel Development

C = Class Module and adds the line Public ModNameID As Long The CreateParent code relies on the presence of this property.

U = Userform and adds nothing.

0:15 I add some more public variables to the class

0:34 The Convert Public to Property finds all the public variables in the class and converts them to private variables and Property Get and Let/Set statements. This is hardcoded to my personal preferences. Namely, all module level variables start with “m” and another prefix indicating the data type. If you don’t like those preferences, you won’t like this add-in.

0:40 Create Parent Class make a class module whose name is the plural of whatever class module is active. If you’re in CPerson, it create CPeople. CCar spawns CCars. And so on. It creates a text file in your My Documents folder and puts all the ATTRIBUTE goodness so you can use For Each and you can avoid using Item. It uses a collection to store the child instances of the class. People seem to prefer to dictionaries, but I think the extra reference dependency makes it not worth it. It generates an Add method, a property that returns an Item (named after the child class) and a Count property. Then it imports that text file into your project. Already have a class with that name? You’ll probably get an error and the error handling is pretty weak right now.

0:53 Create FindBy There are a couple of things that I find myself typing over and over. One of those is a FindBy property in the parent class. This utility sets it up for you. It prompts you for a space delimited list of colon delimited strings (got that?). In the video, I want a property that returns a CEmployee instance given the EmployeeName property. I type EmployeeName:String to create the code. I could also have created a FindBy property for two or more properties. If I had typed EmployeeName:String HireDate:Date, it would have generated a FindBy that looks like this:

And that’s it for class creation. Parent, child, and FindBy in about one minute. The rest of the video is writing a procedure to show that it compiles and works.

The other code that I’m constantly writing but don’t want to is FillFromRange code. If I have a list of employees in a spreadsheet, I want to generate the code that creates all the CEmployee instances and adds them to the parent class. Coming soon I hope. Enjoy and let me know how it goes.

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 do it based on the date. Since 2007, if the month is December through February, standard time is in effect. If the month is April through October, daylight savings time is in effect. Daylight savings time starts the second Sunday in March, and ends the first Sunday in November.

So, by counting Sundays in March and November, I can toggle the time zone. This is what I came up with.

The default WEEKDAY() function returns 1 for Sunday, so if there are 2 Sundays in March including the day in question, turn IsDST to TRUE, and stop the loop. Conversely, if there is 1 Sunday in November including the day in question, leave IsDST as FALSE, and stop the loop.

I use the VBA. leader to ensure this function works on a Mac. Needless to say, it also works in MSWord.

…mrt
©¿©¬

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 enough, you come to realize that there’s a bit of drudgery in setting them up. I’ve tried to eliminate some of that drudgery. So to celebrate Spreadsheet Day, watch four minutes of me writing code with no audio. Fun!

I used CamStudio to record it. I’ve not used it before so I don’t know how to tweak to make the video look better. It’s a little grainy and a little choppy. I don’t have Parkinson’s, it’s the video. The next time I do a video, I’ll do a better job. And I’ll buy a microphone so I can sing while I code.

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 selection, multiple procedure selection, and intra-procedure selection. If there’s no selection, I want the whole procedure that contains the cursor. If the selection spans more than one procedure, I want the entirety of all the procedures that are touched by the selection. If the selection is within one procedure, I want what’s selected.

This code uses the Microsoft Visual Basic Extensibility library.

To get this	Select this

In the last line that begins with

I had to add some extraneous spaces to be able to post code that contains code tags, but they’re not really there in the code.

Other than that I’m merely doing string manipulation with my starting and ending lines and columns.

I think I need to add the

argument when I’m on a single line. I think I’ll use and see how often I’m adding it.

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 season to look at the data. The rankings that are used are current (week 6), which makes the results a little suspect.

I added Points-per-Yard * 100 to Yards-per-Point for each team. For each game, I compared the difference in that calculation between the two teams. Then I calculated the score difference. I used the CORREL function to see how well the data correlated. Ignoring games with D1AA teams, the correlation coefficient was .14.

That’s not too predictive. Maybe week 3 is too early in the season, so I go to week 5. Maybe I need to exclude more crappy opponents. Maybe I need to exclude even more crappy opponents. Maybe instead of looking at stats against highly ranked team, I need to look at those against opponents that are near this week’s opponent’s rank. Maybe not so near. Here’s the data

Maybe, just maybe, this stat doesn’t predict a damn thing.

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 in Auto_Open and Auto_Close (it’s the last two).

Next I use the following code to move between sheets.

If I’m on the first sheet and Control+PageUp, it goes to the last. If I’m not on the first sheet, I tried to replicate the existing function of that hotkey. I may have missed something, but it’s worked so far. Here are the helper functions

One downside to this is the loss of the “infinity space”. That is, if you hold down control and hold Page Up (to infinity) you’ll eventually get to the first sheet and stop. But I think it’s worth it. We’ll see.

I had some redundant code in my previous post that I’m going to fix up. First, this

Gets changed to this

Whenever I assign True or False to a Boolean in an If block, it probably needs to be refactored.

Next I take that huge FillGames method and factor out the two major blocks into its own sub. Fill Games now looks like this

A little cleaner, I’d say. The new private method looks like this

Eliminating redundant code makes it easier to read and understand and helps to prevent errors when making changes.