CS 200 Winter 2019 Excel Scripting The Assignment For This Week 26 Given the

Cs 200 winter 2019 excel scripting the assignment for

This preview shows page 26 - 32 out of 38 pages.

CS 200 Winter 2019 Excel Scripting The Assignment For This Week 26 Given the raw data, duplicate the functionality of this spreadsheet
Image of page 26
CS 200 Winter 2019 Excel Scripting This Week’s Assignment is “Layered” Trivial formulas eg for Actual Balance Simple formulas eg for Statement Balance or Next Transaction Number Not quite so simple formulas eg for the CD Charge cell for US$ purchases Simple macros eg for scrolling, sorting, or filtering Not quite so simple macros eg for making new entries Note that you can create all of these macros by recording them, you do not need to use VBA 27
Image of page 27
CS 200 Winter 2019 Excel Scripting Strategy For The Assignment Start with the simple stuff and proceed in stages towards the more difficult Test as you go Make a copy of your spreadsheet at each successful stage eg, when you have the simple formulas working, ie, squirrel a copy of that away in case you totally muck up the next step and want to start over on it (remember “backups”?) 28
Image of page 28
CS 200 Winter 2019 Excel Scripting “Objects” in Excel Macros Just about everything you see in a workbook is an “object” There is a natural hierarchy to objects You can have multiple instances of everything except the Application These are Excel’s “application data objects” (remember the Model pearl?) A “module” is for holding macros (aka “scripts” ) think of it as a document containing multiple macro definitions in the VBE: Insert > Module and type or ... a module is created automatically when you record a macro 29 Application Workbook Worksheet Range Range Chart Module Cell Cell Cell See Chapter 5 of The Microsoft Excel Visual Basic User’s Guide on “Working with Objects in Visual Basic” (pp 65–84), which is reprinted in the course notes, for more on objects in Excel.
Image of page 29
CS 200 Winter 2019 Excel Scripting Objects Objects have “properties” like “Color,” “Value,” “Font” that you can read or set in macros sometimes a property is just a value (eg 2) sometimes a property is another object Objects have “methods” like “ClearContents,” “Sort,” “Worksheets” that cause something to be done to or with the object a “method” is just another kind of built-in function or subroutine that does something to or with the object it’s attached to methods often have “parameters” (aka “arguments” ) — information they need — just like built-in Excel functions eg C4, ForEx, and 2 in VLOOKUP( C4, ForEx, 2 ) “Member” is programmer-speak for both properties and methods All objects of a particular kind are a “class” of objects 30
Image of page 30
CS 200 Winter 2019 Excel Scripting Especially Useful Properties Application.ActiveWorkbook you can just write ActiveWorkbook for the frontmost workbook Application.ActiveSheet you can just write ActiveSheet for the worksheet whose tab was last clicked Application.ActiveCell you can just write ActiveCell for the currently selected cell And usually you can omit Application.ActiveSheet eg Range ... instead of Application.ActiveSheet.Range...
Image of page 31
Image of page 32

You've reached the end of your free preview.

Want to read all 38 pages?

  • Spring '14
  • BarbaraDaly
  • Visual Basic for Applications

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture