Knowledge Base/VBA/General

From The Thalesians

Built-in volatile functions

Here is the complete list of built-in volatile functions in Excel:

• CELL
• INDIRECT
• INFO
• OFFSET
• NOW
• RAND
• TODAY

Converting a date to string

To convert a date to string in Excel, you can use the TEXT function:

=TEXT(NOW(),"yyyymmdd")

The Immediate Window

Despite (or, perhaps, in virtue of) its simplicity, diagnostic output remains a useful debugging tool. In languages such as Perl this was, for a while, the only debugging tool. VBA Editor has a fully functional integrated debugger, yet it does not obviate the need for diagnostic output.

What are the options?

First, we have message boxes:

MsgBox "Variable sFoo = " & sFoo

By default these are modal, i.e. they interrupt the execution of the program. Message boxes also require the programmer to press the "OK" button, which can seriously slow down debugging.

Second, we have Excel worksheets. Suppose that DebugSheet is a possibly hidden worksheet that you use for debugging output. Then you could do this:

DebugSheet.Range("A1").Value = sFoo

Is this any more convenient than message boxes? Probably not. Our DebugSheet is a nuisance. We need to hide it from the users. It increases the complexity of our workbook solution. Finally, while we are debugging, we need to keep looking at it. It's often useful to see the diagnostic output in the order in which it was produced. So we need to iterate through a range in DebugSheet... It only gets worse.

But we have another option: the Immediate Window. In the VBA Editor (press Alt-F11 in Excel to invoke it) press Ctrl-G. The immediate window will be displayed. Now you can write to it as follows:

Debug.Print "Variable sFoo = " & sFoo

The output will be added to the immediate console. It solves all the problems that I have mentioned above. Also note that it is unnecessary to hide the Immediate Window from the user (and alter your code before productionising the spreadsheet). The Immediate Window will only be visible in the VBA Editor — not in Excel itself.

In everything that follows I assume that you are aware of the Immediate Window and know how to display it and write to it. There are plenty of Debug.Prints in the code examples.

Renaming modules in VBA Editor

Go to the VBA Editor (Alt-F11). Make sure the Project Explorer is visible (Ctrl-R); it presents a tree view of the project. Make sure the Properties Window is visible (F4).

Select the module you wish to rename in Project Explorer. It will be under "VBAProject (<filename.xls>)", "Modules". You will see its "(Name)" in the Properties Window. Edit it and press Enter.

Your module has been renamed.

Speeding up the execution of VBA code in Excel

There are several things that could be done to make the code run faster.

• Hiding the VBE Editor via Application.VBE.MainWindow.Visible may improve the performance in some cases.
• Setting Application.Calculation controls the recalculation of the cells while executing code. Setting it to false should speed up the execution of the code that changes the cell values.
• The Application.ScreenUpdating property controls most display changes on the monitor while a procedure is running. When this property is set to false, toolbars remain visible and Excel can still use the status bar prompts, input boxes, dialog boxes and message boxes to input and output the data. Setting this property to false can speed up the execution in many cases.
• Disabling event handling may improve the performance, but this may delay the execution of the scheduled OnTime events and have other undesirable consequences. Application.EnableEvents should therefore be used with care.

Private Sub FastSub()
    Dim originalVBEMainWindowVisible, _
        originalScreenUpdating, _
        originalEnableEvents As Boolean
    Dim originalCalculation As Integer
 
    originalVBEMainWindowVisible = Application.VBE.MainWindow.Visible
    originalCalculation = Application.Calculation
    originalScreenUpdating = Application.ScreenUpdating
    originalEnableEvents = Application.EnableEvents
 
    Application.VBE.MainWindow.Visible = False
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.EnableEvents = False
 
    ' Your code here
 
    Application.EnableEvents = originalEnableEvents
    Application.ScreenUpdating = originalScreenUpdating
    Application.Calculation = originalCalculation
    Application.VBE.MainWindow.Visible = originalVBEMainWindowVisible
