Excel VBA Programming Dummies Outline and Notes
Jason Xu
II: How VBA Works with Excel
Chapter 3: Working in the Visual Basic Editor * To continue a single line of code from one line to the next, end the first line with a space followed by an underscore (_) * Option Explicit, inserted at the beginning of a VBA Module, forces variables to be defined (Should always use).
Chapter 4: Introducing the Excel Object Model * A collection is a group of objects of the same type. * Workbooks: A collection of all currently open Workbook objects * Worksheets: A collection of all Worksheet objects contained in a particular Workbook object * Sheets: A collection of all sheets (regardless of their type) contained in a particular Workbook object. Contains both Worksheets and Charts. * Etc…. (collections are plural) * Referring to Objects * To reference a single object from a collection, you put the object’s name or index number in parentheses after the name of the collection: * Worksheets(“Sheet1”) * Excel won’t be able to identify the object if the quotation marks are omitted. * If Sheet1 if the first (or only) worksheet in the collection, you can also use the following reference: * Worksheets(1) * If referring to an object by its index number, use a plain number without quotation marks. * Navigating through the hierarchy * Application is at top of the hierarchy * Move down the hierarchy and connect each object with the dot (.) operator * Application.Workbooks(“Book1.xlsx”).Worksheets(1).Range(“A1”).Value * Above is a fully qualified reference. Exact/Most specific reference possible is given, and nothing is left to the imagination. * Simplifying object references: * Application object is always assumed so it can be omitted.