What's with SETATREF anyway?

[wapperdude]

There was a post regarding using SETATREF and a couple of comments regarding the need to have an explanation in "English", that is, readable and understandable, with examples, and so on.  I'm not proposing that the following provides the detail desired, as that would take more time than has elapsed as of this posting.  But, I totally agree with the desire to de-bunk the mystery surrounding SETATREF.

So, here's a beginning.  The following attachments look at the simple, fundamental features of SETATREF, and even a SETATREFEXPR example.  For the most part, the Visio doc has 6 pages, and begin with a very simple application and then expand from there.  Not included, yet, is the more elaborate SETATREF(SETATEVAL(SETATEXPR())) construct.  I (or anyone else) do plan to touch upon that.  But I wanted to get the basic info out now, rather than wait for a full blown development.  (Besides, I'm not sure I should be the one to do that.)

The Word document is, well, more wordy!      It starts with the MS supplied function definitions.  I've made some comments, some "English" changes, and tried to make it more readable.  Between the Word and Visio docs, there ought to be enough info to get a more confortable acquaintance with SETATREF.

There are quite a few SETATREF examples on this forum.  Perhaps the best way to summarize the function is the 3 I's:  intercept, inherit, intermediary.

I hope this proves to be useful.  For now, enjoy the two attachments. 

Wapperdude

[wapperdude]

...and now the Word document...

[wapperdude]

Saw a recent post re controlling shape width using shapetext.  This was briefly touched upon in the previously attached files wrt the SETATREFEXPR() function.  But, I was curious about extending this to that recent post.  Namely, enter text into a group shape, or container I suppose, push the text into the subshapes.  Each subshape has it's width controlled by the greater of its current width or the applied text.  Then, the group shape will adjust its width based upon the width of the subshapes. 

A couple of caveats:  1.)  The group shape needs to be resized via UI, and 2.) the subshape widths cannot change size with group shape manual resizing.

To do this, it seemed like a natural extension to use the SETATREF family of functions.  Specifically, it SETATREFEXPR(), and, what about SETATREFEVAL()?  Could the later be used without invoking the SETATREF wrapper?     The goal was to NOT introduce an intermediary parameter which the SETATREF needs.  Fortunately, the answer is YES!!!   

The advantage of the SETATREF fcns is that no need for GUARD which prevents manual resizing...unless that's desired.  No circular argument issues.

One thing to be aware of:  initialization...the setatrefexpr should have a starting value or the width will collapse to zero.  This value will be overwritten once text is entered. Remember, the function only fires based upon an event.  So, adding the function to a shape with pre-existing text can potentially collapse to zero, as no event has been fired.

Updated the Visio file:  added 2nd page showing the process flow for controlling the shape widths.

[wapperdude]

