Setting Shapesheet value twice with VBA

[TwoBeAss]

Hi,
i have a situation where i need to set a custom property inside a vba function to TRUE and direclty to false again. The True value should trigger some things inside the shapesheet, but need to be resettet after the function is finished.

Code Select Expand

   
If vsoShape.CellsU("Prop.Hide").ResultStr(Visio.VisUnitCodes.visNumber) = "TRUE" Then
        vsoShape.CellsU("Prop.Hide").FormulaU = "FALSE"
        Debug.Print "Setting back to old mode..."
        vsoShape.CellsU("Prop.Hide").FormulaU = "TRUE"
    End If


First of all, is there a better solution to use a boolean property then my solution ??
Second If i run that code step by step it is working, but if i just run it regular its not...

What happens is that the Property changes the ObjType of the shape for a short time from 2 to 4 and back, cause this helps me getting some other calculations in the background...

I think i need some sort of "redraw" or "recalculate" the shapehseet to get this working in one routine...

Any idea appreciated, thanks!

[Yacine]

There's a lot going on this post.
1) ...ResultStr(Visio.VisUnitCodes.visNumber) = "TRUE" is indeed funny. You're casting to a number, but comparing to a string.[/size][/font][/color]
I always wanted to check for the right parameter to get booleans, but ended up using a lazy ResultStr("").
In my case it works, but there out be a better method to catch -1,0,null,"TRUE","FALSE", etc.

2) Swtiching - yes the intention is clear, but you may want to check also the "trigger" command of a cell.

[TwoBeAss]

I am sorry for my bad code, but i am not a programmer and most of my code is just a return of google searches

I thought there might be a solution like

Code Select Expand

vsoShape.CellsU("Prop.Hide").Result Then
...
End If
But i couldnt get it to work, so that "funny" part stayed cause it was working

2) This code is already part of a through cell "triggered" function. I am working on a similar approach to this blogpost
https://visualsignals.typepad.co.uk/vislog/2015/04/building-a-labelled-dynamic-connector-in-visio.html
And to prevent the shown connectors to add linejumps to hidden lines i need to set the ObjType of the hiddenConnector to IF(User.Hide,4,2). To update the Geometry2 Section after a connector is moved, i need to make him visible and switch back to unvisible again.

[Thomas Winkel]

Not sure if I understand correct.
But try the attached modification of Johns Labelled Dynamic Connector.

I only added a User cell to "Dynamic connector" in the document stencil:

User.SetConLineJumpCode=SETF(GetRef(ConLineJumpCode),IF(Prop.HideMidLine,4,2))

No code required.

[TwoBeAss]

Oh my god, i never thought it is that easy
Thank you so much... Wasnt aware of the ConLineJumpCode Cell !!

If anybody else has a better code for using booleans from shapesheet cells in VBA If statements, let me know

[wapperdude]

Since this deals with Boolean values, invoking Strcomp isn't necessary.  Below are a variety of syntax statements that will work.  Choose whichever you like.  Demo file attached.

Often, visNone may be omitted and just include the 2 dbl quotes. 

If you open the code window, place mouse cursor anywhere within the sub, then repeated hit <F8>, you can step thru the code line by line.  The 1st if checks for a false value, the 2nd if for a true value.  It helps to see what's happening if the active window and the code window are open side-by-side.

The demo file also uses Actions to control the setting of the User entry.  To use, right click the shape and a popup window appears.  Click on the "Toggle Color".

Code Select Expand


Sub Toggle_Color()
    Dim vShp As Visio.Shape
   
    Set vShp = ActivePage.Shapes.Item(1)
   
'    If vShp.Cells("User.Row_1").ResultStr(visNone) = "FALSE" Then
'    If Not (vShp.Cells("User.Row_1").ResultStr(visNone)) = "TRUE" Then
    If vShp.Cells("User.Row_1").Result(visNone) = 0 Then
        vShp.Cells("User.Row_1").Formula = True
    End If
   
'    If vShp.Cells("User.Row_1").ResultStr(visNone) Then
'    If vShp.Cells("User.Row_1").Result(visNone) = 1 Then
    If vShp.Cells("User.Row_1").Result("") = 1 Then
        vShp.Cells("User.Row_1").Formula = False
    End If
   
End Sub



[wapperdude]

There is one more syntax structure of interest.  This toggles, i.e., negates the current boolean value:

Code Select Expand


    If vShp.Cells("User.Row_1").Result("") = 1 Then
        vShp.Cells("User.Row_1").Formula = "Not(" & vShp.Cells("User.Row_1").FormulaU & ")"
    End If


[wapperdude]

Well, might as well make this complete, as it might apply to additional non-Boolean cases as well:

Code Select Expand


Sub Toggle_Color()
    Dim vShp As Visio.Shape
   
    Set vShp = ActivePage.Shapes.Item(1)
   
'    If vShp.Cells("User.Row_1").ResultStr(visNone) = "FALSE" Then
    If Not (vShp.Cells("User.Row_1").ResultStr("")) = "TRUE" Then
'    If vShp.Cells("User.Row_1").Result(visNone) = 0 Then
'        vShp.Cells("User.Row_1").Formula = True
'        vShp.Cells("User.Row_1").FormulaU = """True"""
        vShp.Cells("User.Row_1").FormulaU = Chr(34) & "True" & Chr(34)
    End If
   
