Defining data types

VBA makes life easy for programmers because it can automatically handle all the details involved in dealing with data. Some programming languages, however, are strictly typed, which means that the programmer must explicitly define the data type for every variable used.

Data type refers to how data is stored in memory — as integers, real numbers, strings, and so on. Although VBA can take care of data typing automatically, it does so at a cost: slower execution and less efficient use of memory. As a result, letting VBA handle data typing may present problems when you’re running large or complex applications. Another advantage of explicitly declaring your variables as a particular data type is that VBA can perform some additional error checking at the compile stage. These errors might otherwise be difficult to locate.

Table 3.1 lists VBA’s assortment of built-in data types. (Note that you can also define custom data types, which are covered later in this chapter in the section “User-Defined Data Types.”)

Generally, it’s best to use the data type that uses the smallest number of bytes yet still can handle all the data that will be assigned to it. When VBA works with data, execution speed is partially a function of the number of bytes that VBA has at its disposal. In other words, the fewer bytes used by data, the faster VBA can access and manipulate the data.

For worksheet calculation, Excel uses the Double data type, so that’s a good choice for processing numbers in VBA when you don’t want to lose any precision. For integer calculations, you can use the Integer type (which is limited to values less than or equal to 32,767). Otherwise, use the Long data type. In fact, using the Long data type even for values less than 32,767 is recommended because this data type may be a bit faster than using the Integer type. When dealing with Excel worksheet row numbers, you want to use the Long data type because the number of rows in a worksheet exceeds the maximum value for the Integer data type.

Declaring variables

If you don’t declare the data type for a variable that you use in a VBA routine, VBA uses the default data type, Variant. Data stored as a Variant acts like a chameleon: It changes type, depending on what you do with it.

The following procedure demonstrates how a variable can assume different data types:

Sub VariantDemo()
    MyVar = True
    MyVar = MyVar * 100
    MyVar = MyVar / 4
    MyVar ="Answer:" & MyVar
    MsgBox MyVar
End Sub

In the VariantDemo procedure, MyVar starts out as a Boolean. The multiplication operation converts it to an Integer. The division operation converts it to a Double. And finally, it’s concatenated with text to make it a String. The MsgBox statement displays the final string: Answer: -25.

To further demonstrate the potential problems in dealing with Variant data types, try executing this procedure:

Sub VariantDemo2()
    MyVar ="123"
    MyVar = MyVar + MyVar
    MyVar ="Answer:" & MyVar
    MsgBox MyVar
End Sub

The message box displays Answer: 123123. This is probably not what you wanted. When dealing with variants that contain text strings, the + operator performs string concatenation.

Sub VariantDemo3()
    MyVar = True
    MsgBox TypeName(MyVar)
    MyVar = MyVar * 100
    MsgBox TypeName(MyVar)
    MyVar = MyVar / 4
    MsgBox TypeName(MyVar)
    MyVar ="Answer:" & MyVar
    MsgBox TypeName(MyVar)
    MsgBox MyVar
 End Sub

Forcing yourself to declare all variables

To force yourself to declare all the variables that you use, include the following as the first instruction in your VBA module:

Option Explicit

When this statement is present, VBA won’t even execute a procedure if it contains an undeclared variable name. VBA issues the error message shown in Figure 3.1, and you must declare the variable before you can proceed.

Scoping variables

A variable’s scope determines in which modules and procedures you can use the variable. Table 3.2 lists the three ways in which a variable can be scoped.

We discuss each scope further in the following sections.

Sub MySub()
    Dim x As Integer
    Dim First As Long
    Dim InterestRate As Single
    Dim TodaysDate As Date
    Dim UserName As String
    Dim MyValue
'   - [The procedure's code goes here] -
End Sub

Dim x As Integer, y As Integer, z As Integer
Dim First As Long, Last As Double

Dim CurrentValue as Long
 
Sub Procedure1()
'   - [Code goes here] -
End Sub
 
Sub Procedure2()
'   - [Code goes here] -
End Sub

Public CurrentRate as Long

Sub MySub()
    Static Counter as Long
    '- [Code goes here] -
End Sub

Working with constants

A variable’s value may change while a procedure is executing (that’s why it’s called a variable). Sometimes, you need to refer to a named value or string that never changes: a constant.

