Introduction to VBA
Review – Last class and in the homework, we worked with Objects
What do you remember about objects? Imbedding, Inheritance, Methods
Object-Orientated Programming (OOP) follows this same philosophy
Review – What do we use code for? Repetitive tasks, User Friendliness
In Programming, we create Objects to perform a specific tasks and follow specific rules. These objects are then imbedded in other objects to perform a greater task.
When you register for courses, you are working with an Object which is imbedded in a larger object which works with classroom scheduling, Instructors, add codes
In order to survive in business, you must think of processes as objects
All programming languages represent objects in slightly different ways.
Microsoft Excel uses Objects – Objects designed for a specific purpose
In VBA with Excel, the main Object is called an Application (UW Copy Centers)
Applications contain Workbook objects which contain Worksheet objects
An entire Application may contain more than one Workbook
A Workbook may contain more than one Worksheet
It is important to group like objects on the same level together to form collections of similar objects
Look at the Garden Glory Project page 28 in the Database Concepts textbook
The Application to be built is a service tool to be used by the gardeners, to electronically track services performed for a customer.
Lets create a Workbook to support the Application – name it ServiceCalls
To reference this specific Object in VBA code
Application.Workbooks(“ServiceCalls.xls”)
What Worksheets do we need to create to support the Application? Break into Objects
Service locations; Available Services; Actual Services Performed
Create Worksheets – Customers; Services; Performance
To reference this specific Worksheet Objects in VBA code
Application.Workbooks(“ServiceCalls.xls”).Worksheets(“Customers”)
If there is only one Workbook in the Application, you can use
Worksheets(“Customers”)
Objects are listed in their hierarchical order: Application, Workbook, Worksheet
Worksheets(“Customers”).Range(“A1”) (next level – specific cell)
Remember Objects have Properties. Ie. Values, Font Size, Background Color
Worksheets(“Customers”).Range(“A1”).Value
As well as, Methods. Action to be performed. Ie. Clear Contents, Offset, Add Comments
Worksheets(“Customers”).Range(“A1”).ClearContents
Before demo Excel Objects need to discuss basic programming terminology, …
Insert a New Module – Modules are where code is written.
Code is used to manipulate Objects and perform Methods (Object Behavior)
Modules can be used by one or many different Objects
Modules are stored at the Workbook level.
Modules can be Exported and Imported for easy reuse.
Within VBA there are four types of code (learn more in IS320)
Sub Procedures – instructions used to perform an action
Functions – Calculation to obtain a value (Sum records; Current Value)
Properties – Used to manipulate properties within an Object
Declarations – Data Typing a Variable for use within code
What is a Variable? A value that changes all the time (varies)
Are you always going to perform the same service?
Add 3 services – Include Description and Price
What is a Data Type? Characters, Numbers, Dates, Yes/No,
Data Type for Description? Price? Numbers only
*** More on Variables and Data Types next Class
Working with VBA – Class demos from the textbook on the laptop
A new Workbook has standard defaults – cell width, fonts, 3 tabs
Change a Property at the Workbook level and it is inherited to all the sheets and cells
Format…Style…Font bold / size 14
In some cases, we can change what has been inherited from above
The workbook font size can be overridden by formatting the cell
Other Inheritance cannot be overridden – Security
Tools…Protection
Activating the VBE – Visual Basic Editor – Alt+F11
Review the Properties of Each Sheet vs the entire Workbook
Review code windows for each Object
The best way to learn to code is to copy code that works and watch what it does
Type the example Sub Procedure “SayHello”
Sub SayHello()
Dim Msg As String
Msg = "Is your name " & Application.UserName & "?"
Ans = MsgBox(Msg, vbYesNo)
If Ans = vbNo Then
MsgBox "Oh, never mind."
Else
MsgBox "I must be clairvoyant!"
End If
End Sub
Sub SayHello() – Declares the Procedure and the editor automatically adds the End Sub
If conditions – Most popular piece of code
Most have 2 option – What to do if True… do if False
Sometime just a test for something – If True… False do nothing
If … Then but no Else just End if
Msg is this case is a Variable – at home you will be the UserName
Ans is a good example of a Variable which changes all the time.
Variables do not have to be data type unless – Option Explicit
Structured Programming techniques – How code is written.
Most businesses follow the same techniques (similar to IS320)
- One instruction per line
- Indent within a Procedure and If Statements
- Declare Variables at the top of the Sub Procedure (Routine)
Add to the list over the next week
Using the Macro Recorder is another great way to learn to code
Excel will trace your step and then convert it to VBA code.
Record a Macro to Print Preview the Service Worksheet
Is this Macro Generic by nature? Work on all Worksheets?
Adding a Macro to a Command Button (in the UserForm Chapters of textbook)
Add the Forms toolbar to your screen; Click the command button
Draw a command button on the screen; pick the module
Trick and Command Buttons in Excel – use Right-Mouse button to select
Recording a Macro added Comment lines – these are included in Structure Programming
With Macro Recorder – Why write your own code?
The UserName code can’t be done with recorders
Sometimes, Macro Recorder adds a bunch of extra code (which can be removed)
Demo change Page Setup to Landscape – look at added code
With Selection… everything under this is a property of the Selection
DisAdvantage of having the computer code for you is extra stuff
Extra stuff has to be compiled; compiling takes time
Take out unnecessary code – macro still works
Add an If statement around the code with a question – Add a command button
Customizing the VBE Environment – All programming editors are customizable
Homework - Play with these
Record another Macro which highlights the price for the item selected
Use the Short Cut Key option when naming the Macro
Relative vs Absolute cell reference – Default is Absolute
Absolute
Range("D1").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Relative
ActiveCell.Offset(0, 3).Range("A1").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Record another one to Clear the highlighted cell – Use Short-Cut Key
ActiveCell.Offset(0, 3).Range("A1").Select
Selection.ClearFormat
The above statement assumes you use Edit…Clear
If the No Fill option was used it would look different
Difference – Highlighting requires moving to another cell
Needs to be Relative Cell Reference – Rerecord Macro
Relative is a button on the toolbar
*** Must use the arrow keys when recording Relative references
It should now work for all the services in the list
Excel is an Event Triggered program – click a button, use a short-cut key
Others – Double-click, Right-Click, … (Covered in IS320)
Other things in the Chapter not required in the course but helpful
Object Hierarchy; Commenting Objects; Ranges vs Cells
For the exam, students will need to be able to write simple VBA code, read all VBA code discussed in this class and lecture notes, and understand the Terminology listed below.
Terminology –
Application vs Workbook vs Worksheet vs Field
Properties vs Behaviors
Module vs Macro
Sub Procedures vs Functions
Variables vs Data Type
Structured Programming Techniques
Relative vs Absolute
Event Triggered
Homework #2 – Submit both the Excel spreadsheet and the Word Document answering the questions below to the grader at
Set up an Excel Application to support the Garden Glory Project (as discussed in class). Do not build an Access Database.
1)The application has 3 worksheets: Customers, Services, Performance
2)Add 5 Customers (include Customers name, address, city, type of building)
3)Add 6 service items (include service item name and price)
4)Leave Performance blank for now
5)Record a macro which Print Previews the worksheet – have it run on crtl^a
(Advanced users - attach it to 2 different command buttons on the Customer and Items work sheets)
6)Add 2 Macros for the Customers worksheet: one which highlights and one that unhighlights the Type of building for the Customers selected – use relative referencing (like demoed in class)
Use Short-Cut Keys or Command Buttons
Answer the following questions… (use the internet or optional textbook to research)
1)In your own words, What is the difference between a Function and SubRoutine?
2)In your own words, describe a situation when your would use Relative cell references?
3)When to use Absolute?
Page 1 of 7