Suppose I have a workbook, with formula in error in a couple of cells (such as "=10/0", or whatever producing an error).
Via VBA, I read the workbook, and copy cells value in an array via the following lines:
Dim wb As Excel.Workbook
Set wb ActiveWorkbook
Dim ws As Excel.Worksheet
Set ws = wb.Worksheets(1)
Dim rng As Excel.Range
Set rng = ws.Range("A1:D10")
Dim data() As Variant
data = rng.Value
Dim i As Long
Dim j As Long
For i = Lbound(data, 1) to Ubound(data, 1)
For j = Lbound(data, 2) to Ubound(data, 2)
Debug.Print data(i, j) & vbNullString
The Debug.Print line will produce an error bc one cell formula is in error.
How would you handle this ?
I Noticed that the array will hold an Error Object, but I was unable to interract with it much.
I also noticed that the Cstr() function work, but it produce a human readable string (translated in the local language of my system).
I'm looking for a generic solution to handle this, if possible without having to torture myself with local settings (those are a PITA).