Saturday 17 October 2009
Wednesday 7 October 2009
Analysis ToolPak and Solver in Excel 2007
Analysis ToolPak
First things first, let MOB == "Microsoft Office Button" which is the big round button at the top left of the window.
To load Analysis ToolPak in Excel 2007:
1. Click MOB. Hit "i" to enter "Excel Options". (Alternatively: Alt-F-i will get you there).
2. Click add-ins. Add the AnalysisTP and TP-VBA add-ins.
Once Analysis ToolPak (ATP) is loaded it appears as "Data Analysis" icon in Data menu.
Solver
Step 1: Make sure this add-in is installed.
How does Solver work?
Specify: Set target cell [cell which contains formula], By Changing Cells [cells containing inputs], Subject to constraints e.g. $B$5$ <= 5.
First things first, let MOB == "Microsoft Office Button" which is the big round button at the top left of the window.
To load Analysis ToolPak in Excel 2007:
1. Click MOB. Hit "i" to enter "Excel Options". (Alternatively: Alt-F-i will get you there).
2. Click add-ins. Add the AnalysisTP and TP-VBA add-ins.
Once Analysis ToolPak (ATP) is loaded it appears as "Data Analysis" icon in Data menu.
Solver
Step 1: Make sure this add-in is installed.
How does Solver work?
Specify: Set target cell [cell which contains formula], By Changing Cells [cells containing inputs], Subject to constraints e.g. $B$5$ <= 5.
Tuesday 6 October 2009
Excel 2007 is Weird (Part II)
Excel 2007 dispenses with the classic paradigm of menus and accelerator keys. It uses a new "results-oriented user interface" (ROUI). What would happen I wonder if MSVC were to switch to a ROUI, would productivity be significantly enhanced? Also, Excel now supports up to 16 million colors. Why do I need that in a spreadsheet?
Quoting MS: "Commands and features that were often buried in complex menus and toolbars are now easier to find on task-oriented tabs that contain logical groups of commands and features. Many dialog boxes are replaced with drop-down galleries that display the available options, and descriptive tooltips or sample previews are provided to help you choose the right option."
Hang on...complex menus? That's why we have menu accelerator keys....what's going on here?
From Charles Ellis: "On the product team, we differentiate between accelerators, which are keyboard ways to access the Ribbon, menus and toolbars, and shortcuts which are essentially everything else. This list isn't meant to include accelerators, not because they aren't a big part of what keyboard users use (ALT+E+S+V+Enter and ALT+E+A+A come immediately to mind), but rather because shortcuts, unlike accelerators, are much more difficult to discover; the UI only lists a handful of them and good and reasonably complete documentation is often difficult to find."
Difficult to find? Microsoft used acclerators since 1991, at least. Accelerators work. Ribbon? No thanks.
Quoting MS: "Commands and features that were often buried in complex menus and toolbars are now easier to find on task-oriented tabs that contain logical groups of commands and features. Many dialog boxes are replaced with drop-down galleries that display the available options, and descriptive tooltips or sample previews are provided to help you choose the right option."
Hang on...complex menus? That's why we have menu accelerator keys....what's going on here?
From Charles Ellis: "On the product team, we differentiate between accelerators, which are keyboard ways to access the Ribbon, menus and toolbars, and shortcuts which are essentially everything else. This list isn't meant to include accelerators, not because they aren't a big part of what keyboard users use (ALT+E+S+V+Enter and ALT+E+A+A come immediately to mind), but rather because shortcuts, unlike accelerators, are much more difficult to discover; the UI only lists a handful of them and good and reasonably complete documentation is often difficult to find."
Difficult to find? Microsoft used acclerators since 1991, at least. Accelerators work. Ribbon? No thanks.
Sunday 4 October 2009
Adding Controls to Excel Spreadsheets (Forms Toolbox) and other Tricks
Forms Toolbar
First ensure the "Forms Toolbox" is visible (View->Toolbars->Forms). Then add a button to the worksheet (Click on the button icon and then "draw" the button in the sheet). It will prompt you for a macro name. Think of one and click ok. You can then record a macro.
How to Undo an "Undo"
Undoing an "Undo" is the same as doing a "Redo". This can be down via the Redo button (part of the Standard toolbar in Excel).
First ensure the "Forms Toolbox" is visible (View->Toolbars->Forms). Then add a button to the worksheet (Click on the button icon and then "draw" the button in the sheet). It will prompt you for a macro name. Think of one and click ok. You can then record a macro.
How to Undo an "Undo"
Undoing an "Undo" is the same as doing a "Redo". This can be down via the Redo button (part of the Standard toolbar in Excel).
Subscribe to:
Posts (Atom)