VBA - Change Decimal Separator

[Vihar]

I have a code that may OR may not run on computers with decimal separator "," (comma), but my code is running specifically for "."(dot) separator.
During the execution, several new workbooks are created and saved as text files.
I managed to write the following code (saved in the "This Workbook" module), but it solves the problem only for This Workbook. I need a code that solves the issue for all Excel workbooks, or even better - changes the system settings (Windows settings).
The settings should be restored as soon as This Workbook is closed.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Application
.DecimalSeparator = ","
.UseSystemSeparators = True
End With
End Sub

Private Sub Workbook_Open()
With Application
.DecimalSeparator = "."
.UseSystemSeparators = False
End With
End Sub

[GTO]

If you change system settings and the code/workbook/excel hiccup, your users may become less than enthralled.

[snb]

crossposted: http://www.ozgrid.com/forum/showthread.php?t=186918&p=706264#post706264

[SamT]

Public CurrentDecimalSeparator As Variant


Private Sub Workbook_Open()

With Application
CurrentDecimalSeparator = .DecimalSeparator
.DecimalSeparator = "."
.UseSystemSeparators = False
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

With Application
.DecimalSeparator = CurrentDecimalSeparator
.UseSystemSeparators = True
End With
End Sub

[Ago]

Public CurrentDecimalSeparator As Variant


Private Sub Workbook_Open()

With Application
CurrentDecimalSeparator = .DecimalSeparator
.DecimalSeparator = "."
.UseSystemSeparators = False
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

With Application
.DecimalSeparator = CurrentDecimalSeparator
.UseSystemSeparators = True
End With
End Sub


But will that work if the code crashes?
If you get the msgbox with a error and choose to end the code, will the beforeclose code run when I close the workbook?

I have the same "issue", and also need a solution on this.

[SamT]

That code was not tested by me.

I assume you are worried about youor code crashing. If it does not crash Excel, my code won't be effected. The worst that can happen is that when opening the workbook again (after an Excel Crash) you will need to open it with Macros disabled and manually reset the System decimal separator.

That is why you always test your code before putting it into production.

IMO, a better solution is to use a variable or constant
ds As String = Application.DecimalSeparator and use it wherever the code uses a decimal string.
X = CStr(A decimal number)
InStr(X, ds)

[Ago]

Worried... Well not to much but it could.

The problem is not only VBA decimalseperator, but also the Excel sheet decimalseperator.
My code reads, writes and calculates both in VBA and as normal sheetformulas. And then it uses the sheetdata and creates textfiles.

So yes, a tad worried that if one datapoint is incorrect it does crash.


I also thought about creating a error handler that in case of crash resets the decimalseperator, but most of my subs and functions already have one or more error handlers, so making sure a "firendly" error that I can handle does not reset the decimalseperator is going to be very hard I guess.

EDIT: I just tried to create a crash in the main sub and the pressed end.
After that it did not read the Beforeclose code.

[Ago]

I think I have found a solution to my problem with this, could possibly be modified to work for others too.

I have not written the code yet, I just have it in my head.

Have a addin that runs on workbookopen (basicly it will run every time you start Excel), in this code reset the sperators to system default.
Then in your normal code (where needed) set seperators to what you need them to be for this project..

Wouldn't that work?

[Kenneth Hobs]

Those things can be tricky. I would suggest trying a registry method. The Open event could check the registry to see if the Close event was performed. See VBA methods: SaveSetting, GetSetting, DeleteSetting.

[Ago]

I had no idea VBA could access the registry.
But why wouldn't it? :-)

I found this that describes some of how it works. http://www.jkp-ads.com/articles/DistributeMacro08.asp
As I understand it, I will still need a addin that checks if the open or close event was done last tim?
The registry is only meant to save the events.

My initial thought was to do as I usually do, create a textfile and save the settings or events there.
But it looks like a fun thing to test.

Does one or the other way have any advantages/disadvantages?

[snb]

I don't think the OP will return.

@Ago

Maybe it's best to start a new line of inquiry .. ?