School of Construction

MBA CPM 1st Semester

THE VISUAL BASIC EDITOR

IN EXCEL

Authored By:
• Sanjay Bhattacharya

Statistics and Computer Application in Construction CM 656.

Outline
In this module, the following topics will be covered:
– Introduction to the Excel Visual Basic for Applications (VBA)
programming language
The Excel object model
The difference between a property, a method, and an event
Fundamental skills for reading, understanding, and writing basic Excel
VBA code
– Basic user interface development
Assigning sub procedures to shapes (i.e., buttons)

2
The Visual Basic Editor (VBE)

3
The Visual Basic Editor
The Visual Basic Editor (VBE) is the environment in which
you work with Excel VBA code
Excel and the VBE have separate windows
– You can toggle between as needed by pressing Alt+F11

Excel VBE

4
The Visual Basic Editor (cont.)
Access the VBE through the Developer Ribbon (Excel 2013)
1) Open Excel 2013 and click on the green tab labeled “File”
2) Click on “Options” (the window “Excel Options” will appear)
3) Click on “Customize Ribbon”
4) Make sure the entry in the combo box labeled “Customize the Ribbon”
reads “Main Tabs”
5) Select the option “Developer”
6) Click OK

5
The Visual Basic Editor (cont.)
There are three main windows in the VBE
– Project Explorer Window
– Properties Window
– Code Window

Two additional windows in the VBE are mainly used for

debugging
– Immediate Window
– Watch Window

VBE Toolbars
– Standard toolbar

6
The Visual Basic Editor (cont.)
The VBE’s windows

Project
Explorer Code
Window

Properties
Window
Watch
Window

Immediate
Window

7
Project Explorer
Lists all projects in any open workbook

Each workbook has a project, and each

project can have several parts
– Objects (e.g., workbook and worksheets)
– Modules
– Forms

In the VBE, you can use Insert >

Module to add a new module to the
current project
– Can also use icon from standard toolbar

8
Properties Window
Contains detailed information about any
selected part of a project in the Project
Explorer

Some basic naming and formatting

properties can be modified for
worksheets and workbooks

Properties are very important for user

forms
– Formatting
– Position
– Picture
– Scrolling
– Behavior
9
Code Window
Displays the VBA code for the highlighted part of a project in
the Project Explorer

When macros are recorded, VBA code is simultaneously

created and can be edited in the VBE

You will write your own code in this window, once you are
more familiar with Excel VBA

10
Immediate and Watch Windows
Both of these windows are used for debugging
– The Immediate Window allows you to enter code that is executed
immediately
As long as the syntax is correct
– The Watch Window displays values of inserted variables

Use the View menu option to view or hide any window

11
The Excel VBA
Programming Language

12
Visual Basic for Applications
Visual Basic for Applications (VBA) brings a dynamic
element to spreadsheet-based decision support systems
(DSS) applications

VBA can be used to perform the same spreadsheet functions

that are done in Excel along with some other advanced
Excel object manipulation

For example, VBA can be used to:

– Modify a spreadsheet
– Create a user interface
– Perform simulation models
– Solve optimization problems

13
Visual Basic for Applications (cont.)
The VBA programming language is common across all
Microsoft (MS) Office applications
– In addition to Excel, you can use VBA in Word, PowerPoint, Access
and Outlook

When you work with a particular application, you need to

learn about the objects it contains
– E.g., Word Documents, paragraphs, words, etc.

Each MS Office application has a clearly defined set of

objects according to the relationships among them
– This structure is referred to as the application’s object model

14
The Excel Object Model
Objects in Excel include (but are not limited to)
– Workbooks and worksheets
– Cells and ranges of cells
– Charts and shapes

There is an object hierarchy which orders these objects

– Workbooks contain worksheets
– Worksheets contain ranges
– Ranges contain cells

Objects in Excel are manipulated in VBA via

– Properties
Physical characteristics of objects that can be measured or quantified
– Methods
Actions that can be performed by objects or on objects

15
The Excel Object Model (cont.)
Objects in Excel also respond to events
– Double-click on a cell
– Opening or closing a workbook
– A recalculation of a worksheet

16
The Excel Object Model (cont.)

Workbook

Cell Range

Cell

Worksheets

17
Macros
Macros will be used to illustrate basic Excel VBA coding
– Recording macros creates VBA code automatically
This code can be studied
– Macros are useful in developing the fundamental skills for reading,
understanding, and writing VBA code
Main topics will include
– Recording a macro
– Writing simple VBA procedures
– Creating event procedures
– Assigning macros to drawing objects in Excel

18
Macros (cont.)
Macros are technically defined as units of VBA code
– A macro automates a repetitive series of actions in an Excel
spreadsheet application
– Macros can be recorded in Excel or created directly by writing VBA
code in the Visual Basic Editor (VBE)

In VBA, macros are referred to as procedures

– There are two types of procedures
Sub procedures
Function procedures
– The macro recorder can only produce sub procedures

To record a macro, we must know exactly the actions we

wish to perform and then use the Macro Recorder
19
Macro Example
Let us record a macro to copy and then paste data
– Create an Excel file CarDealer_Example.xls
A dealer wants to send the information from the first three
columns and last column of the data table (highlighted in
yellow) to a newspaper

20
Macro Example (cont.)
We should first review/practice the steps we will take when
recording the macro
– Highlight (i.e., select) the first three columns of data with the cursor
(C5:E14)
– Copy the selection using CTL+C
You can also right-click the selection and click on Copy
– Highlight (i.e., select) cell C18
– Paste the data using CTL+P
You can also right-click the selection and click on Paste
– Highlight (i.e., select) the last column (K5:K14)
– Copy the selection using CTL+C
– Highlight (i.e., select) cell F18
– Paste using CTL+P
– Select cell A1 and then press Esc

21
Macro Example (cont.)
Now we are ready to record the macro

You can do this in several different ways

– Select View > Macros > Record Macro…
– Record Macro button from the Developer Toolbar

– Click on the Record Macro icon located next to the legend “Ready”
on the lower left corner of the workbook

22
Macro Example (cont.)
When the Record Macro dialog box appears, we enter a
name for the macro
– The macro name should begin with a letter and may contain only
letters, numbers and the underscore (i.e., _) character
– Maximum length is 255 characters
– Do not use special characters (e.g., !, ?, &) or blank spaces

Use a short and descriptive name

– Use _ to separate words
First_Macro
– Capitalization works well too
FirstMacro

Enter CarCopyPaste as the macro name for this example

23
Macro Example (cont.)
Once you begin recording, notice that the Record Macro
button is now the Stop Recording button

After finishing the steps needed to copy and paste the

information, you can stop recording
– Select View > Macros > Stop Recording
– Click on the Stop Recording button from the Developer Toolbar
There is also a Stop button on the lower left corner of the workbook
– Click on the square button next to the legend “Ready”

24
Saving Excel Files Containing Macros
When a normal workbook (i.e., one without macros) is
saved, MS Excel appends the standard .xlsx extension
For workbooks containing macros, the file must be saved
as a Macro-Enabled file
– In this case, Excel appends the special extension .xlsm to these
files
If you fail to save a file containing macros with the .xlsm
extension, ALL YOUR WORK WILL BE LOST!

25
Macro Security
Macro security is an important feature since MS Excel 2007
– Because macros may be used to harm your computer
The level of protection can be set directly from the
Developer ribbon by selecting Macro Security

26
Macro Security (cont.)
When you open a spreadsheet that contains macros, Excel
displays a warning on the ribbon alerting you that macros
have been disabled

Select Enable Content only if you are sure the file is safe

27
Macro Security (cont.)
To avoid having to authorize every spreadsheet with
macros that is opened, define your working directory as a
trusted location

28
Macro Security (cont.)
Save your file in the trusted location that you just defined
– Make sure to save it as fileName.xlsm

Remember
– If you save your file with the extension .xlsx, all you macros will
be lost!

29
Running the Macro
Once you have recorded a macro, you can run it to ensure it
works correctly
– Select View > Macros > View Macros
Select macro “CarCopyPaste” from list and press Run
– Press button Macros on the Developer Toolbar
Select macro “CarCopyPaste” from list and press Run

30
VBA Code
As we learned earlier, each time a macro is recorded in
Excel, VBA code is generated automatically
Let us review the code that was generated for this macro
– Go the VBE window (Alt-F11)
– A Modules folder has been added to the Project Explorer
– Expand the folder and double-click on “Module1” to see the code in
the Code Window

