Checking if cell exists

[Alex]

Hi everyone,
I wonder if anyone has come across a way to check if a cell exists, for example, in the Data section.  For this example, I made up a fake function called CheckIfCellExists that accepts a string as a parameter and returns TRUE if the argument is a cell that already exists and FALSE if it does not. 

Ideally, I would like to have something like this:

User.MyCheck:   = IF(CheckIfCellExists("Prop.Cell1"),"Cell1 exists in the Data Section", "Cell1 does not exist in the Data Section" )


Unfortunately, I don't know VBA or how to insert and invoke VBA code in a shape, so I'd like to do create the CheckIfCellExists functionality using only functions in shapesheet itself.

Initially I thought that any of the functions that test for errors or if a cell has a formula would help but I noticed that all the arguments in shapesheet functions that refer to a cell are of type cellref, which require that the cell exists before. 

I also found that SETF() accepts a string in double quotes as its first argument, even if the cell does not exist, but I don't know how to leverage that to solve my problem.

Any ideas? 

Many thanks in advance,


Alex

[Yacine]

In VBA there's a standard function that covers this issue: https://msdn.microsoft.com/en-us/library/office/ff766195.aspx?f=255&MSPPError=-2147217396

But I understand that you want to solve it in the shapesheet.
In this case your first thought consisting of inserting the reference of the cell you're looking for is correct. (SETF(GetRef)...)
Checking if the assignment was successful is then done via FORMULAEXISTS.
The problem is, when the target cell has already been assigned a correct value, it won't be emptied if the next assignement fails.
Luckily Visio interprets parts of a formula one after the other and from left to right. So the solution would consist of two consecutive assignments: SETF(GetRef(User.Row_2),"") to empty the cell, then SETF(GetRef(User.Row_2),"prop."&Prop.CheckFor) to fill it again.
HTH,
Y.

[Visio Guy]

IFERROR can work, too.

https://msdn.microsoft.com/en-us/library/office/ff765598.aspx

[Yacine]

Well, I'm obviously in the same situation as Wapperdude: stuck with V2007.
IFERROR is for V2010 upwards.
Time to retire is getting closer.

[ITWorkingSolutions]

Hey guy's, arriving very late to this but have a follow up question related.
The specific scenario is using a custom Data Graphic Set and trying to display the value on the shape in the shape. Unfortunately, MS doesn't add the source field to Prop.msvCalloutField for User.msvCalloutType = "Icon Set". But it does add the value to a user defined field. Unfortunately, again the user-defined field is User.visDGField[a number], the important part is [a number] which is unknown at the time of defining the custom Data Graphic Set.

So reading the great examples from you guy's I could try (I gave up at 12, but we could go to any number, that's the problem) :
User.visTestDGField = =IFERROR(SETF(GetRef(User.visAADGField),"User.visDGField"),IFERROR(SETF(GetRef(User.visAADGField),"User.visDGField1"),IFERROR(SETF(GetRef(User.visAADGField),"User.visDGField2"),IFERROR(SETF(GetRef(User.visAADGField),"User.visDGField3"),IFERROR(SETF(GetRef(User.visAADGField),"User.visDGField4"),IFERROR(SETF(GetRef(User.visAADGField),"User.visDGField5"),IFERROR(SETF(GetRef(User.visAADGField),"User.visDGField6"),IFERROR(SETF(GetRef(User.visAADGField),"User.visDGField7"),IFERROR(SETF(GetRef(User.visAADGField),"User.visDGField8"),IFERROR(SETF(GetRef(User.visAADGField),"User.visDGField9"),IFERROR(SETF(GetRef(User.visAADGField),"User.visDGField10"),IFERROR(SETF(GetRef(User.visAADGField),"User.visDGField11"),IFERROR(SETF(GetRef(User.visAADGField),"User.visDGField12"),"")))))))))))))

User.visAADGField = [result from User.visTestDGField that can be used]

So the skew on this problem is we don't know the increment number of the user-defined cell name that MS will allocate.

Anyway was after brain power greater than me. There might be a better way of approaching this. I did look at the other Data Graphic Set msvCalloutType's which populate Prop.msvCalloutField but I need the 'Icon Set' selection method.

Thanks for any advice.

[wapperdude]

I did a little bit of searching, found David Parker's work on Icon Sets.  Did not investigate in detail, but this article seems like a gateway for his efforts related to the topic.  That is, at the bottom is a list of related links.  Hope this helps...https://bvisual.net/2015/03/18/a-table-of-visio-data-graphic-icon-sets/

[Yacine]

Hi,
Your formulation is confusing, sorry.

I understand, that you don't only want an icon, but also the value as text in the smartgraphic.
Why don't you build the set accordingly. E.g.: an icon set PLUS a text display? That's out of the box, no tweaking.

[ITWorkingSolutions]

Hi,
Your formulation is confusing, sorry.

I understand, that you don't only want an icon, but also the value as text in the smartgraphic.
Why don't you build the set accordingly. E.g.: an icon set PLUS a text display? That's out of the box, no tweaking.

Hi Yacine, sorry I probably could have explained better. The text is dynamic depending on what the user selects as the value when the use the Icon Set. The issue is visio doesn't populate prop.msvCalloutField for an Icon Set. It does for the other types i.e. Text or Data Bar. What MS does set is User.visDGField[sequential count]. So I'm trying to find a smart way of finding what the user-defined name is when I don't know the [sequential count] value. The one method is using the testing method, but I don't know how high [sequential Count] might go.

Another method might be to scan the formula in user.msvCalloutIconNumber and get the name, but I don't know how to get the formula in shapesheet. Below is an example of user.msvCalloutIconNumber where the User.visDGField10 name is in. But I can't see a method to get the formula rather than the value to use a combination of find and other methods to get the name from. I may be asking the impossible.

=IF(STRSAME(User.visDGField10,"Level 1",TRUE),0,IF(STRSAME(User.visDGField10,"Level 2",TRUE),1,IF(STRSAME(User.visDGField10,"Level 3",TRUE),2,IF(STRSAME(User.visDGField10,"Level 4",TRUE),3,IF(STRSAME(User.visDGField10,"Level 5",TRUE),4,-1)))))

Hope this explains a bit better.

[ITWorkingSolutions]

I did a little bit of searching, found David Parker's work on Icon Sets.  Did not investigate in detail, but this article seems like a gateway for his efforts related to the topic.  That is, at the bottom is a list of related links.  Hope this helps...https://bvisual.net/2015/03/18/a-table-of-visio-data-graphic-icon-sets/

Hey thanks wapperdude, David Parker is legend but hasn't solved this one that I can see. I have a real cludge in testing none to 50 sequential numbers, but I have no idea if the number could go to 100, 200, or even higher. Hence need a better way of getting name... if one exists.

Thanks...

[wapperdude]

The attached file has a shape with 5 rows in the User section.  There is also the VBA code shown below.  Thjis code loops thru the User section and checks to see which rows, if any, have row name that contains "From" as the root.  Both the name and entry value arre debug printed.

Code

Sub findRow()
    Dim vsoShp As Visio.Shape
    Dim RowCnt As Integer
    Dim CellVal As String
    
    Set vsoShp = ActiveWindow.Selection(1)  'Select shape to examine
    RowCnt = vsoShp.RowCount(Visio.visSectionUser)  'Determine how many rows in the User section
    sect = Visio.VisSectionIndices.visSectionUser   'Deterine the ID of the User section.  Optioonal.  Less typing.
    
    For i = 0 To RowCnt - 1                              'Row index starts with zero, not 1
        Rname = "User." & vsoShp.CellsSRC(sect, i, 1).RowName           'This is the full row name
        CellVal = vsoShp.CellsSRC(sect, i, 0).ResultStr(Visio.visNone)  'This is the value stored
        If Rname Like "User.From*" Then                                 'This does string comparison allowing for wildcard variants;  successful compare prints out name and value
            Debug.Print Rname
            Debug.Print CellVal
        End If
    Next
        
End Sub

Modify the code to meet your search needs.  Since the comparison test is desired string variant, versus actual row names, there is no error for non-existinting row. 

HTH

[Yacine]

Sorry for sticking with my previous idea.


Have a look at the attachment. It shows that you don't need complicated tweaking for displaying both an icon and the text.


Correct me if I misunderstood.

[ITWorkingSolutions]

Thanks guy's, I should have said I'm not in control of the diagram. I'm allowing the user to create new Icon Set's where they can select shape, size and colors using an add-in. They also have the option to display the 'label' which is the bit I'm having problems with. Once created they can use the standard Visio capability to attach them to shapes in the document. As I want the text inside the icon the callout, although a great idea doesn't achieve what I'm after. The attached shows an example of the output, but the formula doesn't guarantee success. Infact generating the example gave a result where visDGField is appended with a letter rather than a number!  :'(

[ITWorkingSolutions]

Hi guy's, thanks for your input, much appreciated.

To close this out and spending far too many hours working out how Data Graphics (DG) work behind the scenes, I have landed on a less than ideal but workable solution. By creating DG as a 'Text Callout' but adding the values it can evaluate to at time of creating the master you can get a graphic with both text and color changes. This will allow the use to create elliptical and polygon shapes of varying width/height and select the color for each DG. After creating they can be use it in the same way as standard DG Text Callout to associate with a Shape Data field. As I said the only issue is the operator/value/color are part of the master, but I think this works as good as I can get, unless someone knows a trick I haven't found! For now, I've wasted enough hours on this.