Using variables in shapesheet functions like SETF, GETREF, GETVAL, INDEX,...

[jrinn]

I am trying to get a custom property formula in a shape shapesheet to reference a cell in the Document shapesheet user section.  Easy, right?

Well, the wrinkle is that I need the reference to be a variable.

When I set it hard all works fine.
If I set Shape.1's Shape Data Prop.Row_1=GetVal(TheDoc!User.NET_1) I get the value I want.
If I try to put any type of variable or text concatenation in any of the function combinations I've tried I don't get the desired result.

Even =GetVal("TheDoc!User.NET_1") does not work (I assume because of the quotes)
Obviously I don't want to reference NET_1 all of the time or I'd just hard code it.  I want to be able to convert the "NET" to a variable and also the "_1" to be a variable ("_1, _2, _3,...)

I know there is a way of doing this beause I just figured it out the other night (after 5 hours of trial and error), but unfortunately I lost that file and I've spent another 4 hours today with no luck.

I've tried using INDEX(0,"TheDoc!User.NET_1") since it returns a value of TheDoc!User.NET_1 as a result.
So I tried this =GetVal(INDEX(0,,"TheDoc!User.NET_1")), but again no luck.

Please help if you can,
   JPR

[vojo]

dynamic cell lookup is not supported in visio

Need to use VBA

Frankly, some sort of dynamic cell lookup is way way way way way way more interesting than the BS of containers.
Heaven knows why MS wasted time on that dumb idea

[Yacine]

hi JPR,
you should find your answer here

[vojo]

Yacine....I think he is after something else here

The idea of dynamically picking what cells to use in SETF/GETF formulas based on some "handle"...that is my take on what he is after.

Static reference to a cell pretty straight forward...your post should help
define one cell to use is fine...but the real cool thing would be to define a dynamic way of doing this for new/unforeseen cells.

Could do something like (though cell suite is still static).

SETF(if(handle=X, getref(Y), if(handle=A, getref(B), getref(M))),if(handle=X, Z,if(handle=A, C, N))))

However, this presupposes that all cells in play are known at development time

It might be real tricky but he might be able to use setatref to help  (would still need to figure out which shape is the target)

if (A,user.B = setatref(user.Q), NA)
if (L, user.M = setatref(user.Q) NA)
if (X,user.Y = setatrefueser.Q), NA

if (A,user.P = A, NA)
if (L, user.P = L, NA)
if (X, user.P = X, NA)

setf(getref(other P in other shape), user.P)
Setf(getref(other Q in other shape), user.Q)

