Visual Basic for Applications VBA An objectoriented programming

Visual Basic for Applications (VBA) • An object-oriented programming language – Instructions for the manipulation of objects – A structured way to provide instructions to Excel • Excel has an Object Library that defines its set of objects (e. g. workbooks, charts, ranges of cells)

Logic of Language • First part of a command identifies the object • Second part of command: – Sets a property of the object (e. g. color or font) – Takes an action on the object (e. g. copy or move) – Sets an attribute to the object (e. g. assigns a value to it using : = )

Example of an Instruction • Application. Workbooks(“Book 1. xls”). Work sheets(“Sheet 1”). Range(“A 1”). Font. Name= “Arial” • Application. Workbooks(“Book 1. xls”). Work sheets(“Sheet 1”). Range(“A 1”) is the object • Font. Name=“Arial” is the property being set

Control of VBA • A Procedure is a set of instructions that operate on a set of objects • A Function is a procedure that returns a value as a result • A Subroutine is a procedure that can be run or used by another macro

VBA Toolbar (Excel 2003)

Developer Tab (Excel 2007)

Blue. Cell Macro • • • Sub bluecell() ' ' bluecell Macro ' Macro recorded 10/12/2002 by LMU ' ' With Selection. Interior. Color. Index = 8. Pattern = xl. Solid. Pattern. Color. Index = xl. Automatic End With End Sub

Running Macros • • Run Macro option Shortcut Keys: [Ctrl][Shift] letter Command Buttons Custom Toolbar

Running Solver through a Macro • Solver dialog box must have been opened at least once in current Excel session (i. e. Solver must be currently activated in Excel) • Solver. Solve User. Finish: =True • Tools Reference option in VBA must have Solver selected

Debugging VBA Code • When VBA encounters a problem, it will highlight line in yellow. • Use [f 8] key to step through code • Use the Reset icon button (square) at top of VBA to exit step-through mode and troubleshoot code or Excel/VBA settings. Cannot retry macro until yellow highlight is gone.