Visual Basic for Applications(VBA)¤

Goals:

² Familiarity & experience with solving problems al-gorithmically.

² Familiarity with Visual Basic for Applications (VBA).VBA/Excel dialect.

² Empower you to build useful applications and tolearn more on your own.

² Get you comfortable in a programming environ-ment.

Note: But you have to do a lot of work on your own!

Lectures are to provide you a map. You must make the

trip yourself. (cf., VBATutor.xls)

¤File: misnotes-vba-slides-s1999.tex.

1

File: misnotes-vba-slides.TEX. Created: February 13,1998. Modi¯ed version of vb1-19970228.ppt. Modi¯ed:19980329, 19980326, 19980214. See also: VBATu-tor.xls.

File: misnotes-vba-slides-s1999.tex, modi¯ed February

8, 1999.

1-1

Goals for lecture 1.

Assuming:

² Famliarity with the basic concepts of macros in Ex-cel (which are written in VBA).

² Know how to record and run a macro, and examineand edit its code in the Visual Basic Editor.

Then:

1. Use the Visual Basic Editor to create a simple VBAprogram (Sub) and call it for execution from a but-ton on a worksheet.

2. Use the Visual Basic Editor to create a simple VBAprogram (Function) and call it for execution from acell in a worksheet.

3. Introduce the core structure of VBA programs.

4. Introduce program variables

(cf., Worksheets("Lecture1") code module Lecture1)

2

See VBATutor.xls, Worksheets("Lecture1") and codemodule Lecture1.

2-1

Macros

² Programs in VBA. Role of VB and VBA

for Microsoft.

² What is VBAnExcel good for?

{ Assembling, \gluing together," applications inMS O±ce. (Larger issue:\component-based applications.")

{ Utility (small job) programming, e.g., for datapreparation and manipulation, for programmingthe interface, . . .

{ For learning how to program.

{ For prototype programming.

{ For learning about modern software concepts(e.g., OOP) and development environments (nowa good one in Excel for VBA).

3

More on macros

² Recording macros

{ Tools ) Macro ) Record New Macro. . .

{ Stop, relative addressing

{ Running the macro: Tools ) Macro )Macros. . .

{ Viewing the macro: Tools ) Macro )Visual Basic Editor Alt+F11

3-1

Recorded macro, called Bob

Sub Bob()

'

' Bob Macro

' Macro recorded 2/13/98 by Steven O. Kimbrough

'

'

Range("B3:C4").Select

Selection.Copy

Range("A1").Select

ActiveSheet.Paste

Application.CutCopyMode = False

Range("A1").Select

End Sub

4

1. This is VBA code written by the macro recorder.You can edit it just as you would code you hadwritten.

2. Later we'll see details of VBA syntax, etc. For nownote that, e.g., Range("B3:C4") is an object whichhas the method Select. Range("B3:C4").Select means\Select the object Range("B3:C4"). But note howreadable the code is.

3. Application is also an object (here, Excel itself)but CutCopyMode is not a method. Rather, it is aproperty, which as it happens can be true or false.Here we are setting this property of Appication toFalse.

4. In general, think in terms of: Objects which havemethods (which do things) and properties (whichcan have di®erent values). Related to object-orientedprogramming: close but not quite. Still, a perva-sive and very useful metaphor in Microsoft O±ceproducts.

5. Note comment signs.

4-1

Basics of Visual Basic for Applications

² VB, VBA, VBAnExcel, VBAnWord,. . .

² Macro modules

² Subs

² Functions

² Variables & declaring them

² Structure of a VBAnExcel application

5

A simple Sub

1. Tools ) Macro ) Visual Basic Editor

2. Insert ) Module (Not: Class Module)

3. View ) Properties Window

4. Then write some code:

Sub HelloWorld()

MsgBox "Hello world!"

End Sub

² Use the VB Help menu item to search on

MsgBox. (And use it generally and often!)

² Subs do things, but do not return values.

Functions do things, and do return values.

6

Note:

² The editor is helpful. Always type lower case andmake the editor do the conversion to upper case|agood error-prevention strategy.

² Run the macro from the Editor (put the cursor inthe code and click the Run button on the menubar)

