Guide
Cell Navigation
Basic navigation is one of the most important parts of programming and is usually the starting point when learning to code VBA instead of just recording it. Below you will see some useful examples of how to navigate the cells in your spreadsheets.
One of the most useful tips I can give is that the currently selected cell is the 'ActiveCell'.
To simply select cell A1.
Range("A1").Select
To select cell A1 and D2.
Range("A1,D2").Select
To select cell A1 and D1.
Range("A1:D1").Select
The next two lines select cell A1 as the 'Cells' function required the row then the column.
Cells(1, 1).Select
Cells(1, "A").Select
Both of the next two lines will stay in the current row but move to column B.
Cells(ActiveCell.Row, "B").Select
Cells(ActiveCell.Row, 2).Select
The below code will move the cursor to row one but stay in the same column.
Cells(1, ActiveCell.Column).Select
To select column D.
Columns("D").Select
To select Columns E and G.
Range("E:E,G:G").Select
To select columns E to G.
Columns("E:G").Select
To select row 1.
Rows(1).Select
To select rows 4 and 9.
Range("4:4,9:9").Select
To select rows 4 to 9.
Rows("4:9").Select
From cell A1, to get to the end of a range use...
Selection.End(xlDown).Select
You can also use the following codes to navigate in different directions.
Selection.End(xlUp).Select
Selection.End(xlToRight).Select
Selection.End(xlToLeft).Select
If you want to highlight as you go the code can be used within a selection.
Range _
(Selection, Selection.End(xlDown)).Select
Range _
(Selection,Selection.End(xlToRight)).Select

Sheet&Workbooks
At times you may need a macro to open another spreadsheet and gather data from it or perform actions within that spreadsheet.
A few examples below on how to open, close and save spreadsheets.
Open a spreadsheet
Workbooks.Open Filename:= _ "C:\test.xlsx"
Open a password protected spreadsheet
Workbooks.Open Filename:= _ "C:\test.xlsx", Password:="abc"
If the file is locked to 'read only' use..
Workbooks.Open Filename:= _ "C:\test.xlsx", WriteResPassword:="abc"
To save a specific spreadsheet Workbooks("test.xlsx").Save
To save the current spreadsheet ActiveWorkbook.Save
Save as 2007+ spreadsheet ActiveWorkbook.SaveAs _ Filename:="C:\test.xlsx", _ FileFormat:=xlOpenXMLWorkbook, _ CreateBackup:=False
Save as Macro enabled spreadsheet
ActiveWorkbook.SaveAs _ Filename:="C:\test.xlsm", _ FileFormat:= _ xlOpenXMLWorkbookMacroEnabled, _ CreateBackup:=False
Save as binary spreadsheet
ActiveWorkbook.SaveAs _ Filename:="C:\test.xlsb", _ FileFormat:=xlExcel12, _ CreateBackup:=False
To close a specific spreadsheet...(Will prompt if changes have been made)
Workbooks("test.xlsx").Close
Close and save ActiveWorkbook.Close True
Close and don't save
ActiveWorkbook.Close False

Variables
As soon as you start programming macros's instead of just recording them, one of the first things you will need to learn is how to use variables. Variables store values using a word that can be called upon at any time.
For example, there is a value in cell A1 that you want to use multiple times. Instead of using Range("A1").Value each time, you can set the value of that cell to a variable.
Dim myName As String
myName = Range("A1").Value
Now, whenever you need to call upon that value just use 'myName'.
There are different types of variables. Each used for different purposes and can maximise efficiency if you choose the correct one for the task.
Integer: Most used variable and can store whole numbers between -32,768 to 32,767.
Integer Example:
Dim daysonsale as Interger
Dim totalsales as Interger
daysonsale = Range("A2").Value
totalsales = Range ("B2").Value
averagesales = totalsales / daysonsale
Long: Works the same as an Integer but ranging in value from -9,223,372,036,854,775,808 through to 9,223,372,036,854,775,807 (9.2...E+18).
String: Another commonly used variable. Holds a "String" of characters that is contained within quote marks "".
String Example:
Dim myName As String
myName = Range("A1").Value
Boolean: Holds either True or False
Boolean Example:
Dim hitTarget As Boolean
If Range("A1").Value > 5 Then
hitTarget = True
Else
hitTarget = False
End If
MsgBox hitTarget
Date: Similar to Integer but handles date and time values instead of whole numbers. This will ensure that the date value is held correctly.
Date Example:
Dim dateOne As Date
dateOne = DateValue("Feb 02, 1985")
MsgBox dateOne

Error Handling
Error handling is used to redirect the macro to do a different task should something unexpected happen.
For example, you have a spreadsheet with a sheet called "Summary" and you want the macro to select it you would use...
Sheets("Summary").Select
But, if "Summary" had been deleted the macro couldn't run and you would get the error message below.
To handle this problem use the 'On Error' function to redirect the macro.
On Error GoTo foundError:
Sheets("Summary").Select
Exit Sub
foundError:
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Summary"
Resume
Another option is to tell the macro to skip the error line. This can be done with...
On Error Resume Next
This can be useful if you know that the error won't cause problems later in the code but if this was used on the example shown above, whatever follows 'Sheets("Summary").select' would happen on the wrong sheet.
If you want to reset error handling so that the normal error messages will appear use...
On Error Goto 0
This puts the error handling back into the position it was at the start of the macro.

Loops
Loops are vital to VBA, especially when writing large macros.
Before discovering how to use loops, people have been known to record or type out hundreds of lines of code to get a simple task done. By using a 'Loop' you can repeat a piece of code and apply it to different cells or sheets etc.
The three main 'Loops' are...
- For
- Do Until
- Do While
The most basic loop sets i to the value of 1 and increases it by 1 each time it loops...
For i = 1 To 10
Cells(i, "A").Value = Cells(i, "A").Value + 1
Next
This will increase the value of each cell in range("A1:A10") by 1.
Another basic example of the loop would be to perform the same action on each of the cells shown below.
If you want to use the 'Select Case' function on each of these cells to put either 'Odd' or 'Even' in column B, the code below uses 'Select Case' within the loop.
For Each cell In Range("A1:A4")
Select Case cell.Value
Case 1, 3
Cells(cell.Row, "B") = "Odd"
Case 2, 4
Cells(cell.Row, "B") = "Even"
End Select
Next cell
This next code has the same results but instead of specifically running through a range, it starts in cell A1, runs the 'Select Case', drops the cursor down one cell and repeats. When the cursor is on a blank cell it jumps out of the loop.
Range("A1").Select
Do While ActiveCell.Value <> ""
Select Case ActiveCell.Value
Case 1, 3
Cells(ActiveCell.Row, "B") = "Odd"
Case 2, 4
Cells(ActiveCell.Row, "B") = "Even"
End Select
ActiveCell.Offset(1, 0).Select
Loop
Another option would be to use...
Do Until ActiveCell.Value = ""
This will also stop when the cursor hits a blank cell.

Worksheet Functions
VBA doesnt have all of the functions that Excel has. Some basic functions like Sum and Count can be used but by using 'Application.WorksheetFunction'
MsgBox "Sum = " & Application. _
WorksheetFunction.Sum(Range("A1:A4"))
MsgBox "Count = " & Application. _
WorksheetFunction.Count(Range("A1:A4"))
MsgBox "Average = " &Application. _
WorksheetFunction.Average(Range("A1:A4"))
MsgBox "Min = " & Application. _
WorksheetFunction.Min(Range("A1:A4"))
MsgBox "Max = " & Application. _
WorksheetFunction.Max(Range("A1:A4"))

Custom Functions
Although Excel and VBA have enough functions to keep most users happy, there may come a time when you need a custom function that you can use easily when needed. Custom Functions are entered into modules the same as a macro.
For example, you have highlighted some cells in red and now you want to know how many are highlighted.
The example below sets CountRedCells to 0 and increases it's value by 1 every time a red cell is found within the set range.
Function CountRedCells _
(range As range) As Integer
Let CountRedCells = 0
For Each cell In range
If cell.Interior.Color = "255" Then
CountRedCells = CountRedCells + 1
End If
Next cell
End Function
If you wanted to sum the value of the cells highlighted red use this example. It sets SumRedCells as 0 and every time it finds a red cell in the range it increases SumRedCells by the number within the cell.
Function SumRedCells _
(range As range) As Integer
Let SumRedCells = 0
For Each cell In range
If cell.Interior.Color = "255" Then
SumRedCells = SumRedCells + cell.Value
End If
Next cell
End Function
Another more simple example would be to calculate the volume of something using the values in three cells.
Function Volume( _
Height As Double, _
Width As Double, _
Length As Double) As Double
Volume = Height * Width * Length
End Function

References
There is a lot of data that can be extracted from just one cell.
Examples below show what can be extracted and then used in another part of the code.
ActiveCell.Address = $B$3
ActiveCell.Address(0,0) = B3
ActiveCell.Column = 2
ActiveCell.Row = 3
ActiveCell.Width = 48
ActiveCell.Height = 15
ActiveCell.Interior.Color = 16777215
ActiveCell.Font.Size = 11
Left(ActiveCell, 3) = ABC
Right(ActiveCell, 3) = 123
Len(ActiveCell) = 7

