Excel & Visual Basic for Applications (VBA) ‰ Scope of variables ‰ Range objects ‰ Using built-in functions, VBA’s and Excel’s ‰ Program flow: sequence, selection, repetition

1

The “SCOPE” of a variable How far is the “reach” of the variable, or from where can the variable be “seen”? 1) only within a single procedure (Sub or Function) such a variable might be called a “local” variable variable is declared using a Dim or Static statement within the procedure 2) only within the current module

a “module” variable

declared using a Dim statement before the first Sub or Function in the module 3) everywhere, in all procedures, in all modules open declared using a Public statement at the module level You’ll see how all this works through examples

2

1

Current Workbook/VBA Project Module 1

Module 2

Dim k As Integer

Public R As Single

Sub One ()

Sub Two (y) Dim j As Long

Function DoIt (x) Dim y As Double

Function howdy ()

R can be referenced anywhere in the project k can only be referenced in Module 1 y is a local variable in Function DoIt, and can’t be seen anywhere else y in Sub Two is an argument (formal parameter, dummy variable) j is a local variable in Sub Two, and can’t be seen anywhere else

3

Static, alternate to Dim Static y as Single Static is used, instead of Dim, when you want the variable to retain its value after the procedure is finished. In particular, you want the variable to have the same value the next time the procedure is run. The values stored in variables can change. The Temperature “mailbox” Temperature = 43 Temperature = 37

stores 43, until 37 is “assigned” to it, wiping out the 43, and then storing the 37

Symbolic constants can be used to store values with names, where the values should never change. Use the Const keyword. Const Rgas As Single = 8314.3 4

2

The Assignment Statement Variable = Expression Expression can be a constant value (also called a “literal”) a variable an expression involving variables, constants, operators, functions, etc. How it works? Evaluate the expression on the right of the =, then assign the result to the variable (mailbox) on the left Example:

Pressure = Pressure + 5

This is not read “Is the value in Pressure equal to the result of Pressure+5?” The answer to that would be FALSE!

VBA Operators ^ *, / \ mod +,&

Note: these two

exponentiation are reversed from the negation, unary minus Excel spreadsheet multiplication, division, integer division modulo (remainder) addition, subtraction string concatenation (joining 2 strings)

>, =, 10 Then Yval = Xval – 10 Xval = 1 End If

One-line, one-statement form: If n = 2 Then MsgBox “Yo, bubba”

Indentation shown is not req’d, but is good style. Do it.

Note: no End If req’d 15

Two-way If General form:

“If-Then-Else”

F

logical condition

else do this, if FALSE

If Then Else End If

T do this, if TRUE

Example:

This is the most common If structure used

If Switch Then MsgBox “Light is ON” Else MsgBox “Light is OFF” End If “Switch” would be a Boolean (logical) variable here, previously set to TRUE or FALSE

16

8

If Then code block 1 ElseIf Then code block 2

ElseIf Then code block n Else else code block End If F

Multi-alternative If “If-Then-ElseIf ” F

F

condition 2

condition 1

T code block 1

T code block 2

condition n

T code block n

else do this if all conditions fail

17

Multi-alternative If Example If Temperature < 0 Then MsgBox “Brrr!” ElseIf Temperature < 10 Then MsgBox “Chilly” ElseIf Temperature < 20 Then MsgBox “Comfortable” ElseIf Temperature < 30 Then MsgBox “Warm” ElseIf Temperature < 40 Then MsgBox “Whew! Hot!” Else MsgBox “Ssssss! Gimme a break!” End If

Note: the Else part is optional and doesn’t need to be there if there is no Else consequence [ Temperatures here are in Celsius! ]

18

9

Select Case Case code block 1 Case code block 2

Select-Case

which case ?

Case 1

Case 2

Case 3

[often used for “menu” alternatives]

Case Else else code block End Select

Case else

Case Else is optional

19

Example of Select-Case Select Case ChoiceLetter Case “A” Or “a” Call DoA Case “B” Or “b” Call DoB Case “C” Or “c” Call DoC Case Else Call DoNothing End Select

20

10

Repetition Structures in VBA general loop: Do . . . Loop count-controlled loop: For . . . Next General Do . . . Loop “mid-test loop” Do pre-test code block If Then Exit Do post-test code block Loop F

done looping ?

T

Example: Do x = Sqr(x) If x < 1.1 Then Exit Do x = x^1.5 21 Loop

Special Cases of the General Do . . . Loop Do – While

“pre-test loop”

Do While loop code Loop Example:

F

condition

T

Do While AOK AOK = FALSE Loop

loop code

There is no “pre-test” code, and the loop is continued on TRUE / exited on FALSE 22

11

Do – Until

“post-test loop” Do loop code Loop Until Example: loop code

F

done looping ?

T

Do i=i+1 Loop Until i > ilim

There is no “post-test” code. 23

Count-controlled Iteration For . . . Next

Y index passes limit ?

For index = start To limit Step increment loop code if Step increment Next index is left out, a step of 1 is default

index = start index = index + increment

N

loop code

Example: sum = 0 For i = 1 to n sum = sum + x(i) Next i Here, x is an array with n elements and x(i) refers to the ith element of the array

Used frequently with arrays 24

12

By using combinations of the sequence, selection and repetition structures, we can construct algorithms to accomplish a wide variety of computations required in engineering and science. Where to from here? implementing numerical methods like bisection and linear interpolation automatically with VBA working with array data types array-based numerical methods user interfaces: input boxes, message boxes and user forms

25

13