VBA Function inside Custom Formula

[TheCykor]

Dear all,

i am working on a quite nice and flexible interface diagram where I wand to show different text on all connecting lines, based on some checkboxes. I am currently doing this based on Custom Formula which works quite well:

Code Select Expand


=IF(STRSAME(EVALTEXT(ShowID!TheText),"1"),Prop._VisDM_ID,"")&IF(AND(STRSAME(EVALTEXT(ShowID!TheText),"1"),STRSAME(EVALTEXT(ShowUseCase!TheText),"1")),CHAR(10),"")&IF(STRSAME(EVALTEXT(ShowUseCase!TheText),"1"),Prop._VisDM_Category,"")&IF(AND(STRSAME(EVALTEXT(ShowType!TheText),"1"),STRSAME(EVALTEXT(ShowUseCase!TheText),"1")),CHAR(10),"")&IF(STRSAME(EVALTEXT(ShowType!TheText),"1"),Prop._VisDM_Protocol,"")&IF(AND(NOT(STRSAME(EVALTEXT(Prop._VisDM_Proxy),"")),STRSAME(EVALTEXT(ShowProxy!TheText),"1")),"("&Prop._VisDM_Proxy&")","")


However as I have several hundred lines maintaing this is very complex (If I want to add some new options, ...) So I would prefer adding a own VBA Function there like

Code Select Expand


=MYFUNCTION(Prop)


my assumption is that Prop contains all relevant information for this specific line, but also can pass anything else

However this does not seem to work.

Can I somehow define a function returning a string and reference this out of the Custom Formula?
How Can I pass the relevant argument to this function?

Edit: Relevant Info - I am using Visio 2010

I am very happy for any answer

BR

TheCykor

[Yacine]

Hi,
the function, you're looking for is CALLTHIS:

https://msdn.microsoft.com/en-us/library/office/ff766171.aspx?f=255&MSPPError=-2147217396

[TheCykor]

Hi Yacine, and thanks for the hint

However i just played around with it, and I am not shure that it will fulfill my needs as

The CALLTHIS function always evaluates to 0, and the call to procedure occurs during idle time after the recalculation process finishes. Procedure can return a value, but Visio ignores it. Procedure returns a value that Visio can recognize by setting the formula or result of another cell in the document, but not the cell that called procedure, unless you want to overwrite the CALLTHIS formula.

So if this does not work, I assume another option is just writing a function that walks through all elements and sets the text manually, correct? This afterwards can be called on any change event of checkboxes, dropdowns, ...

[Yacine]

Hi TC,
To use CALLTHIS as routine retruning a value - ie as function, you would typically write the result in a cell of the shape's shapesheet.

sub myRoutine(shp as shape)
  shp.cells("prop.myRow").formulaU= myResult

The alternative you named is a viable solution too: iterating through all the shapes of the page, checking each one if it is the right one, then modifiying it.
Something like:

for each shp in activepage.shapes
if shp.cellexists("prop.RowXY", false) then
  shp.Cells("prop.RowXY").FormulaU=temp
end if
next shp