² Run the macro from Excel (Tools ) Macro )Macros. . . )

6-1

Now, make it run from Excel. . . . . .

1. View ) Toolbars ) Control Toolbox

2. Select and draw a button.

3. Right-click with the button selected ) Prop-

erties. Set the properties as desired, then

close the Property Window.

4. Right-click the button selected ) View Code

5. Add a call to HelloWord:

Private Sub cmdHelloWold_Click()

HelloWorld

End Sub

6. Return to Excel, exit design mode, and

click the button.

7

A simple Function

1. Tools ) Macro ) Visual Basic Editor

2. Select a code module, e.g., the one with

Sub HelloWord.

3. Add code and save work:

Function dblUtility(X, Hi, Lo, Risk) As Double

dblUtility = ((X - Lo) / (Hi - Lo)) ^ Risk

End Function

4. Return to Excel and use this function in a

cell, just as any built-in Excel function.

8

Try these procedures in a code module:

Function dblCubed(X As Double) As Double

dblCubed = X ^ 3

End Function

Sub CubeMe()

Dim dblDaNumber As Double

dblDaNumber = _

InputBox("What number " & _

"would you like to cube?")

dblDaNumber = dblCubed(dblDaNumber)

MsgBox "And the answer is " & dblDaNumber

End Sub

² Dim dblDaNumber As Double declares (Dimensions)

the program variable dblDaNumber as type

Double (precision °oating point).

² for line continuations. & for string con-

catenation.

9

VBAnExcel Programs.

Are collections of Subs and Functions (and

Declarations). Typically they:

1. Are called (started) from Excel.

2. Read in information. From, e.g., work-

sheets, dialog boxes, ¯les, databases.

3. Store this information in variables.

4. Computationally manipulate the variables.

5. Write out the information. To., e.g., work-

sheets, dialog boxes, ¯les, databases.

Basic concepts at hand, details now follow.

10

Recall: VBAnExcel Programs.

Are collections of Subs and Functions (and

Declarations). Typically they:

1. Are called (started) from Excel.

2. Read in information. From, e.g., work-

sheets, dialog boxes, ¯les, databases.

3. Store this information in variables.

4. Computationally manipulate the variables.

5. Write out the information. To., e.g., work-

sheets, dialog boxes, ¯les, databases.

11

Variables

Expressions (think of names) in programs that

can hold di®erent values at di®erent times.

² X, Hi, Lo, Risk in the dblUtility function.

Option Explicit

Sub FirstVariableExample()

Dim I As Integer

Dim MyFirstVariable As Integer

'Note: Dim I, MyFirstVariable as Integer

' leaves I an Integer and MyFirstVariable

' as a Variant. Thanks, Bill!

MyFirstVariable = 3

For I = 1 To MyFirstVariable

MsgBox "Showing and counting: " & I

Next I

End Sub

12

Variables have data types

² Types:

