Instant Isometrics

[Thomas Winkel]

It is sad you need to watch out for that stuff all the time...
Most code is jut not tested with different locale.

True, we can be happy that MS didn't also localize the ShapeSheet formulas like they did in Excel

[Nikolay]

Well, actually isn't it done quite smart in Visio?

I think if you always use xxxxxU functions (like "FormulaU", "ResultsU", etc), then it's fine - it's always in US locale. But the moment you mix (like, use "FormulaU" but "Result" like in this example), you are doooomed

[asdfgh]

Hi Thomas,

I did several changes into "Rotation In a Shape_64 - Copy" but having an issur into RotateControlPoints (see below).

Code Select Expand

Sub RotateControlPoints(shp As Visio.Shape, ang As Double)
    Dim Width As Double, Height As Double
    Dim LocPinX As Double, LocPinY As Double
    Dim X As Double, Y As Double
    Dim NewX As Double, NewY As Double
    Dim n As Long, I As Long
   
    Width = shp.Cells("WIDTH").Result(visMillimeters)
    Height = shp.Cells("HEIGHT").Result(visMillimeters)
    LocPinX = shp.Cells("LocPinX").Result(visMillimeters)
    LocPinY = shp.Cells("LocPinY").Result(visMillimeters)
   
    n = shp.RowCount(visSectionControls)
    If n >= 1 Then
        For I = 0 To n - 1
            If (ThisDocument.GoTextRotate = True) Or _
                    ( _
                        (shp.CellsSRC(visSectionControls, I, visCtlX).Name <> "Controls.TxPos") And _
                        (ThisDocument.GoTextRotate = False) _
                    ) Then
           
                    X = shp.CellsSRC(visSectionControls, I, visCtlX).Result(visMillimeters) - LocPinX
                    Y = shp.CellsSRC(visSectionControls, I, visCtlY).Result(visMillimeters) - LocPinY
                   
                    NewX = XRotate(X, Y, ang, LocPinX)
                    NewY = YRotate(X, Y, ang, LocPinY)
                   
                    'shp.CellsSRC(visSectionControls, I, visCtlX).FormulaU = "WIDTH*" & NewX / Width
                    shp.CellsSRC(visSectionControls, I, visCtlX).FormulaU = "WIDTH*" & Replace(NewX / Width)
                    'shp.CellsSRC(visSectionControls, I, visCtlY).FormulaU = "HEIGHT*" & NewY / Height
                    shp.CellsSRC(visSectionControls, I, visCtlY).FormulaU = "HEIGHT*" & Replace(NewY / Height)
                    'shp.CellsSRC(visSectionFirstComponent + iGeom - 1, Irow, 0).FormulaU = "WIDTH*" & Replace((X(1) * Cos(ang) - Y(1) * Sin(ang) + LocPinX) / Width, ",", ".")
                    'shp.CellsSRC(visSectionFirstComponent + iGeom - 1, Irow, 1).FormulaU = "HEIGHT*" & Replace((X(1) * Sin(ang) + Y(1) * Cos(ang) + LocPinY) / Height, ",", ".")
                               
            End If
        Next
    End If
   
End Sub


[Paul Herber]

Watch out for divide-by-zero errors if the shape's Width or Height are zero.

[Nikolay]

I would suggest you to try using stencil before the "Replace(....)" and just change your locale to use "," instead of "." for now maybe? If you are in the USA, change your country to Canada, for example. That should do.

Otherwise, probably the stencil needs to be fixed more carefully, i.e. the actual problem needs to be fixed (using a mixture of "U" and "non-U" functions), and the "replace coma with dot" approach may need some improvement, as it may have some side effects like the one you faced.

[Thomas Winkel]

I cannot review your document.
But in your code above I see a wrong usage of the replace function:
Replace(NewX / Width)
-> newString = Replace(originalString, searchString, replaceString)

[asdfgh]

I put the following but got execution error '-2032464666 (86db0ce6):

Code Select Expand

shp.CellsSRC(visSectionControls, I, visCtlX).FormulaU = "WIDTH*" & Replace(X, NewX, NewX / Width)
shp.CellsSRC(visSectionControls, I, visCtlY).FormulaU = "HEIGHT*" & Replace(Y, NewY, NewY / Height)


[wapperdude]

Yeah.  That isn't going to work.  The syntax is still wrong.  Here's link to description of Replace fcn: https://learn.microsoft.com/en-us/office/client-developer/visio/replace-function-visioshapesheet.

The syntax is REPLACE (old_text, start_num, num_chars, new_text ).  Your use case is incorrect.  The above link has an example.

