1. Procedures

A procedure is a group of statements that is called and executed as a unit

Sub ProcedureName ()
statements
End Sub

We will put all our statements inside procedures. Note: each time a macro is recorded in Excel a sub procedure is automatically generated in a new module.

2. Variables

A variable is a reference to a location in memory where data is stored. It’s good practice to declare the types of your variables. To do this, use the command

Dim variableName As DataType

A data type categorizes the values that can be assigned to a variable. Most common data types used are:

• Integer
• Double
• String
• Boolean
• Variant

A constant variable is a variable whose value will never change. To declare a constant, we use

Const variable = value

You can declare an array (a variable that contains a vector of values instead of a single value) in a similar way, either hard-coding the number of elements it contains or not. This command creates a single-dimension array with 5 values: note that counting starts from 0:

Dim numbers(4) As Integer

We’ll talk more about arrays on a different page.

3. Math Operations and Functions

Arithmetic Operators

+, -, *, /, ^, mod

Math functions

For example, Abs(),  Sqrt(), Int(), Exp(), Log()…A more complete list is here.

Excel functions in VBA

You can use Excel functions in VBA with the Application object and WorksheetFunction method:

Application.WorksheetFunction.Sum()
Application.WorksheetFunction.Average()
Application.WorksheetFunction.VLookup()
Application.WorksheetFunction.NormInv ()

The arguments for these functions are the same as when we use them in Excel, but now you can use VBA variables:

Application.WorksheetFunction.Average(x, y, z)

Note: Use worksheet functions or VBA commands when possible, especially for big sets of data, rather than entering formulas into cells in the spreadsheet. See here for some more thoughts on making big VBA macros efficient.

Formulas on the Spreadsheet

Alternatively, and less efficiently, you can enter formulas or functions into cells on the spreadsheet using the Formula method with the Range object.

Range(“A1”).Formula = “=SUM(B1:E1)”
Range(“A1:A5”).Formula = “=SUM(B1:E1)”

Your formula is a text string that Excel will interpret as a formula, so you can’t use any commands that are exclusive to VBA. For example, the command in Excel to generate a random number (uniform between 0 and 1) is RAND(), but in VBA it’s Rnd(). (I know, right?)

4. Handling Ranges and Cells

Range method: returns a range object. The argument is a string that’s either an A1-style reference or the name of a range.

Sheets(1).Range(“A1”).Value=3
Range(“A1”).Formula=”=5*10″
Range(“C1:B3”).Value=6

Cells method: is similar to the Range method, but takes numeric arguments instead of string arguments:

Sheets(1).Cells(1,1).Value=3
Cells(1,1).Formula=”=10*RAND()”

5. Message Boxes

Message Boxes allow you to print something for the user to see in a small dialog box; use the MsgBox function:

MsgBox (prompt, [buttons], [title], [helpfile, context])

Input Boxes allow you to prompt the user to enter some value in a small dialog box; use the InputBox function:

x = InputBox (prompt, [title], [default], [xpos], [ypos], [helpfile, context])