{ Integer, Long

{ Single, Double

{ Currency

{ Date

{ String

{ Variant

² Why?

² Look them up in the VBA online help.

13

Programs (in general, I emphasize):

² Declare variables

² Put values into variables

² Make calculations with variables

² Store results of calculations in variables

Also (especially in VBA) they:

² Manipulate application objects: call meth-

ods, read and alter properties

14

Declaring variables (in VBA)

² Variables should always be declared.

{ Why?

{ Variant if not declared.

{ Use Option Explicit in the declarations sectionto force declaration of variables.

² Variables have scope. Why?

² Declaring variables

{ Dim in a procedure. Scope: that proce-

dure.

{ Private (or Dim) in the declarations sec-

tion of a module. Scope: that module.

{ Public in the declarations secion of a

module. Scope: entire application.

15

Value(s) of MySecondVariable?

Option Explicit

Private MySecondVariable As Integer

Sub PublicExample1()

MsgBox "We're in PublicExample1 " & _

"and MySecondVariable = " & _

MySecondVariable

End Sub

Sub PublicExample2()

MySecondVariable = 23

MsgBox "We're in PublicExample2 " & _

"and MySecondVariable = " & _

MySecondVariable

End Sub

16

1. Run PublicExample1 using the Run button in theVBA editor. MySecondVariable = 0.

2. Run PublicExample2 using the Run button in theVBA editor. MySecondVariable = 23.

3. Run PublicExample1 using the Run button in theVBA editor. MySecondVariable = 23.

4. Now Reset and try again (MySecondVariable = 0).Discuss. Emphasize that while VBA has automat-ically initialized MySecondVariable to 0, it's a badidea to leave this to VBA. Always initialize variablesyourself!

16-1

Reading from, and writing to, a worksheet

Sub CosineHardWired()

Dim MyNumber As Double

MyNumber = _

Worksheets("Lecture2").Cells(9, 2).Value

'Note: Cells(9,2) = row 9, column 2 of the

'worksheet.

Worksheets("Lecture2").Cells(11, 2).Value = _

Cos(MyNumber)

'This also works:

'Worksheets("Lecture2").Range("B11").Value = _

'Cos(MyNumber)

'And suppose MyTestRange is defined B2:D13.

'Then this works, too:

'With Worksheets("Lecture2").Range("MyTestRange")

' .Cells(10, 1).Value = Cos(MyNumber)

'End With

End Sub

Try a nonnumber in B9. Debug. Reset button.

Later: the debugger.

17

1. Suppose a single cell has a name, Bob. thenWorksheets("Lecture2").Range("Bob").Value= Cos(MyNumber) also works.

17-1

Goals for lecture 2.

1. Say a word or two more on program vari-

ables and how to declare them.

2. Discuss and show how|in VBAnExcel|

to read and write information from and to

worksheets.

3. Brie°y introduce the Object Browser.

4. Do VBA Homework #1 (handed out to-

day) as an in-class exercise|with discus-

sion!

(cf., Worksheets("Lecture2") code module Lecture2)

18

Generalizing on reading & writing

² Reading & writing the Excel worksheet are

special cases of getting and setting

object properties.

² So far, the Value property of a particular

object, a particular cell.

² Why not, say, the color of a cell?

Sub SimpleShowColor()

Dim MyTempHolder As Variant

MyTempHolder = _

Worksheets(2).Cells(14, 2).Interior.ColorIndex

MsgBox "The interior color of B14 is " _

& MyTempHolder & "."

End Sub

LOTS of objects and properties in Excel.

19

Generalizing on reading & writing (con't.)

Sub GetTheWorksheetName()

Dim Temp As String

Temp = Range("CellBob").Worksheet.Name

MsgBox "The name of the worksheet in which " & _

"the range CellBob resides is " & Temp

End Sub

Sub RenameMeTheWorksheet()

Dim Temp As String

Temp = _

InputBox("New name for this worksheet?")

ActiveSheet.Name = Temp

End Sub

20

The object browser, F2 in the VBA Editor

² Displays, and lets you explore, all available

objects, methods, and properties. Nifty!

member = (method _ property)

² Right-click on a member. Your code. Ex-

cel's code.

² Example: Look in Excel, Range class, Cells

member (a property). Call for help.

21

The Object Browser (con't.)

A word to the wise:

Be ever vigilant. No program docu-

mentation is ever complete|or com-

pletely accurate|and the VBA on-line

Help is no exception. Some of the de-

scriptions are just plain wrong. Some

of the code samples don't work. Any

many, many \gotchas" are left unex-

plored. Still, if you take the documen-

tation with a small grain of salt, you'll

¯nd an enourmous amount of impor-

tant information there. And the easi-

est way to get to the information is via

the Object Browser.

|from Excel 97 Annoyances, p. 205.

22

Using VBA subs and functions in other work-

books

Question: Can you use VBA macros from an-

other workbook (without copying them)?

Answer: Yes. Be sure the other workbook is

open (running). Preface your macro calls with

the name of the other workbook followed by a

bang (!).

For example, if the other workbook is

theotherworkbook.xls and you want to call a

function there, named theotherfunction and

that function takes one argument, an integer,

then you might write

=theotherworkbook.xls!theotherfunction(17)

in one of the cells in your workbook.

23

And now . . .

Take a look at VBA Homework #1, handed

out today.

24