1 / 57

Object Variables

3. Object Variables. Visual Basic for Applications. 3. Objectives. In this tutorial, you will learn how to: 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

rowena
Download Presentation

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. 3 Object Variables Visual Basic for Applications

  2. 3 Objectives • In this tutorial, you will learn how to: • 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 • Insert a row, value, and formula into an Excel worksheet using VBA code

  3. 3 Objectives • In this tutorial, you will learn how to: • Format an Excel worksheet using VBA Code • Assign a theme to a Word document usingVBA code • Create a hyperlink in a Word document using VBA code • Open an Access report using VBA code • Order the records in an Access report usingVBA code

  4. 3 Concept Lesson:Memory Cells • Every object in a VBA-enabled application has a set of properties whose values control the object’s appearance and behavior • VBA stores each property, along with its corresponding value, inside the computer in an area called internal memory • When VBA creates an object, it reserves a group of boxes (memory cells) in which it stores information about the object

  5. 3 Illustration of Memory Cells that Store Property Values

  6. 3 How Each Object Occupies a Separate Section in Memory

  7. 3 Memory Cells • In addition to assigning both a name and a value to each property’s memory cell, VBA also assigns a data type • The data type refers to the type of data the memory cell can store • You can determine the type of data that a property's memory cell can store by viewing the property’s Help screen

  8. 3 Variables • A programmer also can reserve memory cells for storing information • The memory cells reserved by the programmer are called variables • Like all reserved memory cells, the variables that you create must have both a name and a data type

  9. 3 Variables • Numeric variables, for example, can store only numbers, while String variables can store numbers, letters, and special characters, such as the dollar sign ($) • Programmers use object variables to make procedures easier to write and understand • Object variables also help to improve the performance of a procedure by allowing the procedure to run more quickly

  10. 3 Object Variables • An object variable is a memory cell (box) that contains the address of an object in memory • The address tells VBA where the object is located • The address contained in an object variable “points” to the location of an object in memory • When VBA processes the MsgBox Prompt: =Application.Presentations(1).Slides(1).Name instruction, first it must locate the appropriate Application object in memory, then it must locate the first Presentation object within the Presentations collection, followed by the first Slide object within the Slides collection

  11. 3 An Object Variable and the Object to Which It Refers

  12. 3 Instructions to Display the First Slide Object’s Name and Layout Properties

  13. 3 Object Variables • VBA repeats this process when it executes the MsgBox Prompt:=Application.Presentations(1) .Slides(1).Layout instruction—once again having to locate the appropriate Application, Presentation, and Slide objects in memory • If you assign the Slide object’s address to an object variable named sldFirst, you can use the last two instructions shown in Figure 3-4 to display the Slide object’s Name and Layout properties

  14. 3 Object Variables • The sldFirst object variable contains an address that points directly to the location of the Slide object in memory • A variable’s scope refers to which procedures in the project can use the variable • Variables in VBA can have one of three scopes: • Procedure-level • Module-level • Public

  15. 3 Reserving a Procedure-Level Variable • A procedure-level variable is reserved, or declared, within a procedure, and it can be used only by the procedure in which it is declared • You use the VBA Dim statement to reserve a procedure-level variable • The syntax of the Dim statement is DimvariablenameAsdatatype, where variablename represents the name of the variable (memory cell) and datatype represents its data type • When VBA processes the Dim statement in a procedure, it reserves a memory cell to which it assigns variablename as the name and datatype as the data type

  16. 3 Selecting the Appropriate Data Type and Name for an Object Variable • You must assign a data type to each of the variables (memory cells) that you reserve • If an object variable will point to a Document object, then the object variable’s data type willbe Document • If an object variable will contain the address of a Worksheet object, then the object variable’s data type will be Worksheet • In addition to assigning a data type to a variable, you also must assign a name to the variable • The name should help you remember both the data type and purpose of the variable

  17. 3 Data Types Corresponding to Some of the Objects Available in the Microsoft Office Applications

  18. 3 Data Types and Their Three-Character IDs

  19. 3 Selecting the Appropriate Data Type and Name for an Object Variable • It is a common practice to type the three-character ID in lowercase and capitalize thefirst letter in the part of the name that identifies the purpose • In addition to being descriptive, the name that a programmer assigns to a variable must follow several specific rules • When you use the Dim statement to declare an object variable, VBA reserves a memory cell to which it attaches variablename as the name and datatype as the data type

  20. 3 Rules and Examples for Variable Names

  21. 3 Selecting the Appropriate Data Type and Name for an Object Variable • VBA also automatically stores the keyword Nothing in the object variable, which is referred to as initializing the variable

  22. 3 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 Set objectVariableName=object, where objectVariableName is the name of an object variable, and object is 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

  23. 3 Examples of the Set Statement

  24. 3 Summary To create a procedure-level object variable, and then assign an address to it: • Use the Dim statement to create the variable • For a listing of data types corresponding to some of the objects available in the Microsoft Office applications refer to Figure 3-5 • Use the Set statement to assign the address of an object to an object variable

  25. 3 Excel Lesson:Creating the FormatWorksheet Macro Procedure • To open Martin’s workbook, and then insert a module and a procedure, use the steps on pages 168 to 170 of the textbook

  26. 3 Add Procedure Dialog Box

  27. 3 Worksheet Format Desired by the District Sales Manager

  28. 3 Worksheet Format Desired by the Regional Sales Manager

  29. 3 Creating the FormatWorksheet Macro Procedure • Pseudocode, which is composed of short English statements, isa tool programmers use to help them plan the steps that a procedure must take in order to perform an assigned task

  30. 3 Inserting Rows Into a Worksheet • You insert a row into a worksheet using the syntax worksheetObject.Rows(rowNumber).Insert, where worksheetObject is the name of a Worksheet object and rowNumber is the number of rows above which the new row will be inserted

  31. 3 Inserting Rows Into a Worksheet • To create an object variable and then use it to enter instructions, use the steps on pages 174 and 175 of the textbook

  32. 3 Entering a Value Into aRange Object • A row, a column, or a group of contiguous or noncontiguous cells in a worksheet also are Excel Range objects • To enter the instruction that will assign “Paradise Electronics” to cell A1, usethe steps on pages 175 and 176 of the textbook

  33. 3 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) • These formulas will add the contents of their respective columns

  34. 3 Formatting a Range Object • A collection of predesigned worksheet formats is availablein Excel • To view a complete listing of the Excel predesigned formats, display the AutoFormat method’s Help screen • To enter the instruction that formats the worksheet, use thesteps on page 178 of the textbook

  35. 3 Current Status of the FormatWorksheet Procedure

  36. 3 Previewing and Printing a Worksheet Object • To complete the FormatWorksheet procedure, then save and run the procedure, use the steps listed on pages 179 to 181 of the textbook

  37. 3 First Quarter WorksheetAfter Running theFormatWorksheet Macro

  38. 3 Word Lesson:Creating the FormatPromoMacro Procedure • Begin by opening Pat’s document, which is located in the Tut03\Word folder on yourData Disk • To open Pat’s document, and then insert a module and a procedure, use the steps on pages 184 to 186 in the textbook • A theme in Microsoft Word is a set of unified design elements and color schemes for background images, bullets, fonts, horizontal lines, and other document elements

  39. 3 Promo Document

  40. 3 Add Procedure Dialog Box

  41. 3 Example of a Formatted Document

  42. 3 Pseudocode for theFormatPromo Procedure

  43. 3 Partial Listing of Themes Availablein Microsoft Word

  44. 3 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.ApplyThemeName:=themeName, where documentObject is the name of a Document object and themeName is the nameof a theme • To create an object variable named docPromo and then use it in the ApplyTheme instruction, use the steps on page 189 of the textbook

  45. 3 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 • To change the document’s font size to 14 points: 1. Type ’change document’s font size and press the Enter key 2. Type docpromo.content.font.size=14 and press the Enter key

  46. 3 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 • To convert the club’s name to a hyperlink, use the steps on pages 190 to 192 of the textbook

  47. 3 Completed FormatPromo Procedure

  48. 3 Promo Document After Running the FormatPromo Macro

  49. 3 Access Lesson:Creating theDisplayByGrade Procedure • Begin by opening Professor Martinez’s database, which is located in the Tut03\Access folder on your Data Disk • Then view the StudentReport • To open Professor Martinez’s database, then view the StudentReport report, use the steps on pages 195 and 196 of the textbook • To begin creating the DisplayByGrade procedure, use the steps on pages 196 and 197 of the textbook

  50. 3 Maximized StudentReport Report

More Related