vba excel n.
Skip this Video
Loading SlideShow in 5 Seconds..
VBA - Excel PowerPoint Presentation
Download Presentation
VBA - Excel

VBA - Excel

562 Vues Download Presentation
Télécharger la présentation

VBA - Excel

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. VBA - Excel • VBA is Visual Basic for Applications • The goal is to demonstrate how VBA can be used to leverage the power of Excel • VBA syntax and usage • the Excel VB programming environment • the Excel object model • an application

  2. VBA - Excel • What advantage is there in using VBA • extend Excel – new functions • makes it possible to use the Execl GUI environment • makes it possible to respond to events (mouse, …) • makes Windows programming API accessible • Excel can be used to control Automation servers (other software components that expose an API through COM) • by understanding how to use the Excel object model with VBA it is a small step to using Excel as an Automation server (controlled by other program)

  3. VBA - Excel • In order to run VBA code your security settings must be properly set • Tools | Macro | Security… • At least Medium security must be set – each macro will require user verification to run • Signed code can be run in all cases

  4. VBA – The Basics • Data types • Integer 2 byte integer • Long 4 byte integer • Single 4 byte floating point • Double 8 byte floating point • Currency 8 byte real • String upto 64K characters • Byte 1 byte • Boolean 2 byte true or false • Date 8 bytes • Object 4 bytes – an object reference • Variant 16 bytes + 1 byte / character

  5. VBA – The Basics • The variant data type is special – a variant can hold any type of data • A variable declared as variant (the default) can hold anything • The actual type of the data is kept in the data • It adds flexibility but at a cost – it requires more processing at compute time to determine what it is and how to handle it

  6. VBA – The Basics • Variables • must start with a letter • can contain _ and numbers • cannot exceed 255 characters in length • Within a procedure declare a variable using • If a variable is not declared it will be created when used, the type will be Variant • Use Option Explicit in the declarations section to require declaration of variables • VBA variables have scope restrictions • variables declared in a procedure are local to that procedure • variables declared in a module can be public or private Dim variable Dim variable As type

  7. VBA – The Basics • String variables • The first form is variable length • The second form is limited to 50 characters • the variable will be space filled if string is < 50 characters • the string will be truncated if the contents are > 50 characters • the Trim and RTrim functions are useful for working with fixed length strings • Boolean variables contain either True or False Dim variable As String Dim variable As String * 50

  8. VBA – The Basics • The Object type is used to store the address (a reference) of an object • this form can be used for any object • this is referred to as late-binding, the object types are checked at runtime (slower) • The declaration of a specific object is • this form will only store Excel Worksheet objects, an attempt to put anything else into it will result in an error • this is referred to as early-binding, the object types are checked at compile time (faster) Dim variable As Object Dim variable As Worksheet

  9. VBA – The Basics • Arrays are declared using • Arrays can be multidimensional • The lower bound starts at zero • can explicitly specify lower bound • can use Option Base command to reset to something other than 0 • The last form above is a dynamic array – it must be dimensioned using ReDim before it can be used • Use ReDim Preserve to retain any existing entries in array - only the upper bound of array can be changed Dim A (1 To 10) As Double Dim B (1 To 10, 1 To 10) As Double Dim C (4,4,4) As Integer Dim D () As Double Option Base 1

  10. VBA – The Basics • Constants are declared using • Constants have the same scope limitations as variables Const pi = 3.14159 Const pi As Double = 3.14159

  11. VBA – The Basics • User defined data types • can only be defined in the declarations section of a Module • can be Public or Private in scope • Declare variable with this type • Referencing fields Public Type SystemInfo CPU As Variant Memory As Long ColorBits As Integer Cost As Currency PurchaseDate As Date End Type Dim MySystem As SystemInfo MySystem.CPU = “Pentium” If MySystem.PurchaseDate > #1/1/2006# Then … End If

  12. VBA – The Basics • Watch out for • it is equivalent to Dim a, b, c As Integer Dim a As Variant Dim b As Variant Dim c As Integer

  13. VBA – The Basics • Objects • VBA can use pre-defined objects – such as intrinsic Excel objects • VBA can create user-defined objects – Class Modules • Declaring a variable to contain an object • the first form declares that the variable will contain a reference to an object of the named class • the second form declares the variable then creates an instance of the class • To instantiate a class Dim variable As class Dim variable As New class Set variable = New class

  14. VBA – The Basics • Objects • To declare a variable that will refer to an instance of the Excel Worksheet class • To put a reference into it • This fragment will print the name of the worksheet “Sheet1” Dim ws1 As Worksheet Set ws1 = Worksheets(“Sheet1”) Dim ws1 As Worksheet Set ws1 = Worksheets("sheet1") Debug.Print ws1.Name

  15. VBA – The Basics • Objects - Collections • There is a special form of objects known as Collections • They contain references to other objects and collections • It is the mechanism by which the object hierarchy is defined • By convention, collection names are usually plural • Workbooks – list of Workbook objects • Worksheets – list of Worksheet objects • Range – list of objects that represent cells, columns, rows • The following example iterates through Workbooks collection For Each ws In Worksheets Debug.Print ws.Name Next

  16. VBA – The Basics • Statements • VBA implements common programming statements • logical statements • looping statements • expressions

  17. VBA – The Basics • Logical statements • The If Then Else statement is the basic logic test If a>10 Then … End If If a>10 Then … Else … End If If a>10 Then … ElseIf a<0 Then … Else … End If

  18. VBA – The Basics • Logical statements • The Select statement can be used to replace a multi-way if statement Select Case expression Case expr1 … Case expr2 … Case Else … End Select

  19. VBA – The Basics • Loop statements • Various Do loop forms Do Until expr … Loop Do While expr … Loop Do … Loop Until expr Do … Loop While expr

  20. VBA – The Basics • Loop statements • A common For loop For i=1 To 10 Debug.print i Next i For i=1 To 10 Step 2 Debug.print i Next i

  21. VBA – The Basics • Loop statements • Another For loop • Commonly used to iterate through collections For Each element In group … Next element For Each ws In Worksheets Debug.Print ws.Name Next

  22. VBA – The Basics • Procedures • Procedures in VBA are either Macros or Functions • a macro does not return a value • a function will return a value • Property functions (Get and Let) are used in Class Modules to provide access to private properties Sub Name() … End Sub Function Name() As Double … End Sub

  23. VBA – The Basics • Dealing with runtime errors • The On Error statement will trap errors • The error name is a label in the code • In the error code a Resume statement will cause the statement that caused the error to be executed again • In the error code a Resume Next statement will restart execution on the statement after the one that caused the error … On Error GoTo label … On Error GoTo check … check: …

  24. VBA – Programming Environment • Excel (all Office components) have a VBA programming environment, VBIDE • It consists of • the Visual Basic Editor • an Object Browser • debugging support • These are accessed from the Excel menu • There is also a set of CHM files that document the components and object models of Office (for version 10) • the Excel file is named VBAXL10.CHM • XLMAIN10.CHM is the main help file for Excel and contains VBAXL10.CHM within it • each version of Office has its own set of files •

  25. VBA – Programming Environment • To start the Visual Basic Editor from Excel select • Tools | Macro | Visual Basic Editor

  26. VBA – Programming Environment • Once in the VB Editor – to start the Object Browser select • View | Object Browser

  27. VBA – Programming Environment • In the Object Browser to select only the Excel objects use the pull down menu at the upper left

  28. VBA – Programming Environment • To view information on the Worksheet object and its Name property

  29. VBA – Programming Environment • The usual way to create code is to double-click on one of the Worksheet or Workbook objects in the Project Explorer and enter the code.

  30. VBA – Programming Environment • By using the menu item Insert it is possible to add other types of procedure containers • forms (GUI) • modules for library-like procedures • class modules for defining classes

  31. VBA – Excel Objects • Excel exposes all of its functionality through objects • These are directly accessible to the VBA programmer • They are also visible to other applications through COM Automation • matlab • scripting languages • other Office components

  32. VBA – Excel Objects • The top level object is the Workbook. • A workbook corresponds to an open XLS file • Each Workbook object is kept in the Workbooks collection • To open a workbook file use • Dim wb = Workbooks.Open(“filename) • To close a workbook use the Close method on the workbook object or the Workbooks entry • Other methods for use with Workbooks are Save and SaveAs

  33. VBA – Excel Objects • The Worksheet corresponds to a Sheet • Each Worksheet object is kept in the Worksheets collection • To add a single new Worksheet • Dim ws = Worksheets.Add(count:=1) • ws.Visible = False will hide the sheet • ws.Delete will delete the sheet • Worksheets(“Sheet1”).Delete will delete a sheet

  34. VBA – Excel Objects • The Range object (collection) corresponds to a set of cells • To set a value into a cell • Worksheets(“Sheet1”).Range(“A5”).Value = 3.14159

  35. VBA – An Example • An example found on the Internet •

  36. VBA – An Example • A view from the Visual Basic Editor