eg Selection instead of How do you find out about objects properties and

Eg selection instead of how do you find out about

This preview shows page 16 - 20 out of 20 pages.

eg Selection ... instead of ... How do you find out about objects, properties and methods? record a macro, then highlight a method or property name & press the help key ie if you see something and wonder about it use the “object browser” if you want to go looking to see, for example, if worksheets have a particular property 31 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 CS 200 Spring 2018 Excel Scripting The Object Browser Click in the “Classes” or “Members” lists type to scroll their contents automatically click on ? to open the help window on that object or member this is the best way to discover what members a class has NB: “Excel” is selected in the pull-down top left 32
Image of page 16
CS 200 Spring 2018 Excel Scripting Online Help for the Range Method 33 You can also access help for a property or method by highlighting it in a macro module and pressing the help key Hints for the assignment! The “Remarks” info about Ranges may be useful Warning: Excel and VB have different Help databases. CS 200 Spring 2018 Excel Scripting 34 This dot marks a “breakpoint” Watching Your Macros Run
Image of page 17
CS 200 Spring 2018 Excel Scripting 35 Debugging (The Big Picture) CS 200 Spring 2018 Excel Scripting Using the Debugger 36 Set a breakpoint by clicking in the left margin to halt a macro when it gets to that statement “clear” the breakpoint by clicking again in the left margin Use ? variableName <return> in the Immediate Window to display the value of the variable Use Debug > Step Into (does dive into functions or subroutines) Debug > Step Over (doesn’t dive into functions or subroutines) to execute one statement and stop again Use Run > Continue to turn the macro loose The Visual Basic Editor’s Debug toolbar has buttons for all of these (View > Toolbars... > Debug)
Image of page 18
CS 200 Spring 2018 Excel Scripting To run a macro delete a macro edit a macro And via the Options... button you can attach a macro to a key (or key combination) on the keyboard 37 Putting It Together: Tools > Macro > Macros... CS 200 Spring 2018 Excel Scripting Attaching a Macro to a Spreadsheet Button Draw a button after selecting on the Forms toolbar Ctrl-click on the button to open the Assign Macro dialog Select and OK the desired macro 38
Image of page 19
CS 200 Spring 2018 Excel Scripting Record to learn how to do something to build a macro faster Edit recorded macros to customize / elaborate them Use on-line help and the object browser to find object names, properties, & methods that you need Experiment with toy macros & documents eg the workbooks used in lecture (see Learn Week 6: Application Scripting (Excel) / Files For Excel Scripting) Use the debugger to understand what your macros are doing! Save everything you do, and include comments (they start with the single quote character ' ) 39 Strategies For Working With Macros CS 200 Spring 2018 Excel Scripting The Assignment For This Week 40 Given the raw data, duplicate the functionality of this spreadsheet
Image of page 20

You've reached the end of your free preview.

Want to read all 20 pages?

  • Spring '14
  • BarbaraDaly
  • Visual Basic for Applications, scripting

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture