Looping through

Following on from my previous post, in which I covered some ShapeSheet and VBA basics, today I'm going to look at some simple looping programming methods that do something a little more practical.

There are many situations where you'll need to run through all of the shapes on a page to carry out an operation.  This might be in order to check each shape to see if it meets a particular criteria, (for example, only shapes based on a particular master) or perhaps to change a value in every single shape (maybe to change a base URL)...

To help achieve this, VBA gives you a number of 'flow control' structures that enable you to carry out an iterative process.  Of the main ones to know about, For Each...Next and For x=1 to n...Next enable you to carry out the process a finite number of times and Do...Loop, Do While...Loop and Do Until...Loop continuously repeat a process until a logical condition is met.

So, let's look at a few examples and see how they work in practice:

• Changing the line of every shape on a page
• Deleting shapes based on a particular master
• Shuffling shapes off page
• Running through all files in a folder
• Resetting master inheritance

Changing the line colour of every shape on a page - This first example takes a page and runs through every shape, regardless of type, and changes its line colour to red.  For Each...Next is useful for running through collections of objects and that's exactly what we're doing here:

Another method is to use an index rather than an item.  This allows you to set the start and end of the loop to something other than the number of objects in a collection.  For example, we could change the middle third of the shapes on a page to a green line colour as follows:

Deleting shapes based on a particular master - A final point regarding the For...Next structure is the Step keyword.  This optional keyword enables you to change the amount by which the loop index increments:

 

This also works in reverse, which is useful when deleting items within a loop to avoid stepping over the upper index.  An example of this might be to run through the shapes on a page in order to delete any that belong to a particular master:

(Note - The default Step is 1, so when you are incrementing normally you can just leave out the keyword Step.)

Shuffling shapes off page - Next we'll look at the Do...Loop trio of structures.  The following example, which shuffles all of the shapes to the right until they are off the page, demonstrates how this type of loop can be used when an end point is not necessarily known in advance:

The While above could easily be replaced by its sister keyword Until to provide a similar effect.  Equally, a straight Do...Loop can be used with a test in the middle that exits the loop (with Exit Do) if an appropriate condition is met.

A word of warning: with the Do...Loop structures it's surprisingly easy to set up a loop that never ends with the obvious thumb-twiddling result.  Should you find yourself in this anxious situation then the Ctrl+Break key combination can help in halting the loop in its tracks.

Running through all files in a folder - So far we've been dealing with the shapes on a single page, but we can also nest the above looping techniques and expand the scope to take in both all pages in a document and all documents in a folder.

(Note - The following code uses a reference to the Microsoft Scripting Runtime object library, so you'll need to add this via the Tools menu in the VBE in order for it to run.  To find out more about using Files and Folders within VBA check out this link.)

Resetting master inheritance - I'll finish with a reasonably common scenario that can occur when you need to update your master shapes in an existing diagram. 

Of course, if it's a simple change then you can just manually update the master and everything will update in your instances accordingly.  But what happens if your instance shapes all have local (rather than inherited) formulae or values, for example when a user has applied a different fill colour to each shape?  The instance shapes no longer get their values from their respective masters and so any changes made to the masters won't be reflected in the instances.  The answer is to reset the instance shapes' cells to once again inherit from their master shape parents.  You do this by adding an "=" formula. 

Without automation this would be a daunting task on any kind of sizeable diagram but a slightly less painful method might look like this:

(Note - Inheritance can be seen directly in the ShapeSheet.  If you open an instance shape's ShapeSheet you'll see inherited formulae and values as black text and local ones as blue.)

I hope the above gives you confidence if you've not tried any code before.  This is really just a taster of what's possible.  For more examples head over to http://visio.mvps.org/VBA.htm.

All of the above code can be downloaded in the attached file (Download looping_through_examples.vsd), which also includes a 'ResetDocument()' procedure that you can run in between trying out the others.  It's worth saying that I've kept the code as simple as possible to illustrate the particular looping process and in reality you might want to add some more defensive statements and error checking.

Comments

[Dirk]

Formula should be replaced with FormulaU in the examples. Otherwise running them on systems with a different localisation than English may fail with error -2032464666.

[mark atkinson]

John, i've read a bit of your blog (very well done). A random question. Can you reference me to an addin, or source code, which will create an organizational chart without using the standard add in? I am using 2007 standard (and I can not upgrade to premium due to corporate IT) so have written code to link an Access database to an org chart. I now need to dynamically create a series of org charts on the fly from the database. the trouble is that as far as i can tell, the Visio standard addin can not be called with VBA code and must be done manually, which does not work in my particular workflow.

Any suggestions here?

[John Goldsmith]

Hello Mark, Actually addons can be called from VBA. Have a look at the 'Run' method on the Addon object:
http://msdn.microsoft.com/en-us/library/ff765587.aspx
and this example where I use it call the Org Chart addon:
http://msdn.microsoft.com/en-us/library/ff765587.aspx

I see you've aready been pointed towards a couple of non-org chart options which provide an alternative.
Does that help?