VBA in Excel by R1c7Mq


									    VBA in Excel

                   Walter Milner

Sep-05 Slide:1
VBA in Excel

 •   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:2
 VBA in Excel
Getting started

 Sep-05 Slide:3
 VBA in Excel
Add a button

Click the button button
Drag on sheet to create one

 Sep-05 Slide:4
 VBA in Excel
Format it

    Right click on button
    Select properties
    Set as required
    Note distinction between name and caption

 Sep-05 Slide:5
 VBA in Excel
Program it

         Right click button
         Select View code:

 Sep-05 Slide:6
 VBA in Excel
Test it

  On Control Toolbox, click set square
  This toggles run/design
  Click the button

 Sep-05 Slide:7
 VBA in Excel
Referring to cells

                     Private Sub CommandButton1_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:8
 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
                  • Have to use on-screen
                    help for reference

 Sep-05 Slide:9
 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
                        • Collection is a set of
                          related objects
                        • Enumeration is action
                          returning complete set
                          of things (eg all fonts
                          on system)

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

 Sep-05 Slide:11
 VBA in Excel
Example properties of Application

                          Program a button which sets a
                          value for this property and see what

                          Try it in a loop for a bizarre effect

 Sep-05 Slide:12
 VBA in Excel
Example method

                   Program a button which calls the
                   findfile method of the application

 Sep-05 Slide:13
 VBA in Excel
Example collection -

                       Display the count property of the
                       worksheets collection in a msgbox
                       to show how many sheets there are
                       in the workbook

 Sep-05 Slide:14
 VBA in Excel
Using For Each .. Next in a collection

  Private Sub CommandButton4_Click()
  Dim w As Worksheet
  For Each w In Application.Worksheets
    MsgBox (w.Name)
  End Sub

 Sep-05 Slide:15
 VBA in Excel
The RangeSelection property of the ActiveWindow

                   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:16
 VBA in Excel
2d array exercise

 Use the RangeSelection property to program a
 button which does a vertical flip of selected cells

 Sep-05 Slide:17
 VBA in Excel

To top