VBA How-To: If/Else and Checking For Empty Strings

*Note:* This post was originally posted on my blog. I hope posting it here will help reach those whom it will help.

Recently I have been using VBA for a project in Excel and, though I know what I want to express in VBA, I don’t always know the syntax since it isn’t my main programming language. I had to look up the syntax for the if / else conditional clause and how to check whether a string variable is empty. Given that I’m an experienced software engineer and still had to look them up I thought sharing them with you here would be helpful.

Note: Don’t just copy and paste before reading the article. Copying and pasting without understanding is unwise. You do so at your own danger.

If – Then – Else Conditional Statements In VBA

The if – else conditional statement is a staple of any programming language. In each language they have the same meaning. That is if some condition is true then take some action. Otherwise do something else or skip the action. Each language, however, has a slightly different way of saying this; that is each language has a different syntax.

In the case of VBA this is handled as follows (and as described in this guide on Microsoft’s docs website. The documentation is here):

If [condition] Then
    ' code
ElseIf [condition] Then
    ' code
Else
    ' code
End If

Checking String Variables For Emptiness

There are two common checks needed when dealing with data coming from a source outside the control of code you’re writing. These are:

• Did I get data or is it empty?
• Does the data conform to what I expect and can deal with?

In the case of the code I was writing I’ve got a class with member variables some of which are optional. When I am going to use them to pass the data on I need to know if there is data present to decide what to do with them. The last piece of pertinent information is that I am treating all the variables as strings because I will be sending the data over an API.

Can We Use The IsEmpty() function?

My initial search for how to check whether a string is empty led me to the IsEmpty() function. This seems like a really good fit for what I was looking for. As described in the documentation:

Returns a Boolean value indicating whether a variable has been initialized.

Given that I want to check whether a member variable of my class has been initialized to a value this looks like exactly what I want. As I was about to write this, however, I thought it would be a good idea to test this just to make sure that I am giving you correct information. As such, I wrote this small bit of code:

Sub CheckVarForEmpty()
    Dim stringVar As String
    If IsEmpty(stringVar) = True Then
        MsgBox "Variable Is Empty!"
    End If
End Sub

After writing this I ran this fully expecting to have a message box pop up saying that the variable is empty. However it did NOT pop up.

VBA Debugger To The Rescue!

However, when I debugged the code to make sure that the message box was really being skipped, I noticed that the variable showed that it had the value “”. Now, to me, this looks like an empty string but I decided to write another conditional testing the value of the string against “”:

Sub CheckVarForEmpty()
    Dim stringVar As String
    If stringVar = "" Then
        MsgBox "Varable Is = to Empty String"
    End If
End Sub

Sure enough the message box is shown. Hooray! This means that checking a string against “” successfully determines if a string variable is empty.

Why Didn’t IsEmpty() Work?

However, because I was curious I why IsEmpty() did not return true in my first attempt, I went back to the documentation and read it again. It specifically says that IsEmpty() only returns True if a variable is not initialized or explicitly set to Empty. If you notice in my first example I declare stringVar as a string because I am used to programming in strongly typed languages and it makes sense to me to mark how a variable is to be used. Given that the documentation for IsEmpty() shows examples where the variables were NOT declared with types. This made me wonder if, by declaring a variable with a type, it initializes it to a value (“” in the case of strings). This would make IsEmpty() return False. As such I wrote another test to see if this train of thought is valid:

Sub CheckVarForEmpty()
    Dim myVar
    If IsEmpty(myVar) = True Then
        MsgBox "MyVar is Empty!"
    End If
End Sub

Sure enough the message box in this test popped up as the documentation indicated it would. This, for me, confirms that declaring a variable in VBA as a particular type will initialize it in a manner appropriate for that type.

Conclusion — I always have more to learn

This exercise shows that, despite my years of experience, there are always more things to learn. I hope that this helps you in your adventures in VBA. For me, the next things I will teach myself regarding VBA is more about how classes are constructed and destructed. I hope to bring you more knowledge as I gain it for myself.