Excel VBA "Code execution has been interrupted"

Very strange bug: After starting an Excel VBA macro, the macro stops and Excel displays a "Code execution has been interrupted" dialog. The only way to get the macro to finish is to keep pressing the "Continue" button. The problem only occurs on my development account not in other accounts. The solution is to wait for the dialog, press the "Debug" button to use VBE then type Ctrl+Break. Apparently, the issue is caused by Excel reactivating breakpoints in the macro from earlier debugging sessions. I guess that is why the macro runs to completion on other accounts. Hat tip to The two WORST Excel Errors EVER.

Recursive preorder traversal of a folder tree in VBA

To process files in a folder tree using Excel, I implemented a recursive preorder traversal of a tree in VBA below. I haven't had to do this earlier because I usually use Gnu "find . -exec" to process files in folders.

Option Explicit

'References
'1. Microsoft Scripting Runtime

Global gFso As Scripting.FileSystemObject

Sub Main()
  Set gFso = New Scripting.FileSystemObject
  TraversePreorder "C:\KS Work\Temp"
End Sub

Sub TraversePreorder(ByVal sPath As String)
  Dim oFolder As Scripting.Folder, vMember As Variant
  
  If gFso.FolderExists(sPath) Then
    Debug.Print sPath 'Process folder
    Set oFolder = gFso.GetFolder(sPath)
    For Each vMember In oFolder.Files
      TraversePreorder vMember
    Next vMember
    For Each vMember In oFolder.SubFolders
      TraversePreorder vMember
    Next vMember
  Else
    Debug.Print sPath 'Process file
  End If

End Sub

Timestamps in yyyymmdd format using Visual Basic

I often use the "yyyymmdd" format for timestamps but the Microsoft FormatDateTime supports only available operating system formats and FormatNumber doesn't support left padding integers with zeros, so here's a little bit of code to get the date format that I want:

 dtNow = Now
 strToday = Year(dtNow) & Right("0" & Month(dtNow), 2) & Right("0" & Day(dtNow), 2)

The dtNow stores the current date to avoid the date changing just after midnight. The Right("0" & Month(dtNow), 2) trick ensures that the output always has two digits. I think I first saw this idiom here: VBScript How can I Format Date?.

UTC using WMI

I wanted to get the current time in UTC (i.e. time without any offset) but there doesn't seem to be a timezone-style function in VBA / VBScript. Here's a function using WMI (Windows Management Instrumentation) to get time in UTC and a hack to pad the numbers so that they always have two digits. The output has this format: yyyy/mm/dd hh:mm:ss

'VBA References
'1. Microsoft WMI Scripting 1.2 Library

Function Utc() As String
  Dim ws As WbemScripting.SWbemServices, colItem As WbemScripting.SWbemObjectSet, objItem As WbemScripting.SWbemObject

  Set ws = GetObject("winmgmts:\\localhost\root\CIMV2")
  Set colItem = ws.ExecQuery("SELECT * FROM Win32_UTCTime")
  For Each objItem In colItem
    Utc = objItem.Year & "/" & Right("00" & objItem.Month, 2) & "/" & Right("00" & objItem.Day, 2) _
      & " " & Right("00" & objItem.Hour, 2) & ":" & Right("00" & objItem.Minute, 2) & ":" & Right("00" & objItem.Second, 2)
  Next

End Function

• Win32_UTCTime class from Microsoft
• Time Zones And Daylight Savings Time

Get Internet Explorer version using WMI StdRegProv

In Windows XP, you could get the version of Internet Explorer on a computer using the "\Applications\MicrosoftIE" namespace in this fragment of VBA: (To use this code in this article in your VBA project, include a reference to Microsoft WMI Scripting 1.2 Library.)

    Dim objSWbemLocator As SWbemLocator
    Dim objSWbemServices As SWbemServices, colSWbemObjectSet As SWbemObjectSet, objSWbemObject As SWbemObject, varValue As Variant

    Set objSWbemServices = objSWbemLocator.ConnectServer(strServer:=".", strNamespace:="\Applications\MicrosoftIE")
    Set colSWbemObjectSet = objSWbemServices.ExecQuery("Select * from MicrosoftIE_Summary")
    For objSWbemObject in colSWbemObjectSet
      Debug.Print objSWbemObject.Version
    Next objSWbemObject

