Subroutines and functionsparameters (aka arguments)Debugginginteractive source-level debuggingRelevant Programming Concepts from CS 100Wednesday, June 18, 2014
CS 200 Spring 2014Excel ScriptingThings We Still Need to Talk About To Do The Assignment“Objects” in Excel — how to name objects on a spreadsheet& the Object Browser, which documents them“Named Parameters” in ExcelExcel’s debuggerConveniently triggering macro executionCell referencesin worksheetsin macros24Wednesday, June 18, 2014
CS 200 Spring 2014Excel ScriptingThe Assignment For This Week25Given the raw data, duplicate the functionality of this spreadsheetWednesday, June 18, 2014
CS 200 Spring 2014Excel 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 macro26ApplicationWorkbookWorksheetRangeRangeChartModuleCellCellCellSee Chapter 5 of The Microsoft Excel Visual Basic User’s Guideon “Working with Objects in Visual Basic” (pp 65–84),which is reprinted in the course notes, for more on objects in Excel.Wednesday, June 18, 2014
CS 200 Spring 2014Excel 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 objects27Wednesday, June 18, 2014
CS 200 Spring 2014Excel ScriptingReferring to an object or propertyReferring to an object or propertyEG: Application.Worksheets("Visa") _.Range("Stmt").Font.Name = "Helvetica"HereApplication is an objectWorksheets() is a method (that returns a Worksheet object)Range() is a method (that returns a Range object)Font is a property (whose value is a Font object)Name is a property (a string)The () surround a method’s parameters — information the method needs to do its thing.
You've reached the end of your free preview.
Want to read all 50 pages?
Visual Basic for Applications, scripting language, scripting