We help IT Professionals succeed at work.

Check VBA macro (1)

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

I'm not good with VBA but I use it a lot, I usually record some stuff then ask my friend  google about what I can't get recorded. I know all my macros are probably very weak but as long as I'm the only one at risk it's not an issue. But this time some colleagues are interested and I don't want to "sell" them a rotten file. So... Would you please have a look on my macros and let me know if you see something that should be improved so I can share my file with good chances it will work for other people ? Your corrections are obviously welcome, but also your advices to help me understand why I should do this and not do that...

Thank you very much for your help, Kind regards,

Mélanie



MACRO 1 - I use this one A LOT, it's stored in my personal.XLSB for the numerous times I have to deal with french dates read as text and want to transform them into actual dates. I use the macro "on the go" or I use it within other macros.

Sub FormatDate()
    Range(Selection, Selection.End(xlDown)).Select
    Selection.TextToColumns Destination:=Range(Selection, Selection.End(xlDown)), DataType:=xlDelimited, _
                            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                            Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
                            :=Array(1, 4), TrailingMinusNumbers:=True
    Selection.NumberFormat = "[$-en-US]d-mmm-yy;@"
End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
It would be better practice to avoid selecting cells.
Sub FormatDate()
    Dim rg As Range
    Set rg = ActiveCell
    Set rg = Range(rg, rg.End(xlDown))
    rg.TextToColumns Destination:=rg, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 4), TrailingMinusNumbers:=True
    rg.NumberFormat = "[$-en-US]d-mmm-yy;@"
End Sub

Open in new window

byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
If the selected range of cells contains one or more blank cells, the suggested code above won't work as desired. In such a case, it would be better to use Selection instead of ActiveCell.
Sub FormatDate()
    Dim rg As Range
    Set rg = Selection
    Set rg = Range(rg, rg.End(xlDown))
    rg.TextToColumns Destination:=rg, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 4), TrailingMinusNumbers:=True
    rg.NumberFormat = "[$-en-US]d-mmm-yy;@"
End Sub

Open in new window

Magpie BavardeExecutive Assistant

Author

Commented:
Thank you very much byundt for those very useful advices !