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:
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:
Declare Function OpenClipboard& Lib "user32" Alias "OpenClipboard" (ByVal hwnd As Long)
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
Left()it returns a
Variantand 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.
vbNullStringis 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
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
Let’s just get this one out of the way shall we.
someRange.Activateif you want to move to a certain cell in a worksheet.
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
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
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:
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:
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.
(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.
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
ElseIf then you can improve readability by using a
Select Case block instead
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.
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 = "£" Then curr = "Pound" ElseIf currencySymbol = "$" Then curr = "Dollar" ElseIf currencySymbol = "€" Then curr = "Euro" ElseIf currencySymbol = "¥" 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 "£": curr = "Pound" Case "$": curr = "Dollar" Case "€": curr = "Euro" Case "¥": 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:
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…)
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.
Application.DoEventsto manually trigger application events in code when the
EnableEventssetting is set to
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?
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.
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