Eg selection instead of how do you find out about

eg Selection ... instead of ...
How do you find out about objects, properties and methods?
31
Sub Sort_By_Mark2()
Range ("B3:D14").Select
Selection .Sort _
Key1 := Range ("D3"), _
Order1 := xlDescending, _
Header := xlGuess, _
OrderCustom := 1, _
MatchCase := False, _
Orientation := xlTopToBottom
Range ("A1").Select
End Sub

CS 200 Winter 2019
Excel Scripting
32
This dot marks a “breakpoint”
Watching Your Macros Run

CS 200 Winter 2019
Excel Scripting
33
Debugging (The Big Picture)

CS 200 Winter 2019
Excel Scripting
Using the Debugger
34
Set a breakpoint
by clicking in the left margin
to halt a macro when it gets to that statement
“clear” the breakpoint by clicking again in the left margin
Use
? variableName <return> in the Immediate Window to display the value of the variable
Use
Debug > Step Into (does dive into functions or subroutines)
Debug > Step Over (doesn’t dive into functions or subroutines)
to execute one statement and stop again
Use
Run > Continue
to turn the macro loose
The Visual Basic Editor’s Debug toolbar has buttons for all of these
(View > Toolbars... > Debug)

CS 200 Winter 2019
Excel Scripting
To
run a macro
delete a macro
edit a macro
And via the Options... button
you can attach a macro to a key
(or key combination) on the keyboard
35
Putting It Together: Tools > Macro > Macros...

CS 200 Winter 2019
Excel Scripting
Attaching a Macro to a Spreadsheet Button
Draw a button after selecting on the Forms toolbar
Ctrl-click on the button to open the Assign Macro dialog
Select and OK the desired macro
36

CS 200 Winter 2019
Excel Scripting
Record
•
to learn how to do something
•
to build a macro faster
Edit recorded macros to customize / elaborate them
Use on-line help and the object browser
to find object names, properties, & methods that you need
Experiment
•
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
'
)
37
Strategies For Working With Macros

CS 200 Winter 2019
Excel Scripting
The Assignment For This Week
38
Given the raw data, duplicate the functionality of this spreadsheet