Eg selection instead of how do you find out about

eg Selection ... instead of ...
How do you find out about objects, properties and methods?
record a macro, then highlight a method or property name & press the help key
ie if you see something and wonder about it
use the “object browser”
if you want to go looking to see, for example, if worksheets have a particular property
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 Spring 2018
Excel Scripting
The Object Browser
Click in the “Classes” or “Members” lists
type to scroll their contents automatically
click on ? to open the help window on that object or member
this is the best way to discover what members a class has
NB: “Excel” is selected in the pull-down top left
32

CS 200 Spring 2018
Excel Scripting
Online Help for the Range Method
33
You can also access help for a property or method by
•
highlighting it in a macro module and pressing the help key
Hints for the assignment!
•
The “Remarks” info about Ranges may be useful
Warning: Excel and VB
have
different Help
databases.
CS 200 Spring 2018
Excel Scripting
34
This dot marks a “breakpoint”
Watching Your Macros Run

CS 200 Spring 2018
Excel Scripting
35
Debugging (The Big Picture)
CS 200 Spring 2018
Excel Scripting
Using the Debugger
36
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 Spring 2018
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
37
Putting It Together: Tools > Macro > Macros...
CS 200 Spring 2018
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
38

CS 200 Spring 2018
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
'
)
39
Strategies For Working With Macros
CS 200 Spring 2018
Excel Scripting
The Assignment For This Week
40
Given the raw data, duplicate the functionality of this spreadsheet