End Sub

Generating #N/A values in VBA

Unfortunately, there is no such thing as Excel.WorksheetFunction.NA in VBA. So how can one generate the #N/A values in VBA, the same as you get when using the Excel formula =NA()?

You can use the following function:

Function NaN() As Variant
    NaN = CVErr(xlErrNA)
End Function

Note that the function's return type is Variant, not Double.

Checking if a value is #N/A in VBA

Fortunately, there is an Excel.WorksheetFunction.IsNA in VBA. Therefore it is straightforward to check if a value is #N/A. You can wrap this check into the following function:

Function IsNaN(ByVal x As Variant) As Boolean
    IsNaN = Excel.WorksheetFunction.IsNA(x)
End Function

We can also use it to test our NaN function. The following returns True:

IsNaN(NaN())

Checking if a dynamic array has been redimmed

A typical definition of a static array looks as follows:

Dim someStaticArr(1 To 10) As String
</pre>
 
This array is static since its size has been specified in the <tt>Dim</tt> statement that declares it; its size can never be changed.
 
'''Dynamic arrays''' are created as follows:
 
<code lang="vb">
Dim arr() As String

Notice that the size has not been specified, hence the array is dynamic. The array can be "redimmed" ("sized" is the right word) later on:

ReDim Preserve arr(5) As String

Before a dynamic array is "redimmed" it is known as empty or unallocated. If you try executing something like UBound on arr before it has been "redimmed" an error will result.

Thus you may need to check if the dynamic array has been "redimmed". Unfortunately, IsEmpty(arr) will return true, so this is not what we need. This is achieved as follows:

If (Not arr) = -1 Then ' It is empty

Since this code is not particularly intuitive, I prefer to wrap it into a function:

Public Function IsUnallocated(ByRef arr() As Variant) As Boolean
    If (Not arr) = -1 Then
        IsUnallocated = True
    Else
        IsUnallocated = False
    End If
End Function

There is a subtle problem with this function. If you are trying to call it on some v of type Variant (which may be a Variant(), but may be something else), VBA will not execute the function. It will come up with a "Type mismatch" message. However, chances are your variable is declared as Variant, not as Variant() even though it is, in fact, a Variant() or a Double(), etc. What to do?

In these cases you can use a (messier) function defined as follows:

Public Function IsUnallocatedArray(ByRef arr As Variant) As Boolean
    If IsArray(arr) And Not IsRange(arr) Then
        Dim u As Long
 
        On Error GoTo ErrorHandler
 
        u = UBound(arr)
    End If
 
    IsUnallocatedArray = False
 
    Exit Function
 
ErrorHandler:
 
    IsUnallocatedArray = True
End Function

You can feed it pretty much anything you like; IsUnallocatedArray will return True if and only if its parameter is an unallocated array.

The IsRange function is defined elsewhere on this page. This check is necessary since if arr is a Range, then IsArray(arr) is True while UBound(arr) produces an error.

Determining the number of dimensions of an array

The following function returns the number of dimensions of a given array (1 for one-dimensional arrays, 2 for two-dimensional arrays, etc.):

Public Function ArrayDimCount(ByRef arr As Variant)
    Dim d, errorCheck As Integer
 
    On Error GoTo DimensionDetermined
 
    ' VBA arrays can have up to 60000 dimensions
    For d = 1 To 60000
        errorCheck = LBound(arr, d)
    Next d
DimensionDetermined:
    ArrayDimCount = d - 1
End Function

Functions and procedures with variable arguments

VBA supports functions and procedures with variable arguments (variable arity functions and procedures) through parameter arrays introduced by the ParamArray keyword. For example:

Function MultiplySum(factor As Integer, ParamArray params() As Variant)
    Dim total, p
    total = 0
    For Each p In params
        total = total + p
    Next
    MultiplySum = factor * total
