Most major apps have some sort of scripting eg word

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...
— in fact, some have more than one!
— Photoshop supports three (or four, depending on how you count)
Scripting can save you a LOT of work
8

CS 200 Spring 2018
Excel Scripting
9
Automating SpreadSheet Creation — Format with a Macro (1)
CS 200 Spring 2018
Excel Scripting
Automating SpreadSheet Creation — Format with a Macro (2)
10
The macro
Note the comments, introduced by the character '
— anything from there to the end of the line is ignored
(add your own to remind yourself later of things you figure out)
This example illustrates speeding spreadsheet development
macros are easy to read & usually you can RECORD what you want to do,
or something close to it, and just edit the recording
look up terms you don’t know with online help (in the VBE environment)
eg select a term like ColorIndex and press the help key
'
' Rule_Left_and_Bottom Macro
' Macro recorded 10/12/95 by John C. Beatty
'
Sub Rule_Left_and_Bottom()
Selection.BorderAround _
Weight := xlThin, _
ColorIndex := xlAutomatic
Selection.Borders(xlRight).LineStyle = xlNone
Selection.Borders(xlTop).LineStyle = xlNone
End Sub

CS 200 Spring 2018
Excel Scripting
Automating Use Of A Spreadsheet — Sort Marks
This illustrates speeding the use of a spreadsheet
11
CS 200 Spring 2018
Excel Scripting
12
Sub Sort_By_Name2 ()
Range(" B3:D14 ").Select
Selection.Sort _
Key1 := Range(" C3 "), _
Order1 := xlAscending, _
Header := xlGuess, _
OrderCustom := 1, _
MatchCase := False, _
Orientation := xlTopToBottom
Range("A1").Select
End Sub
Sort Marks — By Name
“ _ ” means “the statement continues on the next line”
It’s pretty easy to guess what each piece of the Selection.Sort statement does,
right?

CS 200 Spring 2018
Excel Scripting
13
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
Sort Marks — By Mark
CS 200 Spring 2018
Excel Scripting
14
The macro
Function FtoC ( fTemp )
FtoC = (fTemp - 32) * 5 / 9
End Function
illustrates extending an application by means of a macro
Note the use of “Function” instead of “Sub”
“functions” return a value (the value assigned to their name)
“subroutines” don’t — they just “do something”
FtoC can be used anywhere a built-in Excel function
can be used
See also “Marks to Grades” in
Week 7 / Files for Lecture: Excel Macros
Extending Excel — F to C Conversion
Sub Sort_By_Mark2()
Range("B3:D14").Select...
...
End Sub

CS 200 Spring 2018
Excel Scripting
15
Making a spreadsheet look like a hand-built app
CS 200 Spring 2018
Excel Scripting
Excel’s Scripting Environment
16
Selecting Macros... opens the dialog shown above right
Note the “Record New Macro...” menu item
T

CS 200 Spring 2018
Excel Scripting
Editing a Macro
To edit a macro