Tag Archives: tutorial

Data

30 days to context connection

Check this PDF document out from Juice Analytics – it’s a great 30 day course in developing data visualisation skills, including articles and interactive website links around data, dashboards and analytics.  There’s even some graphic design techniques that can be employed to make your information and reporting work for you.

I’ve just finished week one and learned a hell of a lot already – I’ll keep you updated on how things go.

Excel Hints and Tips

Tip of the day #2 (Excel)

To show all formulas and functions in a spreadsheet rather than their resulting values (very useful if formula auditing), you can use the keyboard shortcut CTRL + ` (the key to the left of ‘1’ on most QWERTY keyboards).  Use the same key combination to switch back to displaying values.

Excel Issues and problems

Excel VBA: Pagesetup properties (and problems!)

I’ve just had a complete nightmare with a Microsoft Excel application I’ve been building, especially concerning printing and changing page setup settings through VBA.  Everything seemed to work fine on my machine, but moving to another the code routines I had took ages to the point where the VBA routine which I was writing to save people time was taking almost as long to run as if an experienced Excel user decided to work manually.

This, obviously won’t do.

After quite a while digging around I didn’t really find anything that has fixed the problem, but there are certainly some little hints and tips I’ve found around the matter to speed things up:-

  1. It might be worthwhile trying a different printer driver on your machine.  For some reason Excel communicates with the driver whilst carrying out its actions.  Seems silly to me, really.
  2. Get into your code and get rid of any of the ‘pagesetup’ bits you don’t need, or ones which contain default values.  Think about the bits you’re actually changing settings-wise and delete the rest.  This can speed things up hugely.
  3. You could try creating a blank template worksheet with the correct printer settings such as orientation (landscape, portrait etc) already set up and saved within it.  You can then load this up and do your stuff with it if you’re creating a sheet from scratch – this is something I do quite often as it means I can also set up various other aesthetic bits too which would otherwise result in lots of unwieldy code.
  4. I’ve read some mention of using Excel 4 macros, but I’d suggest steering away from that if possible as it’s outdated as it is and probably won’t be supported in future versions.

I’ve only tried this out in Excel 2003.  Does the same issue exist in Excel 2007 at all?  I don’t have it installed on my machine at the moment so can’t test it.