Visual Basic

1. Object Noun Part of the application Property Adjective Action to do something Method Verb Visual Basic Objects / Properties / Methods Attribute

2. Review of some Excel Basics Cell references of two types: A1Columns are alphabetic, rows are numeric R1C1 R number Column number B2 and R2C2 refer to the same cell Can be set by the Tools / Options menus (Note that the two methods are transposed – A1 – column first, then row R1C1 – row first, then column )

3. Formulae in Excel (R1C1 style) R1C1 is an absolute address – it does not change under copy / paste R[1]C[1] is a relative address – it does change under copy /paste And can use mixed mode: R1C[1] – R1 is absolute, C[1] is relative

4. In VBA, can use either or both A1 and R1C1 styles A1 style tends to be absolute A1 style used with the “Range” object Range(“A4”) Can refer to individual cells with the “Cells” object, which uses an R1C1 style Cells(4,1)

5. VBA Variable Types String A sequence of bytes containing characters Integer 2 byte integer Long 4 byte integer Single 4 byte real number Double 8 byte real number Variant Can hold anything (but “expensive” to use) “Object” A class of data types used by Excel/VBA

6. In Excel, cells & ranges can be named Insert / name menu These names can be used in Formulae VBA

7. Characters & Stings For Excel, anything that is not a number or a formula is a string. If want a set of digits as a string, need to enclose in quote or quotation marks. For VBA, need to define variable that will hold strings as string data types Strings are defined by enclosing the string in quote (“) marks.

8. Procedures Sub procedures return no values except by changes to parameters in the argument list. Can be run from the Macro list or by direct call. Function procedures return a value by assigning it to the name of the function. Can be invoked just like any other function: = Name (argument list)

9. For functions and sub procedures, there is a one to one correspondence between the positions of the arguments in the call to the function and the argument list of the function: The call: = Grade( 90, C40, $C$60 ) The definition: Function Grade (exam1,exam2,exam3) as String

10. Visual Basic Example -- simple Sub to name the active worksheet: Sub NameIt() Dim newname as String newname = InputBox(“Enter a name for the worksheet”) ActiveSheet.Name=newname End Sub

11. Sub Procedures Public Sub ColorList() ' Lists all the available colors in the current pallette ' Number is listed in column 2, color sample in column 3 ' List starts in row 5 Dim I As Integer Dim J As Integer Dim K As Integer J = 5 I = 2 For K = 1 To 56 Cells(J, I).Value = K Cells(J, I + 1).Interior.ColorIndex = K J = J + 1 Next K End Sub Note that ColorList is never assigned a value!

12. Function Procedures Function Grade (exam1,exam2,exam3) as String Dim Sum as Single Sum = exam1+exam2+exam3 If Sum > 95 then Grade = “A” Elseif Sum > 80 Grade = “B” Else Grade = “C” Endif End Function

13. Option Explicit Forces you to ‘Dim’ all variables Helps prevent typos Can set this as the default through the Tools/ Options menu. (Require variable declaration check box)

15. “Object” data type Many of the objects in Excel have their own data type: Dim r as Range Dim q as Worksheet Dim z as Chart About the only one we will use is the “Range” data type.

16. Visual Basic All objects have properties Most objects have methods Will work with only a few of the many objects, methods and properties To get a list of objects and properties, invoke the Object Browser (F2)

17. Program module (Sub or Function) is made up of a series of steps to accomplish a task. Five major categories of steps: A = 5 Assignment statements Conditional statements If ( A > 5) then …. Calls to other modules A = sqrt (12) For I = 1 to 6 … Next i Iteration Statements Input /Output operations (Which are really calls to other modules) Read 5, A

18. Expression evaluation inVBA Operators in priority order Left to right Operator Priority List ( ) ^ - (unary minus) * / + - Comparison Operators (>, < …) Logical Operators (NOT, AND, OR…)

19. Test = 2 + 3 ^2 > 5 AND (3-7)*2 > 6 (3-7) -4 3^2 9 -4*2 -8 9+2 11 11 > 5 True -8 > 6 False True AND FalseFalse Thus, Test has the value “False”

20. Conditional Expressions If / then / else end if Select case …. End Case

21. If (expression) then One or more expressions Else One or more expressions End If If (expression ) then (expression) Note: no End If statement for this type of If /then expression

22. Select Case Statement Like a complex If / Then / ElseIf … / EndIf Select Casetestexpression[Caseexpressionlist-n[statements-n]]...[Case Else[elsestatements]] End Select

23. Select Case Statement Testexpressioncan be any that evaluates to a numeric value or a string Expressionlistis compared to the testexpression to determine if the following statements should be executed. See “Help” for all the allowable forms Statementsare executed up to the next Case statement or the End Select statement

24. A simple example Case statements to determine a letter grade from a numeric grade. Assume the numeric grade is in the variable “grade”

25. Select Case grade Case Is >=90 ltrgrade=“A” Case Is >= 85 ltrgrade = “BA” … Case Is >= 70 ltrgrade = “C” Case Else ltrgrade=“E” End Select Note: Goes to first Case that testexpression satisfies. Is implicitly uses gradein the comparison

26. Generally speaking, anything you can do with a Select Case block, you can do with a set of If / Then / Else If … / End If statements

27. Iteration Statements Used to execute a number of statements repeatedly Two major types --- For / Next Do / Loop

28. For counter to upperlimt step size Statements Next counter For Each cell In Selection Statements cell.value= expression Next cell

29. Basic “ Do Loop” Structure Do statements Loop But we need a mechanism to stop the loop!

30. Do whileexpression_is_true Statements Loop Do Statements Loop whileexpression_is_true

31. Do Untilexpression_is_true Statements Loop Do Statements Loop Untilexpression_is_true

32. To manipulate a cell’s value, use the “value” property of the cell object: Range(“A4”).value = 20 Temp = Range(“A4”).value or Cells(4,5).value = 20 Temp = Cells(4,5).value

33. Build a simple module to set a 5 x 5 array of cells to some value In VBA IDE Insert module Insert procedure / sub Initcells Dim I as integer Dim J as integer For I = 1 to 5 For J = 1 to 5 Cells(I,J) = I*J Next J Next I Return to Excel and run macro Initcells

34. Two useful VBA functions to interact with use: InputBox displays a prompt and awaits a reply Str1 = InputBox(“prompt’) When invoked, displays the prompt and gives you a box to enter a reply, which is placed in Str1 Dim str1 as String str1 = InputBox(“Please enter your name”)

35. MsgBox displays a message and buttons to press. The function value returned is the code for the button J = MsgBox(“prompt”,buttoncodes, “ box name”) Look at ‘Help” for the button code values J is the integer value of the button code the user presses Code=MsgBox(“Are you OK”,vbOKOnly,”My Temp”)

36. Write a simple sub (called Heater) that accepts a temperature as an argument and returns a string to indicate what to do with the heater. Also have MsgBox display a message to the use if the heater status is changed. The logic for the heater control is as follows: If the temperature is less than 65, “Turn heater on” If the temperature is greater than75, “Turn heater off” Otherwise, “Leave heater alone” Function Heater (Temp) As string Code End function