MS Visio: How to synchronize multiple cells beetween beetween multiple master instances in different pages?

Posted on 2016-08-12
Last Modified: 2016-09-07
I have build a system in Visio 2013 back ago, and now my client wants to synchronyze multiple cells beetween multiple master instances in different pages. Each page has 100+ master instances, each master has 10+ cells to be synchronized. So in each master's cell I entered a formula like this:

and so on.

that works well to the synchronize purposes, but it will take a LOT of time to synchronize all the master instances.

I want to take the approach to make the "MasterInstanceID.000001" a variable in the formula, related to a User Defined cell, so I only  have to change the 000001 number once in each master instance, and not in each shapesheet cell, when I have had applied the changes in the master, and all the master instances being actualized. I can't use the EVALTEXT() function, because I'm not allowed to change the text displayed by the master instance.

Any ideas? Best regards.
Question by:Gustavo Garcia
  • 4
  • 3
LVL 30

Expert Comment

by:Scott Helmers
ID: 41754884
Unless I've missed what you're trying to accomplish, I think there is a much more efficient way to achieve the desired result. Am I correct that what you're trying to do is to change one value and then have all instances of that master, regardless of the page on which they are located, reflect the new value?

If so, try this:
  1. In the document ShapeSheet for the stencil, create a user cell, let's call it User.MasterValue1
  2. In each master in the stencil, create a Shape Data cell, let's call is Prop.MasterValue1, and set its value to =TheDoc!User.MasterValue1

When you drag a master onto a page, it's Prop.MasterValue1 cell will automatically reflect the value in the document ShapeSheet. When you change the value in the doc sheet, all instances that refer to it will be updated automatically.

Re #1 above, to get to the ShapeSheet for the stencil document:
  • Right-click the header bar for the stencil and then select Drawing Explorer Window
  • In the Drawing Explorer window, right-click Stenci3 (or whatever name appears at the top of the window), and then select Show ShapeSheet

NOTE: You can create User.MasterValue1 in the doc sheet for the actual diagram, but it isn't necessary. The first time you drag one of your masters onto any page in the document, Visio will create a doc sheet cell for you because the master contains a formula that refers to the doc sheet.

Author Comment

by:Gustavo Garcia
ID: 41756687
Hi Scott! Thank you for your answer, but that's not what I'm trying to accomplish.

I'm trying to synchronize a group of cells in several instances of master 1,  let's call it a SourceMaster. The instances are already dragged to page 1 of a Visio document, and with useful data, with a other group of cells in other several instances of master 2, let's call it a RecipientMaster. RecipientMaster instances are already dragged and placed in page 2 of the same document. I want to synchronize cells of a particular SourceMaster instance, with a particular RecipientMaster instance.

Since there are several instances to synchronize and each instance is already placed, I want to explore the posibility to make the unique ID number of SourceMaster instances reference a "variable" somehow in the synchronization formulas in RecipientMaster instances cells, so once I've edited the RecipientMaster, and updated all its instances, I only have to change the referenced ID number of the SourceMaster instance,  to synchronize all the cells of the instance with synchronization formulas. As far as I've tried, I haven't  found a method to accomplish this. Please help!
LVL 30

Expert Comment

by:Scott Helmers
ID: 41764676
As a general answer, you can't use variables in ShapeSheet formula. However, there is probably still a way to accomplish what you want...

Are your relationships one to many? Or one to one? For example in the diagram below do the cells in A need to be synced with W, X, Y, and Z? Or A to W, B to X, etc.?shape exampleEither way, it's likely that using doc sheet or page sheet cells as the communication vehicle might still work. For example, the SETATREF() formula in the source masters could reference a doc sheet cell instead of a cell in a specific shape, and then the cells in the recipient masters could simply be equated to those same doc sheet cells. That way any time you make a change to a value in a source instance, the corresponding doc sheet cell will get updated, which will automatically change the value in all corresponding recipient instance cells.

Am I getting closer?
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.


Author Comment

by:Gustavo Garcia
ID: 41766077
Hi Scott, thank you for your answer. You're getting a step closer, but not still there yet. My relationships are one cell to one cell for different master instances. Using a doc or page user cell to sync each cell in this particular case is still impractical, because I've in this particular system around 700 master instances and I need to synchronice about 5 to 7 cells in each instance. Making that amount of user cells references in a page or doc would be still too data capture intensive.

I already made a report with the sheetID of each master instance I have to sync, and they wont change in the future, that means that the cell's relationships are static and well known. Right now, with the approach you suggested I've been playing with some user cells  and tunneling techniques, like triple """ """ quotation marks, trying to make the sheetID of master instances to sync a "fixed" variable for the particular master instance, but still I had no luck.
LVL 30

Assisted Solution

by:Scott Helmers
Scott Helmers earned 500 total points
ID: 41766246
Knowing that you have several thousand cells to sync certainly changes the options for what you're trying to do.

With that volume of relationships, and if the relationships don't change, why not write VBA code to build all of the formulas for you? That would eliminate thousands of SETATREF() formulas and the attendant performance hit you've described.

It would take a little while to put the code together, but then it would be easy to tweak it as need to finalize the formula. And making changes in the future would be dramatically simpler as well.

Without knowing what your Excel report contains, but assuming that you've got all of the appropriate shape IDs and cell names, I would suggest writing code to 1) read relationship pair data from the Excel workbook, and then write formulas into shapes.

Author Comment

by:Gustavo Garcia
ID: 41775492
Hi Scott! Thank you for your comment. I was trying to avoid a solution that implies to write a extra code just to do that, but it seems there are no other way. I'm not well versed in Automation yet. Can you point me to the procedures used to write a formula in a cell from excel?

Best regards
LVL 30

Accepted Solution

Scott Helmers earned 500 total points
ID: 41778347
I understand about not wanting to write code if you don't need to, but I think it may save you a lot of work in the long run. Also, keep in mind that the diagrams you distribute won't necessarily need to include the code -- from what you've described, the code should only be necessary for setting the formulae into the shapes. After that the diagram should be able to live on its own.

Two suggestions for getting started:
1) Fellow Visio MVP John Goldsmith posted an intro for Visio developers on his excellent blog.
2) I created a three-part series called "Becoming a Visio Power User" for Microsoft Virtual Academy. Part 3 is all about getting started writing code. It contains a lot of small VBA examples that should be helpful.

Beyond that, keep posting questions here.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The canonical version of this article is on my web site here: A companion presentation is available here:
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  ( Here (http…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question