Personal tools
User menu

Knowledge Base/VBA/General

From Thalesians

Jump to: navigation, search

Contents

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")

Iterating through dates

Sub IterateThroughDates()
    Dim nextDate As Date, lastDate As Date
    nextDate = #1/1/2001#
    lastDate = Date
    While nextDate <= lastDate
        Debug.Print nextDate
        nextDate = nextDate + 1
    Wend
End Sub

This will output

01/01/2001
02/01/2001
03/01/2002
.
.
.
03/12/2008
04/12/2008 
05/12/2008 
06/12/2008 
07/12/2008 

in the Immediate window.

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 value is #VALUE! in VBA

We can use Excel.WorksheetFunction.IsError in VBA. For convenience, we shall wrap it as follows:

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

This will return True for cells containing #VALUE!, among other things.

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

Determining the used range

Sometimes you need to work out the range of the worksheet that is actually used. By this I mean the smallest rectangular range of the worksheet such that it contains all cells with content. Fortunately, you don't need to work very hard:

Sheet1.Select
Sheet1.UsedRange.Select

It turns out that every Excel worksheet has the UsedRange property.

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:

Sub ListAddIns()
    Dim addInIndex, addInCount As Integer
    Dim status As String
    Dim currentAddIn As addIn
 
    addInCount = Application.AddIns.Count
    For addInIndex = 1 To addInCount
        Set currentAddIn = Application.AddIns(addInIndex)
        If currentAddIn.Installed Then
            status = "On"
        Else
            status = "Off"
        End If
        Debug.Print addInIndex & ", " & status & ", " & currentAddIn.Name & ", " & currentAddIn.Path
    Next
End Sub

