Visio Discussions > ShapeSheet & Smart Shapes

Checking if cell exists

(1/3) > >>

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.

Navigation

[0] Message Index

[#] Next page