18 Friday CS 200 Fall 2012 Excel Scripting The general scheme

18 friday cs 200 fall 2012 excel scripting the

This preview shows page 22 - 29 out of 49 pages.

18Friday, October 12, 2012
Background image
CS 200 Fall 2012Excel ScriptingThe general scheme for naming macrosIf there are no spaces in your namesWorkbookName!WorksheetName.MacroNameYou can just use the macro’s nameif it’s defined in a module of the same workbook— names containing spaces must be surrounded by single quotes'Work Book Name'!WorksheetName.MacroNameYour life will be easier if you avoid blanks in names...Interpreting “Sheet1 (Sheet1)” in the Project Explorer window19This is what you use in macros to refer to the worksheetFriday, October 12, 2012
Background image
CS 200 Fall 2012Excel ScriptingBefore row/column insertions After row/column insertionsThe macrosMyLiteralTotal()MyNamedTotal()after inserting a new columnat the left edge of the spreadsheet— notice which total is correct!— and why!So ... when you insert/delete rows/columnsExcel updates all cell references in a worksheet,literal references (“E43”, “$R$13”), range definitions, etc,but the text of macros is untouchedThe moral ... you nearly always want to use named ranges in macros 20Cell References in Excel Worksheets & MacrosFriday, October 12, 2012
Background image
CS 200 Fall 2012Excel ScriptingConsider the Sort Marks example with these macros:Suppose we added a student, or moved the list.Would the macros still work properly?21Sub Sort_By_Name()Range("B3:C14").SelectSelection.Sort _Key1 := Range("B3"), _Order1 := xlAscending, _Header := xlNo, _OrderCustom := 1, _MatchCase := False, _Orientation := xlTopToBottomRange("A1").SelectEnd SubSub Sort_By_Mark()Range("B3:C14").SelectSelection.Sort _Key1 := Range("C3"), _Order1 := xlDescending, _Header := xlNo, _OrderCustom := 1, _MatchCase := False, _Orientation := xlTopToBottomRange("A1").SelectEnd SubCell References in Excel MacrosTFriday, October 12, 2012
Background image
CS 200 Fall 2012Excel ScriptingConsider the Sort Marks example with these macros:Suppose we added a student, or moved the list.Would the macros still work properly?21Sub Sort_By_Name()Range("B3:C14").SelectSelection.Sort _Key1 := Range("B3"), _Order1 := xlAscending, _Header := xlNo, _OrderCustom := 1, _MatchCase := False, _Orientation := xlTopToBottomRange("A1").SelectEnd SubSub Sort_By_Mark()Range("B3:C14").SelectSelection.Sort _Key1 := Range("C3"), _Order1 := xlDescending, _Header := xlNo, _OrderCustom := 1, _MatchCase := False, _Orientation := xlTopToBottomRange("A1").SelectEnd SubCell References in Excel MacrosTFriday, October 12, 2012
Background image
CS 200 Fall 2012Excel ScriptingUse a named range instead of explicit literal cell referencesWhat if I want to add a new student?adding in the middle of a named rangeadding to the end of a named rangeuse a final blank row?would the average be correct?This is another instance of “indirection”22Sub Sort_By_IDnumber2()Range("theData3").SelectSelection.Sort _Key1 := Range("theData3").Range("A1"), _Order1 := xlAscending, _Header := xlGuess, _OrderCustom := 1, _MatchCase := False, _Orientation := xlTopToBottomRange("A1").SelectEnd SubDoing Sort Marks RightFriday, October 12, 2012
Background image
CS 200 Fall 2012Excel Scripting23Variables and declarationsAssignment statementsSequential executionLoopsinitializationtermination testchanging the loop control variable(s)If–then–else statements
Background image
Image of page 29

You've reached the end of your free preview.

Want to read all 49 pages?

  • Fall '13
  • LiBin
  • Visual Basic for Applications, scripting language, scripting

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture