How can I use function DATEVALUE in a code vba

Question

How can I use function DATEVALUE in a VBA code, in order to convert a cell from text to Date.

Ex. I have E1= "29/10/2014"

EXCEL recognizes it as text. If I double click on the cell it reverses it to date correctly.

I want to run a macro and convert all this values to Date. I must say that I have an excel sheet, Range A1:BJ10223 and columns with dates are the E,H,S,V,AB,AF,AJ,AL,AO,AS,AY,BE,BH. Not all dates are reflecting as text values. Some are ok.

Research my friend research! Try Google! – Siddharth Rout Nov 28 '14 at 12:49 — Siddharth Rout, Nov 28 '14 at 12:49

score 1 · Accepted Answer · answered Nov 28 '14 at 15:04

The most expedient method I'm aware of to convert pseudo-Text-Dates to actual date values is through the Data ► Data Tools ► Text-to-Columns command which can be looped through in VBA.

Sub TXT2DMY()
    Dim v As Long, vCOLs As Variant
    vCOLs = Array("E", "H", "S", "V", "AB", "AF", "AJ", "AL", "AO", "AS", "AY", "BE", "BH")
    With ActiveSheet
        For v = LBound(vCOLs) To UBound(vCOLs)
            If CBool(Application.CountA(.Columns(vCOLs(v)))) Then _
                .Columns(vCOLs(v)).TextToColumns Destination:=.Columns(vCOLs(v)), _
                  DataType:=xlFixedWidth, FieldInfo:=Array(0, 4)
        Next v
    End With
End Sub

The difference may not be appreciable on smaller worksheets but anything over 500 rows of data with that many columns will definitely be faster.

score 0 · Answer 2 · answered Nov 28 '14 at 12:50

0

The equivalent function in VBA is CDate which converts its argument (formally a Variant) to a Date type.

answered Nov 28 '14 at 12:50

Bathsheba

231,907
34
361
483

1

I am wondering why would you (of all the people) give the answer without letting the user at least making an effort? :) BTW `DATEVALUE` can be used in VBA as well :) – Siddharth Rout Nov 28 '14 at 12:56
The documentation for VBA is notoriously poor. Indeed `DATEVLAUE` can be used but `CDate` is more natural in VBA and also works in other office packages. – Bathsheba Nov 28 '14 at 12:57
1

`The documentation for VBA is notoriously poor.` Really? Umm, I would disagree. You may want to see this [link](http://wikisend.com/download/314576/untitled.png) – Siddharth Rout Nov 28 '14 at 12:58
2

Another [interesting read](http://stackoverflow.com/questions/24944662/difference-between-datevalue-and-cdate-in-vba) And hence a lack of research on OP's part! – Siddharth Rout Nov 28 '14 at 13:02

How can I use function DATEVALUE in a code vba

2 Answers2