Spreadsheet Macros (Visual Basic for Applications)

1. BAE 1012 Spreadsheet Macros(Visual Basic for Applications) Slides to accompany an in-class demo of spreadsheet technique: Macro Programming

2. Creating and Editingan initial Macro BAE 1012 • Select TOOLS/MACRO/RECORD • This will create a VBA (Visual BASIC for Applications) subroutine shell • Be sure to set the shortcut for executing the macro • You may immediately STOP recording or use the recording to create part or all of a macro • To Edit an existing Macro, select TOOLS/MACRO/MACROS… • Select the Macro by Name • The VBA Programming environment will be selected. • You may fill in VBA programming between the start and end.

3. Creating a Macro toInsert an OSU Logo BAE 1012 • Record a Macro • Set the recorder to “relative” • Later when run the macro will produce results relative to the starting position • ctrl-i as shortcut • OSU_LOGO as Name • Type and Format text • Type: OSU into A1 • Format as: • Goudy Old Style • 36 pt. • Bold • Color = orange • Cell background = Black • Test macro by typing ctl-i

4. Creating a Macro toInsert an OSU Logo BAE 1012 • Draw Triangle in “O” • Enable Drawing Toolbar VIEW/TOOLBARS/Drawing • Select and place Triangle • Autoshapes/Basic Shapes/Isosceles Triangle • Drag triangle to position • Stop Recording • Test the Macro • Modify or Edit to correct errors

5. Creating a Macro toAdd a Function to Excel BAE 1012 • Functions • Definition: • A program that when executed returns a value • Example • The “sum” function when inserted in a cell returns the value of the specified sum • User defined functions • Spreadsheets typically allow user defined functions to be created • Procedure • Open the Visual Basic Editor • Select INSERT/PROCEDURE • Select Function • Enter the function Name • Edit the function • To test, insert the function into a cell in a spreadsheet

6. Example Function BAE 1012 Input variables Public Function headloss(f, l, d, v) ' ' Function Headloss ' This function computes headloss in a pipe given the following parameters ' f - Friction Factor ' l - Pipe length ' d - Pipe diameter ' v - Fluid velocity ' Units should be consistent (all metric or all english and compatible headloss = f * (l / d) * ((v * v) / (2 * 9.81)) End Function Variable must be the same as function name

7. To Access or Write Cell Values BAE 1012 • Within a procedure or function • To write “x” into a cell, use the following syntax: • Worksheets("Sheet1").Cells(i, j).Value = x • Where Sheet1 is the worksheet title • i is the row of the cell • j is the column of the cell • To read a cell value into “x”, use the following syntax: • x = Worksheets("Sheet1").Cells(i, j).Value • Where Sheet1 is the worksheet title • i is the row of the cell • j is the column of the cell • In the above example • Worksheet is an object • .Cells is a property • .Value is a property of a property • The properties of an object can be set.

8. Visual Basic Language Elements BAE 1012 • Loop (For/Next) • For i = 1 to 100 step 4 • Begins a loop which increments i by 4 each time • 1 is the initial value for i • 100 is the final value for i • Next i • Ends the loop of which i is the index • i is tested at the end and if greater than or equal to 100 the loop ends Example: For i = 1 To 12 Worksheets("Sheet1").Cells(i, 5).Value = i Next i Writes 12 values into worksheet cells in E1:E12, (column 5 = column E)

9. Visual Basic Language Elements BAE 1012 • Conditional Test (IF) • IF (condition) THEN (True clause) ELSE (False clause) ENDIF • Example: IF x<5 THEN x=5 ELSE x=0 • Example: IF x<5 THEN x=5 • ENDIF is used for multiple line clauses • Conditional operators: • Less than <, greater than > • Less than or equal <=, greater than or equal >= • Not equal <> For i = 2 To 12 IF i = 6 THEN Worksheets("Sheet1").Cells(i, 5).Value = "SIX” Else Worksheets("Sheet1").Cells(i, 5).Value = I ENDIF Next i

10. Objects and Properties BAE 1012 • Elements that may be referenced are: • Objects (Cells, Worksheets, ChartTitle, etc.) • Can contain properties, methods, other objects • Properties (Value, Size, etc.) • Methods (Clear, Copy, etc.) • Property Reference example: • Font.Size = 9 - Font is an object, size is a property • Method example • Worksheet(“sheet1”).Range(“a1:c3”).Copy • Worksheet and Range are objects, Copy is a method

11. Relative references in Macros BAE 1012 • Regarding relative or absolute in macro recording • The references to movement recorded within a macro can be absolute cell references or relative to starting position • Relative or absolute is toggled with the icon button provided with the start/stop button for macro recording