Tuesday, April 8, 2014

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 ToolsOptions 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

Excel ShortCuts

Heading Short Cut Key Discription The Fundamentals Ctrl + O Open File The Fundamentals Ctrl + N ...