'    If vShp.Cells("User.Row_1").ResultStr(visNone) Then
'    If vShp.Cells("User.Row_1").Result(visNone) = 1 Then
    If vShp.Cells("User.Row_1").Result("") = 1 Then
'        vShp.Cells("User.Row_1").Formula = False
        vShp.Cells("User.Row_1").Formula = "Not(" & vShp.Cells("User.Row_1").FormulaU & ")"
    End If
   
End Sub



[Thomas Winkel]

To be honest, all these ResultXyz properties and their parameters are still a huge black box for me

Anyhow, in VBA I do it like this:

shp.Cells("Prop.Test").ResultIU = Not shp.Cells("Prop.Test").ResultIU

But I have no idea if this is really the best way.

Using strings is dangerous because the values for true and false are language dependent.
For example this works in a US Visio, but will crash in an German Visio:

shp.Cells("Prop.Test").Formula = "false"

And vice versa:

shp.Cells("Prop.Test").Formula = "falsch"

[wapperdude]

Cool.  Another syntax option.  I believe this is perfectly valid.

The Result functions allow read/write of values for cells.  I hadn't looked at ResultIU before, nor even seen examples of it.  But, it uses the default internal units.  That alleviates need for the unit defs inside the parentheses.  All of the options work.  The ResultIU cleans up things.

The Formula functions allow read/write of formulas, typically.

Interesting point about the language issue.  Not having another version or even fluent enough to use another version, it is not something I think about. 

[wapperdude]

****  Sorry.  The ResultIU syntax is somewhat unreliable.  It does toggle.  But, the values it writes are numeric values.  These would normally be valid, except instead of "0" or "1", the syntax seems to produce "0" or "-1".  By themselves, even these produce the desired effect.  However, the "-1" causes error for boolean compare.

[Thomas Winkel]

Oh...
Thanks for investigate my method and sorry for posting bullshit
I can reproduce that it stores -1 or 0 instead of true or false.
I've never noticed that because all numbers except 0 are interpreted as true.
Can you post an example for the -1 error? Here it seems to work, see attached screenshot.

[Thomas Winkel]

Now I could also provoke a strange behavior.
Setting the field manual to true breaks it complete.
Then it always returns false and true in superposition
Setting it manually back to false "repairs" it.

I thing I have to review my project...

[wapperdude]

I initially took it as OK at face value.  But decided to try it.  Initially, in simple case, it worked as expected.  Then I went to a more realistic scenario, and it broke.  I was quite surprised.  I didn't want to believe it and tried various scenarios.  That's when I posted back...thought you should know in case you get unexpected problems.

Went back and re-tried.  The "-1" may be an issue for certain syntax constructs.  However, it can be OK for others.  Here iis the test code.  All commented out code lines are valid except for those with comments indicating otherwise.

So, this can be used with caution.

Code Select Expand


Sub Toggle_Color()
    Dim vShp As Visio.Shape
   
    Set vShp = ActivePage.Shapes.Item(1)
   
'    If vShp.Cells("User.Row_1").Result(visNone) = 0 Then
'    If vShp.Cells("User.Row_1").ResultIU = 0 Then
    If Not vShp.Cells("User.Row_1").ResultIU Then
'    If vShp.Cells("User.Row_1").ResultStr(visNone) = 0 Then
'        vShp.Cells("User.Row_1").Formula = Not vShp.Cells("User.Row_1").ResultIU
        vShp.Cells("User.Row_1").ResultIU = Not vShp.Cells("User.Row_1").ResultIU
    End If
   
    If vShp.Cells("User.Row_1").ResultIU Then               'this is ok
'    If vShp.Cells("User.Row_1").ResultStr(visNone) Then    'this is ok

' *** Fails for cases wth explicit true condition
'    If vShp.Cells("User.Row_1").ResultStr(visNone) = "TRUE" Then    'this fails for -1;
'    If vShp.Cells("User.Row_1").Result(visNone) = 1 Then    'this fails for -1
'    If vShp.Cells("User.Row_1").Result("") = 1 Then         'this fails for -1
'        vShp.Cells("User.Row_1").Formula = False
'        vShp.Cells("User.Row_1").Formula = "Not(" & vShp.Cells("User.Row_1").FormulaU & ")"
        vShp.Cells("User.Row_1").ResultIU = Not vShp.Cells("User.Row_1").ResultIU
    End If
End Sub

[Thomas Winkel]

Thanks, wapperdude.

I checked our 15 year grown project and found many different methods of reading and writing boolean values.
All working in their conditions, but some funny workarounds like:

Code Select Expand


Public Sub Checkbox_onAction(control As IRibbonControl, pressed As Boolean)
    ' Translate boolean to fix german visio bug (true -> wahr, false -> falsch)
    Dim intPressed As Integer
    If pressed Then
        intPressed = 1
    Else
        intPressed = 0
    End If

    shp.Cells("Prop." & name).Formula = Chr(34) & intPressed & Chr(34)
End Sub


What do you think about the following method?

shp.Cells("Prop.Test").Formula = Not CBool(shp.Cells("Prop.Test"))


In C# this is not possible without .Result:

shp.Cells["Prop.Test"].Formula = Convert.ToString(!Convert.ToBoolean(shp.Cells["Prop.Test"].ResultIU));