06_ExcelScripting_2up.pdf - CS 200 Lecture 06 Excel...

CS 200
CS 200 Winter 2020 Excel Scripting
1
Lecture 06
Excel Scripting
CS 200 Winter 2020
Excel Scripting
Miscellaneous Notes
Abbreviations
aka Also Known As
CWS Course Web Site ()
VBE Visual Basic Editor
intra- a prefix meaning within — thus “intra-cellular” means “within the cell”
inter- a prefix meaning between — thus “inter-galactic” means “between galaxies”
For our purposes, a “macro” and a “script” are the same thing
Optional background reading
Writing Excel Macros, Chapters 3 and 4, (in library)
Excel Review (Learn)
Excel Review Exercises (Learn)
A good reference if you’re interested in learning more about Excel Scripting is
Writing Excel Macros (2/e), by Steven Roman
O’Reilly & Associates, ISBN 0-596-00359-5, © 2002
2

CS 200 Winter 2020
Excel Scripting
Administrativia
Please read and highlight the assignment and course notes before lab
Assignment 6 is due Monday February 24 at 11:59 pm
Staff suggests reading the assignment before lecture
There are hyper-text commented source files for most of the Excel macros used in this
lecture
see “Handouts > Commented Excel Macros” on the course website
Files used in this lecture are on Learn
Week 6: Application Scripting (Excel): Files for Lecture
Today
Scripting in Excel
The Copy Pearl
Remember that the CWS contains material on the pearls
Warning
We are running Office 2018, in the Mac Labs
—test Windows docs in the lab before submitting
3
CS 200 Winter 2020
Excel Scripting
Assumption
You have used a spreadsheet before
You understand a simple program/algorithm
4

CS 200 Winter 2020
Excel Scripting
Things to Think About
What are the data objects in a Spreadsheet program?
How does the interface differ from the other applications we have seen so far?
What are the efficiencies/deficiencies of the interface?
When would I use a spreadsheet?
How does a spreadsheet compare to a database?
How would macros be useful in other applications we have seen so far?
5
CS 200 Winter 2020 Pearls
The Copy Pearl
Keep copies of your old stuff around
in well-named files and folders
works better if you comment what you do
Lurk on news groups and capture examples as they come by
Check out what comes with the app
Build a list of what examples can be found where

CS 200 Winter 2020
Excel Scripting
Application Scripting in Excel
What is “(intra-)application scripting”?
a way to automate lengthy manual procedures
a way to customize / extend an application
a way to create a different interface
“programming in the small”
Recording a script
“record” a manual procedure (once)
and “play it back” many times
sort of like a player piano
record a script to learn how to do something
look up terms in the script as necessary
“anything you can do manually, you can script” — & more
Edit a recorded script
to make it more general
to eliminate unnecessary steps
to wrap a loop around it
to do something similar
7
T
CS 200 Winter 2020
Excel Scripting
Why Scripting in CS 200?