Using constants throughout your code in place of hard-coded values or strings is an excellent programming practice. For example, if your procedure needs to refer to a specific value (such as an interest rate) several times, it’s better to declare the value as a constant and use the constant’s name rather than its value in your expressions. This technique not only makes your code more readable, it also makes it easier to change should the need arise — you have to change only one instruction rather than several.

Const NumQuarters as Integer = 4
Const Rate = .0725, Period = 12
Const ModName as String ="Budget Macros"
Public Const AppName as String ="Budget Application"

Public Const InterestRate As Double = 0.0725

Using predefined constants

Excel and VBA make available many predefined constants, which you can use without declaring. In fact, you don’t even need to know the value of these constants to use them. The macro recorder generally uses constants rather than actual values. The following procedure uses a built-in constant (xlLandscape) to set the page orientation to landscape for the active sheet:

Sub SetToLandscape()
    ActiveSheet.PageSetup.Orientation = xlLandscape
End Sub

It’s often useful to record a macro just to discover the various constants that can be used. And, if you have the AutoList Members option turned on, you can often get some assistance while you enter your code (see Figure 3.2). In many cases, VBA lists all the constants that you can assign to a property.

The actual value for xlLandscape is 2 (which you can discover by using the Immediate window). The other built-in constant for changing paper orientation is xlPortrait, which has a value of 1. Obviously, if you use the built-in constants, you don’t really need to know their values.

Working with strings

Like Excel, VBA can manipulate both numbers and text (strings). There are two types of strings in VBA:

• Fixed-length strings are declared with a specified number of characters. The maximum length is 65,535 characters.
• Variable-length strings theoretically can hold up to 2 billion characters.

Each character in a string requires 1 byte of storage, plus a small amount of storage for the header of each string. When you declare a variable with a Dim statement as data type String, you can specify the length if you know it (that is, a fixed-length string), or you can let VBA handle it dynamically (a variable-length string).

In the following example, the MyString variable is declared to be a string with a maximum length of 50 characters. YourString is also declared as a string; but it’s a variable-length string, so its length is not fixed.

Dim MyString As String * 50
Dim YourString As String

Working with dates

You can use a string variable to store a date, but if you do, it’s not a real date (meaning you can’t perform date calculations with it). Using the Date data type is a better way to work with dates.

