VBA for Excel
Lesson 1: The Visual Basic
Editor in Excel (VBE)
Lesson 2: The Project Window in
the Visual Basic Editor of Excel
Lesson 3:The Properties Window in
the Visual Basic Editor of Excel
Lesson 4: The Code Window in the
Visual Basic Editor of Excel
Sub proFirst()
Range("A1").Value = 34
Range("A2").Value = 66
Range("A3").Formula = "=A1+A2"
Range("A1").Select
End Sub
Lesson 5: Developing Macros in
Excel
Sub proTest()
End Sub
Lesson 5: Developing Macros
in Excel
Sub proTest()
Sheets("Sheet1" ).Select
Range("C1" ).Select
Do Until Selection.Offset(0, -2).Value = ""
Selection.Value = Selection.Offset(0, -2).Value & " " &
Selection.Offset(0, -1)
Selection.Offset(1, 0).Select
Loop
Range("A1" ).Select
End Sub
Lesson 6: Testing Macros in the
Visual Basic Editor for Excel
Lesson 7: The Excel Macro
Recorder
In the sheet below (Sheet1) select cells B1
to B5, go to "Sheet2", select cell B6,
come back to "Sheet1" and select cells D2
to D5.
Lesson 8: Modifying a Macro in
Excel
• If you try to run this macro you will get an error
message. The error comes from the sections about
xlInsideVertical and xlInsideHorizontal. These
sections would be necessary if you were working
with a set of cells (A1 to V34) because there would
be borders between cells and there would be
xlInsideVertical and xlInsideHorizontal borders.
• You will notice that this is a series of "With/End
With" statements (4 for the edges and 2 for the
insides). You can copy this code anytime you need it
but make sure that you are not generating an error
so remove the sections about "xlInsideHorizontal"
and "xlInsideVertical" .
VBA Code General Tips and
General Vocabulary
• Coding Tips
- Always key in your code in lowercase
letters. If the spelling is right, the
necessary letters will be capitalized. If
no letter gets capitalized .... check your
spelling.
Lesson 15: Working with
Worksheets
• You access a worksheet with:
Sheets("Balance").Select
Sheets("Results").Select
• Sheets("Sheet1").Name= "Results"
Lesson 15: Working with
Worksheets
• You access a worksheet named " Balance" with:
Sheets("Balance").Select
Note that the word "Sheets" is plural and always use the quotes within the parenthesis
• You cannot select a sheet that is hidden so you will need to write:
Sheets("Balance").Visible= True
Sheets("Balance").Select
and then if you want to hide the sheet again:
Sheets("Balance").Visible= False
• The name of a sheet must not have more than 31 characters and should not include certain
special characters like " ? : \ / [ ]" . If you do not respect these rules your procedure will crash.
• The following lines of code will generate an error message:
Sheets("Sheet1").Name= "Balance and Introduction to Numbers" because there are more
than 31 characters including the spaces
Sheets("Sheet1").Name= " Balance: Introduction" because of the special character :
Sheets("Sheet1" ).Name= " " because the name cannot be blank
• You can not go directly from a sheet to a cell on another sheet. For example if the active sheet is
"Balance" and you want tot go to cell A1 of a sheet named " Results" you cannot write:
Sheets("Results").Range("A1").Select
You must take two steps:
Sheets("Results").Select
Range("A1").Select
Lesson 16: Moving Around the
Worksheet
• Cells
• Ranges
• Columns
• Rows
Lesson 16: Cells
• A lot of VBA beginners start their career using Cells. For example:
Cells(1,1).Select is the same thing as Range("A1").Select and
Cells(11,31).Select is the same as Range("AE11").Select.
• We strongly recommend that you use Range instead of Cells to
work with cells and groups of cells. It makes your sentences much
clearer and you are not forced to remember that column AE is
column 31.
• The only time that you will use Cells is when you want to select all
the cells of a worksheet. For example:
Cells.Select
To select all cells and then to empty all cells of values or formulas
you will use:
Cells.ClearContents
Lesson 16: Range
• To select a single cell you will write:
Range("A1").Select
• To select a set of contiguous cells you will use the colon
and write:
Range("A1:G5").Select
• To select a set of non contiguous cells you will use the
comma and write:
Range("A1,A5,B4").Select
• To select a set of non contiguous cells and a range you
will use both the colon and the comma:
Range("A1,A5,B4:B8").Select
Lesson 16: Offset
• The Offset property is the one that you will
use the most with Range to move around the
sheet.
To move one cell down (from B2 to B3):
Range("B2").Offset(1,0).Select
To move one cell to the right (from B2 to C2):
Range("B2").Offset(0,1).Select
To move one cell up (from B2 to B1):
Range("B2").Offset(-1,0).Select
To move one cell to the left (from B2 to A2):
Range("B2").Offset(0,-1).Select
• To move one cell down from the selected cell:
ActiveCell.Offset(1,0).Select
• As you notice the first argument between the
parentheses for Offset is the number of rows and the
second one is the number of columns. So to move from
A1 to G6 you will need:
Range("A1").Offset(5,6).Select
• You will use very often the following piece of code . It
selects a cell and 4 more to the right to be copied/pasted
somewhere else:
Range(ActiveCell,ActiveCell.Offset(0,4)).Copy
Notice the comma after the first ActiveCell and the
double closing parentheses before the Select.
Lesson 17: Message and Input
Boxes (MsgBox, InputBox)
Sub proLessson17a()
Sheets("Sheet1").Select
Range("A1").Value = 695
MsgBox "The macro has finished running"
End Sub
Lesson 17: b
Sub proLessson17b()
Sheets("Sheet1").Select
Range("A1").Value = 695
MsgBox "The result is in cell ""A1"""
End Sub
Lesson 17: c
Sub proLessson17c()
Sheets("Sheet1").Select
Range("A1").Value = 695
MsgBox "The result is " & Range("A1").Value
End Sub
Lesson 19: Working with Variables
• Hard Coding vs Dynamic Coding
You are hard coding when you write:
Workbooks.Open "MyFile.xls"
• You are dynamically coding when you
enter the name of the file in an cell (A1) of
your Excel sheet and you write.
varWorkbook=Range("A1").Value
Workbooks.Open varWorkbook
At this point you or the user can change
the name of the workbook to open in cell
A1 instead of going to the VBA code in the
Visual Basic Editor.
Lesson 20: Working With
Statements
Sub proDelete()
Do Until Selection.Value = "xxx"
If Selection.Value = "" Then
Selection.EntireRow.Delete
Else
Selection.Offset(1, 0).Select
End If
Loop
Range("A1").Select
End Sub
