VBA -- faster way to deal with numbers stored as text

user_689242 0
Viewed 64

I have this function which manages to remove all numbers stored as text:

Public Function find_numbers_formated_as_text(ByVal sh As Worksheet)
    
    Dim c As Range
    
    On Error GoTo A
    
    ''''For Each r In sh.UsedRange.Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
    For Each c In sh.ListObjects(1).DataBodyRange
    ''''For Each c In sh.UsedRange.Cells.SpecialCells(xlCellTypeConstants, xlNumbers)
    ''''For Each c In sh.ListObjects(1).DataBodyRange.Cells.SpecialCells(xlCellTypeConstants, xlNumbers)
        If IsNumeric(c.Value) = True Then
            c.Value = c.Value
        End If
    Next c
    
    Exit Function
    
A:
    On Error GoTo 0
End Function

But it is really slow... Does anyone have any suggestion on how to make it faster?

I did try some other things which is why there are some of the comments in the source code. But comments didn't work, because range was also empty in my case (even if table was full of data).

vba excel
3 Answers

Please, replace this part of your code:

     For Each C In sh.ListObjects(1).DataBodyRange
    ''''For Each c In sh.UsedRange.Cells.SpecialCells(xlCellTypeConstants, xlNumbers)
    ''''For Each c In sh.ListObjects(1).DataBodyRange.Cells.SpecialCells(xlCellTypeConstants, xlNumbers)
        If IsNumeric(C.Value) = True Then
            C.Value = C.Value
        End If
    Next C

with this one:

  Dim lRng As Range, arr, i As Long, j As Long
  
  Set lRng = sh.ListObjects(1).DataBodyRange
  arr = lRng.Value2
  For i = 1 To UBound(arr)
    For j = 1 To UBound(arr, 2)
        If IsNumeric(arr(i, j)) Then arr(i, j) = CDbl(arr(i, j))
    Next j
  Next i
  With lRng
        .NumberFormat = "General"
        .Value2 = arr
  End With

It places the range in an array and all process take place only in memory, the modified array content being dropped at the end of the code, at once.

The most time consuming is the iteration between each cell and writing cell bay cell...

If "General" formatting may bother your list object format, please state in which columns the conversion should be done, and I will adapt the code to format only the respective table columns.

Now I could see one of your comments saying that in the range to be processed exist (and must remain) formulas. The above code does not deal with such a situation. You should state it in your question, I think...

Edited:

If the formulas used to return a numeric value (not a string) and, by mistake the respective range has been formatted as Text, you can try the next way (to maintain the formulas):

    With sh.ListObjects(1).DataBodyRange
        .NumberFormat = "General"
        .Formula = .Formula
    End With
user_22333080

There are lots of answers to this question. This is a simple thing to try. Add this before:

Application.Calculation = xlManual

and this after:

Application.Calculation = xlAutomatic
user_32463620

It's faster to store the range as array than changing values in the sheet.

sh.ListObjects(1).DataBodyRange.formula = sh.ListObjects(1).DataBodyRange.formula

The numbers will default to numbers if they were text so you don't need to test if it's number.

(You will not lose formulas using this method.)

user_114695190
Related