Archive for the ‘Variables and Data Types’ Category.

IsMissing

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:

Sub DoThing(ByVal lNumber As Long, Optional sName As String = "Joe")
 
End Sub

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.

Error Handling Template

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:

Dim bReturn As Boolean
   
    Const sSOURCE As String = "{PROCEDURE_NAME}()"
   
    On Error GoTo ErrorHandler
    bReturn = True

    {PROCEDURE_BODY}

ErrorExit:
    On Error Resume Next
    {PROCEDURE_NAME} = bReturn
    Exit {PROCEDURE_TYPE}

ErrorHandler:
    bReturn = False
    If bCentralErrorHandler(msMODULE, sSOURCE) Then
        Stop
        Resume
    Else
        Resume ErrorExit
    End If

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:

Me.ListBox1.List = GetProducts(lProductID)

Option 2:

If Not GetProducts(lProductID, aProducts()) Then Err.Raise glHandled_Error
Me.ListBox1.List = aProducts()

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

Floating Point Data Types

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

Sub SinglevDouble()
 
    Dim t As Long
    Dim rCell As Range
   
    Dim snTest As Single
    Dim dTest As Double
    Dim cTest As Currency
   
    Application.Wait Now + TimeValue("00:00:01")
   
    t = Timer
    For Each rCell In ActiveSheet.Columns(1).Cells
        snTest = rCell.Value
        snTest = snTest * snTest
    Next rCell
    Debug.Print "Single", Timer - t
   
    Application.Wait Now + TimeValue("00:00:01")
   
    t = Timer
    For Each rCell In ActiveSheet.Columns(1).Cells
        dTest = rCell.Value
        dTest = dTest * dTest
    Next rCell
    Debug.Print "Double", Timer - t
   
    Application.Wait Now + TimeValue("00:00:01")
   
    t = Timer
    For Each rCell In ActiveSheet.Columns(1).Cells
        cTest = rCell.Value
        cTest = cTest * cTest
    Next rCell
    Debug.Print "Currency", Timer - t
   
    Debug.Print String(50, "-")
   
End Sub

and I got this result

immediate window showing results

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?

Sorting Arrays of User Defined Types

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:

Type MyInfo
    lType As Long
    sName As String
    dStart As Date
End Type
 
Sub Start()
   
    Dim aInfo(0 To 4) As MyInfo
    Dim i As Long
    Dim vaTypes As Variant
    Dim vaNames As Variant
    Dim vaDates As Variant
   
    'fill the array with some unsorted data
    vaTypes = Array(2, 1, 1, 2, 1)
    vaNames = Array("Joe", "Bob", "Bob", "Joe", "Joe")
    vaDates = Array(#1/1/2006#, #2/1/2006#, #1/15/2006#, #6/30/2005#, #1/8/2006#)
   
    For i = 0 To 4
        aInfo(i).lType = vaTypes(i)
        aInfo(i).sName = vaNames(i)
        aInfo(i).dStart = vaDates(i)
    Next i
   
    'call the sort procedure
    SortInfo aInfo
   
    'output the results to the immediate window
    For i = LBound(aInfo) To UBound(aInfo)
        Debug.Print aInfo(i).lType, aInfo(i).sName, aInfo(i).dStart
    Next i
   
End Sub
 
Sub SortInfo(ByRef aInfo() As MyInfo)
   
    Dim i As Long, j As Long
    Dim tTemp As MyInfo
   
    'standard bubble sort loops
    For i = LBound(aInfo) To UBound(aInfo) - 1
        For j = i To UBound(aInfo)
            'sort on the first element
            If aInfo(i).lType> aInfo(j).lType Then
           
                SwapInfo aInfo, i, j
           
            'if the first element is the same, sort on the second
            ElseIf aInfo(i).lType = aInfo(j).lType And _
                aInfo(i).sName> aInfo(j).sName Then
               
                SwapInfo aInfo, i, j
           
            'if the first two elements are the same, sort on the third
            ElseIf aInfo(i).lType = aInfo(j).lType And _
                aInfo(i).sName = aInfo(j).sName And _
                aInfo(i).dStart> aInfo(j).dStart Then
               
                SwapInfo aInfo, i, j
               
            End If
        Next j
    Next i
   
End Sub
 
Sub SwapInfo(ByRef aInfo() As MyInfo, ByVal lOne As Long, ByVal lTwo As Long)
 
    Dim tTemp As MyInfo
   
    tTemp = aInfo(lOne)
    aInfo(lOne) = aInfo(lTwo)
    aInfo(lTwo) = tTemp
   
End Sub

immediate window showing sorted output

Is there an easier way?

Change Named Constants in VBA

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.

Type TaxGroup
    sStateName As String
    sCityName As String
    dStateRate As Double
    dCityRate As Double
End Type

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.

ElseIf Target.Address = Me.Range("inpProjNum").Address Then
   
    Dim sTaxIdFromJob As String
    Dim udtTxGrp As TaxGroup
   
    sTaxIdFromJob = GetTaxIdFromJob(Target.Value)  'function that accesses Job record
    udtTxGrp = GetTaxesFromAR(sTaxIdFromJob) 'function that accesses Tax Group record
   
    Me.Parent.Names("cnsCityName").Value = _
        "=" & Chr$(34) & udtTxGrp.sCityName & Chr$(34)
    Me.Parent.Names("cnsCityTaxRate").Value = _
        "=" & udtTxGrp.dCityRate & "/100*" & StateFactor(sTaxIdFromJob, udtTxGrp)
    Me.Parent.Names("cnsStateName").Value = _
        "=" & Chr$(34) & udtTxGrp.sStateName & Chr$(34)
    Me.Parent.Names("cnsStateTaxRate").Value = _
        "=" & udtTxGrp.dStateRate & "/100*" & StateFactor(sTaxIdFromJob, udtTxGrp)
   
End If

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?

Dots in Dims

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.