Spreadsheet Macros Visual Basic for Applications - PowerPoint PPT Presentation

1 / 11
About This Presentation
Title:

Spreadsheet Macros Visual Basic for Applications

Description:

You may immediately STOP recording or use the recording to create part ... Autoshapes/Basic Shapes/Isosceles Triangle. Drag triangle to position. Stop Recording ... – PowerPoint PPT presentation

Number of Views:112
Avg rating:3.0/5.0
Slides: 12
Provided by: marvin2
Category:

less

Transcript and Presenter's Notes

Title: 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
Write a Comment
User Comments (0)
About PowerShow.com