Title: Spreadsheet Macros Visual Basic for Applications
1Spreadsheet Macros(Visual Basic for Applications)
- Slides to accompany an in-class demo of
spreadsheet technique - Macro Programming
2Creating 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.
3Creating 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
4Creating 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
5Creating 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
6Example 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
7To 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.
8Visual 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)
9Visual 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
10Objects 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
11Relative 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