1
1
Excel & Visual Basic for Applications (VBA)
Object-oriented programming (OOP)
Procedures: Subs and Functions, layout
VBA: data types, variables, assignment
2
Traits of Engineers
Florman’s “Engineering View”1
a commitment to science and to the values that science
demands: independence, originality, dissent, freedom, tolerance a comfortable familiarity with the forces that prevail in the physical universe
a belief in hard work in the quest for knowledge and understanding and in the pursuit of excellence
a willingness to forego perfection in the interest of getting the product “out the door”
a willingness to accept responsibility and the risk of failure a resolve to be dependable
a commitment to social order with a strong affinity for democracy a seriousness of purpose, but not glumness
a passion for creativity, compulsion to tinker and zest for change
1from Florman, Samuel, The Civilized Engineer
3
Object-oriented Programming
a computer-based entity that has properties and can be manipulated
Excel has a pre-defined hierarchy of objects and
properties It is also possible to define new objects, classes of objects, & properties in Excel, but we won’t get to that in this course
Excel is called the Application object It starts with Excel itself the BIG object
There are other Application objects in the Microsoft Office and Windows environments [ Word, Access, etc ] Within the Excel Application object there is an extensive family tree of sub-objects, sub-sub-objects, etc.
And any object, at any level, can have properties
Properties are attributes of objects, while objects are entities
4
Excel’s Object Hierarchy Application
AddIn Name Workbook
WorksheetFunction
Chart
CommandBar
VBProject Worksheet
and so on
Excel’s object structure is complex, so you need a roadmap to work with it.
This is the Object Browser in the VBE.
3
5
The VBE Object Browser launch the Browser with this button on the VBE toolbar Application
Property
Object class You can also view the object “family tree” in VBA Help
6
7
8
Object Collections
Workbooks all currently-open workbook objects
Charts all charts contained in a particular workbook Sheets all sheets contained in a particular workbook Referring to objects in a collection
Worksheets(“Main”) Charts(“XY”)
Complete object references
Application.Workbooks(“Project6”).Worksheets(“Main”).Range(“Pressure”)
Can be simplified when unambiguous
Workbooks(“Project6”).Worksheets(“Main”).Range(“Pressure”) Worksheets(“Main”).Range(“Pressure”)
Range(“Pressure”)
5
9
Properties, Methods and Events properties are attributes of objects
methods are actions to be taken on objects events are happenings that objects respond to
Examples: Range(“A1”).Width Range(“B2”).Clear
property method
There are 1,000s of Objects, Properties, Methods, and Events, but there are only a few that you’ll use all the time. And,
when you need a new one, you can find it.
So, don’t be afraid of drowning in a sea of these things.
Open event (workbook is opened)
10
Object properties
What do we do with them?
examine their settings change their settings Examples
11
Object methods
methods change properties or cause the object to do something Examples
Note: the Copy method has a following “argument”, the destination for the copy
12
Using On-line help for VBA F1
shortcut key
in some Excel installations,
“VBA Help” will have to be installed from the Office 2000 CD/ROMs
7
13
Using the Answer Wizard
Note that in VBA code, the function for square root is Sqr, not SQRT
14
Help Index
15
VBA program units: Subroutines and Functions
Subroutine procedures are typically called “subs” or “macros”
Subs:
a VBA subprogram that can be executed by running it from Excel, from the VBE, or by being “called” by another VBA subprogram
may have zero-to-several arguments that provide for values to be input to or output from the Sub can be created with the macro recorder or typed in from scratch
Sub name (optional arguments)
End Sub
16
functions return values in place of their names
a VBA subprogram that can be executed by using its name in a VBA expression or an Excel formula
Functions:
functions cannot be recorded, but must be typed into the VBE functions may have zero-to-several input arguments
Function name (optional arguments) End Function
9
17
Naming Rules for Subs and Functions
Permissible characters: letters, numbers, some punctuation Must start with a letter
Cannot look like a cell reference
Can’t use #, $, %, &, or ! Can’t use spaces or periods
< 255 characters (!) VBA does not distinguish between upper and lowercase, but the first style entered will be “remembered”
Use reasonably short names that carry a clear meaning Temperature may be better than Temp or T Mole_Fraction or MoleFraction both good
18
VBA “Nuts and Bolts”
Data types, constants, variables, expressions, assignment Data types
the most common: single, integer and string
Single used for most numbers with decimal fractions and possibly exponents
precision: about 6-to-7 significant figures
range: from about 1038down to 10-39, both + and -
Integer used for most counting numbers range: from –32,768 to 32,767 uses 4 bytes of memory
uses 2 bytes of memory
19
String used to store text
1 byte is used for each character characters are stored according to the standard ASCII code
Additional data types
Double used for numbers with decimal fractions and possibly exponents where high precision or extended range are required
precision: about 15-to-16 significant figures
range: from about 10308down to 10-309, both + and - uses 8 bytes of memory
Important: Excel uses “double” for the numbers stored in cells on the spreadsheet
20
Long used for integers where extended range is required
range is about - 2 billion to + 2 billion each number uses 4 bytes of memory used for T/F information
two constant values: TRUE and FALSE 2 bytes are used for each Boolean quantity Boolean
(also called
“logical”)
Object
Variant
used for any defined object takes up 4 bytes of memory
adjusts automatically to the data type required
“chameleon” data type storage requirements vary
11
21
There are additional data types for currency, date
It is also possible to define new data types: user-defined
When you write VBA code, it is possible to “get away” without declaring the data types of variables that you use.
Variables are identifiers for storage locations for one or more data types.
It is useful to think of a variable as the name on the outside of a mailbox, where one or more values can be stored in the mailbox.
When a data type is associated with a variable name, the mailbox can only store information of that type.
But, this is bad programming practice, so it is best to declare the “types” of all variables used in VBA.
This requirement is enforced by putting the Option Explicit statement at the top of every VBA module you develop.
22
Enforcing variable declaration
put Option Explicit statement at the top of every module or
set VBE’s Tools Options
Require Variable Declaration and VBE will do it for you!