End Function

You don't need to know at design time how many arguments have been passed to MultiplySum. If you call MultiplySum as

=MultiplySum(10, 1, 2, 3, 4, 5)

the result will be 150, i.e. 10 * (1 + 2 + 3 + 4 + 5).

Functions and procedures with optional arguments

Functions and procedures with optional arguments can be defined using the Optional keyword:

Sub Hello(ByVal name As String, Optional ByVal surname As String)
    MsgBox "Hello " & name & " " & surname
End Sub

This can be called as Hello "Paul", "Bilokon" and Hello "Paul".

If the caller does not provide a value for an optional argument, it is automatically initialised in the same way it would be if it were a variable - String arguments are initialised to zero-length strings (""), numeric arguments to zero (0), Boolean arguments to False, etc. You can override this behaviour by providing default values:

Sub Hello(ByVal name As String, Optional ByVal surname As String = "Bilokon")
    MsgBox "Hello " & name & " " & surname
End Sub

If called as Hello "Paul", this will display "Hello Paul Bilokon".

If you want to know for certain that an optional argument has not been supplied by the caller then that argument must have the type Variant. Only Variants can contain the value Missing. This is how you can check for a missing optional argument:

Sub Hello1(ByVal name As String, Optional ByVal surname As Variant)
    If IsMissing(surname) Then
        MsgBox "No surname"
    Else
        MsgBox "Hello " & name & " " & surname
    End If
End Sub

Calling Hello1 as Hello1("Paul") will result in a message box: "No surname".

Any arguments that follow an optional argument in an argument list must also be optional.

Simulating "pointers to functions" in VBA

VBA does not support the C-style pointers to functions. However, with some work it is possible to call a function by its name using the Application.Run method. There are a few points to bear in mind though.

Create a new workbook in Excel and save it as C:\Iteration.xls, say. Press Alt-F11 to open the VBA Editor. Make sure the Project Explorer is visible. It presents a tree-view of the project. If you can't see it on the left, press Ctrl-R.

In Project Explorer, right-click on "VBAProject (Iteration.xls)" and select Insert > Module. A new module, a "Module1", will appear. Enter the following code:

Public Function Quadratic(x)
    Quadratic = x ^ 2
End Function

We will now call this function by name.

In Project Explorer, under "VBAProject (Iteration.xls)", "Microsoft Excel Objects" right-click on "ThisWorkbook". Type in the following code:

Public Sub Iterate()
    Dim x, y As Double
 
    For x = 1 To 10
        y = Application.Run("Module1.Quadratic", x)
        MsgBox y
    Next
End Sub

Try running Iterate by pressing F5 while the cursor is in the body of the method.

Notice that you could supply the name of the method as a variable:

Public Sub Iterate()
    Dim x, y As Double
    Dim methodName As String
 
    functionName = "Module1.Quadratic"
 
    For x = 1 To 10
        y = Application.Run(functionName, x)
        MsgBox y
    Next
End Sub

This method name could be a parameter of Iterate this is about as close as you can get to "function pointers" in VBA.

Subroutines may be invoked with Application.Run as well as functions. E.g. you can modify the code in "ThisWorkbook" as follows:

Public Sub MyCallback()
    MsgBox "In MyCallback"
End Sub
 
Public Sub CallIt()
    Application.Run ("ThisWorkbook.MyCallback")
End Sub

Notes

Now the caveats:

1. You must qualify MyCallback with "ThisWorkbook." when referring to it in CallIt, otherwise you will get "Run-time error '1004': Application-defined or object-defined error".
2. If Quadratic were defined in "ThisWorkbook" rather than a module, "Module1", Application.Run would for no apparent reason ignore its output and y wouldn't be set.
3. This was tested in Microsoft® Office Excel 2003 SP2.

"Complile error: Sub or Function not defined" when trying to call a procedure or function from another project

You are getting the "Compile error: Sub or Function not defined" when trying to call a procedure or function from another project, which could be an XLA (add-in) and may also be open in VBA Editor.

Go to the calling project in VBA Editor. Click on Tools > References. Make sure the project containing the procedure/function is ticked in the list of Available References. You may need to browse to the relevant XLA or DLL file.

Variant properties

Sometimes we may want to declare a property that can be set to both object reference and primitive values. However, here we face a hurdle:

Private m_fooBarBaz As Variant
 
Public Property Get FooBarBaz() As Variant
    FooBarBaz = m_fooBarBaz
End Property

This property definition will work if m_fooBarBaz is set to a value of a primitive type, such as Long but will fail miserably if it is set to an object reference ("Run-time error '438': Object doesn't support this property or method"). On the other hand,

Private m_fooBarBaz As Variant
 
Public Property Get FooBarBaz() As Variant
    Set FooBarBaz = m_fooBarBaz
End Property

will fail if m_fooBarBaz is, say, a Long ("Compile error: Object required").

How can we create truly polymorphic properties?

IsObject comes to the rescue. Here is our solution:

Private m_fooBarBaz As Variant
 
Public Property Get FooBarBaz() As Variant
    If IsObject(m_fooBarBaz) Then
        Set FooBarBaz = m_fooBarBaz
    Else
        FooBarBaz = m_fooBarBaz
    End If
End Property
 
Public Property Let FooBarBaz(ByVal newFooBarBaz As Variant)
    m_fooBarBaz = newFooBarBaz
End Property
 
Public Property Set FooBarBaz(ByVal newFooBarBaz As Variant)
    Set m_fooBarBaz = newFooBarBaz
End Property

We can test it as follows:

Public Sub TestFooBarBaz()
    FooBarBaz = 5
    Debug.Print "We have " & FooBarBaz
 
    FooBarBaz = "hello"
    Debug.Print "We have " & FooBarBaz
 
    Set FooBarBaz = ThisWorkbook
    Debug.Print "We have " & FooBarBaz.Name
End Sub

We get the following output in the Immediate window, as required:

We have 5
We have hello
We have QuaRT-Excel.xla

Distinguishing between the arrays and ranges

It is often possible to write polymorphic code that works on both the arrays and ranges. For Each comes in extremely useful. Here is an example of a function that may be passed an array as its parameter, but it will work equally well on a range:

Public Function Numbers(ByRef r As Variant) As Variant()
    Dim result As New Stack
 
    Dim c As Variant
    For Each c In r
        If Excel.WorksheetFunction.IsNumber(c) Then
            result.Push c
        End If
    Next c
End Function

What happens when this polymorphism breaks? Arrays and ranges are in many ways different beasts. If x is a range, x.Count will work, UBound(x) will not. If it is an array, the situation is quite the opposite. In conclusion, we need a method for distinguishing between the two.

Unfortunately, VBA's IsArray returns True for ranges containing multiple cells. Thus calling

Public Function IsArr(arr As Variant) As Boolean
    IsArr = IsArray(arr)
End Function

as =IsArr(A1) from a worksheet will give FALSE as a result, while =IsArr(A1:A2) will return TRUE. IsArray is ignorant of the difference between arrays and ranges. However, we can define a function that will work as desired:

Public Function IsRange(r As Variant) As Boolean
    On Error GoTo ErrorHandler
    IsRange = (TypeName(r) = "Range")
 
    Exit Function
 
ErrorHandler:
 
    IsRange = False
End Function

We can call it from VBA or from a worksheet. =IsRange(A1:A2) and =IsRange(A1) are now true, =IsRange({1, 2, 3}) and =IsRange(123) are now false.

Now we can use it as follows:

If IsRange(r) Then
    ' Range...
ElseIf IsArray(r) Then
    ' Array...
Else
    ' Scalar...
End If

Using regular expressions in VBA

Regular expressions are a set of extremely powerful text processing techniques which were popularised in Perl but have recently found their way into almost all modern programming languages, in one form or another.

Although many people seem to be unaware of the fact, regular expressions are available in VBA via the Microsoft VBScript Regular Expressions 5.5 framework.

In order to use regular expressions in VBA, first open the VBA Editor (Alt-F11), select Tools > References, and in the list of available references tick Microsoft VBScript Regular Expressions 5.5.

You are now ready to use regular expressions in your code:

Sub RegExTest()
    Dim s As String
 
    s = "   Bilokon , Paul, 1982-07-04"
 
    Dim re As RegExp
    Set re = New RegExp
 
    re.Pattern = "^\s*(\w+)\s*,\s*(\w+)\s*,\s*(\d{4})-(\d{2})-(\d{2})\s*$"
    re.Global = True
 
    Dim matches As MatchCollection
    Set matches = re.Execute(s)
 
    Dim m As Match
    Set m = matches(0)
 
    Debug.Print "Name: " & m.SubMatches(1)
    Debug.Print "Surname: " & m.SubMatches(0)
    Debug.Print "Born: " & m.SubMatches(2)
End Sub

This will produce the following output:

Name: Paul
Surname: Bilokon
Born: 1982

For more information on regular expressions please read this.

Obtaining the formatted text of a cell

Press Alt-F11. In Visual Basic Editor, create a new module and type in the following function:

Public Function CellText(r As Range)
    CellText = r.Text
End Function

Enter the value 357 in A1. Click on this cell, then right-click and select "Format Cells..." Select "Currency" and click "OK". The cell will be formatted as "£357.00" (say).

Suppose you want to use "£357.00" in a formula. E.g. you may want to concatenate it:

="Value: " & A1

But you get "Value: 357", not "Value: £357.00". To get the latter, use the following:

=CellText(A1)

Given a certain range on Sheet x, obtaining the "same" range on Sheet y

Say you have

Dim r As Range
r = Sheet1.Range("A1")

r is now "forever" associated with Sheet1, as you can easily check:

Debug.Print r.Parent.Name

