Handling cells in error

Hi,

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
    Next
Next

Open in new window

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).
LVL 13
Fabrice LambertFabrice LambertAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may try it like this...

If Not IsError(data(i, j)) Then Debug.Print data(i, j) & vbNullString

Open in new window

0
Fabrice LambertFabrice LambertAuthor Commented:
This just ignore the error, not really what I'm looking for.

What if I want to react differently based on the error ? divide by 0 ? reference error (#ref) ? Not applicable error (#N/A) ? ect ...
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
One way is this...
Dim ErrVal
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)
        If Not IsError(data(i, j)) Then
            Debug.Print data(i, j) & vbNullString
        Else
            ErrVal = CVErr(data(i, j))
            Select Case ErrVal
                Case CVErr(xlErrDiv0)
                    Debug.Print "#DIV/0!"
                Case CVErr(xlErrNA)
                    Debug.Print "#N/A"
                Case CVErr(xlErrNum)
                    Debug.Print "#NUM!"
                Case CVErr(xlErrRef)
                    Debug.Print "#REF!"
                Case CVErr(xlErrValue)
                    Debug.Print "#VALUE!"
            End Select
        End If
    Next
Next

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Fabrice LambertFabrice LambertAuthor Commented:
Thank you.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Fabrice!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.