VBA Programming Fundamentals

In last post I present an overview of objects, properties, and methods, but I don’t tell you much about how to manipulate objects so that they do meaningful things. This post quietly nudges you in that direction by exploring the VBA language elements, which are the keywords and control structures that you use to write VBA routines. I start by presenting a simple VBA Sub procedure. The following code, which is stored in a VBA module, colors the cell as RED. When the code finishes executing, the procedure displays a message with the result.

Sub VBA_Demo()

' Color Cell on Active Sheet

    Range("A2:D4").Select

    With Selection.Interior

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

        .Color = 255

    End With

    MsgBox ("Range A2 to D4 colored as red")

   

End Sub

This procedure uses some common VBA language elements, including:

• A comment (the line that begins with an apostrophe)
• Range Selection on Active Sheet.
• Fill out color of Selected Cells or Range
• A VBA function (MsgBox)

Variables, Data Types, and Constants

VBA’s main purpose is to manipulate data. Some data resides in objects, such as worksheet ranges. Other data is stored in variables that you create.

A variable is simply a named storage location in your computer’s memory. Variables can accommodate a wide variety of data types — from simple Boolean values (True or False) to large, double-precision values. You assign a value to a variable by using the equal sign operator. Make the habit of your variable names as descriptive as possible. VBA does have a few rules regarding variable names:

