-1

I would like to change VBA array decimal separator to dot. I see it as comma. I tried: Application.DecimalSeparator="."

But when I get the value as MyString = matrix(2, 1), the decimal separator in VBA arrays maliciously persists as comma. I am not able to get rid of the pest.

Is there a way to detect which system separator for VBA arrays is used?

enter image description here

Community
  • 1
  • 1
Przemyslaw Remin
  • 6,276
  • 25
  • 113
  • 191
  • Change *Decimal separator* settings in File --> Options --> Advanced (Use system separators section) – Pankaj Jaju Nov 02 '17 at 15:46
  • I do not want to do it. My solution as to be portable and work with any system. I would not like to force user to change settings. – Przemyslaw Remin Nov 02 '17 at 15:48
  • Obviously when it comes to the debugger, you can probably live with it. In which specific circumstances is it intolerable to your users? – Bathsheba Nov 02 '17 at 15:49
  • OK, in that case update your question for exact requirement. – Pankaj Jaju Nov 02 '17 at 15:49
  • What separator your IDE is using has no impact whatsoever on your code's portability. Notice the [Type] column: it says `Variant/Double`, which means that `3,72` is understood as a `Double` and that's all you need to know: it will be displayed as `3.72` on a system that uses a dot separator, and as `3%72` on a silly system that uses `%` as a decimal separator: the string representation of the number in the debugger has no bearing on its actual value. – Mathieu Guindon Nov 02 '17 at 15:58

3 Answers3

2

VBA uses quite a few bits drawn from various parts of the platform to work out which decimal and thousands separator to use. Application.DecimalSeparator changes a few instances (mostly on the workbook); you can tweak others at the OS level, but even then though you get to a couple of cases where you can't change the settings.

Your best bet is to write a simple function to check which separator your platform uses based on a trial conversion of say 1.2 to a string and see what the second character ends up being. Crude but strangely beautiful.

Armed with that you can force an interchange of . and , as appropriate. Naturally then though you will have to manage all string to number parsing yourself, with some care.

Personally though I think this is epitomises an unnecessary fight with your system settings. Therefore I would leave everything as it is; grin and bear it in other words.

Bathsheba
  • 231,907
  • 34
  • 361
  • 483
0

You have to change it in system settings, Excel takes this kind of settings from system.

Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69
0

I have end up with this function which does exactly what I want. Thank you all for answers, comments and hints. 

Function GetVBAdecimalSep()
    Dim a(0) As Variant
    a(0) = 1 / 2
    GetVBAdecimalSep = Mid(a(0), 2, 1)
End Function
Przemyslaw Remin
  • 6,276
  • 25
  • 113
  • 191
  • 1
    Exactly the right idea, although, personally I'd write 0.5 rather than a 1 / 2. Have an upvote! – Bathsheba Nov 02 '17 at 16:01
  • 1
    It scares me that you think you need to care about this. You say you're worried about portability, but caring for how decimals are represented as strings is only likely to *cause* portability issues. This has all looks of an X-Y problem, and if that's the case then this is the right solution to the wrong problem, and you have a lot of headaches coming your way. – Mathieu Guindon Nov 02 '17 at 16:04
  • 1
    `Application.DecimalSeparator` will do this for you. It sets or returns the character used for the decimal separator as a string --> https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-decimalseparator-property-excel – Pankaj Jaju Nov 02 '17 at 16:10
  • No, guys! I have to fight with darted decimal separators to push the right string with correct numeric values to SQL Server. The reason why I want it may be found in this question: https://stackoverflow.com/questions/39752188/split-string-into-table-given-row-delimiter-and-column-delimiter-in-sql-server – Przemyslaw Remin Nov 02 '17 at 16:11
  • 1
    @PankajJaju no. Application.DecimalSeparator="." seems to change only the way dec sep is displayed in the cells. Not in arrays. – Przemyslaw Remin Nov 02 '17 at 16:13
  • @PrzemyslawRemin - Ahh! Now I see what you are trying to achieve. Cheers. – Pankaj Jaju Nov 02 '17 at 16:17