Author Topic: [SOLVED] How to evaluate a ShapeSheet expression which is inside a string?  (Read 10141 times)

0 Members and 1 Guest are viewing this topic.

nashwaan

  • Full Member
  • ***
  • Posts: 85
Lets say we have a shape, Sheet.1, with a user defined row named User.SheetID where this cell can contain a string of NameID of another shape such as "Sheet.2".
Then how to get the cell value of the other shape from this NameID?

For example, i want to get the width of the other shape by writing something like:
=User.SheetID & "!Width"
but this will evaluates only to a text string =>  "Sheet.2!Width" and not the actual width !!!

The closest thing i got is by using EVALTEXT() function; but it requires to enter the textual formula in the text of the shape which i don't want to use.

Is there some other way that i can write in Sheet.1 something like:
=EVALTEXT(User.SheetID & "!Width")   so that i get the actual width of Sheet.2?

Thanks,
Yousuf.
« Last Edit: April 18, 2012, 03:50:05 PM by nashwaan »
Give me six hours to chop down a tree and I will spend the first four sharpening the axe — Abraham Lincoln

Fin311

  • Jr. Member
  • **
  • Posts: 15
Re: How to evaluate a ShapeSheet expression which is inside a string?
« Reply #1 on: April 17, 2012, 03:29:24 PM »
I have also tried this to a point of frustration, although the quickest way to complete this is with VBA.  Visio is missing a few key functions that excel uses to convert text to formulas.  If you know the sheetID of the shape your wanting to find the width it can be done by simply typing Sheet.1!width to get the value. But if you do not know the sheet.ID.  Might I ask what function this width recall will serve?  If it is a callout that you are resizing by calling the width of sheet.1 then I would suggest custom data graphics. It is a really slick way of capturing data from multiple shapes and staying away from VBA.  Other wise jump into VBA.

http://msdn.microsoft.com/en-us/library/aa468596(v=office.12).aspx#Visio2007CustomDataGraphics_CreatingNewDataGraphics

Fin

vojo

  • Hero Member
  • *****
  • Posts: 1594
Re: How to evaluate a ShapeSheet expression which is inside a string?
« Reply #2 on: April 18, 2012, 07:38:11 AM »
sometimes for some things you can trick visio.

Use 1,2,3 sets of "" around the text you want   (I use this trick with SETF)

something like

user.cell =  """" & user.cellsource & """"

Wont kid you ....only works sometimes on some things (not reliable) and is quite tricky to use

nashwaan

  • Full Member
  • ***
  • Posts: 85
Re: How to evaluate a ShapeSheet expression which is inside a string?
« Reply #3 on: April 18, 2012, 03:36:12 PM »
Wooooow it worked  :o

To my surprise, the solution suggested by vojo really worked!!!

I actually gave up on this and redesigned the shapes and their ShapeSheet formulas, specially after the reply from Fin311.

I have also tried this to a point of frustration, although the quickest way to complete this is with VBA.  Visio is missing a few key functions that excel uses to convert text to formulas.... Other wise jump into VBA.

But only when i read this part from vojo's post:
Wont kid you

 ???
I went on trying his suggestion; because when i first looked at his idea, i didn't think this will help in any way.

Anyways, here is the solution:
I created an extra User defined row User.SheetID_Width and i entered the following formula in the Prompt cell:
User.SheetID_Width.Prompt = SETF(GETREF(User.SheetID_Width), User.SheetID & "!Width")

The value in the User.SheetID_Width changes according to the string NameID of the other shape in User.SheetID (note the trick lies is in SETF function).

Although this method solved the problem of evaluating a string, this approach is not robust specially when copying shapes as the NameID of the copied shapes may get changed by the copy process.

Another approach is to let the other shapes to pass their Width value (or whatever cell value) to the main shape.
In each of the other shapes, we create User data row User.ReportWidth
User.ReportWidth = SETF(GetRef(Sheet.1!User.SheetID_Width),IF(STRSAME(NAME(),Sheet.1!User.SheetID,Width,GetVal(Sheet.1!User.SheetID_Width)))
Note that this formula should be inserted in every other shape. This approach will preserve the relationship between shapes even if they are copied and their NameID get changed.

Fin311 and vojo, thank you for your comments and suggestions.
 :)
Yousuf.
« Last Edit: April 18, 2012, 03:47:16 PM by nashwaan »
Give me six hours to chop down a tree and I will spend the first four sharpening the axe — Abraham Lincoln

vojo

  • Hero Member
  • *****
  • Posts: 1594
glad it offered some limited insight

Ok...so here is potential problem with your solution (and, IMHO a real limitation of visio).

Although all the shapes can post their width, you are assuming the shape they post them to is always sheet.1.
This means, the very 1st shape you drop on the sheet is the "catcher"   Maybe that is what you want.....but does  restrict productivity.

