ShapeSheet formula to test for "No Formula"

[jw76novice]

ETA: This post is RESOLVED in post #3 below.

Is there a function, or combination of functions, to test if a ShapeSheet cell contains the "No Formula" value?  I want this to be a ShapeSheet-only solution, no VBA.  Visio doesn't have many of the Excel functions that would normally do this - ISEMPTY, ISBLANK, or even ISTEXT.  What I'm wanting to do is display empty text until the user enters a value into both Shape Data fields, at which point I want to display the concatenated string.

Prop.TagPrefix = No Formula
Prop.TagNumber = No Formula
Prop.Tag = Prop.TagPrefix & Prop.TagNumber

The Prop.Tag value as written above appears as 00 (no quotes), but I want it to be empty text if either cell contains No Formula.

A trick in Access VBA, where code may deal with multiple data types, is to concatenate a Variant with vbNullString, which forces Null, Empty, Missing, etc. to a zero-length string.  So Len(varAnything & vbNullString) returns zero if varAnything doesn't have a value and a non-zero value if it does.  So you can do If Len(varTest & vbNullString) Then... regardless of what data type varTest may contain.  Pretty slick.  In the ShapeSheet, though, a formula of LEN(Prop.TagPrefix) returns a value of 1.0000, not zero!

So looking at different functions, I thought FORMAT might force No Formula into something I could evaluate, when I found another interesting quirk:
FORMAT(Prop.TagPrefix, "text") = A1899xA

Here's what MSDN says about the FORMAT function's 2nd argument:

Code Select Expand

   Character        Description
--------------------------------------------------------------------------------------------------------------------
"text" or 'text'    Displays the text enclosed in quotation marks AS IS. See also \ (backslash).
  \                 Displays the next character AS IS. To display the backslash character, type \\. See also "text".


ETA: Ignore the rest of this post as it doesn't work when a value is entered instead of No Formula.  See post #3 below for the resolution. :END ETA

So then I tried the backslash argument - FORMAT(Prop.TagPrefix, "\") - and the result appeared to be an empty cell.  So wrap this with the LEN function and voila!

LEN(FORMAT(Prop.TagPrefix, "\")) = 0.0000

So while I have found a combination of functions to meet my goal - display empty text unless the user has entered values in both Shape Data fields - does anyone know of a simpler function to accomplish this?

The final formula right now would be: =IF(AND(LEN(FORMAT(Prop.TagPrefix,"\"), LEN(FORMAT(Prop.TagNumber,"\")), Prop.TagPrefix & Prop.TagNumber , "")

[Yacine]

=GUARD(IF(OR(STRSAME(Prop.TagPrefix,0),STRSAME(Prop.TagPrefix,""),STRSAME(Prop.TagNumber,0),STRSAME(Prop.TagNumber,"")),"",Prop.TagPrefix&Prop.TagNumber))
The "Guard" ist just in case you expose the tag file in the prop section and want to avoid your users to overwrite the formula.


That's indeed way too complicated to accomplish and not obvious at all.
My pleasure,
Y.

[jw76novice]

Thanks for the reply, Yacine.  Right now, I have the Prop.Tag.Invisible field set to True, but I'll go ahead and add the GUARD function in case I decide to change it to False later.  As for the STRSAME function test for "" and/or zero, it doesn't always work for No Formula.  More details below.

RESOLVED: Today I found the FORMULAEXISTS function, which does exactly what I need - returns False if "No Formula" is in a cell (any cell I tried, at least) and True otherwise.

Replying to my own original post, the LEN(FORMAT([somecell], "\")) test doesn't work at all.  It does return zero for all "No Formula" tests that I tried, but it also stays zero whenever the cell has a value, which isn't helpful at all.

More details:
So it turns out that not all ShapeSheet cells are the same!
Here is what I found testing the various cells in the Shape Data and Actions sections of the ShapeSheet when they contain No Formula:

• STRSAME([cell], "") returns TRUE for all cells expecting a String including:
    - Shape Data: Label, Prompt, Format, SortKey
    - Actions: Menu, TagName, ButtonFace, SortKey
    - For all these cells, the LEN([cell]) function returns zero.
• STRSAME([cell], 0) returns TRUE for all cells expecting a Number and some Boolean (and perhaps Date) values including:
    - Shape Data: Type, Value, LangID, Calendar
    - Actions: Action, Checked, Disabled
    - For all these cells, the LEN([cell]) function returns 1.
• Now there are some cells for which both STRSAME tests above return FALSE.  These include:
    - Shape Data: Invisible, Ask (Verify)
    - Actions: ReadOnly, Invisible, BeginGroup, FlyoutChild
    - For all these cells, the LEN([cell]) function returns 5.  I believe this is because, internally, Visio is evaluating the "No Formula" value as the Boolean FALSE, which when converted to text, "FALSE" is 5 characters.  This is reinforced by changing the No Formula to TRUE, which changes the LEN function output to 4, which is the number of characters in the word "TRUE".

So it appears if you want to test for No Formula in any ShapeSheet cell, the FORMULAEXISTS function is the only reliable way, but after all, that appears to be exactly what the function's purpose is!