06_ExcelScripting.pdf - CS 200 Lecture 06 Excel Scripting CS 200 Spring 2020 1 02 \u2013 Styles Miscellaneous Notes Abbreviations aka Also Known As CWS

# 06_ExcelScripting.pdf - CS 200 Lecture 06 Excel Scripting...

• 36

This preview shows page 1 - 8 out of 36 pages.

CS 200 CS 200 Spring 2020 02 – Styles 1 Lecture 06 Excel Scripting
CS 200 Spring 2020 02 – Styles 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 Spring 2020 02 – Styles Administrivia Please read and highlight the assignment and course notes before lab Assignment 6 is due Monday June 22 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 3
CS 200 Spring 2020 02 – Styles Assumption You have used a spreadsheet before You understand a simple program/algorithm 4
CS 200 Spring 2020 02 – Styles 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 Spring 2020 02 – Styles 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 6
CS 200 Spring 2020 02 – Styles Why Scripting in CS 200? Most major apps have some sort of scripting eg Word, Excel, Photoshop, FileMaker... eg system-level macro languages hooked to key presses or menu selection (eg iKey, UI Actions) or sometimes to user-defined palettes to move data between apps & tell those apps how to process the data (eg AppleScript, VBA) eg JavaScript in HTML pages, DreamWeaver, Acrobat...