31
VBA Code (cont.)
Sub CarCopyPaste() This is the initial statement of every
sub procedure
Range("C5:E14").Select – The Select method of the Range
Selection.Copy object selects the specified
Range("C18").Select range of cells
ActiveSheet.Paste – The Copy method is used on the
Selection object
Range("K5:K14").Select – The Paste method is used on
Application.CutCopyMode = False the ActiveSheet object
Selection.Copy – The CutCopyMode method
Range("F18").Select removes the moving border
ActiveSheet.Paste – These steps are repeated to
copy and paste the last column
Range("A1").Select – This last cell selection can be
Application.CutCopyMode = False added to avoid leaving the cells
highlighted
End Sub This is the last statement of every
sub procedure
32
Learning to Code in Excel VBA
The VBA code generated when we record a macro can be
studied
– This way, we can easily learn how to create a similar macro directly
from VBA by copying the code generated

33
Creating New VBA Code (cont.)
Assume the dealer now wants to add vehicle mileage
information to the previously recorded macro
– Mileage information is contained in range F4:F14

Write (do not record) code that accomplishes this

– Name your macro AddMileage()

34
Event Procedures
Event Procedures connect events or actions of an Excel
object to a specific macro of VBA code
– Click
– Change
– Activate

To find a list of available/applicable event procedures in

VBE, look at the top of the Code Window
– Double click on the Sheet1 member of the Microsoft Excel Object
tree
– Choose Worksheet from the object list combo box
The label(General) is displayed in the combo box
– Choose the Change event from the event list combo box
The label (Declarations) is displayed in the combo box

35
Assigning Macros to Shapes
MS Excel shapes (e.g., a rectangle) can be employed to
simulate buttons
– Recall that shapes are also objects in Excel

Macros can then be assigned to these buttons

– Simplifies the process of running macros
– This is part of creating graphical user interfaces for DSS

36
Assigning Macros to Shapes (cont.)
To create a simple button do the following
– Select Insert > Shapes > (Select a Rectangular shape)
– Then add text to it by right-clicking it and choosing Edit Text
– Format the rectangle as desired

37
Assigning Macros to Shapes (cont.)
Once your button shape is ready, right-click on it and select
Assign Macro from the drop-down list
– A list of all macros in the open workbooks will appear

Select a macro for your button to run

– Your button has become activated

We can now create buttons for each of the macros we

previously wrote
– Create as many buttons as needed for your macros
– Assign a macro to each new button

38
Buttons Shifting on Worksheet
You may notice that buttons sometimes
shift inside the worksheet as macros are
executed
Fix for drawn controls
– First, select a cell just outside the control and
click it
– Right mouse-click on the button and select Size
and Properties…
– Select the Properties tab and select the option
“Don’t move or size with cells”
– Click Close

39
Limitations with Macros
Although keyboard macros are well worth learning, they are
limited
In particular, they cannot do the following:
– Perform repetitive actions (i.e., loops)
– Perform conditional actions
Take different actions depending on whether something is true or false
– Assign values to variables
– Specify data types
Variables can store integers, decimal numbers, strings, etc.
– Display pop-up messages
– Display custom dialog boxes
These limitations are overcome by writing your own macros
in VBA

40
Summary
The Visual Basic Editor (VBE) is the environment in which
you work with VBA programming code

There are three main windows in VBE

– The Project Explorer Window
– The Code Window
– The Properties Window

The VBA programming language is common across all

Microsoft (MS) Office applications
– Excel has a particular object model
– Objects in Excel can be manipulated by changing their properties or
calling their methods

41
Summary (cont.)
Properties are the physical descriptions of all Excel objects
– The description of the particular property is called the value of the
property

Methods are the actions that can be performed on the object

– The elements of a method statement are called the arguments of the
method

Macros are technically defined as units of VBA code

– VBA code that is recorded in Excel is referred to as a macro
– VBA code that is written by a programmer is referred to as a
procedure
Sub procedure
Function procedure

42
Summary (cont.)
Events are actions that can take place in the Excel window
that cause an action to occur in the VBA code

An event procedure is just like a sub procedure, except that

it is associated with a particular action of an Excel object

43