eg Selection... instead of ...How do you find out about objects, properties and methods?31Sub Sort_By_Mark2()Range("B3:D14").SelectSelection.Sort _Key1 := Range("D3"), _Order1 := xlDescending, _Header := xlGuess, _OrderCustom := 1, _MatchCase := False, _Orientation := xlTopToBottomRange("A1").SelectEnd Sub
CS 200 Winter 2019Excel Scripting32This dot marks a “breakpoint”Watching Your Macros Run
CS 200 Winter 2019Excel Scripting33Debugging (The Big Picture)
CS 200 Winter 2019Excel ScriptingUsing the Debugger34Set a breakpointby clicking in the left marginto halt a macro when it gets to that statement“clear” the breakpoint by clicking again in the left marginUse? variableName <return> in the Immediate Window to display the value of the variableUseDebug > Step Into(doesdive into functions or subroutines)Debug > Step Over(doesn’tdive into functions or subroutines)to execute one statement and stop againUseRun > Continueto turn the macro looseThe Visual Basic Editor’s Debug toolbar has buttons for all of these(View > Toolbars... > Debug)
CS 200 Winter 2019Excel ScriptingTorun a macrodelete a macroedit a macroAnd via the Options... buttonyou can attach a macro to a key(or key combination) on the keyboard 35Putting It Together: Tools > Macro > Macros...
CS 200 Winter 2019Excel ScriptingAttaching a Macro to a Spreadsheet ButtonDraw a button after selecting on the Forms toolbarCtrl-click on the button to open the Assign Macro dialogSelect and OK the desired macro36
CS 200 Winter 2019Excel ScriptingRecord•to learn how to do something•to build a macro fasterEdit recorded macros to customize / elaborate themUse on-line help and the object browserto find object names, properties, & methods that you needExperiment•with toy macros & documents•eg the workbooks used in lecture (see Learn Week 6: Application Scripting (Excel) / Files For Excel Scripting)Use the debugger•to understand what your macros are doing!Save everything you do, and include comments (they start with the single quote character ')37Strategies For Working With Macros
CS 200 Winter 2019Excel ScriptingThe Assignment For This Week38Given the raw data, duplicate the functionality of this spreadsheet