• You can use alphabetic characters, numbers, and some punctuation characters, but the first character must be alphabetic.
• VBA doesn’t distinguish between case. To make variable names more readable, programmers often use mixed case (for example, InterestRate rather than interestrate).
• You can’t use spaces or periods. To make variable names more readable, programmers often use the underscore character (Interest_Rate).
• You can’t embed special type declaration characters (#, $, %, &, or !) in a variable name.
• Variable names can be as long as 254 characters — but using such long variable names isn’t recommended.

Below is some examples of assignment expressions that use various types of variables. The variable names are to the left of the equal sign. Each statement assigns the value to the right of the equal sign to the variable on the left.

x = 1

InterestRate = 0.075

LoanPayoffAmount = 243089.87

DataEntered = False

x = x + 1

MyNum = YourNum * 1.25

UserName = “Bob Johnson”

DateStarted = #12/14/2009#

VBA has many reserved words, which are words that you can’t use for variable or procedure names. If you attempt to use one of these words, you get an error message. For example, although the reserved word Next might make a very descriptive variable name, the following instruction generates a syntax error:

Next = 132

Syntax error messages aren’t always descriptive. If the Auto Syntax Check option is turned on you get the error: Compile error: Expected: variable. If Auto Syntax Check is turned off, attempting to execute this statement results in: Compile error: Syntax error. It would be more helpful if the error message were something like Reserved word used as a variable. So if an instruction produces a strange error message, check the VBA Help system to ensure that your variable name doesn’t have a special use in VBA.

Defining data types

VBA can automatically handle all the details involved in dealing with data. Not all programming languages make it so easy. For example, some languages are strictly typed, which means that the programmer must clearly 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. Below is the Table for built-in data types.

Data type	Storage size	Range
Byte	1 byte	0 to 255
Boolean	2 bytes	True or False
Integer	2 bytes	-32,768 to 32,767
Long (long integer)	4 bytes	-2,147,483,648 to 2,147,483,647
Single (single-precision floating-point)	4 bytes	-3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values
Double (double-precision floating-point)	8 bytes	-1.79769313486231E308 to -4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values
Currency (scaled integer)	8 bytes	-922,337,203,685,477.5808 to 922,337,203,685,477.5807
Decimal	14 bytes	+/-79,228,162,514,264,337,593,543,950,335 with no decimal point; +/-7.9228162514264337593543950335 with 28 places to the right of the decimal; smallest non-zero number is +/-0.0000000000000000000000000001
Date	8 bytes	January 1, 100 to December 31, 9999
Object	4 bytes	Any Object reference
String (variable-length)	10 bytes + string length	0 to approximately 2 billion
String (fixed-length)	Length of string	1 to approximately 65,400
Variant (with numbers)	16 bytes	Any numeric value up to the range of a Double
Variant (with characters)	22 bytes + string length	Same range as for variable-length String
User-defined (using Type)	Number required by elements	The range of each element is the same as the range of its data type.

Generally, it’s best to use the data type that uses the smallest number of bytes. 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.

Determining a data type

You can use the VBA TypeName function to determine the data type of a variable. This displays the data type of MyVar at each step. You see that it starts out as a string, is then converted to a double, and finally ends up as a string again.

Sub VariantDemo ()

MyVar = “1550246”

MsgBox TypeName(MyVar)

MyVar = MyVar / 2

MsgBox TypeName(MyVar)

MyVar = “Answer: “ & MyVar

MsgBox TypeName(MyVar)

MsgBox MyVar

End Sub

Declaring each variable in a procedure before you use it is an excellent habit. Declaring a variable tells VBA its name and data type. Declaring variables provides two main benefits:

• Your programs run faster and use memory more efficiently. The default data type, Variant, causes VBA to repeatedly perform time-consuming checks and reserve more memory than necessary. If VBA knows the data type, it doesn’t have to investigate, and it can reserve just enough memory to store the data.
• You avoid problems involving misspelled variable names. This benefit assumes that you use Option Explicit to force yourself to declare all variables Say that you use an undeclared variable named CurrentRate. At some point in your routine, however, you insert the statement CurentRate = .075. This misspelled variable name, which is very difficult to spot, will likely cause your routine to give incorrect results.

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 and you must declare the variable before you can proceed.

Local variables

A local variable is a variable declared within a procedure. You can use local variables only in the procedure in which they’re declared. When the procedure ends, the variable no longer exists, and Excel frees up the memory that the variable used.

The following procedure uses six local variables declared by using Dim statements:

Sub MySub()

Dim x As Integer

Dim First As Long

Dim InterestRate As Single

Dim TodaysDate As Date

Dim UserName As String

Dim MyValue

‘ - [main code goes here] -

End Sub

Unlike some languages, VBA doesn’t let you declare a group of variables to be a particular data type by separating the variables with commas. For example, the following statement, although valid, does not declare all the variables as integers:

Dim i, j, k As Integer

In VBA, only k is declared to be an integer; the other variables are declared variants. To declare i, j, and k as integers, use this statement:

Dim i As Integer, j As Integer, k As Integer

Module-wide variables

Sometimes, you want a variable to be available to all procedures in a module. If so, just declare the variable before the module’s first procedure (outside of any procedures or functions).

In the following example, the Dim statement is the first instruction in the module. Both Procedure1 and Procedure2 have access to the CValue variable.

Dim CValue as Long

Sub Procedure1()

‘ - [Code goes here] -

End Sub

Sub Procedure2()

‘ - [Code goes here] -

End Sub

Public variables

To make a variable available to all the procedures in all the VBA modules in a project, declare the variable at the module level before the first procedure declaration by using the Public keyword rather than Dim. Here’s an example:

Public CurrentRate as Long

The Public keyword makes the CurrentRate variable available to any procedure in the VBA project, even those in other modules within the project. You must insert this statement before the first procedure in a module (any module). This type of declaration must appear in a standard VBA module, not in a code module for a sheet or a UserForm.

Static variables

Static variables are a special case. They’re declared at the procedure level, and they retain their value when the procedure ends normally. However, if the procedure is halted by an End statement, static variables do lose their values. You declare static variables by using the Static keyword:

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.

Declaring constants

You declare constants with the Const statement. Here are some examples:

Const NumQuarters as Integer = 4

Const Rate = .0725, Period = 12

Const ModName as String = “Budget Macros”

Public Const AppName as String = “Budget Application”

Like variables, constants also have a scope. If you want a constant to be available within a single procedure only, declare it after the Sub or Function statement to make it a local constant. To make a constant available to all procedures in a module, declare it before the first procedure in the module. To make a constant available to all modules in the workbook, use the Public keyword and declare the constant before the first procedure in a module. For example:

Public Const InterestRate As Double = 0.0725

If your VBA code attempts to change the value of a constant, you get an error (Assignment to constant not permitted). This message is what you would expect. A constant is a constant, not a variable.

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 (xlPortrait) to set the page orientation to Portrait for the active sheet:

Sub SetToPortrait()

ActiveSheet.PageSetup.Orientation = xlPortrait

End Sub

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.

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

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.

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/2010#

Const Noon = #12:00:00#

Assignment Statements

An assignment statement is a VBA instruction that makes a mathematical evaluation and assigns the result to a variable or an object. Excel’s Help system defines expression as “a combination of keywords, operators, variables, and constants that yields a string, number, or object. An expression can perform a calculation, manipulate characters, or test data.”

VBA uses the equal sign (=) as its assignment operator. The following are examples of assignment statements (the expressions are to the right of the equal sign):

x = 1

x = x + 1

x = (y * 2) / (z * 2)

FileOpen = True

FileOpen = Not FileOpen

Range(“TheYear”).Value = 2010

Often, expressions use functions. These functions can be built-in VBA functions, Excel’s worksheet functions, or custom functions that you develop in VBA.  Operators play a major role in VBA. Familiar operators describe mathematical operations, including addition (+), multiplication (*), division (/), subtraction (–), exponentiation (^), and string

concatenation (&). Less familiar operators are the backslash (\) (used in integer division) and the Mod operator (used in modulo arithmetic). The Mod operator returns the remainder of one number divided by another. For example, the following expression returns 2:

17 Mod 3

Arithmetic	Comparison	Logical
Exponentiation (^)	Equality (=)	Not
Negation (–)	Inequality (<>)	And
Multiplication and division (*, /)	Less than (<)	Or
Integer division (\)	Greater than (>)	Xor
Modulus arithmetic (Mod)	Less than or equal to (<=)	Eqv
Addition and subtraction (+, –)	Greater than or equal to (>=)	Imp
String concatenation (&)	Like Is

Arrays

An array is a group of elements of the same type that have a common name. You refer to a specific element in the array by using the array name and an index number. For example, you can define an array of 12 string variables so that each variable corresponds to the name of a month. If you name the array MonthNames, you can refer to the first element of the array as

MonthNames(0), the second element as MonthNames(1), and so on, up to MonthNames(11).

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

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

Object Variables

An object variable is a variable that represents an entire object, such as a range or a worksheet. Object variables are important for two reasons:

• They can simplify your code significantly.
• They can make your code execute more quickly.

Object variables, like normal variables, are declared with the Dim or Public statement. For example, the following statement declares InputArea as a Range object variable:

Dim InputArea As Range

Use the Set keyword to assign an object to the variable. For example:

Set InputArea = Range(“C16:E16”)

To see how object variables simplify your code, examine the following procedure, which doesn’t use an object variable:

Sub NoObjVar()

Worksheets(“Sheet1”).Range(“A1”).Value = 124

Worksheets(“Sheet1”).Range(“A1”).Font.Bold = True

Worksheets(“Sheet1”).Range(“A1”).Font.Italic = True

Worksheets(“Sheet1”).Range(“A1”).Font.Size = 14

Worksheets(“Sheet1”).Range(“A1”).Font.Name = “Cambria”

End Sub

This routine enters a value into cell A1 of Sheet1 on the active workbook, applies some formatting, and changes the fonts and size. That’s a lot of typing. To reduce wear and tear on your fingers (and make your code more efficient), you can condense the routine with an object variable:

Sub ObjVar()

Dim MyCell As Range

Set MyCell = Worksheets(“Sheet1”).Range(“A1”)

MyCell.Value = 124

MyCell.Font.Bold = True

MyCell.Font.Italic = True

MyCell.Font.Size = 14

MyCell.Font.Name = Cambria

End Sub

After the variable MyCell is declared as a Range object, the Set statement assigns an object to it. Subsequent statements can then use the simpler MyCell reference in place of the lengthy Worksheets(“Sheet1”).Range(“A1”) reference.

User-Defined Data Types

VBA lets you create custom, or user-defined, data types. A user-defined data type can ease your work with some types of data. For example, if your application deals with customer information, you may want to create a user-defined data type named CustomerInfo:

Type CustomerInfo

Company As String

Contact As String

RegionCode As Long

Sales As Double

End Type

After you create a user-defined data type, you use a Dim statement to declare a variable as that type. Usually, you define an array. For example:

Dim Customers(1 To 100) As CustomerInfo

Each of the 100 elements in this array consists of four components (as specified by the userdefined data type, CustomerInfo). You can refer to a particular component of the record as follows:

Customers(1).Company = “Acme Tools”

Customers(1).Contact = “Tim Robertson”

Customers(1).RegionCode = 3

Customers(1).Sales = 150674.98

You can also work with an element in the array as a whole. For example, to copy the information

from Customers(1) to Customers(2), use this instruction:

Customers(2) = Customers(1)

The earlier example is equivalent to the following instruction block:

Customers(2).Company = Customers(1).Company

Customers(2).Contact = Customers(1).Contact

Customers(2).RegionCode = Customers(1).RegionCode

Customers(2).Sales = Customers(1).Sales

Built-in Functions

Like most programming languages, VBA has a variety of built-in functions that simplify calculations and operations. Many VBA functions are similar (or identical) to Excel worksheet functions. For example, the VBA function UCase, which converts a string argument to uppercase, is equivalent to the Excel worksheet function UPPER.

To get a list of VBA functions while you’re writing your code, type VBA followed by a period (.). The VBE displays a list of all its members, including functions. The functions are preceded by a green icon. If this technique doesn’t work for you, make sure that the Auto List Members option is selected. Choose Tools➜Options and then click the Editor tab.

You use functions in VBA expressions in much the same way that you use functions in worksheet formulas. Here’s a simple procedure that calculates the square root of a variable (using the VBA Sqr function), stores the result in another variable, and then displays the result:

Sub ShowRoot()

Dim MyValue As Double

Dim SquareRoot As Double

MyValue = 25

SquareRoot = Sqr(MyValue)

MsgBox SquareRoot

End Sub

The VBA Sqr function is equivalent to the Excel SQRT worksheet function.

Manipulating Objects and Collections

As a programmer, you’ll spend a lot of time working with objects and collections. Therefore, you want to know the most efficient ways to write your code to manipulate these objects and collections. VBA offers two important constructs that can simplify working with objects and collections:

• With-End With constructs
• For Each-Next constructs

With-End With constructs

The With-End With construct enables you to perform multiple operations on a single object. Lets 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 aRange object):

