VBA Tips & Tricks

I recently saw a post in a LinkedIn group where someone had discovered that certain VBA string manipulation methods actually return a Variant type instead of a String type (We’ll get back to this shortly).

This made me think – I wonder what other small things people aren’t aware of with VBA that could improve their office programming. So here’s just a few tips that might speed up your code or hopefully just give you a better understanding of how to design your code to get better performance or readability:


Type Characters

VBA has type characters which were more commonly used in previous versions as a shorthand way of declaring a variable as a certain type.

'// These examples do the same thing.
Dim s As String
Dim s$

Dim l As Long
Dim l&

These type characters are dervied from Visual Basic and have a very broad range of uses, but as with most things in VBA there is a subset available for us to use which is as follows:

  • % – Integer
  • & – Long
  • @ – Currency
  • ! – Single
  • # – Double
  • $ – String

“Why are you telling me this?”
Because there are times where you may still see or use these characters in today’s VBA code. One example I see more commonly is in code for WinAPI functions that have been written or copied from before VBA7 or on 32-bit systems:

Declare Function OpenClipboard& Lib "user32" Alias "OpenClipboard" (ByVal hwnd As Long)

(Where OpenClipboard would return a Long because it uses the & type character directly after the function name)

But also, and probably a more useful reason is because of something called explicit type conversion.

I won’t get into the gory details but at this point all you need to know is there is something called implicit conversion whereby a type is converted from a to b based on the data types on either side of the operation, and something called explicit conversion whereby you tell the executing code what you want to convert something to by using something like CStr() or CDate().

Tip: in programming you will often hear the word ‘cast’ used instead of ‘convert’ – for what we are demonstrating below, this is the same thing.
Back to the point at hand – when you use something like Left() it returns a Variant and allows the executing code to then use implicit type conversion e.g.

Dim foo As String
Dim bar As Integer

foo = "123" '// This is a string, obviously.
bar = Left(foo, 2) '// result is implicitly converted to an Integer before being assigned to 'bar'

So this is why these methods return a variant, but if we’re working with strings on both sides of the operation we can use a type identifier to cast the result to a string straight away. This is explicit type conversion.

Dim foo As String
Dim bar As String

foo = "Hello, world!"
bar = Left$(foo, 5)

The vbNullString constant

There are many vb_ constants in the language but I’m just going to touch on this one because I tend to see "" being used a lot where this should be instead.

“What’s the difference?”
Glad you asked. vbNullString is a pointer to nothing, null, empty, nada. It is a black hole of zilch in the section of memory that normally stores your empty string of "".


Tip: you can test this yourself:

Debug.Print StrPtr(vbNullString) '// returns 0
Debug.Print StrPtr("") '// returns a pointer to the string

When you use "" you are actually still creating a string in memory, it’s just that the string has a length of zero. This means when you do a comparison to "" you require an operation that retrieves this string from a place in memory. Using vbNullString instead eliminates this process because there is nothing to retrieve.

To summarise, if you want to check if a string is empty it’s quicker (and better practice) to use:

If testString = vbNullString Then
    Debug.Print "nothing in your testString!"
End If 

Selecting Objects

Let’s just get this one out of the way shall we.

DON’T DO THIS. LIKE, EVER.
There is very, very, very rarely any need to select an object in VBA, and it’s been written about thousands of times before so I won’t waste any efforts in reproducing all that material here. If you want to have a read of something I would suggest this answer on Stack Overflow written by Siddharth Rout

Tip: Use Application.GoTo instead of someRange.Activate if you want to move to a certain cell in a worksheet.


Boolean assignments

If you’re assigning a boolean value to a variable based on a condition then you do this in a single line of code rather than using an If statement.

e.g. instead of using:

Dim i As Integer
Dim i2 As Integer
Dim test As Boolean

i = 5
i2 = 10

If i < 4 And i2 >=6 Then
    test = True
Else
    test = False
End If

consider:

Dim i As Integer
Dim i2 As Integer
Dim test As Boolean

i = 5
i2 = 10

test = (i < 4 And i2 >=6)

This actually brings me on to one of my favourite functions that I feel is underappreciated in VBA for lack of understanding:


Immediate If

caveat: this method is marginally slower than If/Else but can save typing multiple lines of code if used effectively.

The ‘immediate if’ takes the form of:

IIf(condition, true_part, false_part)

similar to the worksheet =If() formula, however there is a pitfall to using this function VBA that needs to be understood first:

Every part of this function will be evaluated regardless of the condition returning true or false
So what does this mean? It means – imagine that each part of the function was a separate line of code, would all three lines run? e.g.

i = 0
'// (condition)
Debug.Print i = 0 '// Prints "True"
'// (true part)
x = i + 5 '// x now = 5
'// (false part)
x = 5 / i '// ERROR: cannot divide by zero

in the same way this would throw an error:

i = 0
x = IIf(i = 0, i + 5, 5 / i)

the error is thrown because the false_part of the formula is still evaluated regardless.

“So when would I use this?”
There are unlimited uses for this, but here’s one of my favourites just to get you started:

(For assigning the first blank cell in a column, testing for a header)

Set myCell = Cells(Rows.Count, 1).End(xlUp).Offset(IIf(Cells(1, 1).Value = vbNullString, 0, 1), 0)

