CODEWITHSUNDEEP
vbaexceldate
Matteo NNZ
Matteo NNZ

Reputation: 12665

VBA: interpeting English date format as string

I have a spreadsheet containing dates in English format: 

12 Feb 2015
13 Oct 2016
etc.

However, since my MS Excel is in French, it doesn't take them as dates but rather as strings.

I need to do some parsing with those values, so I run my procedure but something annoying happens. VBA, set in English no matter the OS/Microsoft language, gets the 12 Feb 2015 as a date and so, when I print it back, it returns me the French date (12-févr-2015, read as 12/02/2015). This stops me comparing that value with the previous 12 Feb 2015.

Is there anyway I can ask VBA not to take the initiative of interpreting my string as a date?

Here is a simplified version of my code + test case (but I guess you won't be able to see the issue if running it on an English Excel):

  1. Put the string 12 Feb 2015 in the Range A1 of your spreadsheet

  2. Run the following code:

    Sub test()
    myVal = Range("A1").Value
    Range("A2").Value = myVal
End Sub

  3. The value in Range("A2") will be a date in your Excel language, and not a string as it was before.

Note: I have thought of writing Range("A2").Value = "'" & myVal to let the value be a String instead of a date, but it doesn't make me possible to later compare 12 Feb 2015 with '12 Feb 2015 and, anyway, 12 Feb 2015 becomes a date already when it's read by myVal = Range("A1").Value.

Upvotes: 0

Views: 234

Answers (1)

Wolfie
Wolfie

Reputation: 30046

If you want to retain the string, just use Text in place of Value, and define the storage variable as a string. Like so

Sub test()
    Dim myVal as String
    myVal = Range("A1").Text
    Range("A2").Value = myVal
End Sub

Text is read only, so still using Value to write to the new cell. Docs which include an example for the difference between Value and Text: https://msdn.microsoft.com/en-us/library/office/ff840217.aspx

Upvotes: 2

Related Questions