Here's an update to the SETATREF functionality file.  I've added a 7th page.  It tries to illuminate the features of the full expression: SETATREF(SETATREVEVAL(SETATREFEXPR()) using a simple example of slaving one shape to another.  It involves controlling just the PinX, so only the horizontal movements are slaved, not the vertical.  It also involves adding one "reference parameter", which I have dubbed the "intermediary".  I think, IMHO  , that my term has a more natural understanding.  No, you are not allowed to disagree!   

By UI, I mean either dragging the shape, or typing an entry into the X value cell of the Size and Position Window.

The various shapes do have the formulas shown in their respective shapesheets.  The displayed text values will update as the shapes are moved. 

Enjoy!
Wapperdude

[wapperdude]

Here's a pic of the new page.  The 500KB limit prevented posting both the png and vsd at same time.

Wapperdude

[wapperdude]

Here's another update to the file, an 8th page.  This page kinda brings everything together in a single "coloring" example.  All of the SETATREF techniques on this new page were touched upon on previous pages in the Visio file.

The goal, as always, is to have formula driven relations and accommodate direct user interface.  In this example, the shape fill color is entered either by direct UI, e.g., the menu bar (or the ribbon) or by using shapedata.  The text color is derived from the shape fill color by a formula or, again by UI.  The coloring formula comes from VisioGuy blog site:  http://www.visguy.com/2008/10/31/make-your-text-stand-out/.  Finally, the line color and text color are linked together so that they always match, but either color may be set by the other.

Wapperdude

[wapperdude]

Using SETATREF with "IF" function isn't as obvious as one might expect.  It is a little quirky.  Normally, the syntax would be:  IF(test condition, do IF true, do IF false).

So, let's say you want to set one parameter if true, and another if false.  Based upon syntax, the temptation would be:  IF(test condition, SETATREF(User.R1,SETATREFEXPR()), SETATREF(User.R2,SETATREFEXPR())).    The expectation is that if the test condition is TRUE, then the 1st SETATREF is used, and if FALSE, the 2nd SETATREF is used.  Well, this syntax has the unexpected result of setting both User.R1 and User.R2, regardless of the text condition.  SETATREF just fires.  That just the way it is.

The solution is to modify the SETATREF statement:  SETATREF(IF(test condition, User.R1, User.R2),SETATREFEXPR()).  Since SETATREF always fires, then the target intermediary must change to correctly direct the intercept action.  Moving the IF statement inside the SETATREF, guarantees that it always is evaluated.  Thus, based upon the test condition, either User.R1 or User.R2 will be the selected intermediary.

Note, it may be necessary to wrap the SETATREFEXPR() with the SETATREFEVAL, depending upon how things are triggered.  If the formula is in the cell that is being changed, the "eval" is optional.  If some other cell is the trigger, the "eval" is required.  This is demonstrated in the previously attached Visio file.  The full syntax would be:  SETATREF(IF(test condition, User.R1, User.R2),SETATREFEVAL(SETATREFEXPR())).

HTH
Wapperdude

[wapperdude]

Another update to the Visio file, it's now at Rev3.  Added another page, its genesis is a recent post regarding pushing text from one shape into another.  While investigating that scenario, found a solution that uses the SETATREF functions.  Interestingly, also found an alternative syntax to prior examples, in the Visio file, more in keeping with the spirit of SETATREF.  Those examples have all been updated.

Specifically, the examples involved controlling the shape rounding of multiple shapes.  The initial syntax employed was: SETATREF(Prop.Bend) + SETF(GetRef(Sheet.2!Prop.Bend),Prop.Bend))).  This works perfectly fine.  It's the catenation of two functions.

What I discovered was those two functions could be merged into a single SETATREF function.  The resulting syntax is:  SETATREF(Prop.Bend,SETATREFEVAL(SETF(GetRef(Sheet.2!Prop.Bend),Prop.Bend)))  Both versions seem to work identically.  Not sure which is more efficient or if there are drawbacks to this 2nd implementation.  Use either.

Wapperdude

[wapperdude]

Well, this is close to being an obsession!  Add another page, so, Rev4.  Again, spawned by another recent post.  This new page also demonstrates catenating commands within the SETATREF function.

Unfortunately, the file size is getting too large and I had to delete the simple page examples.  Guess that means Rev3 will be the last with the simple examples.

Enjoy.

Ooops!  Forgot to say what this last page is about.     It is about control, the struggle of power, to assert domination.  The desire to be free and independent. 
It is The Tale of Four Shapes!   

 

Wapperdude

[wapperdude]

In response to another post, the following file was generated which uses the full instantiation of the SETATREF() function.  It takes Shape Data entry (use dbl clk for easy access), and sends to an intermediate value.  This allows the Shape Data entry to be updated.  It also includes SETF() function to send an RGB formula to the LineColor cell.  The Shape Data is used to vary the line color based upon an applied formula to the Shape Data.

Within SETATREF is the SETATREFEVAL which handles both the intermediary value and the RGB calculation.  The RGB calculation is for only the "B" term of the RGB function.  The formula scales the Shape Data to provide a "B" value range of 0 to 255 corresponding to Shape Data range of 0 to 10.  "Wrapping" the formula is the INT() fcn to limit values to whole numbers.  This is "wrapped" within the Bound fcn which limits the "B" value range. 

Wapperdude

[cliff50]

excuse my belated reply

excellent explanation