VBA in Excel

Document Sample
VBA in Excel Powered By Docstoc
					    VBA in Excel

                   Walter Milner




Sep-05 Slide:1
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:2
 VBA in Excel
Getting started
      View..Toolbars..Control




 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
                    methods
                  • 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
                          object
                        • 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
                          happens

                          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)
  Next
  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

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:20
posted:2/8/2012
language:Latin
pages:17