CODEWITHSUNDEEP
excelvbadate
Cachirro
Cachirro

Reputation: 13

VBA changes my variable content

i have this value inserted in a cell "02-01-2013"

  Range("C2").Offset(aPos, 0).NumberFormat = "dd/mm/yy;@"
  Range("C2").Offset(aPos, 0).Value = forma(t1, 2, t2)
  MsgBox forma(t1, 2, t2)

which the MsgBox command there displays correctly, so the string is arriving correctly to the code, cause the msgbox displays it ok, however when the second line here does it's job it records it as "01-02-2013", note the day/month inversion, i've tried everything and can't figure out why this happens, i even added the 1st line to force format before receiving the data, and still no luck. All input fields are formated correctly in dd/mm/yy.

I need this so I can make a sort, and as it is, it kills me.

Upvotes: 0

Views: 179

Answers (1)

Tim Williams
Tim Williams

Reputation: 166136

When placing a string value into a date-formatted cell you're asking Excel to convert that value to an actual date. How it does that can depend on your local settings and on the actual value...

Sub Tester()
    Debug.Print CDate("02-01-2013") '2/1/2013
    Debug.Print CDate("20-01-2013") '1/20/2013
    Debug.Print CDate("02-22-2013") '2/22/2013
End Sub

If you want more control and you know your strings are in a specific format:

Dim tmp As String, arr, dt As Date
tmp = "02-01-2013" 'forma(t1, 2, t2)
arr = Split(tmp, "-")
dt = DateSerial(arr(2), arr(1), arr(0))
Debug.Print dt

Upvotes: 1

Related Questions