Check VBA Macro (follow-up)

Magpie Bavarde
Magpie Bavarde used Ask the Experts™
on
Hello hello,

I think I shouldnt have "marked as solution" as a thanks for your help on this topic
https://www.experts-exchange.com/questions/29166939/Check-VBA-Macro-3.html

... as I couldnt add my file to answer Martin question

And this question interests me because I think Martin has something interesting regarding my regional settings and so on.

So, I'll remind my both codes and attach a picture of a typical result. If you see any way to standardize this so that it will work on most machines and most settings to avoid headaches to get our formats rights, it will be very welcome !

Format-Macros.png
Sub TextToNumbers()
    '
    ' ToNumbers Macro
    ' Converts the selected column to numbers (when text stored as numbers)
    Range(Selection, Selection.End(xlDown)).Select
    With Selection
        Selection.NumberFormat = General
        .Value = .Value
    End With
End Sub

Open in new window


Sub ToNumbers()
    Range(Selection, Selection.End(xlDown)).Select
    With Selection
        Selection.Replace What:=",", Replacement:=".", LookAt:=xlPart, _
                          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                          ReplaceFormat:=False
    End With
    Range(Selection, Selection.End(xlDown)).Select
    With Selection
        Selection.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
    End With
End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
HuaMin ChenProblem resolver

Commented:
Excel file (with Macro inside) should be saved as Excel file Macro-Enabled sheet (.xlsm file).

What is issue now to run macro vs your file?
Magpie BavardeExecutive Assistant

Author

Commented:
Hello,

Yes I know about the macro format, why do you say that ?

There's no real issue, it's working fine for me, but I'm wondering if I can share with coworkers who dont have the same regional settings, and Martin was wondering what my macro looked like on my own machine... so I show that in case it can help helping me getting something stable I can share with colleagues with different regional settings

Thanks for your attention !
HuaMin ChenProblem resolver
Commented:
If you can run the macro on one machine, it will also work within other machines.

Usually we have issue like

Mm/dd
Dd/mm

to the date format in  Excel.
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
If you can run the macro on one machine, it will also work within other machines.
That's not strictly true in the case of 32-bit versus 64-bit environments. For example if you use something like LockWindowUpdate in a macro, you need to do something like this when you declare the function.

#If Win64 Or VB7 Then
    Private Declare PtrSafe Function LockWindowUpdate Lib “user32” (ByVal hwndLock As LongPtr) As LongPtr
#Else
    Private Declare Function LockWindowUpdate Lib “user32” (ByVal hwnd As Long) As Long
#End If

Open in new window

"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
I'm wondering if I can share with coworkers who dont have the same regional settings
You can test it by temporarily changing your own regional setting.
Magpie BavardeExecutive Assistant

Author

Commented:
Yes of course and I've done that with frensh vs. english but I cant test for all our regional formats because we got plants all around the worlds in like 30 different countries...

The thing very weird (for me) is that the following macro that replaces the commas by dots (french -> english) is not only working the other way around (english -> french) but is mandatory ! If I don't replace the commas by dots, the macro fails to convert text to numbers. If I use the macro, its all working perfectly well (for what I want = format to numbers) but I cant understand why

Anyway, your answers are comforting, I was thinking it wouldnt have the slightest chance to work, I understand the chances are huge

So... I will just try and see what the co-workers say ;)

Thank you very much for your help everyone !


Sub ToNumbers()
    Range(Selection, Selection.End(xlDown)).Select
    With Selection
        Selection.Replace What:=",", Replacement:=".", LookAt:=xlPart, _
                          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                          ReplaceFormat:=False
    End With
    Range(Selection, Selection.End(xlDown)).Select
    With Selection
        Selection.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
    End With
End Sub

Open in new window

Magpie BavardeExecutive Assistant

Author

Commented:
Thank you very much !
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You're welcome, but testing 30 different settings while tedious, only needs to be done once.
Magpie BavardeExecutive Assistant

Author

Commented:
Yeah but I just cant, my machine is stuck with only french and english regional formats...
The idea was more to know if there was obvious big issues with this type of code and it probably isnt... we'll see ;)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial