In this tutorial, we will cover the basics of variables and decision structures. Then we will create a more advanced version of the simple message box created in the first tutorial. If you are unsure how to access the developer tab within Excel, please see the first tutorial here: Excel Visual Basic #1: Getting Started.

Terms to remember and their definitions:

Variables: If you recall from your algebra days in school some math problems like X + 2 = 5 or X – 6 = 1. X would be a variable. In these examples we can solve for X fairly easily, algebra and VBA (and pretty much all programming languages) can both become a little more complicated than these simple examples. 

A variable is a representation of a value. The variable holds the value allowing you to use it later. This works well in applications, for example, when you expect some user input or information entered in and you don’t know in advance what that information will be. You can use a variable to hold the information and work with the entered value despite not knowing what that value is in advance. 

Within VBA, in general, a variable is declared and assigned a type of information. Common variable types include “String”,”Boolean”,  and “Integer”. A String type variable would include words or sentences, and an Integer would be numerical values. A String can hold numerical values, an Integer cannot hold String values or non-numerical values. A Boolean type is a true or false value, a binary type holding either true or false.

To declare a variable of type String:

Dim MyVariable1 As String

To declare a variable of type Integer:

Dim MyVariable2 As Integer

To declare a variable of type Range (Range is a reference to the cells in Excel):

Dim MyVariable3 As Range

To declare a variable of type Boolean:

Dim MyVariable4 As Boolean

Decision Structures or Control Structures: Decision or Control Structures give the application the ability to decide something and change (or control) the flow of the program if something is found to be true or false. There are several types of decision structures, some are better suited for given situations, in some situations it doesn’t matter which you would choose. Some of the decision structures are shown below.

If…Then Decision Structure:

If MyVariable4 = True Then
     'Execute some code
End If

If…Then…Else Decision Structure:

If MyVariable4 = True Then
     'Execute some code
Else
     'Execute some different code
End If

If…Then…ElseIf Decision Structure:

If MyVariable2 = 0 Then
     'Execute some code
ElseIf MyVariable2 = 1 Then
     'Execute some different code
ElseIf MyVariable2 = 2 Then
     'Execute a third code option
Else
     'Execute a fourth code option
End If

Creating a Variable Message (Video):

Creating a Variable Message (Written Steps):

First, make sure Excel is open and that you have access to the “Developer” tab.  If you are unsure how to access the developer tab within Excel, please see the first tutorial here: Excel Visual Basic #1: Getting Started.

1. Navigate to the developer tab and click “Insert”

2. On the “Insert” dropdown menu find the section labeled “ActiveX Controls” and click “Command Button”

3. Click an area within your spreadsheet to place the “Command Button”

4. Feel free to move it around, format the button using “Properties”. Make the Excel sheet look a little nicer.

5. When finished formatting the button to your liking, make sure the sheet is still in “Design Mode” and double-click the button. This will open up a development panel and auto-create some code for you.

6. Declare your first variable, a String type variable

In between the Private Sub and End Sub section write the line:

Dim MessageCell As String

It should look something like the image below:

7. We will set the variable equal to the value of a cell. I have chosen cell “A5” for this written tutorial. The video tutorial uses cell “A6”. Add the bolded code to your program in the location shown. While I provide you the ability to copy and paste, I recommend that you practice manually typing it in.

Private Sub VariableButton1_Click()
      Dim MessageCell As String

      MessageCell = ThisWorkbook.Sheets("Sheet1").Range("A5").Value

      MsgBox MessageCell
End Sub

You can put any cell reference you want in the range – “Range(“??”)” as long as it resembles the below image:

The Line “MessageCell = ThisWorkbook.Sheets(“Sheet1”).Range(“A5″).Value” takes the value located in cell “A5” and puts it into the variable “MessageCell.” MessageCell is like our X value in algebra. We don’t know what it could be, it could be just about anything the user decides to put in.  

“MsgBox MessageCell” takes the value in the variable and displays it in a Message Box.

8. Test it.

Go to your Excel sheet and enter some text into your chosen cell, in my case I will enter text into cell “A5,” and click the button.

Make sure “Design Mode” is de-activated and click the button. You should get a pop-up box with the message you entered in the cell.

*To turn “Design Mode” off click on the “Design Mode” icon in the “Developer” tab.

9. Add a decision structure to make it a little more advanced

Change your code by inserting an If…Then…Else structure. 

Private Sub VariableButton1_Click()
      Dim MessageCell As String

      If ThisWorkbook.Sheets("Sheet1").Range("A5").Value = "" Then
            MessageCell = "Your Message is Blank"
      Else
            MessageCell = ThisWorkbook.Sheets("Sheet1").Range("A5").Value
      End If

      MsgBox MessageCell
End Sub

The above code adds in a check for the cell to determine if the cell contains an empty value. The line “If ThisWorkbook.Sheets(“Sheet1”).Range(“A5”).Value = “” Then” compares the value in cell “A5” to an empty value “” and either returns “True” or “False“. If the cell value equals “” then your variable will be assigned a value of “Your Message is Blank“, if the cell contains anything else the variable MessageCell will be set to the value of the cell.

As before, it will then take that variable and display it as a message box using the line “MsgBox MessageCell“. It doesn’t matter which code was executed, it just displays the contents of your MessageCell variable. 

Dim MessageCell As String <—-Variable Declaration

If ThisWorkbook.Sheets(“Sheet1”).Range(“A5”).Value = “” Then <—-Check for Blanks
MessageCell = “Your Message is Blank” <——If Blanks Exist, Put this in the variable MessageCell
Else <——If Not Blank, we Execute the "Else" Code
MessageCell = ThisWorkbook.Sheets(“Sheet1”).Range(“A5”).Value <——Assign A5 to MessageCell
End If <—-End the If…Then…Else Decision Structure

MsgBox MessageCell<——Display the contents of MessageCell through a pop-up box

10. Test it again

This time, leave the cell blank and see if you get the message “Your Message is Blank”

Then click your button to see what the pop-up displays:

Congratulations! You just completed your first use of variables and decision structures. Now I suggest you play around with things a bit. The video contains 1 additional step I added on the fly, check it out and try it… and then see if you can use that step I show to have your button read from two cells.

The important thing is to try to have fun. Don’t be scared, you won’t destroy your computer, that won’t happen until we get to infinite loops.