In other shape
if (local user.P = A, setf(getref(local B),local user.Q, NA)
if (local user.P= L, setf(getref(local M), local user.Q, NA)
if (local user.P = X, setf(getref(local Y, local user,Q), NA)

If shape pair are known to coexist (if shape A...must have a shape B somewhere) then could look at a similar scheme to find it
(shape A ask user for shape ID of B....use that ID for the P/Q transmission).

In essence this creates a comm channel between shapes (if it could be made to work).   
This would be real powerful since could in essence cascade information to along a string of shapes without having to edit the shapesheet or do any VBA.

Think Junes calculator shapes without VBA

you could do things like
   - System performance of a topology....aka MVA of processor, memory, disks based on a workload
   - Visio version of DHCP or DNS (shape broadcast some attrributes to all other shapes or even a designated "switch" shape)
   - Topology mismatches (aka shape A source a 40Gbps "connector" link to shape B but shape B is only 100 Mbps)
   - Scaling a shape (Shape is in foreground...on or more shape Bs in background page....shape A has latest / "cached" info....pushes old stuff to shape B)
   - etc.

[jrinn]

Yacine, thanks, I did look at the calculator reference, but I am looking for exactly what vojo is describing, dynamic cell referencing.  It seemed as if the calcualtor reference still had someone typing in a cell reference as a custom property, where I cannot afford that luxury.

vojo, I'm intrigued by your thought process, but honestly, I am too new to Visio to fully follow or understand it right now.  I'll spend some time trying to get more familiar with SETATREF and your comments (maybe when I'm smarter I'll go "Aha!" and get it). SETATREF was one of the few functions I didn't try.  I did look at GETREF, GETVAL, SETF, EVALCELL, EVALTEXT, ARG, INDEX, LOOKUP, ...

I must have spent about 20 hours to come up with a "half" solution.  I know this can be done easily in Excel, but there are just a few nuances in Visio (and unfortunately I truly am a Newbie) that were driving me crazy.  Between cells or values looking equal, but not being equal and text not being considered an adequate substitution for a clean cell reference I couldn't make heads or tails out of this. Finally I came up with this simple looking SETF formula that got me through.  I'll explain below.

Code Select Expand

SETF(GetRef(Prop.INT_NETPT_DEST1),User.INT_NETPT_DEST_SHAPE1&"!Prop.INT_NETPT_ORIG")

I am working on an Interconnect Diagram function that works like a schematic capture "netlist" program where all I need to do is add a shape to a uniquely named NET and it automatically knows and lists right next to it who it also connects to (who else is on that NET).  Hopefully, by using this technique I can drop unit shapes (units with cable ends "dangling" off of them) onto a drawing and since they are part of the same master after the upper group's manual shape data is entered (name reference designator, part number,...) that data is "passed" down the line from the racked unit into a connector, into a cable and into the NET that is positioned near the unit.  After that, one only needs to assign NET names and all of the interconnectivity is automatically established for on-screen cross-referencing and cable connectivity reports and markerband output.  No dyamic connectors.  This treats on-page connections the same as off-page connections which is helpful since I am dealing with 50 page Interconnection Diagrams.

It still relies on VBA to setup some existing cells in the Document User section to be that static place to store NET data (or have empty cells waiting for data) and then use a technique of cross-referencing to tham as needed.  By double-clicking the NET shape a macro is executed that stores the shape's ID in the Doc User section.  From there I've established a formula in the shape that takes it's NET name and mixes it together with the reference to the Doc User Section and gives a resultant value.  The important aspect of this is that the VBA code only gets executed when shapes are added or removed from the NET list.  All other cross-references are done in the shapesheets and I thought that would make a much cleaner and faster execution as the size of this drawing grew large.  The end result could be 50 sheets with about 200 NET points per page (~10,000 NET points) and I didn't want to have VBA code cycling through 10,00 entities trying to find other matches.  This way the code only needs to cycle down 10,000 rows in one section of one shapesheet.  The lesser of two evils, right?


As an example,...

When the shape's NET is renamed to "USB" using the double-click macro it adds that name as a custom property to the shape and then checks for similarly named NETs in the Doc User section and fills in one of the blank ones or if a brand new NET it creates multiple entries in the Doc User section.

Code Select Expand

User.USB_1 = (the ID of the desired shape, let's say "Pages[Page-5]!Sheet.37")
User.USB_2
User.USB_n..(for however many shapes can be on one NET)

And then in the shape itself creates multiple entries in it's own User section as well

Code Select Expand

User.INT_NETPT_DEST_SHAPE1 = TheDoc!User.USB_1 (which equals "Pages[Page-5]!Sheet.37" but can be used as variable in this shapesheet)
User.INT_NETPT_DEST_SHAPE2 = TheDoc!User.USB_2
User.INT_NETPT_DEST_SHAPEn = TheDoc!User.USB_n...(for however many shapes can be on one NET)

Each NET has origin data (Prop.INT_NETPT_ORIG) that has been passed down using cross-shape refernces built into a master shape (so that part was easy!)

...so all that's left is having multiple rows for the SETF command below (again, one for each max number of shapes/NET)

Code Select Expand

User.SETF1=SETF(GetRef(Prop.INT_NETPT_DEST1),User.INT_NETPT_DEST_SHAPE1&"!Prop.INT_NETPT_ORIG")
User.SETF2=SETF(GetRef(Prop.INT_NETPT_DEST2),User.INT_NETPT_DEST_SHAPE2&"!Prop.INT_NETPT_ORIG")
User.SETFn=SETF(GetRef(Prop.INT_NETPT_DESTn),User.INT_NETPT_DEST_SHAPEn&"!Prop.INT_NETPT_ORIG")...(for however many shapes can be on one NET)

This command sets the DEST1, DEST2,...DESTn fields with the data from all of the NET shapes on that NET.

Resulting in the originating shape now having the values of all of the other shapes on the NET as DESTination filds in itself that it can use for shape text or data graphics.

Code Select Expand

Prop.INT_NETPT_DEST1=Pages[Page-5]!Sheet.37!Prop.INT_NETPT_ORIG (the ORIG of the first shape on the NET)
Prop.INT_NETPT_DEST2=(the ORIG of the second shape on the NET)
Prop.INT_NETPT_DESTn=(the ORIG of the nth shape on the NET)")...(for however many shapes can be on one NET)

As you can see the closest thing to dynamic cross-referencing done here is in the second half of the SETF function (the formula).  I spent most of my time using different variations of EVERY function I could think of to make this access the cell I wanted directly, but had to end up with this half-solution.  It's still needs VBA so it isn't a total shapesheet solution which would be sweet.


This is only the begininning of my efforts so I've still got a lot of subtle details to work out, but getting over this hurdle was like a huge weight being removed off of my shoulders.  It actually helped me understand what I've done by typing it out to you all as well.  I'm sure I'm confusing some with my mix of formulas, values and comments embedded as code in this reply, but hey, it's my first!  Hopefully I'll get better.

Thanks all for your comments.  If there is anything anyone sees that I'm doing that is just silly because there is an easier method please tell me. This is the cornerstone of everything I'm doing.

Thanks again,
   JPR

[vojo]

I think the key problem you will hit is that you will need to have to either
   A) have enough "prop" rows to cover worse case (could look at scratch rows since you get 5 arbitray cells per row)
   or
   B) in VBA, need to add rows as they are needed.

instead of muscling this in VBA for visio...you might want to think about some VBA that creates a simple DB (maybe leverage excell) where its sourc, dest1, dest2, dest3, etc
Ie do all the heavy lifting in VBA that is tasked from visio ....vs....trying do this all in shapesheet forumulas.

From there you can populate the shapes as needed  (find shape ID in source or dest....pull all pertinent attributes)...aka...when printing, update info for all shapes via vba.

On a new shape drop, add it to the "db" or array of arrays kind of thing.....delete...remove...etc

Once you have the "db" or the array of arrays....you have the frame work to extend (X,Y of source....X,Y of each dest....type of source and dest...etc).

There is probably sample VBA do build such a "db" or array of arrays...might save you time.

I guess all I am saying is to do the heavy lifting all in VBA and use VISIO to just present the results.