The "\Applications\MicrosoftIE" namespace is not available after the introduction of Vista (and Windows 7), so the alternative is to examine the registry:

Const HKEY_LOCAL_MACHINE = &H80000002
...
    Dim objSWbemLocator As SWbemLocator
    Dim objSWbemServices As SWbemServices, objSWbemObject As SWbemObject, varValue As Variant

    Set objSWbemServices = objSWbemLocator.ConnectServer(strServer:=".", strNamespace:="\root\default")
    Set objSWbemObject = objSWbemServices.Get("StdRegProv")
    objSWbemObject.GetStringValue HKEY_LOCAL_MACHINE, "SOFTWARE\Microsoft\Internet Explorer\", "Version", varValue
    Debug.Print = varValue

Specify Rows or Columns for Excel AutoFit

If you use the Excel AutoFit Method to resize cells to display their contents nicely, remember to specify whether to AutoFit rows or columns in Excel, i.e. use Range().Columns.AutoFit or Range().Rows.AutoFit, not just Range().AutoFit. If you don't specify rows or columns, Excel produces Run-time error '1004': AutoFit method of Range class failed.

Create an MSIE Favorite using VBA & WSH

A little sub-routine to create MSIE Favorites for MS-Office users. It uses Windows Scripting Host (WSH) which has a shell object that has functions to find a computer's Favorites folder (SpecialFolders()) and create a URL shortcut (CreateShortcut()).

Option Explicit

'Add references:
'1. Microsoft Scripting Runtime (Scripting namespace)
'2. Windows Script Host Object Model (IWshRuntimeLibrary namespace)

Public Sub CreateFavorite(ByVal strName As String, ByVal strUrl As String)
  Dim fso As Scripting.FileSystemObject
  Set fso = CreateObject("Scripting.FileSystemObject")
  
  Dim wshShell As IWshRuntimeLibrary.wshShell
  Set wshShell = CreateObject("Wscript.Shell")
  
  Dim strFavorites As String
  strFavorites = wshShell.SpecialFolders("Favorites")
  
  Dim shortcut As IWshRuntimeLibrary.WshURLShortcut
  Set shortcut = wshShell.CreateShortcut(fso.BuildPath(strFavorites, strName & ".url"))
  shortcut.TargetPath = strUrl
  shortcut.Save
  
  Set shortcut = Nothing
  Set wshShell = Nothing
  Set fso = Nothing
End Sub

Note to myself: don't confuse:

• IWshRuntimeLibrary.WshShell with Shell32.Shell in Microsoft Shell Controls and Automation
• WshShell.SpecialFolders() with FileSystemObject.GetSpecialFolders() function. The latter function only returns a small number of special folders (windows, system and temporary).

Creating ZIP files with VBA / VBScript

I wanted to create a Windows scheduled task to regularly compress a log file on several servers. The simple solution would be to install a command-line ZIP program and write a CMD script. After some searching, it became more interesting to write a VBScript program that uses the Compressed (zipped) folder feature in the Windows Explorer.

The result is the ZipFile VBA and VBScript program at the end of this posting. I wrote and tested the VBA program first then converted it to VBScript. You can run the VBScript program by providing the path of the ZIP archive and the path of the file to compress.

The program first deletes any existing ZIP file with the same name and creates an empty ZIP folder (actually a file). Then it calls the Windows Shell CopyHere() method to add a new file into the ZIP folder. The CopyHere() method doesn't block (i.e. it returns control immediately to the script) so the program polls the ZIP folder once a second to check if a file has been added (the ZIP folder's Items.Count is incremented). Without this polling loop, the program ends before the file is added (you can test it by commenting out the polling loop statements and archiving a large file).

The numeric argument for the CopyHere() method is a bit-string to avoid displaying the Windows Compressing... progress window. However, I found that the progress window is still displayed but it doesn't seem to affect the ZIP archive when the program is run as a scheduled task (whew!).

References

• ZIP files
• Converting VBA Programs to VBScript using SED
• Folder.CopyHere Method

VBA Version

Attribute VB_Name = "ZipFile"
Option Explicit
Option Base 0