Also, you are assuming that some how...only the chosen posting shape posts his value
(note, the visio engine does not cycle thru the cells in the same order each time....believe it or not....so run X might get shape 3 data....run y might get shape 5 data
(if its a free for all....last one in wins).

Basically, visio shapes can not be shape aware without VBA.  I sure a big part of that is that its real tough to develop some sort of formula that iterates thru all shapes.
But it does mean that a given diagram is fairly static (yes can change attributes like color of text in a shape based on formulas....but there is no real user friendly approach to reference another shape).

Perhaps a better way to do this is one of the following:

A)
    - set up some user cells on the page
           - 1 cell advertises to all pitches the specific pitcher its looking for
           - 1 cell has some value the appropriate pitcher has placed ther
    - Catcher cell
           - 1 cell that has some functions that result in it posting some request to the pitcher of interest (this is the 1st user cell above...could be an index, name, string, etc)
           - 1 cell that pulls the value from the page cell (2nd one above)
                  (can play with null values and Dependson to gate the acquisition of the page value cell)
     - All pitches cells
           - 1 cell to determine if the catcher is asking this given pitcher to respond
           - 1 cell to actually place the value in the page value cell

B)
   - set up a couple user cells on the page (means this whole thing becomes a template)
   - make the catcher shape post its ID to the page user cells (may also want to post the catchers intended target pitcher of interest....so that you dong get all the pitchers pitching)
   - Pitcher shape pulls the catcher ID from page and uses that to pitch to the catcher

C)
   - Could do A or B and use actions on the given cell to coordinate pitching and catching....probably makes the logic simpler

In the past, I played around with A ( get a bi direction comm channel between 2 shapes without VBA) and got it working....but in the end, it proved to be too complex to be really useful
If you fish around this board, you may find my post from about 2 years ago when I played with it

To be candid...A and B make the quotes game you just played look trivial.

Also, once you cross the bridge of a template....then the only thing holding you back from using VBA is the security concerns (will your users be comfortable enabling macros)
if ok with macros....could do alot richer kinds of things in VBA.

nashwaan

  • Full Member
  • ***
  • Posts: 85
Thanks vojo for the extra ideas... They are a bit over my head and i think these are more generic solutions.

The kind of problem i am trying to solve is simpler because it is realationship between a parent shape and a child shape in a group.

If you look at the attached document, there is a "sky" shape that contains three other shapes "Cloud", "Sun", and "Moon".
When a user selects one of the options in the Shape Data window, the width of the red rectangle adjusts itself to the selected shape width.







Note the width of the red rectangle changes according to selected shape. Also, note that the formulas relationship is maintained even if the shape is moved or copied to same/other page.

I am not sure if i have implemented this in this best way; but this fits my requirements.

Thanks,
Yousuf.
Give me six hours to chop down a tree and I will spend the first four sharpening the axe — Abraham Lincoln

vojo

  • Hero Member
  • *****
  • Posts: 1594
np....if they are in a group, then you are going way way way way way way way (did I say way??) out of your way to get this done

If a group, the shape relations are static and known.   So if known, forumlas are static / fixed...no quotes games, no exotic parsing, no tunneling thru the page cells, etc
You could just code up the references directly....dont need any of the stuff you are talking about to accomplish your goal

user.cellx  = setf(getref(sheet.2492!user.shape8456width), sheet.8456!width)

shape 2492 and shape 8456 would be know apriori and  fixed since they are in a group.

 

nashwaan

  • Full Member
  • ***
  • Posts: 85
Yes, you are right vojo.

My initial approach was wrong: I was trying to make the parent shape to fetch the width of the selected child shape.
i stored the string of NameIDs of the child shapes in the parent shape and get the width of the selected child shape by evaluating the NameID of the child shape using the SETF() trick that you suggested.

Then I redesigned the solution in a way that a child shape posts its width to the parent shape whenever it gets selected. The name of the parent shape was used in each of the child shape without being enclosed in a string. Slightly more work but robust design.

However, it comes to me occasionally the problem of evaluating an expression inside a string which only now (after your post) i know how to evaluate it using the SETF() trick.  8)

Thanks,
Yousuf.
Give me six hours to chop down a tree and I will spend the first four sharpening the axe — Abraham Lincoln

Visisthebest

  • Hero Member
  • *****
  • Posts: 519
Re: How to evaluate a ShapeSheet expression which is inside a string?
« Reply #8 on: January 14, 2022, 06:57:39 AM »
Vojo this is a great trick but I cannot get it to work on a user cell like your example (Visio 2019). Good you indicated this is not reliable, but would be great if it was at least on user cells!

Ideally I'd like to put the ID() in one cell then create a string in the other, but even with a string in the original cell it does not work.

sometimes for some things you can trick visio.

Use 1,2,3 sets of "" around the text you want   (I use this trick with SETF)

something like

user.cell =  """" & user.cellsource & """"

Wont kid you ....only works sometimes on some things (not reliable) and is quite tricky to use
Visio 2021 Professional

wapperdude

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 4161
  • Ideas Visio-lized into solutions
Hmmmm.  This might be solvable using containers rather than groups.  Just a thought.. 
Visio 2019 Pro