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
– user2140173, Commented Oct 17, 2014 at 9:35
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
– Dear Deer, Commented Oct 17, 2014 at 10:31
date1 = CDate(Replace(Cells(ActiveCell.Row, 7).Value, ".", "/")) gives me "Type mismatch" error. — Dear Deer
– Dear Deer, Commented Oct 17, 2014 at 10:33
put MsgBox Cells(activecell.row, 7).Value as the first line in your Sub and tell us what the value is — user2140173
– user2140173, Commented Oct 17, 2014 at 10:42
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
– Dear Deer, Commented Oct 17, 2014 at 12:40

ajay_whiz · Accepted Answer · 2019-01-04 15:45:44Z

6

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

edited Jan 4, 2019 at 15:45

ajay_whiz

18.1k4 gold badges39 silver badges44 bronze badges

answered Oct 17, 2014 at 10:57

Matteo NNZ

12.8k13 gold badges63 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TheTrowser Over a year ago

More general: DateValue(year & "-" + month & "-" & day)

Collectives™ on Stack Overflow

VBA creating date with strings

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related