This checks if the first cell is blank, and uses an offset of 0 if it is to make sure you get this cell. If the first cell isn’t blank then we can assume that there is a header row and therefore an offset of 1 is used instead.

Select Case

Again, this method is slightly (like, milliseconds) slower but can save typing lines of code.

if you’re testing a single variable against multiple conditions using If/ElseIf then you can improve readability by using a Select Case block instead

Every If or ElseIf line in a block is evaluated, which means if you’re testing against 10 possible values and your condition is met on the second test, then you’ve got 8 lines of redundant code being evaluated. This can sometimes result in unexpected behaviour if your conditions aren’t designed properly.

A Select Case block resolves this issue because once a condition is found to be true, the other conditions are not evaluated and code execution is immediately passed to the next line of executable code after the end of the block (unless code is redirected inside the block of course…)

For example, instead of code like this:

currencySymbol = "$"

If currencySymbol = "&pound;" Then
    curr = "Pound"
ElseIf currencySymbol = "$" Then
    curr = "Dollar"
ElseIf currencySymbol = "&euro;" Then
    curr = "Euro"
ElseIf currencySymbol = "&yen;" Then
    curr = "Yen"
Else
    curr = "Unknown"
End If

where the last couple of tests are redundant, you could write this instead:

currencySymbol = "$"

Select Case currencySymbol
    Case "&pound;": curr = "Pound"
    Case "$": curr = "Dollar"
    Case "&euro;": curr = "Euro"
    Case "&yen;": curr = "Yen"
    Case Else: curr = "Unknown"
End Select

In this code, the second comparison will evaluate to true and so the rest of the block is not evaluated.

Application Settings (MS Excel Specific)

You’ve probably seen something like this before at the top of a sub, or been advised to use it to speed your code up:

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual

but what do these settings do? Why do they speed up your code and what are the potential issues with changing them?

Firstly I’m going to address the settings in general and make something very clear. You absolutely must have effective error handling in place to manage these settings and re-enable them in the event of an unexpected error in your code.

Application settings are set at Application level – this means that they supercede the environment in which your code is running and that any changes made to them will still be in place when your code has finished. Put simply, if you turn something off and your code unexpectedly bombs out without turning it back on again, it will stay off until you turn it back on again. Below I’ll go through each one of these settings and explain what it does, as well as common pitfalls:

Screen Updating

What it does: Everytime your code performs an action, a message is sent to the application to see if it needs to update the screen as a result of that action. Turning this off prevents the message from being sent and therefore speeds up execution.

Pitfalls: The user interface can appear stuck and unresponsive after your code has finished. (If you ever won at solitaire on a windows PC, that’s what your screen will look like if you move a form around your screen with this setting turned off…)

Enable Events

What it does: Events are named as such because they are triggered and therefore run as the result of another action (or ‘event’). An example of this is when you enter a formula in a cell, be it manually or in code, when you leave that cell the Worksheet_Calculate() event is triggered – this causes the application to calculate the result of that formula and display it in the cell. Nearly every interaction you have with an object will trigger an event so it’s very important to undertand how they work and what this setting is doing.

Pitfalls: When switched off, no events linked to the application will be automatically triggered. This means that if you enter a formula in a cell – you won’t see the result until you manually force a calculation. Similarly, this can cause undesired results if you are depending on the application (in this context, ‘application’ includes any child objects such as workbooks and worksheets) to calculate something within your code and return a value.

Tip: Use Application.DoEvents to manually trigger application events in code when the EnableEvents setting is set to False

Display Alerts

What it does: Alerts are displayed when you take certain actions within the application. Two of the most common alerts are when you attempt to delete a worksheet that has been used, and when try to close a workbook that hasn’t been saved. These alerts appear as messages that will stop your code from running until they are dealt with.

Pitfalls: As much as they can seem an annoyance when writing your code – they do actually server a very useful purpose to the user. They are a safety net. Can you imagine the frustration of accidentally deleting a sheet, or closing a workbook without saving and nothing even prompted you to make sure you actually meant to do that?

Calculation Mode

What it does: As mentioned before, one of the events regularly triggered in the application is the calculate event. This event occurs after most interactions with workbook and worksheet objects but can be cumbersome if you have lots of complex formulas running in a worksheet for example, and want to make changes to the structure of the workbook. The calculation setting can either be Automatic (default – controlled by application), semi-automatic (controlled by application, but ignores changes in tables) or manual (controlled by user).

Pitfalls: Again, this has already been mentioned in the Events section, but the automatic calculation is something that most users just take for granted within the application, for end-users that aren’t aware of this setting it will inevitably cause issues and frustration when it isn’t working as they expected.

Objects

Last but not least in this list, you can set objects to Nothing when they are no longer required to free up a bit of memory. By default in VBA, a variable will be destroyed once it falls out of scope so there is not as much of a requirement to manage memory this closely in comparison to other languages. That being said it can be a good habit to get into early, especially if you want to go on and learn other languages. Similar to the vbNullString explanation, setting an object to Nothing replaces it’s memory content with, well, nothing which reduced the overhead of the executing code slightly.

That’s all for now, but if I’ve made a mistake somewhere or you have an even better top tip- feel free to let me know via the contact form on the home page.

– Macro Man

Leave a Reply

Your email address will not be published. Required fields are marked *