5 Rarely Used VBA Tricks

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

16 thoughts on “5 Rarely Used VBA Tricks

  1. Jan Karel Pieterse

    I’m no fan of the colon as a means to write statements on a single line, I’d advise to use that sparingly.

    Having said that, did you know you can put that to use in the immedate window and write a one-liner like this to get a list of your worksheet names, ready to copy/paste:

    For Each oSh in Worksheets:Debug.Print oSh.Name:Next

    Also, I ‘d advise to use module level variables sparingly and try to get into the habit of using arguments when a variable needs to be passed from one routine to another.

  2. datapig Post author

    Jan Karel:

    ooooh….I like that immediate window trick! Nice one! I think I’ll steal that trick for a blog post in the future.

  3. Pedro Paulo

    É bom ver seu blog novamente.
    ————————————————
    It’s good to see your blog again.

  4. Marlin Snyder

    Mike,

    The variable declaration in the Module is one I’ve been using for several years. There are many times where you want to pass that variable value from one Function or Sub to another, and this sure cuts down on the typing (not having to declare the variable in each sub) and the headaches from pounding your head on the desk trying to get the variable to populate correctly…

    Good tips!

    Marlln

  5. Danny

    the first ‘tip’ is incredibly bad practice – it makes predicting the state of your program incredibly difficult. A variable should be as tightly scoped as possible, which in VBA is procedure level.

    A variable that is accessible to all routines is liable to be (ab)used in an unintended way, thereby breaking the program later on.

    https://programmers.stackexchange.com/questions/148108/why-is-global-state-so-evil

    Also, be careful using IIf – because it evaluates all conditions, any condition that returns an error (i.e. division by zero) will still raise an error even if it is not encountered by evaluating the condition.

  6. PeterB

    Marlin,

    Like Jan Karel said, use module level variables sparingly. It is far easier to debug your code if you pass variables as arguments to your functions.

    If you find that you are passing lots of arguments then look in to using a custom class module (but that is a whole different topic).

  7. AlexJ

    I use IIF for specific situations, and like it just fine.

    However, I believe that it is not favoured by the VBA gods because it will error if ANY of the terms is an error, unlike the IF statement.

  8. JanM

    I do like the tip about the mid function. It is so much simpler than the worksheetfunction.replace that I use quite often

  9. Rich

    I’ve been using the colon in the immediate window for a while now but wouldn’t think or want to use it within a module. I would think it makes it that bit more harder to debug.

    The no End If, I thought was a pretty basic one, I remember learning this before doing the full statement.

    And module level variables, for all their criticism are okay to use when your module has procedures in it with only a few lines of code each, surely?

  10. Patrick M

    Shingles can be hella painful. Sorry you had to go through that, Mike, and I hope you’re full on the mend now!

    That Mid trick is pretty darned clever.

    As to module level variables, I try to avoid them. When I need to pass variables between subs/functions I typically do it ByRef.

  11. David B

    Most of these tips I wouldn’t use, nor suggest. Are they interesting? Sure, but it drops the readability of your code significantly.

    I’d take a few extra lines of code any day of the week, if it helps me debug later on down the road.

    Regarding your module variable trick, if you start doing that you should really just use classes.

  12. Vince

    Actually the mid() (and mid$() as well) function is a really useful one, especially when dealing with strings containing structured data.

    It is particularly useful in retrieving specific parts of a string, much more than for replacing text in a string.

    For instance, it can be used to retrieve the 2 check digits of an IBAN number, because they will always be 3rd and 4th position in the string:

    Dim ChkNum as integer

    ChkNum = CInt(Mid$(str_IBAN, 3, 2))

    I often use it together with left() and right() (or left$() and right$() actually) as well, that do just the same but from start or end.

  13. VBA Demi-God

    Let’s go through the list:

    1: 99% of the time Global (in this case Module-level) variables are used to the benefit of the programmer and not the system. As long as you understand to whom you are trying to benefit when declairing a varible, I can accept globals.

    2: Someone mentioned that they thought IF THEN was pretty common and I thought so as well. I don’t use one line if statements that often, but they are a good trick to have.

    3: Other than case statements (and that handy trick in the immediate window) I frown on colons in VB code. I happen to use them in case statements whether I’m using one line or not but that’s just because that’s how I write in real life (notice my comment).

    4: IIF statements are the devil in VBA. Good in Access queries, but why would you risk an error handling situation that potentially doesn’t affect the main flow of your program?

    5: This is the real gem in the list. The Mid function not only serves as an assignment opperator, but it is faster BY FAR than the replace or & (append) functions. Here’s a thread with a little more detail:
    http://forums.codeguru.com/showthread.php?496589-RESOLVED-Mid%28%29-vs-Mid-%28%29

    The only main limitation here is that if you are using the mid function like a quick append, you need to ensure that the insertion point to the length of your string has enough characters to fit the text you want to insert.

    For example, the code below produces “hellow” and not “hello world”

    x = “hello ”
    y = “world”
    mid(x,len(x),len(y)) = y

  14. Mark M

    In response to some of the negative comments about module level variables – Whilst I generally agree about the comments regarding scope, there are a few exceptions (as an aside, in reply to a previous comment, variables declared a module level are NOT global, they are restricted to that module) One noteable exception that I am a fan of is use of Enums – I use enums to define the layout of the data tabs my clients are using, then all the procedures that reference the data use the enum variables instead of hard coded ranges – This makes the code more robust as if the structure of the data sheets change, you only need to change the enums and not the rest of the code. Its worth saying that “context” is important here as well, If you only have as simple code module, probably not worth doing, but my spreadsheets are “applications” 18,000 code lines and used by multiple clients across multiple locations. So enums are a great way of ensuring that when things change, you dont have to find everywhere you have referenced a particalar cell address

  15. Chris

    I use a public variables for the sheets and table names and I call a public module to set them. In each module of the program I call that public module. I could probably place all my column ranges in there and use proxy variables to point to them, so the only place I need to occasionally update is in public variable module.

Leave a Reply

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