Declare Sub Sleep Lib "kernel32" (ByVal dwMiliseconds As Long)

'VBA add these references
'1. Microsoft Scripting Runtime
'2. Microsoft Shell Controls and Automation

Public Sub MakeZip(zipPath As String, filePath As String)
  MakeEmptyZip zipPath
  AddFile zipPath, filePath
End Sub

Private Sub AddFile(zipPath As String, filePath As String)
  Dim sh As Shell32.Shell, fdr As Shell32.Folder, cntItems As Integer 'cnt = Count
  Set sh = CreateObject("Shell.Application")
  Set fdr = sh.Namespace(zipPath)
  cntItems = fdr.Items.Count
  fdr.CopyHere filePath, 4 + 16 + 1024
  Do
    Sleep 1000
  Loop Until cntItems < fdr.Items.Count
  Set fdr = Nothing
  Set sh = Nothing
End Sub

Private Sub MakeEmptyZip(zipPath As String)
  Dim fso As Scripting.FileSystemObject
  Set fso = CreateObject("Scripting.FileSystemObject")
  If fso.FileExists(zipPath) Then
    fso.DeleteFile zipPath
  End If
  fso.CreateTextFile(zipPath).Write "PK" & Chr(5) & Chr(6) & String(18, Chr(0))
  Set fso = Nothing
End Sub

'Entry MakeZip WScript.Arguments(0), WScript.Arguments(1)

VBSCript Version

Option Explicit


'VBA add these references
'1. Microsoft Scripting Runtime
'2. Microsoft Shell Controls and Automation

Public Sub MakeZip(zipPath, filePath)
  MakeEmptyZip zipPath
  AddFile zipPath, filePath
End Sub

Private Sub AddFile(zipPath, filePath)
  Dim sh, fdr, cntItems
  Set sh = CreateObject("Shell.Application")
  Set fdr = sh.Namespace(zipPath)
  cntItems = fdr.Items.Count
  fdr.CopyHere filePath, 4 + 16 + 1024
  Do
    WScript.Sleep 1000
  Loop Until cntItems < fdr.Items.Count
  Set fdr = Nothing
  Set sh = Nothing
End Sub

Private Sub MakeEmptyZip(zipPath)
  Dim fso
  Set fso = CreateObject("Scripting.FileSystemObject")
  If fso.FileExists(zipPath) Then
    fso.DeleteFile zipPath
  End If
  fso.CreateTextFile(zipPath).Write "PK" & Chr(5) & Chr(6) & String(18, Chr(0))
  Set fso = Nothing
End Sub

MakeZip WScript.Arguments(0), WScript.Arguments(1)

Converting VBA Programs to VBScript using SED

Writing VBScript programs can be a pain without an IDE with early-binding to ensure that the name and arguments of procedures are correct and you have start the Microsoft Script Debugger to debug your code separately from your editor. How very 90s! On the other hand, MS-Office has an IDE for writing and debugging VBA programs. What's more, since VBScript is very similar to VBA (and VB), it can be simpler to write a VBA program first, export it as a BAS file then convert it to VBS file (still very 90s but slightly less painful).

Below is a simple text converter using sed (the Unix stream editor), followed by an explanation of each rule. To use these rules, copy and save them into a file such as bas2vbs.sed, then run it using sed -f bas2vbs.sed <file>.bas > <file>.vbs. You should be familiar with sed regular expressions to follow the rules. The conversion isn't exhaustive or perfect but it works well enough for me.

/^Attribute/d
/^Declare/d
/^Option Base/d
s/ As [^,)]*//g
s/Next [^ ]*/Next /
s/^'Entry //

General Syntactic Conversions

/^Attribute/d

An exported VBA file has one or more lines of metadata such as Attribute VB_Name="<module>". These metadata lines aren't supported in VBScript, so this rule deletes them.

/^Declare/d and Sleep

The Windows Scripting Host (WSH / WScript) includes a useful function called Sleep() which has an equivalent function in kernel32.dll. To make the kernel32.dll function visible in VBA, you declare it like this:

Declare Sub Sleep Lib "kernel32" (ByVal dwMiliseconds As Long)

then use it like this:

Sleep(1000)

