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

what is the Cells(ActiveCell.Row, 7).Value returning? Can you try: date1 = CDate(Replace(Cells(ActiveCell.Row, 7).Value, ".", "/")) — user2140173
How can I check what does return Cells(ActiveCell.Row, 7).Value ? When I point a mouse above str2() it doesn't prompt me anything after executing Split method. On the other hand when I try to see its values like this : Dim strCheck1 As String strCheck1 = str2(0) it gives me an compile error "Subscript out of range" — Dear Deer
date1 = CDate(Replace(Cells(ActiveCell.Row, 7).Value, ".", "/")) gives me "Type mismatch" error. — Dear Deer
put MsgBox Cells(activecell.row, 7).Value as the first line in your Sub and tell us what the value is — user2140173
Sorry. I made mistake because I didn't notice that seventh column was empty ... So MsgBox Cells(activecell.row, 7) returned me an empty value. When I tried MsgBox Cells(activecell.row, 6) which was not empty it returned me 26.05.14 value — Dear Deer

Matteo NNZ Matteo NNZ · Accepted Answer · 2014-10-17T10:57:24

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))

VBA creating date with strings

1 Answers