Ch 7 - Introduction to Visual Basic for Applications (VBA)

Ch 7 - Introduction to Visual Basic for Applications (VBA)

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