Discussion:
Visio 2003: Referencing cell via a variable
(too old to reply)
Udo
2007-03-26 23:43:12 UTC
Permalink
Hello again,

my old problem: getting the value of a cell via the cell-name, which
is defined as a string (reference to a cell via a variable).
Suppose the following (simplified) situation. Two User-defined Cells
User.CellName
User.CellValue
In User.CellName the name of the cell is defined (a string, e. g.
"PinX") and User.CellValue should then return the value.
In more detail I want access the value of a cell (e. g. PinX) of a
shape which name (only the name, not the cell) is defined in another
shape via a Custom Property:

Thanks for any suggestion - looking for a shapesheet-bases solution,
when possible at all.


Greetings
Udo
Chris Roth [MVP]
2007-03-27 03:56:12 UTC
Permalink
Servus Udo,

Unfortunately, you can't "piece together" cross-sheet cell references. So
something like this won't work.

User.Shape = "Sheet.37"
User.Cell = "PinX"
User.Test = User.Shape & "!" & User.Cell

User.Test will have the TEXT-value "Sheet.37!PinX", but Visio won't
recognize it as a valid reference. : (
--
Hope this helps,

Chris Roth
Visio MVP

Free Visio shapes:
http://www.visguy.com/category/shapes
Visio programming info:
http://www.visguy.com/category/programming/
Other Visio resources:
http://www.visguy.com/visio-links/
Post by Udo
Hello again,
my old problem: getting the value of a cell via the cell-name, which
is defined as a string (reference to a cell via a variable).
Suppose the following (simplified) situation. Two User-defined Cells
User.CellName
User.CellValue
In User.CellName the name of the cell is defined (a string, e. g.
"PinX") and User.CellValue should then return the value.
In more detail I want access the value of a cell (e. g. PinX) of a
shape which name (only the name, not the cell) is defined in another
Thanks for any suggestion - looking for a shapesheet-bases solution,
when possible at all.
Greetings
Udo
Chris Roth [MVP]
2007-03-27 07:52:52 UTC
Permalink
Udo, I spoke too soon, you *may* be able to get this to work using the SetF
ShapeSheet formula.

Start with our previous example:

User.Shape = "Sheet.37"
User.Cell = "PinX"
User.Formula = User.Shape & "!" & User.Cell

and add another cell:

User.JamFormula = SETF( GETREF( PinX ), User.Formula )

This jams the formula that you assembled in "User.Formula" into the PinX
cell. If Visio recognizes the formula as valid, it will try to interpret it.
The trick is getting your "jammer cell" to fire when you need it to fire.
--
Hope this helps,

Chris Roth
Visio MVP

Free Visio shapes:
http://www.visguy.com/category/shapes
Visio programming info:
http://www.visguy.com/category/programming/
Other Visio resources:
http://www.visguy.com/visio-links/
Post by Chris Roth [MVP]
Servus Udo,
Unfortunately, you can't "piece together" cross-sheet cell references. So
something like this won't work.
User.Shape = "Sheet.37"
User.Cell = "PinX"
User.Test = User.Shape & "!" & User.Cell
User.Test will have the TEXT-value "Sheet.37!PinX", but Visio won't
recognize it as a valid reference. : (
--
Hope this helps,
Chris Roth
Visio MVP
http://www.visguy.com/category/shapes
http://www.visguy.com/category/programming/
http://www.visguy.com/visio-links/
Post by Udo
Hello again,
my old problem: getting the value of a cell via the cell-name, which
is defined as a string (reference to a cell via a variable).
Suppose the following (simplified) situation. Two User-defined Cells
User.CellName
User.CellValue
In User.CellName the name of the cell is defined (a string, e. g.
"PinX") and User.CellValue should then return the value.
In more detail I want access the value of a cell (e. g. PinX) of a
shape which name (only the name, not the cell) is defined in another
Thanks for any suggestion - looking for a shapesheet-bases solution,
when possible at all.
Greetings
Udo
Udo
2007-03-27 20:08:13 UTC
Permalink
Hello Chris,

many thanks for you help!
Post by Chris Roth [MVP]
User.Shape = "Sheet.37"
User.Cell = "PinX"
User.Formula = User.Shape & "!" & User.Cell
User.JamFormula = SETF( GETREF( PinX ), User.Formula )
This jams the formula that you assembled in "User.Formula" into the PinX
cell. If Visio recognizes the formula as valid, it will try to interpret it.
The trick is getting your "jammer cell" to fire when you need it to fire.
I have added one additional User-defined Cell
User.Result
for the result, so User.JamFormula is changed to
User.JamFormula = SETF( GETREF( User.Result ), User.Formula )
User.Result shows the result as Formula AND Value
"Sheet.37!PinX"
but it seems that that String is converted correctly in the
corresponding numeric value via formating.

Now I use User.Result as Field in the ShapeText of the SlaveShape
where all the above Formulas are defined, BUT with Format "General
units" - that's really important, otherwise you don't get the numeric
value.
When I move the MasterShape (Sheet.37), the numeric value is updated
correctly afterwards. Then I select the SlaveShape and move that
Shape, the Formula is shown as String. After moving the numeric value
is shown again - BUT ONLY while the SlaveShape is selected. When the
SlaveShape is unselected, the Formula is shown as String!!
When I try a dummy calculation (for getting a numeric value), e. g.
Formula * 1 or Formula + 0
other interesting effects happen - usually the value isn't updated.

Another problem. When I define a Field via a Custom Formula, e. g.
=Sheet.5!User.Result
the unique ShapeName Sheet.5 is changed to the given name of the
Shape. e. g.
=MasterShape!User.Result
When I then want to edit that Formula again, I get an error message -
I must
first replace "MasterShape" with "Sheet.5" - that's really nasty.


I really like working with Visio, but there are too many
inconsequence, especially concerning the ShapeSheet-programming. And
programming inside the ShapeSheet is really uncomfortable.
And I *HATE* the Multiple Document Interface (MDI) - hoped that this
is changed in Visio 2007 - but it isn't?! To be honest - that's really
old fashioned...


Thanks for any additional hints
Udo
Post by Chris Roth [MVP]
Post by Udo
Hello again,
my old problem: getting the value of a cell via the cell-name, which
is defined as a string (reference to a cell via a variable).
Suppose the following (simplified) situation. Two User-defined Cells
User.CellName
User.CellValue
In User.CellName the name of the cell is defined (a string, e. g.
"PinX") and User.CellValue should then return the value.
In more detail I want access the value of a cell (e. g. PinX) of a
shape which name (only the name, not the cell) is defined in another
Thanks for any suggestion - looking for a shapesheet-bases solution,
when possible at all.
Loading...