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 10
Fabrice LambertFabrice LambertAsked:
Who is Participating?
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 ConnectWise

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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.