Daily Dose of Excel

IsMissing is a built-in function that can be used to see if an optional, Variant argument was passed to a sub or function. Since optional arguments are, well, optional, your code needs to determine if they've been supplied. If the optional argument is declared as a Variant data type, the IsMissing function will tell you if the argument has been supplied or omitted.

Tony Toews recently blogged about IsMissing.

I'm sure I've used IsMissing in the past, but it's very rare. There's a trade-off. To use IsMissing, the argument must be declared as a Variant, and no other data type. In that case, I lose the benefits of strong data typing. For instance, I would have to declare a string as a Variant just to use IsMissing.

The other side is that VBA provides defaults for strongly typed optional arguments. If my optional argument is typed as Long and it's omitted, it becomes a zero. If my optional argument is typed as String and omitted, it becomes a zero length string. That's not necessarily bad, unless I need to distinguish if a zero was passed or the argument was omitted. For Long optional arguments, there's no way to tell the difference.

A third possibility is supplying my own defaults, which I almost always do. I can declare optional argument thusly:

All I've done is replace the default default with my own default, that is a zero length string with Joe. I still have the problem that I won't know if Joe was passed or the argument was omitted, but since I have a specific case as the default, it's less likely to matter.

Since Professional Excel Development was published, I've been using the error handling method described in chapter 12. It's very comprehensive and easy to implement. It's no guarantee that my code is right, of course, but it does guarantee that the end user won't be plopped into the VBE when an error occurs.

I also MZ-Tools while I'm coding. One of my favorite features is being able to add module headers, procedure headers, and error handling code easily. Below is what I have on the Error Handling tab under MZ-Tools > Options:

If you haven't read the book, all non-trivial, non-entry point procedures are made into Boolean functions that return TRUE if no error occurs. When I add the above code into a an entry point procedure, I have to delete any line with the variable bReturn in it because that's only applicable for functions.

The part that I'm not totally clear on is for functions that return something other than Boolean. For instance, I have a function that reads a Jet table, fills an array, and returns a Variant array to be used to populate a ListBox. I can't make this procedure a Boolean function because I need it to return an array. One option is to treat this function as an entry point, which has the effect of not pushing the error back into the calling procedure. This is the way I've been doing it. A second option is to make the function return a Boolean, but pass the "return" variable ByRef. This would change the way I call the procedure, but would still achieve the result I need.

Option 1:

Option 2:

I'm having second thoughts about choosing Option 1. How do you do it?

In Long vs. Integer we settled, once and for all, the debate over integer data types. But what about floating point? Pace recently asked which was better, single or double. (Pace: I know it was two months ago, but cut me some slack.) My gut reaction is that Double is the fastest. Excel stores it's cell values as Double and any other data type would require some internal conversion. If you put a "1" in cell A1 and type

?TypeName(Range("a1").Value)

in the Immediate Window, you get Double as a result. That seems to indicate that cell values are stored as Double. InsaneExcel indicates that Doubles take longer than Singles, but they're not converting cell values. I've never heard that VBA uses Double exclusively and converts other floating points, like it converts other integers to Long. But it seems that if you were reading from cell values, it would have to do some conversion.

There is a slight problem with this theory, however. If you put a string in A1, TypeName will return String as the data type. That indicates that the cell contents are really Variants. If that's true, it shouldn't take any longer to convert a Variant to a Single than it does to convert it to a Double, speaking as someone who wouldn't know a pointer from a hole in his head.

Well, I decide to run this little test

and I got this result

If I didn't include Application.Wait, I got some screwy result. Currency almost always came up negative. Initially, I thought I discovered a way to travel back in time, but alas, it wasn't so. What I did conclude, however, is that it doesn't make a lick of difference whether I use Single or Double. Since Double has more capacity, there's really no reason not to use it. One could make the same argument for Currency, but Currency is really a fixed point data type, not floating point. I only include it in the test because it can store large numbers. Besides, I'm really used to typing Double.

