-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

Improve this question
5
  • Change Decimal separator settings in File --> Options --> Advanced (Use system separators section) Commented Nov 2, 2017 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. Commented Nov 2, 2017 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? Commented Nov 2, 2017 at 15:49
  • OK, in that case update your question for exact requirement. Commented Nov 2, 2017 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. Commented Nov 2, 2017 at 15:58

3 Answers 3

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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

Improve this answer

2 Comments

This is more a comment than an answer
Is there a way in VBA to detect which system separator is currently used?
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
Improve this answer

6 Comments

Exactly the right idea, although, personally I'd write 0.5 rather than a 1 / 2. Have an upvote!
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.
Application.DecimalSeparator will do this for you. It sets or returns the character used for the decimal separator as a string --> msdn.microsoft.com/en-us/vba/excel-vba/articles/…
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: stackoverflow.com/questions/39752188/…
@PankajJaju no. Application.DecimalSeparator="." seems to change only the way dec sep is displayed in the cells. Not in arrays.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.