1 / 9

Visual Basic for Applications: Object Variables

Visual Basic for Applications: Object Variables. Craig K. Tyran Western Washington University Winter 2009. Agenda. Object Variables Naming conventions Variable declarations The “Set” statement Examples In-class Exercise. A. Object Variables. A variable that represents an object

dane-chandler
Download Presentation

Visual Basic for Applications: 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. Visual Basic for Applications: Object Variables Craig K. Tyran Western Washington University Winter 2009

  2. Agenda • Object Variables • Naming conventions • Variable declarations • The “Set” statement • Examples • In-class Exercise

  3. A. Object Variables • A variable that represents an object • Why use object variables??? • Can significantly simplify code • Can make code execute more quickly • Example (MS Excel) • Two ways to assign a value to a cell • Worksheets(“Sheet1”).Range(“B2”).Value = 6 or • rngPayment.Value = 6 • Which statement is simpler to write/read? • Which statement is more efficient to execute?

  4. A. Object Variables (cont.) 3. Example (cont.) • Assigning an object variable • Dim rngPayment as Range • Set rngPayment = Worksheets(“Sheet1”).Range(“B2”) • Using an object variable • rngPayment.Value = 6

  5. A. Object Variables (cont.) 4. As indicated in example, object variables need to be declared and assigned 5. Common objects and variable data typesin Excel ObjectData Type • Workbook Workbook • Worksheet Worksheet • Range Range Note: Easy to remember these data types!!!

  6. A. Object Variables (cont.) • Examples of object variable declaration statements • Dim wkbSales as Workbook • Dim shtQuarter1 as Worksheet • Dim rngTotal as Range • Quick questions: • What does “Dim” stand for? • What purpose do the three letter prefixes serve? Naming conventions!!!! Please use them. • wkb  workbook • sht  sheet • rng  range

  7. A. Object Variables (cont.) 7. Examples of object variable “set” statements (and examples of use) • Dim wkbSales as Workbook Set wkbSales = Workbooks(“Sales.xlsm”) • e.g., wkbSales.Close • Dim shtQuarter1 as Worksheet Set shtQuarter1 = Workbooks(“Sales.xlsm”). Worksheets(“First Quarter”) • e.g.,shtQuarter1.Range(“D4”).Formula= “=Sum(“D1:D3”)” • Dim rngTotals as Range Set rngTotals = Workbooks(“Sales.xlsm”).Worksheets(“First Quarter”).Range(“A10:E10”) • e.g., rngTotals.ClearContents

  8. A. Object Variables (cont.) 8. What is wrong with the following statements? • Dim shtInventory as Workbook shtInventory = Workbooks(“Inventory.xlsm”) • Dim Results as Range Set Results = Workbooks(“Research.docx”). Worksheet(“Findings”).Range(“C12”)

  9. F. In-Class Exercise • Activities • Create short “macro” programs • Enhance a “What If” DSS • Gain practice with declaring, setting, and using object variables!!! • Submit exercise via e-mail

More Related