How to format currency value

[Nikolay]

I have a property of type "Currency". How do I get the same format as in the "Properties" window?

In the "define property" I see:


In the Properties window I see:


In the ShapeSheet I see:


But the ResultStr function gives "123,4500"
The Application.FormatResult(...) gives the same.

But it should be "123,45 €"

In general, I don't get the "$" (or euro) symbol in the output at all.
Instead, it just gives the flat number, with long number of digits.
Does currency formatting work in Visio API at all?

How can I get the properly formatted currency value (using VBA)?

I mean, there are also options how user can format that. How do I format the value the same way (programmatically)?


I've consulted this great article on the similar subject (got a bug that currency export format is invalid), but it seems to do the "FormatResult" thing (so not helping really in this particular case of currency):
https://bvisual.net/2015/07/24/formatting-shape-data-in-visio/

[Yacine]

First remark: a currency field seems to rely on the GUI to function propperly. When using the GUI Visio includes the value as parameter to a CY-function.
That could be the solution to your problem. ie: write the values accordingly in that prop.

otherwise none of the result type functions seemed to make a useful use of the visCurrency parameter.
I've listed below the debug.print results for various result functions and for both the formula type value and the raw one.


If you would rather tackle the problem at the reading, then the regular FORMAT function of VBA would be your choice.

                                                                     =CY(123.45,"EUR")   123.45
shp.Cells("prop.row_1").ResultStr("")                              123,45 €   123,45
shp.Cells("prop.row_1").ResultStr(visCurrency)                123,45 €   123,45
shp.Cells("prop.row_1").Result(visCurrency)                       123,45   123,45
shp.Cells("prop.row_1").Result(visNoCast)                          123,45   123,45
shp.Cells("prop.row_1").ResultIU                                        123,45   123,45
Format(shp.Cells("prop.row_1").Result(visNoCast), "0.## EUR")   123,45 EUR   123,45 EUR



Rgds,

[wapperdude]

I'm a bit confused...it's not clear what your ultimate goal is.   Are you trying to stufftrying to a currency value into shapesheet prop cell or trying to show the prop.cell value as a currency value in the shape?

The macro recorder can provide code for both.  Hope this helps. 

Code Select Expand


Sub Macro1()

'Cleaned  up macro recorder results...
'First section creates shape data entry in shapesheet, formats the entry, and sets value to 17.76
   
    Dim vsoShape1 As Visio.Shape
    Dim intPropRow2 As Integer
   
    Set vsoShape1 = ActiveWindow.Page.Shapes.ItemFromID(1)
    intPropRow2 = vsoShape1.AddRow(visSectionProp, visRowLast, visTagDefault)
    vsoShape1.Section(visSectionProp).Row(intPropRow2).NameU = "Crncy"
    vsoShape1.CellsSRC(visSectionProp, intPropRow2, visCustPropsLabel).FormulaU = """Currency"""
    vsoShape1.CellsSRC(visSectionProp, intPropRow2, visCustPropsType).FormulaU = "7"
    vsoShape1.CellsSRC(visSectionProp, intPropRow2, visCustPropsFormat).FormulaU = """U 0.00"""
    vsoShape1.CellsSRC(visSectionProp, intPropRow2, visCustPropsLangID).FormulaU = "1033"
    vsoShape1.CellsSRC(visSectionProp, intPropRow2, visCustPropsCalendar).FormulaU = ""
    vsoShape1.CellsSRC(visSectionProp, intPropRow2, visCustPropsPrompt).FormulaU = """""""Enter  value."""""""
    vsoShape1.CellsSRC(visSectionProp, intPropRow2, visCustPropsValue).FormulaU = "CY(17.76,""USD"")"
    vsoShape1.CellsSRC(visSectionProp, intPropRow2, visCustPropsSortKey).FormulaU = ""

'Second section takes the shape data entry and field inserts it to display the formated value in the shape

    ActiveWindow.DeselectAll
    ActiveWindow.Select Application.ActiveWindow.Page.Shapes.ItemFromID(1), visSelect
    Dim vsoCharacters4 As Visio.Characters
    Set vsoCharacters4 = Application.ActiveWindow.Selection.Item(1).Characters
    vsoCharacters4.Begin = 0
    vsoCharacters4.End = 0
    vsoCharacters4.AddCustomFieldU "Prop.Crncy", 255

End Sub



[Nikolay]

Thank you for quick responses

The ultimate goal is to replicate Visio formatting. The original problem is actually a reported bug in my svgpublish Visio extension. It exports the properties as well, and shows on HTML page.
It is supposed to show them exactly as they are formatted in the "properties" window in Visio. The problem is, it is not the case for the "Currency", it differs.

So I'm basically trying to format (any) currency property (defined by user) the same way Visio does (convert value to a string, not to a shape), using Visio API, but somehow this is not happening

Meaning, the code I want probably needs to be somehwat generic, it's not EUR, but rather should work from Visio formatting.
In the format combobox above, it may be even defined like this:

Code Select Expand


FORMAT   => VALUE
         => €2.75
@        => €2.75
0.00 U   => 2.25 €
0.00 UU  => 2.75 Euro
0.00 UUU => 2.75 EUR

These "formats" are Visio-specific as far as I understand, right?



I expected the "Application.FormatResult" to take care of those, but it seems that somehow it does not.
So I thought maybe there is some other function that I should use?

Formatting currency myself, like Yacine suggests ("CY"), sounds as a viable option.
But I thought maybe there could be still some better option. Especially considering the possibility of those custom formats.

[wapperdude]

Ah!  Thank you.  Now I get it!  Phew.  The only thing I was sure of was that I hadnt answered the question.  I think Yacine probably has most reasonable answers.  It seems as though Visio doesn't provide many currency tools. 

[wapperdude]

The only impact upon the formating that I was able to invoke was in Text Fields' Format cell.  Changes were pretty limited and include how many digits, spacing between currency symbol and value, and deleting the symbol.  And o, the  Value cell entry. 

In the Shape Data section, only the Value cell was pliable to showing changes.  Visio reads the local system definitions for the most part.

Not much help, I'm afraid.