Some suggestions...
> Declare n and i as integers, not long.
> get your language syntax correct as previously recommended. 
> for sake of debugging, introduce a couple more variables so you can check code as you step thru it...
    > pNewX = NewX/width.  This will give you value that Visio uses, "." or ","  before an execution error occurs.
    > pre-build your formula as a string:  sfNewX = "Width * " & pNewX.
    > create your entry: blah,blah.formulau = sfNewX.

If you have the drawing window and vba windows open, side-by-side, you can watch execution line by line with <f8>.  In drawing window, have shapesheet open, too.  That way, you can see what formula is pushed into control point coordinates.

Sample test code:

Code Select Expand

Sub test()
    Dim pVal As Double
    Dim sVal As String
    Dim gVal As String
    Dim vShp As Visio.Shape
    Dim Width As Double
    Dim Height As Double
   
    Set vShp = ActivePage.Shapes.ItemFromID(1)
    Width = vShp.Cells("WIDTH").Result(inch)
    Height = vShp.Cells("HEIGHT").Result(inch)

    pVal = Height / Width
    sVal = "Width * " & pVal
    vShp.Cells("PinX").FormulaU = sVal    'I used PinX to receive new formula, for simplicity...and laziness.
   
End Sub


[wapperdude]

   
Just realized, that the REPLACE function is a shapesheet function.  It is NOT a programming (VBA) function.  Not sure how I overlooked that.  So, for coding purposes, it must be placed literally into a shapesheet cell, not the evaluated outcome.

[Thomas Winkel]

Ok, lets convert the following line of code step by step:
shp.CellsSRC(visSectionControls, I, visCtlX).FormulaU = "WIDTH*" & NewX / Width

Code Select Expand

Dim originalString as String
Dim searchString as String
Dim replaceString as String
Dim newString as String

originalString = "WIDTH*" & NewX / Width
searchString = ","
replaceString = "."

newString = Replace(originalString, searchString, replaceString)

shp.CellsSRC(visSectionControls, I, visCtlX).FormulaU = newString

Or, in one line of code:
shp.CellsSRC(visSectionControls, I, visCtlX).FormulaU = Replace("WIDTH*" & NewX / Width, ",", ".")

[Surrogate]

that the REPLACE function is a shapesheet function.  It is NOT a programming (VBA) function

Yes, we have SUBSTITUTE function (ShapeSheet) it same as REPLACE function (VBA).

[wapperdude]

Bit of confusion.  There is a REPLACE shapesheet function, similar to SUBSTITUTE function.  This is here: https://learn.microsoft.com/en-us/office/client-developer/visio/replace-function-visioshapesheet, as I had previously indicated.

Like a neutrino passing by, I failed to notice that the topic was dealing with the VBA REPLACE fcn, not the shapesheet version: https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/replace-function

BTW, there is similarly, two syntactically different SUBSTITUTE functions, one for shapesheet and one for  VBA.  Just saying.  User beware.

Hope that clears up any confusion I may have introduced.  My apologies.

[wapperdude]

Finally, to find either a shapesheet fcn or a VBA fcn, here are links to both...

SHAPESHEET:  https://learn.microsoft.com/en-us/office/client-developer/visio/functions-visio-shapesheet-reference

VBA:  https://learn.microsoft.com/en-us/office/vba/language/reference/functions-visual-basic-for-applications

[Thomas Winkel]

I was thinking about what Nikolay said about Xxxxxx vs XxxxxxU.
To be honest, I never bothered to really understand it. Instead, I always copied my working solutions...

But if we apply this new insight to the original problem:
shp.CellsSRC(visSectionControls, I, visCtlX).FormulaU = "WIDTH*" & NewX / Width

FormulaU accepts a string that must be a valid Visio formula in universal format.
Universal means that the decimal point is always ".", the delimiter is always ",".

Using the & operator to combine a string with a double implicit converts the double to string using the current locale.
So, the result of the string operation ("WIDTH*" & NewX / Width) could be:
"WIDTH*0.1234E-2" for US systems or "WIDTH*0,1234E-2" for German systems.
Only the first is a valid formula for FormulaU.

But, if we use Formula instead, this will always accept whatever the string conversion results in, because it's in the same locale.
(Formula is the local version of FormulaU and thus depends on your current locale setting.)
I think this is much better, the Replace() approach felt like a dirty hack from the start.

Long story short: remove the "U" and it will work without Replace().



P.S.
I don't suggest just always using local parameters.

[wapperdude]

@Thomas:  tried to give this a double like, but, that's not an option.