VBA creating date with strings

Question

I'm trying to do simple tasks with date type but what I'm getting is not what I want. I would like to Split some cell value with delimiter and return string values to DateValue method.

My code

Dim str2() As String
str2() = Split(Cells(ActiveCell.Row, 7).Value, ".")

Dim date1 As Date
date1 = DateValue(str(0) & "-" + str(1) & "-" & str(2))

What I'm trying to do is getting some specific date format. Date in the cell is formated like this : 26.05.14 (day.month.year) and I want to have format like this 2014-05-26(year-month-day). That's all. DateValue method returs me a date 2000-01-02. I don't knwo how can I look into the str2() array to see what values are there after Split method. How can I do that ?

It seems VBA is completely different from VB.NET ... I'm not experienced with VBA. Thanks

Answer 1

You are storing the return of your split into "str2" but then using the variable "str" into the DateValue input, which is not defined in your code (so it's empty). Avoid this kind of mistakes by putting "Option Explicit" on top of the project, in VBA is not compulsory like VB.NET but, indeed, optional. Here is the working code:

Dim str2() As String
str2 = Split(Range("A2").Value, ".")

Dim date1 As Date
date1 = DateValue(str2(0) & "-" & str2(1) & "-" & str2(2))