1 / 50

CST-273-01 Microsoft VBA R. Juhl

CST-273-01 Microsoft VBA R. Juhl. VBA Excel Objects. VBA Excel Objects. Introduce some VBA and Excel Specific Programming Concepts and Capabilities Using programming tools called objects We will look at: Objects VBA Collection Objects The Object Browser The Application Object

filipina-pawlak
Download Presentation

CST-273-01 Microsoft VBA R. Juhl

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CST-273-01Microsoft VBAR. Juhl VBA Excel Objects

  2. VBA Excel Objects • Introduce some VBA and Excel Specific Programming Concepts and Capabilities • Using programming tools called objects • We will look at: • Objects • VBA Collection Objects • The Object Browser • The Application Object • Workbook and Window Objects • The Range Object • With / End With and For / Each

  3. VBA Excel Objects • The Project Battle Cell • Uses Excel’s • top level objects • most common objects • Object browser • Relies heavily on using the following objects • Application • Workbook • Worksheet • Range

  4. VBA Excel Objects • About VBA and Object Oriented Programming • VBA shares many of the same concepts as genuine object-oriented languages • Existence of objects • Tools used to manipulate these objects • Tools include • Properties • Events • Methods

  5. VBA Excel Objects • In the real world • Requirements for an Object Oriented Language are strict • Must allow programmers to build new classes • Object definitions • Objects created from a new class must support inheritance • Inheritance refers to the ability of one class to inherit from another • A new class (derived class) will have all the members of the inherited class (parent class) • VBA allows programmers to define new classes but does not support inheritance • VBA is “Object Based”

  6. VBA Excel Objects • Objects Defined • Think of objects as: • Separate computer programs with specific and common functions that are available for repeated use • Dynamic / easily manipulated in code with the various parameters used to define them

  7. VBA Excel Objects • Object Analogy • Objects are equated with nouns i.e. Bicycle • Objects can be described • Properties • Size • 26” • Color • Blue • Type • Ten-speed

  8. VBA Excel Objects - Object Analogy • A bicycle can perform various actions • Methods • Move straight • Turn • Stop • A bicycle can be built out of other objects • Frame • Wheels • Properties • Diameter • Material • Titanium • Aluminum • Method(s) • Roll • Handlebars • Pedals

  9. VBA Excel Objects • The Wheel Object could be composed of other Objects • Tire • Material • Organic polymers • Rim • Material • Aluminum • Titanium • Spoke • Diameter • Material • Aluminum • Titanium

  10. VBA Excel Objects • Objects have events • External stimulus of an object • Rider initiates • HandleBarTurn() event • Invokes • TurnBike() method • Written coded response to the user’s stimulus • Events allow for a much more user interactive experience • Between the program and user

  11. VBA Excel Objects • VBA Collection Objects • A group or collection of the same object types • A bicycle collection is analogous to going into a bicycle shop • Different sizes, colors, and types • Collection objects allow you to work with objects as a group • In VBA collection objects are typically denoted with the plural form of the object • Can you think of a VBA collection object ?

  12. VBA Excel Objects • VBA Collection Objects • A group or collection of the same object types • A bicycle collection is analogous to going into a bicycle shop • Different sizes, colors, and types • Collection objects allow you to work with objects as a group • In VBA collection objects are typically denoted with the plural form of the object • Can you think of a VBA collection object ? • Workbooks collection consists of all open workbook objects

  13. VBA Excel Objects

  14. VBA Excel Objects • To select a workbook object • Create an excel file with three workbooks • Insert a standard Module • Create a procedure to test selecting a workbook object using • Workbooks(2).Activate • This line of code uses the Workbooks property of the Application object to return a single Workbook object from the Workbooks collection and could have been written as • Application.Workbooks(2).Activate • It uses the Activate() method to select the object

  15. VBA Excel Objects • Syntax when addressing VBA objects • object.property • object.method • Example • Application.ActiveSheet.Range("A1").Value = 1 • Application.ActiveSheet.Range(“A1”).Font.Bold = True • Are of the form • object.property.property.property.property • Because • Activesheet, Range, (“A1”), and Font represent properties • Bold is a Boolean property of the Font object

  16. VBA Excel Objects • Use can select a workbook unambiguously if you know the name • Workbooks(“Book2”).Activate • If you need to step through several objects in a collection use a loop and a loop / index variable For I = 1 to 3 If Workbooks(I).Saved Then Workbooks(I).Close Next I

  17. VBA Excel Objects • Other VBA Collection Objects include • Worksheets • Each Workbook object contains 3 Worksheet objects • Belong to the Worksheets Collection Object • There are 3 Worksheet collection Objects in the previous example • Windows • Charts

  18. VBA Excel Objects • The Object Browser • The VBAIDE Object Browser allows you to view all available objects and their • Properties • Methods • Events • Can be used to view • Excel’s Object Model • All procedures / constants from your current project • To open select • View  Object Browser • Press F2 • To Use • Select the library to view or select All Libraries

  19. VBA Excel Objects

  20. VBA Excel Objects • An Object Library is a collection of objects provided by a specific application • Notice libraries for • Excel • Objects specific to Excel • Office • Objects common to all Office Applications • Word • PowerPoint • Excel • VBA • Add VBA Objects to the current Excel Project • VBA Project • You can use Excel Help as well to view the OM

  21. VBA Excel Objects

  22. VBA Excel Objects • After selecting the Excel library you will see • A List of all availabel objects • In the bottem left window of the OB • The window is labled Classes • A class is an object definition • A class definition is used to create an instance of the object it defines • Instantiating the object • When you see the term “class” think “object” • The class list represents all of the object available to you to use in your program

  23. VBA Excel Objects • Select an object from the list to view the available • Properties • Methods • Events • The right side window is labled “Members” • These items belong to or are members of the object selected • Selecting an item in the members list displays information about that item in the bottom window • Property • Method • Constant • To learn more about an Object or Member • Select it and press F1

  24. VBA Excel Objects

  25. VBA Excel Objects • More on the Excel object model can be found at: • http://msdn.microsoft.com/en-us/library/wss56bz7(VS.80).aspx • Think of the hierarchy as a path to the object of interest • Like the path to a file • Consider the example: • How do we insert the string below, into: • “VBA Can Be Used To Customize An Excel Application” • cell A4 • Sheet2 • Book2

  26. VBA Excel Objects • Range(“A4”).Value = “VBA Can Be Used To Customize An Excel Application” • This will insert the text into the “current” or “active” worksheet • May not be the target • Workbooks(“Book2”).Activate • To find the next object in the desired path: • Workbooks(“Book2”).Worksheets(“Sheet2”).Activate • To set the value of Cell A4 use the Range property Workbooks("book2").Worksheets("sheet2").Range("A4").Value = "VBA Can Be Used To …"

  27. VBA Excel Objects – Cells Property

  28. VBA Excel Objects • Using the Cell property has the same effect… Workbooks("book2").Worksheets("sheet2").Cells(4, “A”).Value = "VBA Can Be Used To ……“ • The above code uses the Worksheets property of the Workbook object to return a Worksheet object from the Worksheets collection object • Since the Worksheet object is lower in the object hierarchy than the Workbook object, it follows it in the line of code. • The Range property is found in the list of members for the Worksheets object • The Range property returns a Range object that represents one or more cells in a continuous block on a Worksheet

  29. VBA Excel Objects • You must work though the entire object hierarchy to set a property • only if each object referenced in the code needs to be identified out of a collection of objects+ • If there is only one Workbook object open then • Sheets(“sheet2”).Range(“A4”).Value = “ ……. “ • Works just as well • This code will execute if multiple Workbooks are open • Only the currently active Workbook is effected • Each Object qualifier is necessary only as long as its needed to identify one specific object out of several posibilities.

  30. VBA Excel Objects • Top Level Objects • The Application Object • Top-level in Excels Object Model • Represents the Entirety of the Microsoft Excel Application • Its unique and usually not needed to reference • Some Uses follow • Setting the height and width properties • Application.Width = 600 • Application.Height= 450

  31. VBA Excel Objects • Setting the formula bar on or off • Application.DisplayformulaBar = <True / False> • In general you use the Application Object to set • The appearance of the Excel Window • The overall behaivior of Excel • Application.Calculation = xlManual • Turns off autocalculate • Application.Calculation = xlCalculationAutomatic • Turns on autocalculate • Application.EditDirectlyInCell = False • Enables or Disables editing in cells • Application.DefaultFilePath = “C:\My Documents” • Set default file location

  32. VBA Excel Objects • The Application object can be used with ScreenUpdating and WorksheetFunction properties • Application.ScreenUpdating = false • Turn screen updating off to speed up your macro code. You won't be able to see what the macro is doing, but it will run faster. • Remember to set the ScreenUpdating property back to True when your macro ends. • Range(“A11”) = Application.WorksheetFunction.Sum(Range(“A1:A10”)) • Envoke a formula and copy via Range • If you only need to set properties of lower-level objects, then the Application object qualifier is not needed. • ActiveCell.Formula = “=Sum(A1:A10)” • To view all the Application object’s properties, methods, and events use the Object Browser

  33. VBA Excel Objects • The Workbook and Window Objects • Window objects refer to instances of windows within either: • The same workbook • The application • Within the Excel Application the windows collection object contains all Windows objects currently open • All Workbook objects • Copies of any Workbook objects • The window objects are indexed according to their layering i.e. A three workbook application • Application.Windows(2).Activate • Because Book2 is in the center of a three Windows Object its index would be changed to 1 usng the windows collection object • Different from accessing Book2 using the Workbooks collection object • Workbooks are indexed in the order of their creation after the value of 1, which is reserved for the selected, or top level Workbook object.

  34. VBA Excel Objects • You may use the NewWindow() method of either the window or workbook object in your code • Application.Windows(1).NewWindow • When a new window is created, the caption in the title bar from the original window (active) is concatenated with a colon and an index number i.e. • If Book3 is currently active then the original Book3 window title changes to Book3: 1 and the new window changes to Book3: 2 • These captions can be changed using the caption property of the window Object • Application.Windows(3).Activate • Application.Windows(1).NewWindow • Application.Windows("Book3:2").Caption = "Book3:2 is a new window“ • Don’t confuse new Window with new Workbook • Application.Workbooks(“Book3:2”).Activate • Will Fail • Creating a new workbook also creates a new window but the opposite is not true • If a new window is created through the Window Menu in Excel or the NewWindow() method in VBA then the window does not belong to the Workbooks collection object • It belongs to the windows collection object

  35. VBA Excel Objects

  36. VBA Excel Objects • Access the new window with Public Sub test() Application.Windows(3).Activate Application.Windows(1).NewWindow Application.Windows("Book3:2").Caption = "Book3:2 is new" 'Application.Workbooks("Book3:2").Activate Application.Windows("Book3:2 is new").Activate End Sub • Note : The windows have to be LAYERED with 3 ON THE BOTTOM for this to work

  37. VBA Excel Objects • To Add WorkBooks use can use a loop Public Sub AddWorkbooks() Dim i As Integer For i = 1 To 3 Workbooks.Add Next i End Sub • Add the following code after the Next i Workbooks(Workbooks.count).Activate • It will activate the last workbook to be opened in excel • Count returns the nuber of open workbooks and is used to index the last workbook added

  38. VBA Excel Objects • The Range Object • One example of a VBA collection object that does not use the plural form of an existing object for its name • It represents a collection of cells in a worksheet (even if the collection contains one cell) • The following lines operate on the currently selected worksheet • Range(“A1”).Value=“Column A” • Range(“A1:G1”).Columns.Value=AutoFit • Range(“A1:C1”, “E1:F1”).Font.Bold = True With / End Statement Range(“A1:N1”).Select With Selection.Font .Bold = True .Name = “Arial” .Size = 18 End With With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter End With • The selection property of the Window Object is used to return a Range Object From which the Font Property returns a Font Object associated with the selected Range

  39. VBA Excel Objects

  40. VBA Excel Objects

  41. VBA Excel Objects

  42. VBA Excel Objects

  43. VBA Excel Objects

  44. VBA Excel Objects

  45. VBA Excel Objects

  46. VBA Excel Objects

  47. VBA Excel Objects

  48. VBA Excel Objects

  49. VBA Excel Objects

  50. VBA Excel Objects

More Related