VBA in Excel Walter Milner Sep05 Slide VBA

VBA in Excel Walter Milner Sep-05 Slide: VBA in Excel

Introduction • • • VBA = Visual Basic for Applications Enables end-user programming In MS Office applications Formulae and macros OK for simple actions, but. . Advantages over formulae and macros: – Multiple nested if is easy – Loops easy – Debugging easy Sep-05 Slide: VBA in Excel

Getting started View. . Toolbars. . Control Sep-05 Slide: VBA in Excel

Add a button Click the button Drag on sheet to create one Sep-05 Slide: VBA in Excel

Format it Right click on button Select properties Set as required Note distinction between name and caption Sep-05 Slide: VBA in Excel

Program it Right click button Select View code: Sep-05 Slide: VBA in Excel

Test it On Control Toolbox, click set square This toggles run/design Click the button Sep-05 Slide: VBA in Excel

Referring to cells Private Sub Command. Button 1_Click() Dim x As Integer Dim y As Integer Dim z As Integer Dim result As Integer x = Cells(1, 2). Value y = Cells(2, 2). Value z = Cells(3, 2). Value result = x * y + z Cells(4, 2). Value = result End Sub Sep-05 Slide: VBA in Excel

The VBA Excel Object model • Hierarchy of classes • Use to refer to things in an Excel application • Very large number of classes, properties and methods • Have to use on-screen help for reference Sep-05 Slide: VBA in Excel

The VBA Excel Object model • Object is a thing • Property is some property of an object • Method is something the object can be told to do • Event is something that can happen to the object • Collection is a set of related objects • Enumeration is action returning complete set of things (eg all fonts on system) Sep-05 Slide: VBA in Excel

Example object - Application Sep-05 Slide: VBA in Excel

Example properties of Application Program a button which sets a value for this property and see what happens Try it in a loop for a bizarre effect Sep-05 Slide: VBA in Excel

Example method Program a button which calls the findfile method of the application Sep-05 Slide: VBA in Excel

Example collection - Display the count property of the worksheets collection in a msgbox to show many sheets there are in the workbook Sep-05 Slide: VBA in Excel

Using For Each. . Next in a collection Private Sub Command. Button 4_Click() Dim w As Worksheet For Each w In Application. Worksheets Msg. Box (w. Name) Next End Sub Sep-05 Slide: VBA in Excel

The Range. Selection property of the Active. Window This is a Range object So it has the properties of a Range object Use them to program a button which displays the total of the numbers in the cells selected Sep-05 Slide: VBA in Excel

2 d array exercise Use the Range. Selection property to program a button which does a vertical flip of selected cells Sep-05 Slide: VBA in Excel