Changing page size for all pages

A newsgroup poster has asked how to change the page size for all pages in a document in one go.  Although there’s no way of doing this through the UI, a code based answer to the problem is fairly straight forward and I think makes a nice simple first try at VBA for anyone that hasn’t had a go…

If you haven’t seen the ShapeSheet or Visual Basic Editor before then you might want to quickly read this ‘Just for starters’ post, which introduces the two areas.

Recording a macro

So the first place to start, if you’re unsure of how to write some code, is to use the macro recorder (see ‘Visio's Most Useful Tool’ by John Marshall for more details), and you can use this while changing the size of a single page:

1. Click Tools / Macro / Record New Macro… to turn on the recorder.
2. Click File / Page setup… and select a new paper size from the Printer Paper drop down.
3. Click OK, to close the dialog and then Tools / Macro / Stop Recording.
4. Now click Alt + F11 to open the VBE code editor to review the code that’s been generated.  

You can see an example of the generated output here:

The macro recorder wraps up the actions that you took in the UI into a single ‘UndoScope’, which equates to a single undo unit that you see when clicking Edit / Undo…

The important part, as far as we’re concerned, is highlighted in blue.  A lot of the time, code that you write in Visio is really just reading and writing to cells in the ShapeSheet and you can see which cells are being written to in the code above:- PageWidth, PageHeight and PaperKind (the latter cell being paper type, ie A4, A5 or Letter, Legal etc).

One final point on the macro recorder is that it generally references cells using the .CellsSRC method, which takes a section, row and cell parameter.  Another way to reference a cell is to use the .Cells (or .CellsU) method and I’m going to use this in the following code as it’s a little easier read.

Changing the code to change multiple pages

So the macro you’ve just generated gets you half way to your goal – so far you changed one page.  To change multiple pages my suggested plan of attack is as follows:

• Select a page that is already set to your preferred size and keep a reference to that page
• Loop through all of the other pages in the document and apply the size properties of your reference page to each one in turn.

A result of changing the macro in this way might look like this:

Let’s have a look at what the above code is doing:

1. It starts by setting a reference to the active page as the procedure is called. 
2. It then checks to see if the user wants to include background pages or not and then begins a loop to run through each page in the document. 
3. Inside the loop the code first checks that page being checked is not the same as the reference page. 
4. If that’s the case, then it moves on to check if a) the page is a background page and b) if the user chose to exclude background pages.  Only if both of those options are not true does the code move on to apply the changes. 
5. Finally, the actual changes are applied by setting the respective formulae in the reference page to the particular page in the loop.     

Extending the code to apply other settings

Up to this point we’ve managed to write some code to copy three cell formulae across the pages of a document.  Of course there are a great many other page settings that might also require changing.  One strategy would be to just continue the above pattern, ie:

pag.PageSheet.CellsU("SomeCellName").FormulaU = _
     refPag.PageSheet.CellsU("SomeCellName").FormulaU

A more concise route, however, might be to cycle through an array of cell names.  Using this latter option would allow you to easily conditionally apply a set of changes (or array of cells) depending on choices made by the user.  Here’s an example using a single array set:

There’s not a great deal of change here.  Basically I’ve added an array of cell names and another loop to run through that array.  Each iteration of the loop calls a helper procedure (CopyCellFormula) that takes three parameters:- two shape references and a string cell name.  (Note that Page.PageSheet returns a shape data type.)  This array-based approach allows you to tailor the list of names to include any cells that you want to change.

All of the code can be downloaded in this file: ChangingPageSizes.zip