Thursday, 19 November 2009

Excel Memory Madness

The memory madness that is new Excel can lead to situations where new worksheets are impossible create due to memory constraints. This usually happens when you already have a worksheet open that you opened from an email (this takes more memory). Solution is to save the file locally, close Excel and reopen.

Wednesday, 18 November 2009

Apply Multiple Slide Masters to PowerPoint

All pressies contain at least one SLIDE MASTER - idea is to make universal style changes that apply to every slide.

View -> Master -> Slide Master (ALT-VM)

Right click the slide master you want to copy. Copy and PASTE.

Now you have copied and pasted all the SLIDE MASTERs, the next phase is to copy slides across and associate those slides with slide masters.

Now for the TRICKY PART.....

Right click on a slide and select Slide Design. Right Click on a prototype slide in the Slide Design window and click "Apply to Selected Slide". Boombah!

Saturday, 17 October 2009

Programming Puzzles Ahoy!

Never mind Logic Problems, solve these engaging Prolog puzzles right now!

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.

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.

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).

Tuesday, 29 September 2009

Excel Formatting Demystified

To display negative numbers in brackets
Set custom format to: #,##0;(#,##0);0