Make sure that the Immediate window is visible in the VBA Editor (press Ctrl-G if it's hidden).

The indices of the add-ins displayed by this subroutine should match the order in which the add-ins are displayed in the Tools > Add-ins... box.

Installing an add-in programmatically

Sub LoadXLL()
    Dim oAddIn As AddIn
 
    Set oAddIn = Application.AddIns.Add("C:\MyAddIn.xll", False)
    oAddIn.Installed = True
End Sub

Caveat: Dim a, b, c As ...

Dim a, b, c As Long

means

Dim a As Variant
Dim b As Variant
Dim c As Long

and not

Dim a As Long
Dim b As Long
Dim c As Long

To express the latter more concisely, use

Dim a As Long, b As Long, c As Long

Caveat: Short-circuit evaluation

A quick reminder from Wikipedia: "Short-circuit evaluation or minimal evaluation denotes the semantics of some boolean operators in some programming languages in which the second argument is only executed or evaluated if the first argument does not suffice to determine the value of the expression: when the first argument of and evaluates to false, the overall value must be false; and when the first argument of or evaluates to true, the overall value must be true. In some programming languages (Lisp), the usual boolean operators are short-circuit. In others (C, Ada), both short-circuit and standard boolean operators are available."

VBA is none of these languages. In VBA there is no short-circuit evaluation.

To ascertain this, let us perform a simple test. Let us define the following functions:

Public Function AlwaysFalse() As Boolean
    Debug.Print "Executing AlwaysFalse"
    AlwaysFalse = False
End Function
 
Public Function AlwaysTrue() As Boolean
    Debug.Print "Executing AlwaysTrue"
    AlwaysTrue = True
End Function

We shall call them from a test function as follows:

Public Sub ShortCircuitEvaluationTest()
    If AlwaysFalse() And AlwaysTrue() Then
        Debug.Print "Will never get here"
    End If
End Sub

In the Immediate window (press Ctrl-G if you can't see it), on executing ShortCircuitEvaluationTest, we shall not see "Will never get here" (as expected, because the If-condition is false). However, we shall see both of the following lines, precisely because there is no short-circuit evaluation:

Executing AlwaysFalse
Executing AlwaysTrue

Why is this a caveat? Well, the following innocent-looking code will cause "Run-time error '13': Type mismatch":

Public Sub ShortCircuitEvaluationCaveat1()
    Dim v As String
 
    v = "Hello world"
 
    If IsNumeric(v) And v > 5 Then
        Debug.Print "v > 5"
    End If
End Sub

Even though IsNumeric(v) is False, v > 5 is evaluated (v is not numeric, hence the "type mismatch".

The following code will execute without any errors:

Public Sub ShortCircuitEvaluationCaveat2()
    Dim v As String
 
    v = "Hello world"
 
    If IsNumeric(v) Then
        If v > 5 Then
            Debug.Print "v > 5"
        End If
    End If
End Sub

Bug: Controls on an Excel 2003 worksheet change size after you change the screen resolution

You change the screen resolution (this sometimes happens automatically when you log into your computer remotely using the Remote Desktop Connection). Then you click on a control (a button, combo box, list box, etc.) and observe that the control spontaneously changes its size. Clearly this behaviour is abnormal and indeed it is a bug in Excel:

To resolve it, install the hotfix mentioned in the above article or manually change the registry as explained in "More Information".

Inserting new line characters into concatenated strings in VBA

You will remember that you can use CONCATENATE in VBA to concatenate strings, like so:

=CONCATENATE("foo", "bar", "baz")

which will give you

foobarbaz

Or you could use the ampersand (&) to achieve the same result, which is more succinct:

="foo" & "bar" & "baz"

But what if you would like to get "foo", "bar", and "baz" to appear on different lines? Use the CHAR function and the ASCII code 10 for the new line:

="foo" & CHAR(10) & "bar" & CHAR(10) & "baz"

Chances are the result will be still

foobarbaz

But this is because the cell is not formatted for "wrap text". So select the cell, right-click on it, choose "Format Cells..." and tick "Wrap text" on the "Alignment" tab.

You should now see

foo
bar
baz

Obtaining the file path and name of the Excel spreadsheet in an Excel formula

There are several options. One is to use the somewhat cryptic formula

=CELL("filename")

which will give you something that looks as follows:

C:\some\directory\[some_file.xls]SheetCaption

this can then be processed using Chip Pearson's method. To get the file name (base name alone, no path), use

=MID(CELL("filename"), FIND("[", CELL("filename")) + 1, FIND("]", CELL("filename")) - FIND("[", CELL("filename")) - 1)

to get the full directory name (excluding the file base name), use

=MID(CELL("filename"), 1, FIND("[", CELL("filename")) - 1)

Alternatively, you may wish to define the following VBA functions in a module and use them from your formulae:

Public Function GetWorkbookPathName() As String
    GetWorkbookPathName = ActiveWorkbook.FullName
End Function
 
Public Function GetWorkbookBaseName() As String
    GetWorkbookBaseName = ActiveWorkbook.Name
End Function
 Function GetWorkbookDirectoryPathName() As String
    GetWorkbookDirectoryPathName = ActiveWorkbook.Path
End Function

like this:

=GetWorkbookPathName()

This will give you

C:\some\directory\some_file.xls

in that cell.

Selecting all nonempty cells on a sheet

You can use the following sub:

Sub SelectNonemptyCells(ByVal oSheet As Worksheet)
    Dim oNonemptyRange As Range
    Dim oRange As Range
 
    For Each oRange In ActiveSheet.UsedRange
        Dim appendIt As Boolean
        appendIt = False
 
        If IsNaN(oRange) Then
            appendIt = True
        ElseIf IsError(oRange) Then
            appendIt = True
        ElseIf oRange.Value = "0" Then
            appendIt = True
        ElseIf oRange.Value = "False" Then
            appendIt = True
        ElseIf oRange.Value <> Empty Then
            appendIt = True
        End If
 
        If appendIt Then
            If oNonemptyRange Is Nothing Then
                Set oNonemptyRange = oRange
            Else
                Set oNonemptyRange = Union(oNonemptyRange, oRange)
            End If
        End If
    Next
 
    If Not oNonemptyRange Is Nothing Then
        oNonemptyRange.Select
    Else
        ActiveSheet.Range("A1").Select
    End If
End Sub

Which uses

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

For convenience, you may also define

Sub SelectNonemptyCellsOnActiveSheet()
    SelectNonemptyCells ActiveSheet
End Sub

Summing up numerical (only) / non-error values in Excel

Say your range is A1:B10. And some of the values are #VALUE!. If this is the only kind of error you encounter in A1:B10, you can add them up by using

=SUMIF(A1:B10, "<>#VALUE!")

If you don't have any #VALUE!s but do have #N/As, you can use

=SUMIF(A1:B10, "<>#N/A")

But what if you have a mixture of #VALUE!s, #N/As, and numbers you do want to add up? A more general and powerful approach is the following:

=SUM(SUMIF(A1:B10, {"<0",">0"}))

Overcoming the limitations of array constants

Array constants are a very useful feature. For example, you can put this in a cell:

=SUM({3, 5, 7})

and the result will be 15. Here {3, 5, 7} is an array constant.

You can also mix types:

={"Isaac", "Newton", 1643}

You will only see "Isaac" if you don't select a 1 row by 3 column area and turn this into an array formula by pressing Ctrl-Shift-Enter.

Moreover, you can have columns rather than rows:

={"Isaac"; "Newton"; 1643}

and even two-dimensional arrays:

={"Isaac", "Newton", 1643; "Gottfried", "Leibniz", 1646}

Read this if you wish to learn more about array constants.

Bear in mind, though, they have limitations. Array constants can contain numbers, text, logical values (TRUE and FALSE) and error values such as #N/A. They cannot contain additional arrays, formulae, functions, or references to other cells. This is unfortunate. However, we can remedy this shortcoming. Let's define the following VBA functions:

Function MakeArray1D(ParamArray params() As Variant) As Variant
    ReDim result(0 To UBound(params)) As Variant
 
    nextIndex = 0
 
    For Each p In params
        result(nextIndex) = p
        nextIndex = nextIndex + 1
    Next
 
    MakeArray1D = result
End Function
 
Function MakeArray2DRowWise(columnCount As Integer, ParamArray params() As Variant) As Variant
    ReDim result(0 To -Int(-(UBound(params) / columnCount)) - 1, 0 To columnCount - 1) As Variant
 
    Dim nextColumn As Integer
    Dim nextRow As Integer
    nextColumn = 0
    nextRow = 0
 
    For Each p In params
        result(nextRow, nextColumn) = p
        If nextColumn = columnCount - 1 Then
            nextColumn = 0
            nextRow = nextRow + 1
        Else
            nextColumn = nextColumn + 1
        End If
    Next
 
    MakeArray2DRowWise = result
End Function
 
Function MakeArray2DColumnWise(rowCount As Integer, ParamArray params() As Variant) As Variant
    ReDim result(0 To rowCount - 1, 0 To -Int(-(UBound(params) / rowCount)) - 1) As Variant
 
    Dim nextColumn As Integer
    Dim nextRow As Integer
    nextColumn = 0
    nextRow = 0
 
    For Each p In params
        result(nextRow, nextColumn) = p
        If nextRow = rowCount - 1 Then
            nextRow = 0
            nextColumn = nextColumn + 1
        Else
            nextRow = nextRow + 1
        End If
    Next
 
    MakeArray2DColumnWise = result
End Function

We can now replace

={"Isaac"; "Newton"; 1643}

with

=MakeArray1D("Isaac", "Newton", 1643)

We can replace

={"Isaac", "Newton", 1643; "Gottfried", "Leibniz", 1646}

with either

=MakeArray2DRowWise(3, "Isaac", "Newton", 1643, "Gottfried", "Leibniz", 1646)

or with

=MakeArray2DColumnWise(2, "Isaac", "Gottfried", "Newton", "Leibniz", 1643, 1646)

Moreover, we can now use something like

=MakeArray2DColumnWise(2, I100, I101, I100 + I101, 0)

Keyboard shortcuts for improved productivity

Automatically inserting the names of a function's parameters

When inputting a function into a cell, press [Ctrl] + [Shift] + [A] to fill in its parameter names.

E.g. enter

=SUM(

Press [Ctrl] + [Shift] + [A]. You should immediately see

=SUM(number1,number2,...)

in that cell. You can then replace the names with the corresponding argument values.

Error 1004 when accessing VBProject

When accessing ThisWorkbook.VBProject from VBA you may see the following pop-up:

-------------------------------------------
Microsoft Visual Basic
-------------------------------------------
Run-time error '1004':

Application-defined or object-defined error
-------------------------------------------
OK | Help
-------------------------------------------

This cryptic message generally means that access to VBProject is not trusted.

You can resolve this as follows.

From Excel's menu, select Tools > Macto > Security. Select the "Trusted Publishers" tab. Make sure that "Trust access to Visual Basic Project" is ticked (enabled).

  • This page was last modified on 19 April 2010, at 08:11.
  • This page has been accessed 95,057 times.