CS 200 Winter 2019Excel ScriptingThe Assignment For This Week26Given the raw data, duplicate the functionality of this spreadsheet
CS 200 Winter 2019Excel ScriptingThis Week’s Assignment is “Layered”Trivial formulaseg for Actual BalanceSimple formulaseg for Statement Balance or Next Transaction NumberNot quite so simple formulaseg for the CD Charge cell for US$ purchasesSimple macroseg for scrolling, sorting, or filteringNot quite so simple macroseg for making new entriesNote that you can create all of these macros by recording them, you do not needto use VBA27
CS 200 Winter 2019Excel ScriptingStrategy For The AssignmentStart with the simple stuffand proceed in stages towards the more difficultTest as you goMake a copy of your spreadsheet at each successful stageeg, when you have the simple formulas working,ie, squirrel a copy of that awayin case you totally muck up the next stepand want to start over on it(remember “backups”?)28
CS 200 Winter 2019Excel Scripting“Objects” in Excel MacrosJust about everything you see in a workbook is an “object”There is a natural hierarchy to objectsYou can have multiple instancesof everything except the ApplicationThese are Excel’s “application data objects”(remember the Model pearl?)A “module” is for holding macros (aka “scripts”)think of it as a documentcontaining multiple macro definitionsin the VBE: Insert > Module and typeor ... a module is created automaticallywhen you record a macro29ApplicationWorkbookWorksheetRangeRangeChartModuleCellCellCellSee Chapter 5 of The Microsoft Excel Visual Basic User’s Guide on “Working with Objects in Visual Basic” (pp 65–84), which is reprinted in the course notes, for more on objects in Excel.
CS 200 Winter 2019Excel ScriptingObjectsObjects have “properties” like “Color,” “Value,” “Font”that you can read or set in macros sometimes a property is just a value (eg 2)sometimes a property is another objectObjects have “methods” like “ClearContents,” “Sort,” “Worksheets”that cause something to be done to or with the objecta “method” is just another kind of built-in function or subroutinethat does something to or with the object it’s attached tomethods often have “parameters” (aka “arguments”)— information they need— just like built-in Excel functionseg C4, ForEx, and 2 in VLOOKUP( C4, ForEx, 2 )“Member” is programmer-speak for both properties and methodsAll objects of a particular kind are a “class” of objects30
CS 200 Winter 2019Excel ScriptingEspecially Useful PropertiesApplication.ActiveWorkbookyou can just write ActiveWorkbookfor the frontmost workbookApplication.ActiveSheetyou can just write ActiveSheetfor the worksheet whose tab was last clickedApplication.ActiveCellyou can just write ActiveCellfor the currently selected cellAnd usually you can omit Application.ActiveSheeteg Range... instead of Application.ActiveSheet.Range...