Best (faster) way to apply formula to a range

[dtrujillano]

Hello,

I have a file 1 column and 100.000 rows filled with hexadecimal values that I want to convert to decimal.

I have to use VBA and I need to find something really fast to convert this data.

One idea is to copy the range in an array and access each element with a loop applying hex() function and then return to the sheet.

Other idea is to calculate the decimal value in the next column with Range ("A1:A100000").formular1c1 = "HEX2DEC(RC[-1])" and finally copy values of B1:B100000 to A1:A100000 and clear range B1:B100000.

I think this second method is faster but could it be possible to perform the second idea without making the clear and the copy/paste from column B to column A? (because these operations are wasting time in things that I do not need) Probably it would be even faster if I return directly the results in column A without using column B. I would appreciate any other suggestion.

Thank you

 

[hurgadion]

Hi,
try using a macro of the form:

Sub Witch()
Dim tbl(), i&

tbl = Range("A1:A100000")
For i = LBound(tbl, 1) To UBound(tbl, 1)
   tbl(i, 1) = Application.Hex2Dec(tbl(i, 1))
Next i
Cells(1, 1).Resize(100000) = tbl
End Sub

Best regards.