This prints "Sheet1" (provided that's the name of Sheet1). r.Parent is a read-only property. You cannot change it.

Suppose you want to "move" r to Sheet2 while preserving the cell addresses. Is this achievable?

The answer is, you cannot achieve this by changing r. You have to construct a new Range object.

Let us define the following function:

Public Function SameRangeOnSheet(r As Range, sh As Worksheet) As Range
    Set SameRangeOnSheet = sh.Range(r.Address)
End Function

Now we can achieve our goal by simply calling

Dim s As Range
Set s = SameRangeOnSheet(r, Sheet2)

This method works for ranges containing multiple areas, such as

$A$1:$A$5,$C$1:$C$5

Hiding gridlines

To hide the gridlines, go to Tools > Options and untick "Gridlines" under "Window Options". Although it may appear that this setting pertains to your Excel application, it actually affects the workbook. So if you distribute it to your users, they won't see the gridlines either.

Unhiding all worksheets quickly

In general, you would use Format > Sheet > Unhide... to unhide an Excel sheet. Unfortunately, you can only unhide the sheets one by one using this method. To unhide all sheets quickly, you can use the following macro:

Sub UnhideSheets()
    Dim s As Worksheet
 
    Application.ScreenUpdating = False
 
    For Each s In ActiveWorkbook.Sheets
        s.Visible = xlSheetVisible
    Next
 
    Application.ScreenUpdating = True
End Sub

Renaming a chart object in Excel

By default, chart object will have non-informative names, such as "Chart 14". To rename an embedded chart object, select any cell, hold Shift, and click anywhere on the chart. Release Shift. The chart is now surrounded by white resizing handles. Type the new name in the Name Box (right above cell A1) and press Enter. The chart has been renamed.

Checking that the value entered in a combo box appears on the combo box' list

Use the following:

If SomeComboBox.ListIndex > -1 Then
    ' OK, the entered/chosen value appears on the list
Else
    ' The user has entered a value that is not on the list
End If

Using a form with a Cancel button to enable the user to interrupt a long calculation

Your spreadsheet needs to perform an operation that takes minutes. Hours perhaps. You would like to enable the user to interrupt this operation. So you create a UserForm with a "Cancel" button. Let's call it CancelButton. You write a Click handler for this function button, like so:

Private Sub CancelButton_Click()
    Cancelled = True
End Sub

Cancelled is a property. We have defined it in our UserForm like so:

Private m_bCancelled As Boolean
 
Public Property Get Cancelled() As Boolean
    Cancelled = m_bCancelled
End Property
 
Public Property Let Cancelled(ByVal bCancelled As Boolean)
    m_bCancelled = bCancelled
 
    If bCancelled Then
        CancelButton.Caption = "Cancelled..."
        CancelButton.Enabled = False
    Else
        CancelButton.Caption = "Cancel"
        CancelButton.Enabled = True
    End If
 
    Repaint
End Property

So far so good. Now we can check whether the user has cancelled the execution or not by querying the Cancelled property.

So let us write our calculation code (of course, it won't be in our UserForm, it will be in another module):

Public Sub PerformCalculation()
    CancelForm.Show vbModeless
 
    Dim lIteration As Long
 
    For lIteration = 1 To 100000
        If CancelForm.Cancelled Then
            MsgBox "Cancelled by user"
            Exit For
        End If
 
        ' ... Perform a very slow calculation here ...
    Next lIteration
End Sub

Here we are assuming that our UserForm is called CancelForm. Try running this code. Does it work?

This depends on the nature of the "very slow calculation". In most cases, the answer is no. Most calculations won't yield the execution to enable the operating system to process the events. The user will see the CancelForm but he will be unable to click on the "Cancel" button. The mouse cursor will be the hourglass. All the clicks over the "Cancel" button will be ignored.

There is a solution: DoEvents(). Let's add this line to PerformCalculation:

Public Sub PerformCalculation()
    CancelForm.Show vbModeless
 
    Dim lIteration As Long
 
    For lIteration = 1 To 100000
        If CancelForm.Cancelled Then
            MsgBox "Cancelled by user"
            Exit For
        End If
 
        ' ... Perform a very slow calculation here ...
 
        DoEvents
    Next lIteration
End Sub

Now it works — the user's clicks are no longer ignored. What happened?

DoEvents passes control to the operating system. Control is returned after the operating system has finished processing the events in its queue and all SendKeys events have been executed. This includes the events that take care of the user's clicks.

Displaying a UserForm defined in another project

According to XL97: Unable to Show UserForms in Other Projects, "there is no way to directly show a UserForm that is contained in another Microsoft Visual Basic for applications project".

There are two possible workarounds. Either import the UserForm from the other project, or reference the other project and call a subroutine (which shows the UserForm) in the other project.

Both workarounds are explained in detail in XL97: Unable to Show UserForms in Other Projects.

Closing the current Excel application from a VBA procedure

You want to write a VBA procedure that will close the current Excel application along with the workbook that defines this procedure. You can achieve this as follows:

Sub CloseExcel()
    ' Prevent Excel from prompting the user to save the open workbook(s):
    Application.DisplayAlerts = False
    Application.Quit
End Sub

Note that you should not do this before calling Application.Quit:

ActiveWorkbook.Close False

Otherwise the execution of your procedure will stop before it reaches Application.Quit.

Disabling the "Do you want to save the changes you made to '<workbook_name>'?" message

In the Workbook_BeforeClose event handler enter the following code:

ThisWorkbook.Saved = True

Listing all available add-ins and finding out which XLA and XLL files are behind them

Use the following code to list all available add-ins, whether enabled ("On") or disabled ("Off") along with the names and paths of the corresponding XLA and XLL files: