Use a named range instead of explicit literal cell

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