A variable defined as a date uses 8 bytes of storage and can hold dates ranging from January 1, 0100, to December 31, 9999. That’s a span of nearly 10,000 years — more than enough for even the most aggressive financial forecast! The Date data type is also useful for storing time-related data. In VBA, you specify dates and times by enclosing them between two hash marks (#).

Here are some examples of declaring variables and constants as Date data types:

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

If you use a message box to display a date, it’s displayed according to your system’s short date format. Similarly, a time is displayed according to your system’s time format (either 12- or 24-hour). You can modify these system settings by using the Regional Settings option in the Windows Control Panel.

Declaring arrays

You declare an array with a Dim or Public statement, just as you declare a regular variable. You can also specify the number of elements in the array. You do so by specifying the first index number, the keyword To, and the last index number — all inside parentheses. For example, here’s how to declare an array comprising exactly 100 integers:

Dim MyArray(1 To 100) As Integer

By default, VBA assumes zero-based arrays. If you would like VBA to assume that 1 is the lower index for all arrays that declare only the upper index, include the following statement before any procedures in your module:

Option Base 1

Declaring multidimensional arrays

The array examples in the preceding section are one-dimensional arrays. VBA arrays can have up to 60 dimensions, although you’ll rarely need more than three dimensions (a 3-D array). The following statement declares a 100-integer array with two dimensions (2-D):

Dim MyArray(1 To 10, 1 To 10) As Integer

You can think of the preceding array as occupying a 10-x-10 matrix. To refer to a specific element in a 2-D array, you need to specify two index numbers. For example, here’s how you can assign a value to an element in the preceding array:

MyArray(3, 4) = 125

Following is a declaration for a 3-D array that contains 1,000 elements (visualize this array as a cube):

Dim MyArray(1 To 10, 1 To 10, 1 To 10) As Integer

Reference an item in the array by supplying three index numbers:

MyArray(4, 8, 2) = 0

Declaring dynamic arrays

A dynamic array doesn’t have a preset number of elements. You declare a dynamic array with a blank set of parentheses:

Dim MyArray() As Integer

Before you can use a dynamic array in your code, however, you must use the ReDim statement to tell VBA how many elements are in the array. You can use a variable to assign the number of elements in an array. Often the value of the variable isn’t known until the procedure is executing. For example, if the variable x contains a number, you can define the array’s size by using this statement:

ReDim MyArray (1 To x)

You can use the ReDim statement any number of times, changing the array’s size as often as you need to. When you change an array’s dimensions the existing values are destroyed. If you want to preserve the existing values, use ReDim Preserve. For example:

ReDim Preserve MyArray (1 To y)

Arrays crop up later in this chapter when we discuss looping (see the section “Looping blocks of instructions”).

With-End With constructs

The With-End With construct enables you to perform multiple operations on a single object. To start understanding how the With-End With construct works, examine the following procedure, which modifies six properties of a selection’s formatting (the selection is assumed to be a Range object):

Sub ChangeFont1()
    Selection.Font.Name ="Cambria"
    Selection.Font.Bold = True
    Selection.Font.Italic = True
    Selection.Font.Size = 12
    Selection.Font.Underline = xlUnderlineStyleSingle
    Selection.Font.ThemeColor = xlThemeColorAccent1
End Sub

You can rewrite this procedure using the With-End With construct. The following procedure performs exactly like the preceding one:

Sub ChangeFont2()
    With Selection.Font
        .Name ="Cambria"
        .Bold = True
        .Italic = True
        .Size = 12
        .Underline = xlUnderlineStyleSingle
        .ThemeColor = xlThemeColorAccent1
    End With
End Sub

Some people think that the second incarnation of the procedure is more difficult to read. Remember, though, that the objective is increased speed. Although the first version may be more straightforward and easier to understand, a procedure that uses the With-End With construct to change several properties of an object can be faster than the equivalent procedure that explicitly references the object in each statement.

For Each-Next constructs

Recall from the preceding chapter that a collection is a group of related objects. For example, the Workbooks collection is a collection of all open Workbook objects. You can also work with many other collections.

Suppose that you want to perform some action on all objects in a collection. Or suppose that you want to evaluate all objects in a collection and take action under certain conditions. These occasions are perfect for the For Each-Next construct because you don’t have to know how many elements are in a collection to use the For Each-Next construct.

The syntax of the For Each-Next construct is:

For Each element In collection
    [instructions]
    [Exit For]
    [instructions]
Next [element]

The following procedure uses the For Each-Next construct with the Worksheets collection in the active workbook. When you execute the procedure, the MsgBox function displays each worksheet’s Name property. (If five worksheets are in the active workbook, the MsgBox function is called five times.)

Sub CountSheets()
    Dim Item as Worksheet
    For Each Item In ActiveWorkbook.Worksheets
        MsgBox Item.Name
    Next Item
End Sub

The next example uses For Each-Next to cycle through all objects in the Windows collection and count the number of windows that are hidden:

Sub HiddenWindows()
    Dim Cnt As Integer
    Dim Win As Window
    Cnt = 0
    For Each Win In Windows
        If Not Win.Visible Then Cnt = Cnt + 1
    Next Win
    MsgBox Cnt &" hidden windows."
End Sub

For each window, if the window is hidden, the Cnt variable is incremented. When the loop ends, the message box displays the value of Cnt.

Here’s an example that closes all workbooks except the active workbook. This procedure uses the If-Then construct to evaluate each workbook in the Workbooks collection:

Sub CloseInactive()
    Dim Book as Workbook
    For Each Book In Workbooks
      If Book.Name <> ActiveWorkbook.Name Then Book.Close
    Next Book
End Sub

A common use for the For Each-Next construct is to loop through all cells in a range. The next example of For Each-Next is designed to be executed after the user selects a range of cells. Here, the Selection object acts as a collection that consists of Range objects because each cell in the selection is a Range object. The procedure evaluates each cell and uses the VBA UCase function to convert its contents to uppercase. (Numeric cells are not affected.)

Sub MakeUpperCase()
    Dim Cell as Range
    For Each Cell In Selection
        Cell.Value = UCase(Cell.Value)
    Next Cell
End Sub

VBA provides a way to exit a For-Next loop before all the elements in the collection are evaluated. Do this with an Exit For statement. The example that follows selects the first negative value in Row 1 of the active sheet:

Sub SelectNegative()
    Dim Cell As Range
    For Each Cell In Range("1:1")
        If Cell.Value < 0 Then
            Cell.Select
            Exit For
        End If
    Next Cell
End Sub

This example uses an If-Then construct to check the value of each cell. If a cell is negative, it’s selected, and then the loop ends when the Exit For statement is executed.

GoTo statements

The most straightforward way to change the flow of a program is to use a GoTo statement. This statement simply transfers program execution to a new instruction, which must be preceded by a label (a text string followed by a colon, or a number with no colon). VBA procedures can contain any number of labels, but a GoTo statement can’t branch outside a procedure.

The following procedure uses the VBA InputBox function to get the user’s name. If the name is not Howard, the procedure branches to the WrongName label and ends. Otherwise, the procedure executes some additional code. The Exit Sub statement causes the procedure to end.

Sub GoToDemo()
    UserName = InputBox("Enter Your Name:")
    If UserName <>"Howard" Then GoTo WrongName
    MsgBox ("Welcome Howard...")
'   -[More code here] -
    Exit Sub
WrongName:
    MsgBox"Sorry. Only Howard can run this macro."
End Sub

This simple procedure works, but it’s not an example of good programming. In general, you should use the GoTo statement only when you have no other way to perform an action. In fact, the only time you really need to use a GoTo statement in VBA is for error handling (refer to Chapter 4).

Finally, it goes without saying that the preceding example is not intended to demonstrate an effective security technique!

If-Then constructs

Perhaps the most commonly used instruction grouping in VBA is the If-Then construct. This common instruction is one way to endow your applications with decision-making capability. Good decision-making is the key to writing successful programs.

The basic syntax of the If-Then construct is:

If condition Then true_instructions [Else false_instructions]

The If-Then construct is used to execute one or more statements conditionally. The Else clause is optional. If included, the Else clause lets you execute one or more instructions when the condition that you’re testing isn’t True.

The following procedure demonstrates an If-Then structure without an Else clause. The example deals with time, and VBA uses a date-and-time serial number system similar to Excel’s. The time of day is expressed as a fractional value — for example, noon is represented as .5. The VBA Time function returns a value that represents the time of day, as reported by the system clock. In the following example, a message is displayed if the time is before noon. If the current system time is greater than or equal to .5, the procedure ends, and nothing happens.

Sub GreetMe1()
    If Time < 0.5 Then MsgBox"Good Morning"
End Sub

Another way to code this routine is to use multiple statements, as follows:

Sub GreetMe1a()
    If Time < 0.5 Then
        MsgBox"Good Morning"
    End If
End Sub

Note that the If statement has a corresponding End If statement. In this example, only one statement is executed if the condition is True. You can, however, place any number of statements between the If and End If statements.

If you want to display a different greeting when the time of day is after noon, add another If-Then statement, like so:

Sub GreetMe2()
    If Time < 0.5 Then MsgBox"Good Morning"
    If Time >= 0.5 Then MsgBox"Good Afternoon"
End Sub

Notice that we used >= (greater than or equal to) for the second If-Then statement. This covers the remote chance that the time is precisely 12:00 noon.

Another approach is to use the Else clause of the If-Then construct. For example,

Sub GreetMe3()
    If Time < 0.5 Then MsgBox"Good Morning" Else _
      MsgBox"Good Afternoon"
End Sub

Notice that we used the line continuation sequence; If-Then-Else is actually a single statement.

If you need to execute multiple statements based on the condition, use this form:

Sub GreetMe3a()
    If Time < 0.5 Then
        MsgBox"Good Morning"
        ' Other statements go here
    Else
        MsgBox"Good Afternoon"
        ' Other statements go here
    End If
End Sub

If you need to expand a routine to handle three conditions (for example, morning, afternoon, and evening), you can use either three If-Then statements or a form that uses ElseIf. The first approach is simpler:

Sub GreetMe4()
    If Time < 0.5 Then MsgBox"Good Morning"
    If Time >= 0.5 And Time < 0.75 Then MsgBox"Good Afternoon"
    If Time >= 0.75 Then MsgBox"Good Evening"
End Sub

The value 0.75 represents 6:00 p.m. — three-quarters of the way through the day and a good point at which to call it an evening.

In the preceding examples, every instruction in the procedure gets executed, even if the first condition is satisfied (that is, it’s morning). A more efficient procedure would include a structure that ends the routine when a condition is found to be True. For example, it might display the Good Morning message in the morning and then exit without evaluating the other, superfluous conditions. True, the difference in speed is inconsequential when you design a procedure as small as this routine. But for more complex applications, you need another syntax:

If condition Then
    [true_instructions]
[ElseIf condition-n Then
    [alternate_instructions]]
[Else
    [default_instructions]]
End If

Here’s how you can use this syntax to rewrite the GreetMe procedure:

Sub GreetMe5()
    If Time < 0.5 Then
        MsgBox"Good Morning"
    ElseIf Time >= 0.5 And Time < 0.75 Then
        MsgBox"Good Afternoon"
    Else
        MsgBox"Good Evening"
    End If
End Sub

With this syntax, when a condition is True, the conditional statements are executed, and the If-Then construct ends. In other words, the extraneous conditions aren’t evaluated. Although this syntax makes for greater efficiency, some find the code to be more difficult to understand.

The following procedure demonstrates yet another way to code this example. It uses nested If-Then-Else constructs (without using ElseIf). This procedure is efficient and also easy to understand. Note that each If statement has a corresponding End If statement.

Sub GreetMe6()
    If Time < 0.5 Then
        MsgBox"Good Morning"
    Else
        If Time >= 0.5 And Time < 0.75 Then
            MsgBox"Good Afternoon"
        Else
            If Time >= 0.75 Then
                MsgBox"Good Evening"
            End If
        End If
    End If
End Sub

The following is another example that uses the simple form of the If-Then construct. This procedure prompts the user for a value for Quantity and then displays the appropriate discount based on that value. Note that Quantity is declared as a Variant data type. This is because Quantity contains an empty string (not a numeric value) if InputBox is cancelled. To keep the procedure simple, it doesn’t perform any other error checking. For example, it doesn’t ensure that the quantity entered is a non-negative numeric value.

Sub Discount1()
    Dim Quantity As Variant
    Dim Discount As Double
    Quantity = InputBox("Enter Quantity:")
    If Quantity ="" Then Exit Sub
    If Quantity >= 0 Then Discount = 0.1
    If Quantity >= 25 Then Discount = 0.15
    If Quantity >= 50 Then Discount = 0.2
    If Quantity >= 75 Then Discount = 0.25
    MsgBox"Discount:" & Discount
End Sub

Notice that each If-Then statement in this procedure is always executed, and the value for Discount can change. The final value, however, is the desired value.

The following procedure is the previous one rewritten to use the alternate syntax. In this case, the procedure ends after executing the True instruction block.

Sub Discount2()
    Dim Quantity As Variant
    Dim Discount As Double
    Quantity = InputBox("Enter Quantity:")
    If Quantity ="" Then Exit Sub
    If Quantity >= 0 And Quantity < 25 Then
        Discount = 0.1
    ElseIf Quantity < 50 Then
        Discount = 0.15
    ElseIf Quantity < 75 Then
        Discount = 0.2
    Else
        Discount = 0.25
    End If
    MsgBox"Discount:" & Discount
End Sub

Select Case constructs

The Select Case construct is useful for choosing among three or more options. This construct also works with two options and is a good alternative to If-Then-Else. The syntax for Select Case is as follows:

Select Case testexpression
    [Case expressionlist-n
        [instructions-n]]
    [Case Else
        [default_instructions]]
End Select

The following example of a Select Case construct shows another way to code the GreetMe examples presented in the preceding section:

Sub GreetMe()
    Dim Msg As String
    Select Case Time
        Case Is < 0.5
            Msg ="Good Morning"
        Case 0.5 To 0.75
            Msg ="Good Afternoon"
        Case Else
            Msg ="Good Evening"
    End Select
    MsgBox Msg
End Sub

And here’s a rewritten version of the Discount example using a Select Case construct. This procedure assumes that Quantity is always an integer value. For simplicity, the procedure performs no error checking.

Sub Discount3()
    Dim Quantity As Variant
    Dim Discount As Double
    Quantity = InputBox("Enter Quantity:")
    Select Case Quantity
        Case""
            Exit Sub
        Case 0 To 24
            Discount = 0.1
        Case 25 To 49
            Discount = 0.15
        Case 50 To 74
            Discount = 0.2
        Case Is >= 75
            Discount = 0.25
    End Select
    MsgBox"Discount:" & Discount
End Sub

The Case statement also can use a comma to separate multiple values for a single case. The following procedure uses the VBA WeekDay function to determine whether the current day is a weekend (that is, the Weekday function returns 1 or 7). The procedure then displays an appropriate message.

Sub GreetUser1()
    Select Case Weekday(Now)
        Case 1, 7
            MsgBox"This is the weekend"
        Case Else
            MsgBox"This is not the weekend"
    End Select
End Sub

The following example shows another way to code the previous procedure:

Sub GreetUser2()
    Select Case Weekday(Now)
        Case 2, 3, 4, 5, 6
            MsgBox"This is not the weekend"
        Case Else
            MsgBox"This is the weekend"
    End Select
End Sub

Here’s another way to code the procedure, using the To keyword to specify a range of values.

Sub GreetUser3()
    Select Case Weekday(Now)
        Case 2 To 6
            MsgBox"This is not the weekend"
        Case Else
            MsgBox"This is the weekend"
    End Select
End Sub

And to demonstrate the flexibility of VBA, a final example in which each case is evaluated until one of the expressions evaluates to True:

Sub GreetUser4()
    Select Case True
        Case Weekday(Now) = 1
            MsgBox"This is the weekend"
        Case Weekday(Now) = 7
            MsgBox"This is the weekend"
        Case Else
            MsgBox"This is not the weekend"
    End Select
End Sub

Any number of instructions can be written below each Case statement, and they’re all executed if that case evaluates to True. If you use only one instruction per case, as in the preceding example, you might want to put the instruction on the same line as the Case keyword (but don’t forget the VBA statement-separator character, the colon). This technique makes the code more compact. For example:

Sub Discount3()
    Dim Quantity As Variant
    Dim Discount As Double
    Quantity = InputBox("Enter Quantity:")
    Select Case Quantity
        Case"": Exit Sub
        Case  0 To 24: Discount = 0.1
        Case 25 To 49: Discount = 0.15
        Case 50 To 74: Discount = 0.2
        Case Is >= 75: Discount = 0.25
    End Select
    MsgBox"Discount:" & Discount
End Sub

Select Case structures can also be nested. The following procedure, for example, uses the VBA TypeName function to determine what is selected (a range, nothing, or anything else). If a range is selected, the procedure executes a nested Select Case and tests for the number of cells in the range. If one cell is selected, it displays One cell is selected. Otherwise, it displays a message with the number of selected rows.

Sub SelectionType()
    Select Case TypeName(Selection)
        Case"Range"
            Select Case Selection.Count
                Case 1
                    MsgBox"One cell is selected"
                Case Else
                    MsgBox Selection.Rows.Count &" rows"
            End Select
        Case"Nothing"
            MsgBox"Nothing is selected"
        Case Else
            MsgBox"Something other than a range"
    End Select
End Sub

This procedure also demonstrates the use of Case Else, a catch-all case. You can nest Select Case constructs as deeply as you need, but make sure that each Select Case statement has a corresponding End Select statement.

This procedure demonstrates the value of using indentation in your code to clarify the structure. For example, take a look at the same procedure without the indentations:

Sub SelectionType()
Select Case TypeName(Selection)
Case"Range"
Select Case Selection.Count
Case 1
MsgBox"One cell is selected"
Case Else
MsgBox Selection.Rows.Count &" rows"
End Select
Case"Nothing"
MsgBox"Nothing is selected"
Case Else
MsgBox"Something other than a range"
End Select
End Sub

Fairly incomprehensible, eh?

Looping blocks of instructions

Looping is the process of repeating a block of instructions. You might know the number of times to loop, or the number may be determined by the values of variables in your program.

The following code, which enters consecutive numbers into a range, demonstrates what is considered to be a bad loop. The procedure uses two variables to store a starting value (StartVal) and the total number of cells to fill (NumToFill). This loop uses the GoTo statement to control the flow. If the Cnt variable, which keeps track of how many cells are filled, is less than the value of NumToFill, the program control loops back to DoAnother.

Sub BadLoop()
    Dim StartVal As Integer
    Dim NumToFill As Integer
    Dim Cnt As Integer
    StartVal = 1
    NumToFill = 100
    ActiveCell.Value = StartVal
    Cnt = 1
DoAnother:
    ActiveCell.Offset(Cnt, 0).Value = StartVal + Cnt
    Cnt = Cnt + 1
    If Cnt < NumToFill Then GoTo DoAnother Else Exit Sub
End Sub

This procedure works as intended, so why is it an example of bad looping? Programmers generally frown on using a GoTo statement when not absolutely necessary. Using GoTo statements to loop is contrary to the concept of structured coding. (See the “What is structured programming?” sidebar.) A GoTo statement makes the code much more difficult to read because representing a loop using line indentations is almost impossible. In addition, this type of unstructured loop makes the procedure more susceptible to error. Furthermore, using lots of labels results in spaghetti code — code that appears to have little or no structure and has a tangled flow.

Because VBA has several structured looping commands, you almost never have to rely on GoTo statements for your decision-making.

For counter = start To end [Step stepval]
    [instructions]
    [Exit For]
    [instructions]
Next [counter]

Sub SumSquareRoots()
    Dim Sum As Double
    Dim Count As Integer
    Sum = 0
    For Count = 1 To 100
        Sum = Sum + Sqr(Count)
    Next Count
    MsgBox Sum
End Sub

Sub SumOddSquareRoots()
    Dim Sum As Double
    Dim Count As Integer
    Sum = 0
    For Count = 1 To 100 Step 2
        Sum = Sum + Sqr(Count)
    Next Count
    MsgBox Sum
End Sub

Sub DeleteRows()
    Dim RowNum As Long
    For RowNum = 10 To 2 Step -2
        Rows(RowNum).Delete
    Next RowNum
End Sub

Sub DeleteRows2()
    Dim RowNum As Long
    For RowNum = 2 To 10 Step 2
        Rows(RowNum).Delete
    Next RowNum
End Sub

Sub GoodLoop()
    Dim StartVal As Integer
    Dim NumToFill As Integer
    Dim Cnt As Integer
    StartVal = 1
    NumToFill = 100
    For Cnt = 0 To NumToFill - 1
      ActiveCell.Offset(Cnt, 0).Value = StartVal + Cnt
    Next Cnt
End Sub

Sub ExitForDemo()
    Dim MaxVal As Double
    Dim Row As Long
    MaxVal = Application.WorksheetFunction.Max(Range("A:A"))
    For Row = 1 To 1048576
        If Cells(Row, 1).Value = MaxVal Then
            Exit For
        End If
    Next Row
    MsgBox"Max value is in Row" & Row
    Cells(Row, 1).Activate
End Sub

Sub NestedLoops()
    Dim MyArray(1 to 10, 1 to 10, 1 to 10)
    Dim i As Integer, j As Integer, k As Integer
    For i = 1 To 10
        For j = 1 To 10
            For k = 1 To 10
                MyArray(i, j, k) = -1
            Next k
        Next j
    Next i
'   [More code goes here]
End Sub

Do [While condition]
    [instructions]
    [Exit Do]
    [instructions]
Loop

Do
    [instructions]
    [Exit Do]
    [instructions]
Loop [While condition]

Sub EnterDates1()
'   Do While, with test at the beginning
    Dim TheDate As Date
    TheDate = DateSerial(Year(Date), Month(Date), 1)
    Do While Month(TheDate) = Month(Date)
        ActiveCell = TheDate
        TheDate = TheDate + 1
        ActiveCell.Offset(1, 0).Activate
    Loop
End Sub

Sub EnterDates2()
'   Do While, with test at the end
    Dim TheDate As Date
    TheDate = DateSerial(Year(Date), Month(Date), 1)
    Do
        ActiveCell = TheDate
        TheDate = TheDate + 1
        ActiveCell.Offset(1, 0).Activate
    Loop While Month(TheDate) = Month(Date)
End Sub

Do [Until condition]
    [instructions]
    [Exit Do]
    [instructions]
Loop

Do
    [instructions]
    [Exit Do]
    [instructions]
Loop [Until condition]

Sub EnterDates3()
'   Do Until, with test at beginning
    Dim TheDate As Date
    TheDate = DateSerial(Year(Date), Month(Date), 1)
    Do Until Month(TheDate) <> Month(Date)
        ActiveCell = TheDate
        TheDate = TheDate + 1
        ActiveCell.Offset(1, 0).Activate
    Loop
End Sub

Sub EnterDates4()
'   Do Until, with test at end
    Dim TheDate As Date
    TheDate = DateSerial(Year(Date), Month(Date), 1)
    Do
        ActiveCell = TheDate
        TheDate = TheDate + 1s
        ActiveCell.Offset(1, 0).Activate
    Loop Until Month(TheDate) <> Month(Date)
End Sub

Sub DoUntilDemo1()
    Dim LineCt As Long
    Dim LineOfText As String
    Open"c:data	extfile.txt" For Input As #1
    LineCt = 0
    Do Until EOF(1)
        Line Input #1, LineOfText
        Range("A1").Offset(LineCt, 0) = UCase(LineOfText)
        LineCt = LineCt + 1
    Loop
    Close #1
End Sub