CS 200 Spring 2018Excel Scripting9Automating SpreadSheet Creation — Format with a Macro (1)CS 200 Spring 2018Excel ScriptingAutomating SpreadSheet Creation — Format with a Macro (2)10The macroNote 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 developmentmacros are easy to read & usually you can RECORD what you want to do,or something close to it, and just edit the recordinglook 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 2018Excel ScriptingAutomating Use Of A Spreadsheet — Sort MarksThis illustrates speeding the useof a spreadsheet11CS 200 Spring 2018Excel Scripting12Sub Sort_By_Name2()Range("B3:D14").SelectSelection.Sort _Key1 := Range("C3"), _Order1 := xlAscending, _Header := xlGuess, _OrderCustom := 1, _MatchCase := False, _Orientation := xlTopToBottomRange("A1").SelectEnd SubSort 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 2018Excel Scripting13Sub Sort_By_Mark2()Range("B3:D14").SelectSelection.Sort _Key1 := Range("D3"), _Order1 := xlDescending, _Header := xlGuess, _OrderCustom := 1, _MatchCase := False, _Orientation := xlTopToBottomRange("A1").SelectEnd SubSort Marks — By MarkCS 200 Spring 2018Excel Scripting14The macroFunctionFtoC( fTemp )FtoC= (fTemp - 32) * 5 / 9End Functionillustrates extending an application by means of a macroNote 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 usedSee also “Marks to Grades” in Week 7 / Files for Lecture: Excel MacrosExtending Excel — F to C ConversionSubSort_By_Mark2()Range("B3:D14").Select......End Sub
CS 200 Spring 2018Excel Scripting15Making a spreadsheet look like a hand-built appCS 200 Spring 2018Excel ScriptingExcel’s Scripting Environment16Selecting Macros... opens the dialog shown above rightNote the “Record New Macro...” menu itemT
CS 200 Spring 2018Excel ScriptingEditing a MacroTo edit a macro