Wednesday, December 25, 2013

Making formulae easier to follow in Excel

In the old days, we would write really long formulae with nested functions. Pre Excel 5.0 and in other products, we did not have the niceties of VBA as a supplementary classic programming language so we used to torture ourselves.

Fast forward to 2013 and Excel generations 2007 and later have niceties in display and useage that reduce the pain of long formulae. Courtesy of +Bill Jelen’s discussions on social media, we know that we can use:
  • you can use spaces in the formula. Instead of typing A1+A2, you can type A1 + A2 chunking out the formula and improving readability. You can’t put a leading space before the first = sign. You can’t put a space between the function name and the bracket e.g. SUM( is ok, SUM ( is not
  • you can use Alt+Enter to insert line breaks in the formula bar. Remember that in Excel 2007 onwards, you can pull down the floor of the formula bar, increasing the number of lines Excel will display. This allows more classic programming language display.
  • you can use the Name Box when you build the formula in the Function Builder. We know that. However, did you know that you can use the Function Builder repeatedly in a formula with nested functions?

Tuesday, December 17, 2013

Screen Capture in the Windows 8

There are several options to screen capture classic desktop (programs):

  • PrtScr to save to clipboard
  • the Windows Snipping Tool
  • Microsoft Office 2010 (and later) programs have an Ribbon Command – Insert > Screen Shot > Screen Clipping
  • Greenshot – a very nifty free utility that can capture, save to file automatically, upload to various photo gallery sites automatically
  • the Windows Problem Steps Recorder (records a screenshot every keystroke sequence or mouse action you carry out until you stop.
  • and a host of third party free and paid utilities

However, the Windows 8 Apps Tiles Layer breaks your workflow – If you use one of the utilities above, after the screenshot, Windows returns to the classic Desktop. If you return to the Tiles layer, some Tile Apps lose state.

Use Windows logo key + PrtScr and the screenshot will save to My Pictures/Screenshots as a PNG file automatically, without switching to the Classic Desktop.

Friday, December 13, 2013

Free Intro to VBA Excel

I often look for reference material for people intending to pick up Visual Basic for Applications (VBA) and program Microsoft Excel. Just today, encountered this compilation by Hooman Askari-Nasab