Message Box
The MsgBox function in VBA displays a pop up message and waits for the user to click a button before the macro can continue.
The most simple message box code available is.. Msgbox "Finished!"
You can also add small icons to the Msgbox to make it look a little more professional. Some examples below.
msgbox "Finished!", vbExclamation
msgbox "Finished!", vbCritical
msgbox "Finished!", vbInformation
msgbox "Finished?", vbQuestion
Another modification available to the msgbox is the title bar text and is set after the second comma...
msgbox "Warning!", vbCritical, "Error"
Message boxes can also be used to redirect the macro based on a users choices. Similar to when you close a spreadsheet that hasn't been saved, you are prompted to save or close without saving.
The code below will promp for a Yes or No and run different code based on the selection.
If msgbox _
("Delete data?", vbYesNo) = vbYes Then
Cells.Delete
msgbox "Data deleted"
Else
msgbox "Data not deleted"
End If
Using vbYesNo isn't the only button choice available. The table below shows the other options available.
Button OptionsButtons Displayed
vbOKOnlyOK
vbOKCancelOK - Cancel
vbAbortRetryIgnoreAbort - Retry - Ignore
vbYesNoCancelYes - No - Cancel
vbYesNoYes - No
vbRetryCancelRetry - Cancel
This table shows the vb Value for each of the buttons after they have been pressed.
Button Title vb Value
YesvbYes
NovbNo
OKvbOk
CancelvbCancel
AbortvbAbort
RetryvbRetry

Delay&Wait
There are a few ways of temporarily pausing a macro from running in VBA. This is handy to use if you want to see what is happening while the macro is running at a certain part. Adding a pause will just stop the macro for however long you want it to.
The example below will pause the macro for 5 seconds.
Application.Wait Now + TimeValue("00:00:05")
Using this method the minimum delay available is 1 second and this is normally ok. There will be times however, that you want to delay by half a second or a tenth of a second etc. This can be useful within a loop that runs all the way through almost instantly. By inserting a delay of half a second it will slow the loop down and allow you to see it in action.
Dim x As Single
x = Timer
While Timer - x < 0.5
Wend
If you want to pause the macro until an exact time this can also be done and is based on the time on the PC/Laptop.
Application.Wait "21:15:45"

Single Dimension Array
Arrays are mainly used to group data of the same variable type. A variable can only hold a single item but an array can hold multiple items.
An array stores each part of the array to an index number which starts at 0 by default.
For a nice and simple example... Let's say you want to use an array to return the current day of the week.
There are two options.
Option 1
Create a dynamic array with no fixed number of entries. The example array below sets "Mon" to 0, "Tue" to 1 etc...
Then show a message box with the weekday using this array. This example sets the first day of the week to vbMonday.
If today was Saturday the weekday function would generate '6' but "Sat" is the '5' on the array index. Therefore, before showing the message box just insert -1 in the weekday function to bring it in line with the array.
Sub Weekday_Array1()
Dim WDArr() As Variant
WDArr = Array("Mon", _
"Tue", _
"Wed", _
"Thu", _
"Fri", _
"Sat", _
"Sun")
MsgBox WDArr(Weekday(Date, vbMonday) - 1)
End Sub
Option 2
Create a static array. This example still stores values to the index numbers within the array but they are now assigned to index numbers 1 to 7.
There is now no need to bring the weekday function inline with the array.
Sub Weekday_Array2()
Dim WDArr(1 to 7) As String
WDArr(1) = "Mon"
WDArr(2) = "Tue"
WDArr(3) = "Wed"
WDArr(4) = "Thu"
WDArr(5) = "Fri"
WDArr(6) = "Sat"
WDArr(7) = "Sun"
MsgBox WDArr(Weekday(Date, vbMonday))
End Sub
Both of the above examples with show a message box like this..

Macro's
If you need to copy data that has been filtered insert this code at the part of the macro that will do the copying and what ever is left on the auto filter will be copied to the destination set at the bottom.
Sub copyfilterdata()
Dim rng As Range
Dim rng2 As Range
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
If rng2 Is Nothing Then
MsgBox "No data to copy"
Else
Sheets("Sheet2").Rows("2:1000").ClearContents
Set rng = ActiveSheet.AutoFilter.Range
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _
Destination:=Sheets("Sheet2").Range("A2") 'Set destination here
End If
ActiveSheet.ShowAllData
End Sub

Autofilter-Delete
If you need to delete data that has been filtered insert this code at the part of the macro that will do the copying and adjust the Sheet name, Column number and Criteria. This is currently set to remove "#N/A"s from the second column within the autofilter.
Sub DeleteFilterData()
With ActiveSheet
On Error Resume Next
If .AutoFilterMode = False Then .Cells.AutoFilter
.Range("A1").AutoFilter Field:=2, Criteria1:="#N/A"
.Range("A1").CurrentRegion.Offset(1, 0).SpecialCells _
(xlCellTypeVisible).EntireRow.Delete
ActiveSheet.ShowAllData
End With
End Sub

Clear Pivot Table Cache
If you have ever had a pivot table with selectable items that are no longer in the source data this code can be used to refresh the pivot table in a way that only items in the source data will appear. Just simply insert this code into a module and run.
Sub Clean_Pivot_Cache()
Dim pc As PivotCache
For Each pvt In ActiveWorkbook.PivotCaches
pvt.MissingItemsLimit = xlMissingItemsNone
pvt.Refresh
Next
End Sub