VBScript doesn't support the Declare keyword, so that declaration is deleted. When the VBSscript script is run, the call to Sleep(n) function calls WScript.Sleep(n).

/^Option Base/d

In VBA, you can specify the default lowest index value for your variables. Coming from programming in C-style languages, I habitually define Option Base 0 so that my arrays start from index 0. In VBSript, array indexes start from 0 and the Option Base declaration is not supported, so that declaration is deleted from the VBS code.

s/ As [^,)']*//g

If you declare the type of your variables, the VBA IDE can auto-complete the class constants, properties or methods relating to your variables using early-binding. If you use Option Explicit, you have to declare variable names but all VBScript variables are type Variant so the type name after the variable is not required. This sed rule deletes the type name from statements (e.g. Dim x as type1, y as type2 becomes Dim x, y) and procedure definitions (e.g. Sub f (ByVal a as type3, ByVal b as type4) becomes Sub f (ByVal a, Byval b)).

s/Next [^ ]*/Next /

VBA allows you to specify the control variable to advance in a For...Next statement, e.g. the variable i in For i ... Next i. Specifying the control variable after Next is optional but I use it to make loops (especially nested ones) easier to read. VBScript doesn't allow a control variable after the Next keyword, so the variable string has to be deleted.

Inconsistently, the control variable is allowed after Next in For Each...Next statements in VBA and VBScript!

Environment or Library Conversions

s/New \([^ ]*\)/CreateObject("\1")/

You can create new ActiveX objects in VBA using the New keyword. This keyword isn't available in VBScript so you have to use the CreateObject() function instead. The rule just takes the string after the New keyword and makes it an argument for the CreateObject function. However, the two strings aren't necessarily the same (see below).

