changing the loop control variable(s) If–then–else statements Subroutines and functions parameters (aka arguments) Debugging interactive source-level debugging Relevant Programming Concepts
CS 200 Spring 2018 Excel Scripting Things We Still Need to Talk About To Do The Assignment “Objects” in Excel — how to name objects on a spreadsheet Excel’s debugger Conveniently triggering macro execution Cell references in worksheets in macros 25 CS 200 Spring 2018 Excel Scripting The Assignment For This Week 26 Given the raw data, duplicate the functionality of this spreadsheet
CS 200 Spring 2018 Excel Scripting This Week’s Assignment is “Layered” Trivial formulas eg for Actual Balance Simple formulas eg for Statement Balance or Next Transaction Number Not quite so simple formulas eg for the CD Charge cell for US$ purchases Simple macros eg for scrolling, sorting, or filtering Not quite so simple macros eg for making new entries Note that you can create all of these macros by recording them, you do not need to use VBA 27 CS 200 Spring 2018 Excel Scripting Strategy For The Assignment Start with the simple stuff and proceed in stages towards the more difficult Test as you go Make a copy of your spreadsheet at each successful stage eg, when you have the simple formulas working, ie, squirrel a copy of that away in case you totally muck up the next step and want to start over on it (remember “backups”?) 28
CS 200 Spring 2018 Excel Scripting “Objects” in Excel Macros Just about everything you see in a workbook is an “object” There is a natural hierarchy to objects You can have multiple instances of everything except the Application These are Excel’s “application data objects” (remember the Model pearl?) A “module” is for holding macros (aka “scripts” ) think of it as a document containing multiple macro definitions in the VBE: Insert > Module and type or ... a module is created automatically when you record a macro 29 Application Workbook Worksheet Range Range Chart Module Cell Cell Cell See 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 Spring 2018 Excel Scripting Objects Objects 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 object Objects have “methods” like “ClearContents,” “Sort,” “Worksheets” that cause something to be done to or with the object a “method” is just another kind of built-in function or subroutine that does something to or with the object it’s attached to methods often have “parameters” (aka “arguments” ) — information they need — just like built-in Excel functions eg C4, ForEx, and 2 in VLOOKUP( C4, ForEx, 2 ) “Member” is programmer-speak for both properties and methods All objects of a particular kind are a “class” of objects 30
CS 200 Spring 2018 Excel Scripting Especially Useful Properties Application.ActiveWorkbook you can just write ActiveWorkbook for the frontmost workbook Application.ActiveSheet you can just write ActiveSheet for the worksheet whose tab was last clicked Application.ActiveCell you can just write ActiveCell for the currently selected cell And usually you can omit Application.ActiveSheet eg Range ... instead of Application.ActiveSheet.Range...
You've reached the end of your free preview.
Want to read all 20 pages?
- Spring '14
- Visual Basic for Applications, scripting