Sub ChangeFont1()

Selection.Font.Name = “Cambria”

Selection.Font.Bold = True

Chapter 8: VBA Programming Fundamentals 221

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

For Each-Next constructs

The Workbooks collection is a collection of all open Workbook objects, and there are many other collections that you can work with. 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 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

Controlled Code Execution

Some VBA procedures start from top and progress line by line to the bottom. For example, Macro we record always works in this fashion. However, we need to control the flow of routines by skipping over some statements, executing some statements multiple times, and testing conditions to determine what the routine does next.

The additional ways of controlling the execution of VBA procedures are given below:

• GoTo statements
• If-Then constructs
• Select Case constructs
• For-Next loops
• Do While loops
• Do Until loops

GoTo statements

The simplest 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 of a procedure.

The following procedure uses the VBA InputBox function to get the user’s name. If the name is not Codingden, 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 <> “Codingden” Then GoTo WrongName

MsgBox (“Welcome Codingden...”)

Exit Sub

WrongName:

MsgBox “Sorry. Only Codingden can run this macro.”

End Sub

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.

If-Then constructs

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

Sub DailyGreet ()

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

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 following example of a Select Case constructs for Discount example. This procedure assumes that Quantity is always an integer value. For simplicity, the procedure performs no error checking.

Sub Discount()

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 Comma act as OR Operator in select statement.

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 I call a SeriesFiller. 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, is filled, is less than the value of

NumToFill, the program control loops back to DoAnother.

Sub SeriesFiller()

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

For-Next loops

The simplest type of a good loop is a For-Next loop. Following is an example of a For-Next loop that doesn’t use the optional Step value or the optional Exit For statement. This routine executes the Sum = Sum + Sqr(Count) statement 100 times and displays the result — that is, the sum of the square roots of the first 100 integers.

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

Do While loops

This section describes another type of looping structure available in VBA. Unlike a For-Next loop, a Do While loop executes as long as a specified condition is met.

Sub EnterDates ()

‘ 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

Do Until loops

The Do Until loop structure is very similar to the Do While structure. The difference is evident only when the condition is tested. In a Do While loop, the loop executes while the condition is True; in a Do Until loop, the loop executes until the condition is True.

Sub EnterDates ()

‘ 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

My Next post is on Working with VBA Sub Procedures