You can create new ActiveX objects in VBA using the New keyword. This keyword is available in VBSCript (though it's not listed) to create new instances of classes but you have to use the CreateObject() to create an ActiveX object in VBScript.

s/Shell32.Shell/Shell.Application/

A VBA project may include the Microsoft Shell Controls and Automation library in VBA to interact with the Windows Shell. To use the Windows Shell in VBA, you could write Set ws = New Shell32.Shell (the namespace for this library is Shell32 and Shell represents Windows Shell class). Since the New keyword is not available in VBScript, the CreateObject rule and this rule would write Set ws = CreateObject("Shell.Application"), where Shell is the name of the automation server and Application is type of object to create.

Convenience Conversion

s/^'Entry //

A VBScript module requires some entry point that takes command-line arguments or to run a procedure, for example DoSomething WScript.Arguments(0). You can't add this statement by itself into a VBA module (all statements have to be within a procedure) so this convenience rule takes any comment starting with 'Entry and removes that prefix, leaving you with a VBS statement.

References

• Windows Scripting 5.8 Array Function
• Windows Scripting 5.8 VBScript Variables
• VBA For...Next Statement
• Windows Scripting 5.8 For...Next Statement
• VBA For Each...Next Statement
• Windows Scripting 5.8 For Each...Next Statement
• How Object Creation Works in Visual Basic Components
• Windows Scripting 5.8 CreateObject Function
• Windows Scripting 5.8 Keywords (VBScript)

VBA One-liner to Concatenate Excel Cell Values

Here's a VBA one-liner to concatenate a column of Excel cell values, with a separator, into one string: Join(Application.Transpose(<column range>), ","). The column range is an Nx1 array, Transpose() returns a one-dimensional N-element array and Join() function a one-dimensional array as the first parameter.

To concatenate values from a row of cells, you have to use Transpose() twice like this: Join(Application.Transpose(Application.Transpose(<row range>)), ","). Excel's Range always returns a two-dimensional array (in this case, a row is a 1xM array) and you flatten the two-dimensional array by calling Transpose() twice. There doesn't seem to be a built-in function to flatten two-dimensional arrays.

References

• The Join Function
• WorksheetFunction.Transpose Method

Excel Inplace (or embedded) without ActiveWorkbook

An Excel workbook in a SAP InPlace view (or embedded in within a window) stopped working, displaying a run-time 1004 error: Method 'Sheets' of object '_Global' failed. The problem was caused by a UserForm dialog in that workbook which the user can open to enter a value. The code in the dialog then filtered the data in all the sheets based on the user's input. The code failed when it tried to call the Sheets() method.

The Sheets() is a short-cut for Application.ActiveWorkbook.Sheets(), so when a workbook is embedded in a view and loses focus (such as when the dialog was opened), Application.ActiveWorkbook doesn't have a valid value so the Sheets() method fails. When the workbook is opened in an Excel window, there is always an active workbook (and a valid ActiveWorkbook value).

The VBA code was part of the workbook, not in a separate add-in file, so a solution was to use ThisWorkbook.Sheets(). Another solution could be to use Application.Workbooks(1).Activate.

There is also an Application.IsInPlace property which returns TRUE if Excel is embedded within another window.

2010-05-20: Hm ... I could have also used ThisWorkbook.Activate to ensure that there was always an active workbook.

References

• Displaying Data in Excel Inplace View
• ActiveWorkbook Property
• ThisWorkbook Property
• IsInPlace Property
• Macro to Determine If Excel Is Open for In-Place Editing

Prevent VBScript in Custom Outlook Form From Executing

My custom Outlook form added some canned text in the message object in the Item_Open() function. Annoyingly, when I open the form in design mode, the Item_Open() is called, cluttering up the message object. It turns out that you have to hold down the Shift key when opening a form to prevent VBScript in the form from executing.

Holding down the Shift key while starting MS-Access also prevents start-up options from executing, so it is a somewhat consistent feature in MS-Office.

References

• Microsoft Outlook Custom Forms, Tips
• 201087: How to update existing items in an Outlook folder to use a new custom form
• Ignore startup options, Microsoft Office Access 2003

Adding Text To Message Body in Outlook Custom Form

I had a simple request to add some canned text to the message body of a Outlook custom form when the form was first opened, so I did this in VBScript:

Sub Item_Open()
  ...
  Item.HTMLBody = "Blah blah blah"
  ...
End Sub

Yet when I created a new item, the message body was empty! The same problem occurs with Item.Body. After the usual bit of trial-and-error, I discovered that the text only appears if you use HTMLBody or Body on the RHS of a statement:

  Dim strTest
  strTest = Item.HTMLBody
  Item.HTMLBody = "Blah blah blah"

More concisely:

  Item.HTMLBody = "Blah blah blah" & Item.HTMLBody

The text also appears if I display a message box after I add text to the message box.

It seems like the HTMLBody or Body field is not instantiated or refreshed unless it is used.

References

• Description of the Message or Notes field in Outlook 2002

Outlook To: Field Unexpectedly Shrinks

While developing a custom Outlook form, I found that the To: field would unexpectedly shrink to a few pixels tall and pull the remaining controls in the form upwards. The problem occurred when I added Item.Recipients.Add "test" in my VBScript code to populate the To: field with a dummy e-mail address then clicked in the To: field. (I was using a dummy address to avoid accidentally sending test messages to unsuspecting recipients.) It seems as if putting the cursor into that field while Outlook is resolving the non-existent address (i.e. before it marks that address with a wavy red underline) causes the problem. The problem never happens if you populate the To: field manually with a dummy address, probably because Outlook is resolving the address as you type.

In my case, I found that typing Ctrl+K (Check Names) in the To: field restores the height of the field.

The solution during testing is to use a real address (such as your own).

VBA Is*() Functions Only Apply To Variant Types

VBA has several informational functions prefixed with "Is", such as IsNull(), IsMissing() and IsEmpty(), that only work with Variant types. I didn't realise it until I tried to apply them to non-Variant variables and noticed that they didn't work as I expected or always returned the same result.

For instance, if you define optional parameters for a procedure, you can only test if they are used in the procedure call using IsMissing() if the parameter is a Variant type. Using any other type of parameter with IsMissing() always returns False because all other parameter types have a default value supplied by the compiler or by the programmer.

Similarly, only Variant variables can have a Null value so that they can be tested by the IsNull() function. Object types can have a Nothing value, not Null, and can only be tested using the Is operator.

In the same vein, only Variant variables can be uninitialised and tested with IsEmpty(). All other variable types are initialised to some value (e.g. Objects are initialised to Nothing, Integers are initialised to 0).

Simple cache in Excel using VBA static variables

Static variables in VBA functions, like their counterparts in C, retain their values between function calls. A common use for static variables is to maintain a running total or count. This article describes how static variables can be used for cacheing in Excel VBA, compares the performance of a simple approach versus a cache in Excel, and some discussion for using this approach.

Excel applications often have read or write data in specific cells, columns or rows, which are identified by cells containing specific strings. A simple approach is to write a function that returns a cell reference given a string, as below.

Function GetHeaderFind(ByVal str As String) As Range
  Set GetHeaderFind = Sheet1.Range("A:A").Find(What:=str, SearchOrder:=xlByRows, SearchDirection:=xlNext)
End Function

If we know that the cells referenced do not change during the execution of the program, then we could find them when they are first required and cache their values in a Collection. The next time the same string is presented, we just return a reference from the Collection:

Function GetHeaderStatic(ByVal str As String) As Range
  Static colRange As New Collection
  Dim rng As Range
  On Error Resume Next
  Set rng = colRange(str)
  If Err.Number <> 0 Then
    Set rng = Sheet1.Range("A:A").Find(What:=str, SearchOrder:=xlByRows, SearchDirection:=xlNext)
    colRange.Add key:=str, Item:=rng
  End If
  On Error GoTo 0
  Set GetHeaderStatic = rng
End Function

Does caching the results make a difference? We test it by searching for three values 100000 times and writing the time elapsed between the two approaches:

Public Sub TestCache()
  Dim sStart As Single, sTimeFind As Single, sTimeCache As Single
  Dim rng As Range, wks As Worksheet
  Dim rngHeader As Range
  Const iCOUNT As Long = 100000
  Dim i As Long, j As Integer
  Dim aKey(3) As String
  aKey(0) = "x"
  aKey(1) = "y"
  aKey(2) = "z"
  
  'Find implementation
  sStart = Timer
  For i = 1 To iCOUNT Step 1
    For j = 0 To 2 Step 1
      Set rng = GetHeaderFind(aKey(j))
    Next j
  Next i
  sTimeFind = Timer - sStart
  
  'Cache implementation
  sStart = Timer
  For i = 1 To iCOUNT Step 1
    For j = 0 To 2 Step 1
      Set rng = GetHeaderStatic(aKey(j))
    Next j
  Next i
  sTimeCache = Timer - sStart
  
  Debug.Print "Find, Cache times = " & sTimeFind & "," & sTimeCache
  Debug.Print "Ratio = " & sTimeFind / sTimeCache
  End 'Dispose of static variables
End Sub

The results below show that the cache is about 29 times faster than the simple implementation.

Find, Cache times = 16.92969,0.5859375
Ratio = 28.89333
Find, Cache times = 16.85156,0.578125
Ratio = 29.14865
Find, Cache times = 16.84375,0.578125
Ratio = 29.13514

Of course, we could have simply declared and initialised a module-level Collection, and saved ourselves the effort of writing a cache. Below are some reasons why creating a cache in a function is useful:

• We are refactoring an existing function and do not want to (or cannot) change the function's interface.
• Unlike a VBA module-level variable which can be inadvertently changed by any function, we ensure that the Collection can only be changed in a one location because only the function can modify its static variable.

References

• Visual Basic for Applications Reference, Static Statement

R1C1 notation in Excel VBA

Excel supports both A1 and R1C1 notation for referencing cells in formulas. Almost everyone uses A1 notation but it can be easier to use R1C1 notation to generate column references in VBA. For instance, to generate a formula that references a column in A1 notation, you need a function that returns A to Z for the first 26 columns, then returns two letter column names (such as AA) for columns past the 26th. With R1C1 notation, you just concatenate C with a column number (such as C1 or C27). Lastly, to assign a formula to a cell using R1C1 notation, use the cell's FormulaR1C1 property.

Excel VBA programming with run-time errors

When writing VBA programs for Microsoft Excel 2003, you quickly find that you have to detect and handle run-time errors in your programs. VBA provides On Error and Resume statements for handling run-time errors. This article discusses how run-time errors can be used, explicitly or implicitly, in VBA programs.

Let's start with a script below, which calls the WorksheetFunction class' Match() function to find a cell with the value z in the range B1:B8:

Sub TestMatch()
  Dim wks As Worksheet
  Set wks = Sheet1
  Dim dblMatchResult As Double
  dblMatchResult = Application.WorksheetFunction.Match("z", wks.[B1:B8], 0)
  Debug.Print "Found at " & dblMatchResult
End Sub

If Match() function succeeds, then the relative row number is returned. However, if Match() fails, Excel generates this error message: Run-time error '1004': Unable to get the Match property of the WorksheetFunction class. In this case, the error means that Match() did not find z in the range B1:B8. It is similar to a #N/A result in a cell (though Error 1004 is a VBA error code and not related to the Excel-specific xlErrNA).

Below, we explicitly use the run-time error to (trivially) determine what to print in the statement after the error with On Error Resume Next. We also explicitly clear the error handler and allow errors to be detected again with On Error GoTo 0 (of course, detecting errors again doesn't matter in this short example).

Sub TestMatch()
  Dim wks As Worksheet
  Set wks = Sheet1
  Dim dblMatchResult As Double
  On Error Resume Next
  dblMatchResult = Application.WorksheetFunction.Match("z", wks.[B1:B8], 0)
  Debug.Print "z " & IIf(Err.Number = 0, "found at " & dblMatchResult, "not found")
  On Error GoTo 0
End Sub

Another common situation where you may explicitly test error codes is when you use a Collection. VBA's Collection class do not have a method to test if a key exists, so the only way to know if a key exists in your collection object is to try and retrieve the key from the collection and get a run-time error, as in the script below.

Sub TestCollection()
  Dim col As New Collection
  col.Add Key:="b", Item:="bog"
  col.Add Key:="d", Item:="dog"
  
  Dim str As String
  On Error Resume Next
  str = col("c")
  Debug.Print "Key c " & IIf(Err.Number = 0, " found", " not found") & " in collection"
  On Error GoTo 0
End Sub

In this example, the error would be Run-time error '5': Invalid procedure call or argument since the key c doesn't exist in the collection.

(Some run-time error messages generated by VBA are misleading: Error 1004 sounds like VBA failed to resolve the Match() function in the WorksheetFunctions class, and Error 5 is unclear because neither the procedure nor the argument is invalid. Error messages are also inconsistent. The commonly used Sheets Collection generates a different run-time error message than a VBA Collection if you try to access a key that doesn't exist. Try this in the Immediate Pane: ? Sheets(99).Name or ? Sheets("Blah").Name. Unless you have 99 sheets or a sheet called Blah in your workbook, you will see this more comprehensible error, Run-time error '9': Subscript out of range rather than Error 5.)

Some errors can be incidental to your program's logic and easily rectified, such that you can use VBA's On Error GoTo <label> statement to jump to a statement block to fix the error, then re-execute the statement that caused the error with Resume 0. For instance, the following function returns a reference to a sheet. It tries to obtain a sheet reference using the statement on line 3, jumping to lblCreateSheet to create a sheet first if it does not exist, then re-executing line 3.

1 Function AddSheetGoTo(ByVal str As String) As Worksheet
2   On Error GoTo lblCreateSheet:
3   Set AddSheetGoTo = Sheets(str) 'Run-time error '9': Subscript out of range
4   Exit Function
5 lblCreateSheet:
6   Sheets.Add.Name = str
7   Resume 0
8 End Function

Using On Error GoTo <label> ... Resume 0 means that the body of your function is not cluttered with exception handling statements, though you have to include Exit (Function or Sub) to explicitly end your function (and avoid inadvertently executing the error handler), and you should ensure that your error handler can actually fix the error, otherwise you will end up with an infinite loop. Compare this approach with explicitly testing and clearing an error using On Error Resume Next ... On Error GoTo 0, where you end up with a slightly longer and more cluttered function:

 1 Function AddSheetResume(ByVal str As String) As Worksheet
 2   Dim wks As Worksheet
 3   On Error Resume Next
 4   Set wks = Sheets(str) 'Run-time error '9': Subscript out of range
 5   If Err.Number <> 0 Then
 6     On Error GoTo 0
 7     Sheets.Add.Name = str
 8     Set wks = Sheets(str)
 9   End If
10   Set AddSheetResume = wks
11 End Function

This article discussed how VBA's run-time error handling can be used explicitly as part of your program logic, or implicitly to keep your program logic clear. It also notes that VBA run-error codes are not always clear or consistent.

References

• Error Trapping with Visual Basic for Applications
• An Explanation of Trappable Errors in Visual Basic for Apps
• How to Use "On Error" to Handle Errors in a Macro

VBA IIf() as a ternary conditional expression (sometimes)

VBA doesn't have a ternary conditional expression (e.g. C-style ?:), which leads to a long-winded conditional statement like this when you just want assign a value to the same variable based on a condition:

If <condition> Then
  x = 1
Else
  x = 2
End If

In C-style languages, you would just write x = <condition> ? 1 : 2. You can mimic the ?: ternary operator using VBA's IIf(expr, truepart, falsepart) function, x = IIf(<condition>, 1, 2), which reduces the verbosity of VBA code.

There are some limitations, though. Unlike the C-style ?: operator which only evaluates either the truepart or falsepart depending on the condition, IIf() is a function call so all its arguments are evaluated and this evaluation strategy can lead to side-effects or cause an unexpected error message.

Side-effects: x = IIf(condition, function1, function2) would cause both function1 and function2 to be called, so this statement would be slower than the equivalent If condition Then function1 Else function2 End If, where either function1 or function2 are called.

Unexpected error message: You may encounter an unexpected error message if you rely on the value of a variable in truepart or falsepart. For example, the following Excel VBA script searches for a value in a worksheet range B1:B8 and prints its address if it is found, otherwise it prints "Not found".

Sub TestFind()
  Dim wks As Worksheet
  Set wks = Sheet1
  Dim rng As Range
  Set rng = wks.[B1:B8].Find(What:="z")
  Debug.Print IIf(Not rng Is Nothing, "Found " & rng.Address, "Not found")
End Sub

If z is in the range, the script would execute without any errors. On the other hand, if it is not, you may see this unhelpful error: Run-time error '91': Object variable or With block variable not set. The reason is that rng is Nothing, so evaluating the second argument, "Found " & rng.Address, causes an error.

To sum up, you can use IIf() as a exact substitute for ?: if both truepart and falsepart can always be evaluated (e.g. literals, constants or variables).

Optimising VBA for Excel 2003: Avoid EntireRow.Insert

While optimising an Excel 2003 VBA program to write thousands of rows of data, I noticed that Excel's EntireRow.Insert consumed most of the program's processing time. To quantify the time, here's a simple test that just inserts rows into a workbook ...

Option Explicit

Sub Insert10kRows()
  Dim wks As Worksheet
  Set wks = Sheets("Sheet1")
  Dim intI As Integer
  For intI = 1 To 10000
    wks.Range("A1").EntireRow.Insert
    wks.Cells(1, 1).Value = "Test"
  Next intI
End Sub

Sub TestInsert()
  Dim douStart As Double
  Dim i As Integer
  For i = 1 To 6
    douStart = Timer
    Insert10kRows
    Debug.Print i & "0000:" & Timer - douStart
  Next i
End Sub

... and the results:

Rows Inserted Time
10000 7.703125
20000 11.125
30000 14.75
40000 18.9375
50000 23.40625
60000 28.140625

On the other hand, if you just write rows and advance a row counter, like this ...

Sub Write10kRows()
  Dim wks As Worksheet
  Set wks = Sheets("Sheet1")
  Dim intI As Integer
  For intI = 1 To 10000
    wks.Cells(intI, 1).Value = "Test"
  Next intI
End Sub

Sub TestWrite()
  Dim douStart As Double
  Dim i As Integer
  For i = 1 To 6
    douStart = Timer
    Write10kRows
    Debug.Print i & "0000:" & Timer - douStart
  Next i
End Sub

... you get much better performance (low numbers are better).

Rows Written Time
10000 0.6875
20000 0.6875
30000 0.703125
40000 0.6875
50000 0.6875
60000 0.6875

Another observation is that the more rows exist in a worksheet, the longer Excel takes to insert rows (hence the nested loops in the sample code). I can imagine that Excel is spending a lot of time just shuttling data from one block of memory to another to make room for new rows.

For my program, the solution was to refactor the code to replace row insertion with a row counter and to write data to the appropriate row. It's a little more complicated but the program runs at least 10x faster.