Does my test support that conclusion, or is there a flaw somewhere?

I have an array of user defined types. The type has three elements and I need to sort on all three. Surprisingly, I've never had to sort an array of udt's before. Here's how I did it:

Is there an easier way?

I have an invoice template that has a formula to compute tax, namely

=ROUND(SUMIF(bdyTaxFlag,"T",bdyAmt)*cnsStateTaxRate,2)

If I put a 'T' in a certain column, this will compute tax on that amount. I used a named constant, cnsStateTaxRate, for some reason that escapes me now. I probably thought that I was some kind of decent developer and that decent developers used named constants instead of literal values in their formulas.

There probably are some advantages to it. It makes the formula easier to read, for one. If, instead of cnsStateTaxRate, it said .055, it may not be abundantly clear that it's a calculation of the state taxes. If I were to use that same named constant elsewhere in the sheet, I could change the rate in one place rather than everywhere it occurs. This particular constant is only used here and since the cell next to it says "State Tax:", it's pretty obvious what it does.

Recently, I had to add other taxing authorities to this template, which is why I'm suddenly interested in my motives for creating the constant. It's not hurting anything, but now I feel like I've forced myself to go down a road, down which I may not want to go. When the taxing authority is different, do I change the named constant? That's what I did.

The tax information is stored in an external database. When the user selects a Job ID, the Tax Group is retrieved from the Job record. Then the city and state information are retrieved from the Tax Group record. Once the proper information is retrieved, I change the defined constants' values to the correct information. I started by creating a user defined type in a standard module.

This will hold the information from the external database until I can put it in the defined constants. I use the Worksheet_Change event to determine if the Job ID has been entered or changed.

After I wrote this, I questioned if this was the right thing to do. I had to create cnsCityName and cnsStateName because those strings were simply hard-coded onto the spreadsheet. They never changed, even if the rates could. My other options include changing the formula/cell directly, and storing the current tax information on a separate sheet and referring to that range in the formula, rather than a constant (that's not really constant, is it?). What do you think? How would you approach it?

Hi, I'm the dot operator. You may remember me from such VBA statements as object.property and object.method.

Okay, enough of that. When you see a dot operator in a Dim statement, you're seeing it in the form of library.object. For instance, if you're automating Outlook, you may use:

Dim olMail As Outlook.MailItem

Outlook isn't an object, it's the name of the library to which you are early binding. MailItem is the name of an object in that library. Another common one is an ADO Recordset:

Dim rs As ADODB.Recordset

You don't need to call out the library for your program to compile, but you may need to for it to run properly. The problem that you may run into is when you dimension a variable as an object that exists in two different libraries. Each library has a priority and if you specify an object from multiple libraries, the object from the library with the highest priority will be used. That's not a problem if the library you intend to use has the highest priority, but that's not something you can depend on.

Go to the VBE and Tools > References and you'll see a Priority label with up and down buttons. You can change the priority of the libraries that are selected. On my wife's Office 2003 installation, the default libraries in order of priority are:

• VBA
• Excel
• OLE Automation
• Office

If I exit the References dialog and open the Object Browser (F2), I also see something called stdole. I have no idea what this is.

In general, I specify the library if it's not a default library. If you were to examine my code, you would probably find exception after exception to this rule. I should have written that I try to specify non-default libraries. I almost never specify the MSForms library and it's not default. MSForms gets added when you add a userform. It contains a Textbox object and there's also a Textbox object in the Excel object library (from the Drawing toolbar, I believe). That's one that I really should specify.

Jamie recently metioned in a newsgroup post that he sometimes includes the Excel library in his Dim statements. In Excel VBA, this isn't necessary because Excel is always the highest priority library (unless the user changes it). However, if you want to port your code to VB6 or some other VBA, you will save yourself time by including the Excel library. Unless I specifically intend to port the code, I leave it off. But I think it's a good point, and it really costs nothing to specify the Excel library. If nothing else, I'd learn what was in the Excel library vs. the VBA library.