19.2 Control Flow Basics

As we begin writing our own functions, it will be helpful to first cover some basics. This section will provide an overview of several concepts that will be useful in helping you to understand how to use VBA. I’m sure you are aware there are many books, some thousands of pages long, and courses dedicated to mastering VBA. Obviously, we won’t be able to cover everything, but we will give you enough to be able to start using it to solve problems that are of interest to you.

Function Declarations

Functions only work if they are written in a module. If you switch over to the VBA editor, you can see various worksheets and files.

Projects can have code associated with a specific sheet, workbook, or module. All functions must be written in a module to be able to work. When you begin writing a function, make sure you are in a module. If needed, you can add a module to a workbook by right-clicking on the workbook and selecting Insert and then Module.

Comments

You should use human-readable comments liberally. If you’ve never done any programming, you’ll start to see that your code makes a lot of sense as you’re doing it, but if you step away from it for just a little bit and then come back, the code may even look foreign. So, make sure that you use those comments liberally.

As discussed previously, you use a single apostrophe (') to make a comment. You can also comment or uncomment a whole block of code at a time.

The Edit toolbar allows you to quickly comment and uncomment a block of code. To use these, highlight the block of code that you want to comment or uncomment on and then click the appropriate button. The one on the left comments, and the one on the right, with the undo arrow, uncomments a previously commented block.

You may need to add the Edit toolbar by right-clicking on the toolbar and selecting the Edit option.

Variables

Variables are essentially labels or names that we can allocate to our program, and we can assign certain values to those variables. We can generally choose almost any name we want, but some names are off-limits. For instance, “next,” “for,” “if,” and other similar words operate as keywords in VBA. We can’t use those as variable names.

Each variable is a specific data type, determined by the value you want to store in the variable. The following table shows a variety of Boolean, numeric, date, text, and object variable types. Each of these data types will consume a specific amount of computer memory. When dealing with large datasets, it might make sense to explicitly declare variables of a specific type. Notice the variant data type. This is a generic or catch-all data type that can store any type of data in it. If we don’t explicitly tell VBA which type our variable is, then it will use the variant data type by default.

Let’s look at a couple of these data types in more detail. Interestingly, a Boolean takes two bytes, even though it’s just a TRUE or FALSE. A byte can represent 256 values from 0 to 255. An integer requires two bytes, but the range is still quite limited: negative 32,000 to positive 32,000. If we want something larger or smaller than that—more negative, I should say—then we might use a long integer that allows us to go from negative two billion to positive two billion. We’d use a currency when dealing with monetary amounts, a date for when we deal with dates and times, and then we have a couple of different strings.

We can be explicit about how long our strings can be. That’s called a fixed-length string. Maybe a variable capturing a first name would only require 50 characters. If we declare it as a fixed-length string, it’ll save us some memory if we use fewer characters. However, I usually just use a variable length string because I’m not too concerned about the memory constraints.

We have an object type. This is just a reference to an ActiveX object. Essentially, it’s an identifier that tells us where to find the data in memory. And then we also have kind of a catch-all type called variant. If we don’t declare a variable as a specific type, it will be variant type and can take on any value you see listed there.

Declaring Variables

If the Option Explicit option is included in your code, you have to explicitly declare your variable. This tells the computer to set aside some memory for those values. To declare a variable, use the Dim keyword. Dim is short for “dimension,” an old computer word for allocating memory.

To declare a variable, we use Dim, and then we provide the variable name—in this case, we’ll create a new variable called count, and then we’ll provide the data type as an integer.

Dim count As Integer

Now, if we want x, y, and z declared as integers, we’d use the following code.

Dim x As Integer, y As Integer, z As Integer

Note that in a lot of other programming languages, you can just list a whole bunch of variables together and give it the data type, and it’ll treat all the variables as that data type. Consider the following code.

Dim x, y, z As Integer

In VBA, x and y here are treated as variants (any data type and requiring 16 bytes), and z is created or treated as an integer.

Constants

Like other programming languages, VBA has constants. A constant is like a variable, but the value is assigned only once and cannot change. The value is set when it is declared. We use the Const keyword, followed by the name of the constant and its value.

Const NumQuarters as Integer = 4
Const Rate = .0725, Period = 12
Const CompanyName as String = "TechMountain Consulting"

We don’t ever reassign number of quarters or rate or period later in the program. These stay fixed while the program’s running.

Strings

The term “string” is a computer programming term that refers to a text value. Strings in VBA can be either fixed or variable in length. Fixed-length strings can save memory and make your procedures faster and more efficient. However, unless you have strict requirements, the added flexibility of a variable-length string might not be a big tradeoff.

If we knew our first names were only going to require 50 characters, then we might specify a fixed-length string that’ll make our program a little bit more efficient and take less memory. As I mentioned, I’ll usually just use variable-length strings.

To declare a fixed-length string, you simply tell it how many characters you want using the following syntax.

Dim MyString As String * 50
Dim MyString As String

Dates

We can also declare variables as dates. Consider the following code.

Dim Today As Date
Dim StartTime As Date
Const FirstDay As Date = #1/15/2023#
Const Noon = #12:00:00#

Here we declare variables called Today as a Date and StartTime as a Date. These are both variables that can be assigned dates. Of course, we can also create constants that contain values. Dates are interesting. Notice how values are assigned to them. We can enclose dates with the # and then use the dates as you might expect. Dates also contain a time portion and can be used for just times as well.

Operators

As we do calculations in VBA, we’ll use a variety of operators. We can use mathematical operators that we’re familiar with, like plus, minus, multiply, divide, and exponent:

+   -   *   /   ^   &   \   <   >   =   Mod

Do you know the difference between the / and \ operators? Both are for division. The / is normal division. The \ is used for integer division. Integer division takes the whole numbers that go into something. Let’s consider an example: 10 divided by 3. If you use regular division, the answer would be 3.3333 repeating. However, with integer division, 10 divided by 3 is 3. Integer division retains the integer portion of the answer only.

Interestingly, the Mod operator is an operator here, not a function like you are familiar with when working on a worksheet. Thus, you could say 10 Mod 3, and it would return the 1. Remember, Mod is the remaining portion.

Arrays

We can use arrays in VBA. In order to use an array, we simply declare a variable and then in parentheses following the name of the variable, we put how many values of that variable are part of our array. Then we declare the data type we’re using.

Dim MyArray (0 To 100) As Integer
Dim MyArray (100) As Integer

Both of these declarations are legitimate, but you would only do one of them because they both do the same thing. So how many values are in our array? If you answered 101, you are correct. A lot of times in computer programming languages, we start at 0 or 1, and Microsoft’s particularly bad at this. Sometimes it mixes the two concepts. Arrays are 0-based, so we use values from 0 to 100, including the 0. So, 1 to 100 is 100 different values. Add 0, and that’s 101 values in the array.

Now, if you wanted to use base 1 instead of base 0, then you can set your option base to be 1 instead.

Option Base 1

If you did this, then the second declaration of MyArray above would have only contained 100 values instead of 101.

We can also create a two-dimensional array using a comma inside the parentheses.

Dim MyArray (2,3) As Integer

This two-dimensional array has three rows and four columns, assuming it’s 0-based. Then in our VBA code, we can set or retrieve specific values in the array. For example, if we wanted to set both the second row’s and the third column’s values, we could use the following code.

MyArray(1,2) = 350

VBA Functions Versus Worksheet Functions

VBA functions are different from the worksheet functions you have used in your worksheets previously. However, there is a lot of overlap between them. When writing VBA code, you will use the VBA functions by default. If we wanted to, we could prefix all our functions with “VBA” and then a period (i.e., VBA.) and the name of the function we want to use.

Try to type the above commands in the Immediate window. Notice as you press the period that IntelliSense provides a list of all VBA functions you can scroll through. Select the ABS function that returns the absolute value of a number passed to it. Try it with and without the VBA library prefix.

VBA only has a limited number of functions. If you want to access any of the functions you use within your worksheets, you can do so by prefixing the function name with Worksheet, followed by a period.

Suppose we wanted to convert 10,000 feet into miles. We could paste the following code into the Immediate window.

DistanceInFeet=10000
? Worksheetfunction.Convert(DistanceInFeet,"ft","mi")

The mile equivalent of 10,000 feet is 1.89394 miles. Although the CONVERT function doesn’t exist in VBA, we can still use it in our VBA code.