Use a named range instead of explicit literal cell references What if I want to add a new student? adding in the middle of a named range adding to the end of a named range use a final blank row? would the average be correct? This is another instance of “indirection” 25 Sub Sort_By_IDnumber2() Range(" theData3 ").Select Selection.Sort _ Key1 := Range(" theData3 ").Range("A1"), _ Order1 := xlAscending, _ Header := xlGuess, _ OrderCustom := 1, _ MatchCase := False, _ Orientation := xlTopToBottom Range("A1").Select End Sub Doing Sort Marks Right
CS 200 Fall 2015 Excel Scripting 26 Variables and declarations Assignment statements Sequential execution Loops initialization termination test changing the loop control variable(s) If–then–else statements Subroutines and functions parameters (aka arguments) Debugging interactive source-level debugging Relevant Programming Concepts from CS 100
CS 200 Fall 2015 Excel Scripting Things 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 Excel Excel’s debugger Conveniently triggering macro execution Cell references in worksheets in macros 27
CS 200 Fall 2015 Excel Scripting The Assignment For This Week 28 Given the raw data, duplicate the functionality of this spreadsheet
CS 200 Fall 2015 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”?) 29
CS 200 Fall 2015 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 30 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 Fall 2015 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
You've reached the end of your free preview.
Want to read all 44 pages?
- Spring '14
- Visual Basic for Applications, scripting, Visual Basic Editor