Note :
All course guides are in PDF format and you must
have an appropriate PDF reader installed on your
computer to open and print these course guides. If
you do not already have one, you can find and down-
load a free copy of Acrobat Reader at: [Link]
[Link]/reader/
Course Reference Handout
MICROSOFT EXCEL -
VBA PROGRAMMING
Contact us at Learn iT!
(415) 693-0250
[Link]
MICROSOFT EXCEL
What is VBA? Parts of VBA “Grammar”:
■■ VBA stands for Visual Basic for Applications. Object:
■■ Visual Basic for Applications is a computer programming NOUN
language developed by Microsoft. Element of an application (i.e., workbooks, worksheets, charts,
■■ VBA is a system of programming code statements that can cell ranges) (i.e., Worksheet, ActiveCell, Range etc.)
be used maniputlate activities in that application.
Method:
VERB
What Can Excel VBA Do?
Behavior or action of an Object … [Link] (i.e., Worksheet.
■■ Perform workbook tasks automatically. Add, [Link]) (i.e., [Link] Close method close
■■ Save time and add productivity to workday. the active workbook)
■■ Enter data into specified areas of worksheet.
■■ Calculate and enter results into cells. Property:
■■ Format cells based on conditions. ADJECTIVE
■■ Eliminate errors due to end-user entry. Characteristic of an Object (separated by a period) As in object.
■■ Perform tasks not built-in to Excel. property=value (i.e., Worksheets(“Sheet1”).Name=“January”
■■ Interact with other Office apps.
Parameter:
■■ Create consistency in data presentation.
ADVERB
Parameters of that method. Parameter and its Value are
Macros vs VBA separated by colon and equal sign. (i.e., [Link]
Before:=Worksheet(1)
Macros VBA
Perform a specific set of Can evaluate conditions or Comment:
repetitive instructions and take input and make decisions NOTES
stops that are manually that can alter the results prior Comments are text denoted by an apostrophe (‘) that allows
recorded. Can not interpret to performing the requested users to place notes and explanations within the VBA code.
current situations in a actions. VBA programming is Comments are green colored.
worksheet or cell manually encoded.
Dim:
DIMENSION
Common VBA Terminology:
Declaring a variable to identify its data type. (i.e., Dim PmtType
VBE Window: as Integer)
Visual Basic Editor (Application window that is used to create /
edit VBA).
VBA Programming Code Colors:
Project: Blue:
Current Workbook.
Keywords reserved by VBA (i.e., Dim myCell As Range)
Modules: Black:
Storage area for VBA codes. Each time you start Excel and record
Normal VBA code (i.e., [Link](0, 1).Range(“A1”).Select)
VB Excel stores it in a module.
Red:
Class Modules: Code found in error (i.e., [Link](0, 1).Range(“A1”)Select)
Area in which you can create your own custom objects.
Note: No period before select method
Procedure:
Set of tasks Starts with “sub Name()” and ends with “End Sub”. Green:
Comments referenced at start with an apostrophe (‘)
User Defined Form: ‘ Formats subtotal lines in bold and color
Used to have users interact and make selections that trigger
VBA events. Forms are graphic dialog boxes.
2 Kinds of VBA Procedures:
Collections:
Related objects with the same properties (i.e. Worksheets Sub Routines:
collection includes all worksheets in a workbook). Do not return a value.
Cannot refer to a sub routine in a worksheet cell.
Event:
Actions such as mouse clicks, double-clicks, opening a workbook. Functions:
Returns a Value.
Debugging:
The process of locating and correcting errors in code. Need to give excel the values.
Can be run as any other function in Excel cell.
02
MICROSOFT EXCEL
Example of a VBA Function Procedure
Example of a VBA Sub Procedure
Declaring Variables:
The keyword, Dim is used to declare a variable to the type of data VBA should expect in that procedure.
Examples:
Dim curEarnings As Currency Dim bytAge As byte, intUnitsSold As Integer, Percent
As Single
Dim strFName As String
Dim Earnings As Currency, FName As String, HireDate
Dim dtmHireDate As Date as Date
Excel Object Variables:
Dim rng As Range, wks as Worksheet, wkb As Workbook
Set wks As ActiveSheet (declared objects need Set keyword statement to set scope of variable)
Creating Input Boxes and Message Boxes:
Input Box Message Box
03
MICROSOFT EXCEL
Examples of Using Cell Range References in VBA Procedures:
Creating Decision Structures:
Select Case Statement
IF…Then…Else Statement
04
MICROSOFT EXCEL
IF…Then…ElseIFs Statement
Creating Looping Structures:
Fixed-Number Loop Example:
Sub LoopExample()
Dim i As Integer
For i = 1 To 10
Cells(i, i).Value = i
Next i
End Sub
05
MICROSOFT EXCEL
For Next Loop Example:
Sets Starting Row at 4 and
begins Loop Through Row 18.
Continues Loop
until Done at Row 18.
06
MICROSOFT EXCEL
Infinite Loop Example:
Sets Defaults
Starts Loop
Re-Evaluates Loop
Ends Loop
Designing Custom Forms/Dialog Boxes:
Insert User Form Command:
07
MICROSOFT EXCEL
Form Formatting Properties:
Properties that can be assigned to the form or its controls (fields)
include:
■■ BackColor
■■ BorderColor
■■ Font
■■ ForeColor (text color)
■■ Picture (insert an image on the form)
Note:
The image on the right is set on the Categorized
tab instead of Alphabetic for this example.
Example of Form Code Used to Use Collected Data:
Attached to the frmEmployee Info Attached to the Sheet code to
form to enter data into cells open form from command button
User Form Completed:
08
MICROSOFT EXCEL
Debugging and Error Handling Tools:
Types of Programming Code Errors: Stepping Through Code (Debug Menu):
Error Cause Step Purpose
Incorrect code syntax (i.e., left off a Runs each line of code sequentially. Allows
Compile-time Step Into
parenthesis, quote, or period) you to view the result of each step.
VBA cannot evaluate code. (i.e., code delivers Runs each procedure as a single statement.
an error message as the result of a calculation, Step Over Use when you want to skip areas of code such
Run-time
text is placed in a cell where a number is as calls to other procedures.
expected)
Runs remaining code in procedure as one
Code runs successfully but returns the Step Out
statement. Executes remainder of code.
Logical incorrect result. (i.e., used the wrong
mathematical operator in a formula)
VBA Editor Debugging Tools:
Tool Purpose
Pause the execution of code at a specific
Breakpoint
point in that code
Monitors values of specified variables and
Watch Window
expressions
Immediate Assess the results of an expression or variable On Error GoTo Statement:
Window by trying different sets of values Error GoTo Statement tells Excel
Monitors all declared variables of a procedure to stop running the procedure
Locals Window (Exit Sub) and perform a task if
that is currently running
there is an error (e.g., display a
message box)
Setting A Breakpoint:
Setting a Breakpoint (red highlight) forces the procedure to stop
executing at a given line of code. This could be a line of code
that is causing an error to occur. Excel will automatically set a
breakpoint (yellow highlight) when a Run-Time error occurs.
Breakpoints are temporary and are not saved with the code.
On Error Resume Statement:
Error Resume Statement tells Excel to ignore any error messages
and continue processing the tasks in the procedure.
09