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.

Leave a Reply

Your email address will not be published. Required fields are marked *