Hey folks! I'm back from a long hiatus. I've been down with (of all things) the Shingles. I know…weird. Apparently, if you've had the chicken pox as a child (which I did) there is a chance that the dormant virus causing chicken pox can rear its ugly head and develop into Shingles. It's actually fairly rare in men my age to get Shingles.
So in celebration of my recent recovery from my rare Shingles outbreak, I'll share with you a few rarely used VBA tricks.
Trick 1: Using the Same Variables across All Macros in a Module
Most of us are used to declaring variables within a Macro. When we do this, those variables are good for that one Macro. But did you know that you create variables that work across all Macros within a Module? That's right! You can place your Dim declaration statements at the top of your Module. This lets you expand the scope of your variables, letting Excel know that they are good for all Macros within the Module.
Here's an example where a generic variable (Exp1) is used in both the Macros as a container for cell values.
Trick 2: Look Mom….No End If
We've all been taught that if you start an If statement, you need to have a corresponding End If. That's actually only true if your test criteria and your resulting action are on separate lines. If you place your entire If…Then statement on one line, Excel lets you get away with not entering an End If.
Trick 3: Using a Colon to Enter Case Statements on One Line
The Case statement is another conditional statement where we have been taught to enter the resulting action below the criteria evaluation. But did you know you can use the colon character to get each Case statement on one line?
In this example, you can see the resulting action of each Case is on the same line (thanks to the inclusion of the colon).
Trick 4: Using the IIF Function
Microsoft Access users will be familiar with the IIF function. The IIF function is one of the main functions used in Microsoft Access to apply If..Then..Else conditional checks. This is function works in the same way as the standard If..Then..Else statement. You can leverage it in Excel VBA to do things like the example below. In this example, the IIF statement evaluates the value in cell D3 and returns either "Greater Than 50" if or "Not Greater Than 50" depending on the value.
So what is the difference between the IIF function and standard If..Then..Else statements? The difference is that where the standard If..Then..Else statement stops evaluating when condition is True, the IIF function continues to evaluate all arguments even if any of the conditions are true. For instance, in this example, if the value in D3 is greater than 50, the IIF function will continue to evaluate the nested IIF function to see if the value is less than 50.
It's Not the easiest thing in the world to read, but the IIF funciton can help reduce the amount of syntax needed.
Trick 5: Using the Mid Statement as a Replacement Operator
Most of us know that the Replace function in VBA helps you replace specific text with some other text. But did you know that VBA has a Mid Statement designed to replace text at a specific position with some other text? The syntax for VBA's Mid Statement is this:
Mid([Text], [Start Position], [Number of Characters to Replace], [Replacement Text])
In this example, we are replacing the three characters starting at position 5 of the TestString with "fox"
So why would you use this instead of the Replace function? Well remember that the Replace function requires the exact text you are replacing. In some scenarios, you may not be able to specify the exact text. For instance take a look at this example. Here, we are need to replace the 5th character in the string variables with the letter Z. Using the Mid statement allows us to perform a replace based on character position instead of specifying specific characters.
As useful as this Mid statement may sound, there is a strange drawback you should be aware of. The Mid statement will not return more characters than you feed it. For example, note that in the code below, when trying to replace the four characters starting at position 5 ("York") with "Mexico", the Mid Statement truncates my replacement text to only return the exact number of characters that was in the original text.
Well there you have it – a list of rarely used VBA tricks.
I don't use these very often. Some of these are so specific in their utility that they turn out to be irrelevant for most of my work. Others, I simply forget to use.
Feel free to comment about the rare VBA trick you use.
Or you can comment to tell us about your own gross disease (like the Shingles).