Changing the loop control variables ifthenelse

changing the loop control variable(s)
If–then–else statements
Subroutines and functions
parameters (aka arguments)
Debugging
interactive source-level debugging
Relevant Programming Concepts

CS 200 Spring 2018
Excel Scripting
Things We Still Need to Talk About To Do The Assignment
“Objects” in Excel — how to name objects on a spreadsheet
Excel’s debugger
Conveniently triggering macro execution
Cell references
in worksheets
in macros
25
CS 200 Spring 2018
Excel Scripting
The Assignment For This Week
26
Given the raw data, duplicate the functionality of this spreadsheet

CS 200 Spring 2018
Excel Scripting
This Week’s Assignment is “Layered”
Trivial formulas
eg for Actual Balance
Simple formulas
eg for Statement Balance or Next Transaction Number
Not quite so simple formulas
eg for the CD Charge cell for US$ purchases
Simple macros
eg for scrolling, sorting, or filtering
Not quite so simple macros
eg for making new entries
Note that you can create all of these macros by recording them, you do not need
to use VBA
27
CS 200 Spring 2018
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”?)
28

CS 200 Spring 2018
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
29
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 Spring 2018
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
— just like built-in Excel functions
eg C4, ForEx, and 2 in VLOOKUP( C4, ForEx, 2 )
“Member” is programmer-speak for both properties and methods
All objects of a particular kind are a “class” of objects
30

CS 200 Spring 2018
Excel Scripting
Especially Useful Properties
Application.ActiveWorkbook
you can just write ActiveWorkbook
for the frontmost workbook
Application.ActiveSheet
you can just write ActiveSheet
for the worksheet whose tab was last clicked
Application.ActiveCell
you can just write ActiveCell
for the currently selected cell
And usually you can omit Application.ActiveSheet
eg Range ... instead of Application.ActiveSheet.Range...