Spreadsheet Macros Visual Basic for Applications

1
Spreadsheet Macros(Visual Basic for Applications)

• Slides to accompany an in-class demo of
  spreadsheet technique
• Macro Programming

2
Creating and Editingan initial Macro

• 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

• 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

• 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

• 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
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

• 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

• 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").C
ells(i, 5).Value i Next i Writes 12 values
into worksheet cells in E1E12, (column 5
column E)
9
Visual Basic Language Elements

• Conditional Test (IF)
• IF (condition) THEN (True clause) ELSE (False
  clause) ENDIF
• Example IF xlt5 THEN x5 ELSE x0
• Example IF xlt5 THEN x5
• ENDIF is used for multiple line clauses
• Conditional operators
• Less than lt, greater than gt
• Less than or equal lt, greater than or equal gt
• Not equal ltgt

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

• 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(a1c3).Copy
• Worksheet and Range are objects, Copy is a method

11
Relative references in Macros

• 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