Access to VBA - a quick way to change Excel cell values ​​over a wide range?

Question

I have some VBA code in Access that gets a range of cells from an Excel worksheet and then converts them from a number to a date. A range, for example, would be H1: Y25000

Public Sub FixDates(theRange as Range) Dim RangeCell as Range For Each RangeCell In theRange If IsNumeric(RangeCell.Value) And RangeCell.Value > 0 And Not IsEmpty(RangeCell.Value) Then lngDate = CLng(RangeCell.Value) RangeCell.Value = DateAdd("d", lngDate -1, "1/1/1968") End If Next RangeCell End Sub 

The code seems to work, but it takes a very long time to run. Could it be written differently to work faster? I read about using Variant instead of Range, but I can't figure it out.

Oh, to call Sub, I use something like:

 path = "C:\myfile.xlsx" Set xlApp = CreateObject("Excel.Application") Set xlWbk = xlApp.Workbooks.Open(path) Set xlWks = xlWbk.Worksheets(1) lastRow = xlWks.Range("A1").End(xlDown).Row FixDates (xlWks.Range("H1:Y" & lastRow)) 

Thanks!

Answer 1

You can simply assign the range to the option that the array will create - it will look like the code below - you must add the appropriate error handling:

 Public Sub FixDates(theRange as Range) Dim data as Variant Dim i as Long Dim j as Long Dim lngDate as Long data = theRange For i = LBound(data, 1) to UBound(data, 1) For j = LBound(data, 2) to UBound(data, 2) If IsNumeric(data(i, j)) Then If data(i, j) > 0 Then lngDate = CLng(data(i, j)) data(i, j) = DateAdd("d", lngDate - 1, "1/1/1968") End If End If Next j Next i theRange = data End Sub