1 / 45

Working with object variables

Working with object variables. Visual Basic for Applications. Objectives. Explain how properties and variables are stored in memory Create an object variable using the Dim statement Select the appropriate name and data type for an object variable

marinel
Download Presentation

Working with object variables

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. Working with object variables Visual Basic for Applications

  2. Objectives • Explain how properties and variables are stored in memory • Create an object variable using the Dim statement • Select the appropriate name and data type for an object variable • Use the Set statement to assign an object’s address to an object variable

  3. Objectives • Excel: • Insert a row, value, and formula into a worksheet • Format an Excel worksheet • Word: • Assign a theme to a Word document • Create a hyperlink in a Word document • Access: • Open an Access report • Order the records in an Access report

  4. Concept Lesson:Using object variables • Every object has a set of properties • Values of those properties control the object’s appearance and behavior • VBA stores each property, along with its corresponding value in the internal memory • VBA automatically reserves memory cells for the properties of every object it creates • Memory cells have value, name and data type

  5. Variables • A programmer also can reserve memory cells for storing information • The memory cells reserved by the programmer are called variables • Variables created by the programmer must have • Name • data type

  6. Variables • Numeric variables • Store only numbers • String variables • Store numbers, letters, and special characters • Object variables • Store memory address of the object • Make procedures easier to write and understand • Help to improve the performance of a procedure • Faster access to the property of an object: Application.Presentations(1).Slides(1).Name

  7. An Object Variable and the Object to Which It Refers

  8. Instructions to Display the First Slide Object’s Name and Layout Properties Exhibit 3-2: The instructions to display the first Slide object’s Name and Layout properties

  9. Instructions to Display the First Slide Object’s Name and Layout Properties • Without an object variable • Application.Presentations(1).Slides(1). Name • Application.Presentations(1).Slides(1). Layout • With object variable named sldFirst • sldFirst.Name • sldFirst.Layout

  10. Object Variables • Without an object variable: • VBA needs to locate the appropriate Application, Presentation, and Slide objects in memory for each property • With an object variable: • VBA uses to the memory address of the object for each property • The sldFirst object variable contains an address that points directly to the location of the Slide object in memory

  11. Scope of Variables • A variable’s scope refers to which procedures in the project can use the variable • There are three scoping levels: • procedure-level • private module-level (available only to procedures in that module) • public module-level (available to all procedures in all modules) • Module-level variables and constants are defined in the Declarations section of a module • Module-level constants are private by default.

  12. Reserving a procedure-level variable • A procedure-level variable is declared, within a procedure, and it can be used only by the procedure in which it is declared • Use Dim statement to reserve a procedure-level variable • The syntax of the Dim statement is Dim variablenameAs datatype where variablename represents the name of the variable and datatype represents its data type • Using Dim statement in a procedure VBA reserves a memory cell called variablename whose data type is datatype

  13. Selecting the Appropriate Data Type and Name for an Object Variable • Need to assign a data type to each variable • If an object variable will point to a • Document object, then the object variable’s data type willbe Document • Worksheet object, then the object variable’s data type will be Worksheet • You also must assign a name to the variable • The variable name should help you remember • data type • purpose of the variable

  14. Data Types Corresponding to Some of the Objects Available in the Microsoft Office Applications Exhibit 3-3: The data types corresponding to some of the objects available in Microsoft Office applications

  15. Naming Conventions • Following a naming convention is a valuable practice. • RVBA Naming Conventions • http://www.xoc.net/standards/rvbanc.asp • Leszynski Naming Conventions • http://en.wikipedia.org/wiki/Leszynski_naming_convention • Microsoft Consulting Services Naming Conventions for Visual Basic • http://support.microsoft.com/?kbid=110264 • Object Hungarian Notation Naming Conventions for VB • http://support.microsoft.com/kb/q173738 • Our Notation: Define your variable beginning with three-character ID that identifies it’s data type

  16. Data Types and Their Three-Character IDs Exhibit 3-4: The data types and their three-character IDs

  17. Rules and Examples for Variable Names Exhibit 3-5: The rules and examples for variable names

  18. Selecting the Appropriate Data Type and Name for an Object Variable • VBA automatically initializes the object variable to the keyword Nothing Exhibit 3-6: Some examples of the Dim statement used to declare object variables

  19. Using the Set Statement • You use the Set statement to assign the address of an object to an object variable • The syntax of the Set statement is • SetobjectVariableName=object • objectVariableNameis the name of an object variable • objectis the object whose address you want to store in the variable • The Set statement locates the object in memory and then stores the object’s address in the memory cell whose name is objectVariableName

  20. Examples of the Set Statement • Set sldFirst = Application.Presentations(1).Slides(1) • Set docSales = Application.Documents(1) • Set shtPay = Application.Workbooks(1).Workshees(1) • Set rptBonus = Application.Reports(“bonus”) • Set rngVBA = Application.Documents(“vba.doc”).Sentences(4)

  21. Summary To create a procedure-level object variable, and then assign an address to it: • Use the Dim statement to create the variable • Use the Set statement to assign the address of an object to an object variable

  22. Excel: Creating the FormatWorksheet Macro Procedure Exhibit 3-8: The sales workbook showing First Quarter worksheet

  23. Worksheet Format Desired by the District Sales Manager Exhibit 3-9: The worksheet format desired by the district manager

  24. Worksheet Format Desired by the Regional Sales Manager Exhibit 3-10: The worksheet format required by regional sales manager

  25. Creating the FormatWorksheet Macro Procedure Exhibit 3-11: The pseudocode for the FormatWorksheet procedure

  26. Inserting Rows Into a Worksheet • You insert a row into a worksheet using the syntax: • worksheetObject.Rows(rowNumber).Insert • worksheetObject is the name of a Worksheet object • rowNumber is the row above which the new row will be inserted

  27. Entering a Value and Formula Into aRange Object • Recall that a row, a column, or a group of contiguous or noncontiguous cells in a worksheet also are Excel Range objects

  28. Entering a Formula Into a Range Object • You need to enter the following formulas in cells B13 through D13 in the worksheet: B13 formula = SUM (B4:B12) C13 formula = SUM (C4:C12) D13 formula = SUM (D4:D12) Exhibit 3-13: The two ways of entering the SUM formulas into cells B13 through D13

  29. Formatting and previewing Worksheet Objects • A collection of predesigned worksheet formats is availablein Excel Exhibit 3-14: The names of some of the Excel predesigned formats

  30. Word Lesson:Creating the FormatPromo Macro Procedure • A theme in Microsoft Word is a set of unified design elements and color schemes for • Background images • Bullets • Fonts • Horizontal lines • Other document elements

  31. Promo Document

  32. Example of a Formatted Document Exhibit 3-16: The formatted document

  33. Pseudocode for theFormatPromo Procedure Exhibit 3-17: The pseudocode for the FormatPromo procedure

  34. Partial Listing of Themes Availablein Microsoft Word • The default location for theme data folders is C:\Program Files\Common Files\Microsoft Shared\Themes • You must use the folder name for the theme

  35. Applying a Theme to a Document • You use the ApplyTheme method to apply a theme to a document • The syntax of the ApplyTheme method is: • documentObject.ApplyTheme Name:=themeName • documentObject is the name of a Document object • themeName is the name of a theme • Example: • docVBA.applythemename:=“Breeze"

  36. ApplyTheme in Word 2007

  37. Changing the Document’s Font Size • You use the following syntax to change the size of the font used in the document: documentObject.Content.Font.Size=font Size • Example: docVBA.content.font.size = l2

  38. Adding a Hyperlink to a Document • You use the Add method of the Document object’s Hyperlinks collection to add a hyperlink to a document • The syntax of the Add method is documentObject.Hyperlinks.Add Anchor:=rangeObject, Address:=linkAddress • Example: docVBA.Hyperlinks.Add _ Anchor:=docVBA.Sentences(1),Address:="http://www.Oakton.edu"

  39. Hyperlinks.Add in Word 2010

  40. Access Lesson:Creating and using object variables in Access • Open Professor Martinez’s database, which is located in the • Student Data\Tut03\Access folder • View the StudentReport

  41. Creating the DisplayByGrade Procedure Exhibit 3-19: The pseudocode for the DisplayByGrade procedure

  42. Opening an Access Report • You use the OpenReport method of the DoCmd object to open an Access report • The syntax of the OpenReport method is: • DoCmd.OpenReport Reportname:=reportName, View:=viewName • where reportName is the name of a report • viewName is the name of a view • Example • DoCmd.OpenReport Reportname:=“Students”

  43. DoCmd.OpenReport in Access 2010

  44. Valid View Names for the OpenReport Method’s View Argument Exhibit 3-20: The valid view names for the OpenReport method’s View argument

  45. Ordering the Records in a Report • To change the order of the records that appear in a report, you need first to set the Report object’s OrderByOn property to the Boolean value True

More Related