1 / 11

Spreadsheet Macros (Visual Basic for Applications)

Spreadsheet Macros (Visual Basic for Applications). Slides to accompany an in-class demo of spreadsheet technique: Macro Programming. Creating and Editing an initial Macro. Select TOOLS/MACRO/RECORD This will create a VBA (Visual BASIC for Applications) subroutine shell

portia-mcmahon
Download Presentation

Spreadsheet Macros (Visual Basic for Applications)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related