Error Handling

[richardSmith]

I have several hundred modules of code written in Excel 2000. We have just upgraded to Excel 2010, and some of the code doesn't execute exactly as it used to. Is there a way to just quickly add in the horrid statement of

On Error Resume Next

so that all errors will be suppressed and code will continue to execute as opposed to having to hit the debug button, comment out the line of code causing issues, write down the module name causing the issue, then step thro it later in a smaller scale to determine what the problem is?

[Jan Karel Pieterse]

Why would you want to ignore errors when they might need to be resolved in order for your macro to do the work it was designed for?

[richardSmith]

Why would you want to ignore errors when they might need to be resolved in order for your macro to do the work it was designed for?

The "bones" macro's that were written work perfect and those are the ones that are most important. The ones that always seem to throw an error are the "cosmetic" macro's which can easily be done manually, so it's okay to just ignore them. That's why I would just want to ignore them, it's more of a luxury to have them execute not a necessity.

[Bob Phillips]

I don't believe you can say that the bones work correctly, unless you are intimate with every line of code, in which case it would be better to strip out the code that throws errors and doesn't work.

I shudder to think how many years of VBA and other coding experience Jan Karel and myself have, but I think I can say that neither of us would ever do what you are suggesting in your position. The chances that you ignore an error that you shouldn't are just too great.

[richardSmith]

I don't believe you can say that the bones work correctly, unless you are intimate with every line of code, in which case it would be better to strip out the code that throws errors and doesn't work.

I shudder to think how many years of VBA and other coding experience Jan Karel and myself have, but I think I can say that neither of us would ever do what you are suggesting in your position. The chances that you ignore an error that you shouldn't are just too great.

Hmmm if two of the guru's here are advising against, it's probably not my brightest idea then. I'll go down the path you suggested of stripping out the code that throws an error and attempt to re-write in 2010 VBA.

Thanks to all for the advice!

[Jan Karel Pieterse]

Well, it is up to you to judge whether or not the macros in question are harmless when parts of them don't work. I think a rewrite is a good idea. You get to learn some of the pitfalls of upgrading to a newer version of Excel :-)

[richardSmith]

For example, this Function, no longer highlights the cell if the criteria is met in 2010, but worked to perfection in 2000

Public Function CheckTotal()
Dim tTotal
tTotal = Array("Total", "Cumulative Total")
If Sheets(1).Cells.Find(tTotal).Offset(0, 1).Value <> Range("A8").Value Then
Range("A8").Interior.ColorIndex = 3
On Error GoTo CheckTotalError
End If
Exit Function
CheckTotalError:
Range("A8").Interior.ColorIndex = 3
End Function

Does someone see an issue as to why it won't execute properly in 2010?

[Jan Karel Pieterse]

I've never seen a Find with an arrray argument. What is is supposed to find?

[Bob Phillips]

That error handler seems a bit superfluous, what can go wrong with an End If or Exit Function?

[richardSmith]

That error handler seems a bit superfluous, what can go wrong with an End If or Exit Function?

Sometimes people will key punch the data incorrect so it will be a slight misspelling of the one of the two items in the array, so I thought that adding in that error handler would set it up to just highlight the cell if it's not found either.

Essentially, if they do not match, or if the Array words are not found, highlight red.

[richardSmith]

I've never seen a Find with an arrray argument. What is is supposed to find?

Maybe I set it up totally incorrect then...I know it works great in 2000.

What it does is it searches for the text listed in the array, the workbook will ALWAYS contain one or the other, offsets one cell for the value. Then compares that value to (A8), if they match, it does nothing and moves on, if they do not match it fills cell A8 with red color so it jumps out at you, like hey look at me, something is wrong!

[Jan Karel Pieterse]

Could you upload a workbook with just the relevant information and the macro (and write down what values in which cells should trigger which action in the macro)?

[Aflatoon]

I don't believe the array does anything there - only the first text will be checked (most likely it will match either unless the user has done an entire cell find operation simply because 'Total' is contained in 'Cumulative Total')

[richardSmith]

(most likely it will match either unless the user has done an entire cell find operation simply because 'Total' is contained in 'Cumulative Total')

I'm not following what you meant by that?

[richardSmith]

Could you upload a workbook with just the relevant information and the macro (and write down what values in which cells should trigger which action in the macro)?

Actually let me back-track...this macro executes fine in 2010 when I place it in the workbook alone and execute. I will step-thro my full code and determine where the snare is and post back if I find something. Thanks for your time.

[Aflatoon]

I mean that using
tTotal = "Total"
would have the same effect

[Bob Phillips]

Sometimes people will key punch the data incorrect so it will be a slight misspelling of the one of the two items in the array, so I thought that adding in that error handler would set it up to just highlight the cell if it's not found either.

Essentially, if they do not match, or if the Array words are not found, highlight red.

My point was that where the error handler is placed, the only statements that fall within its scope are End If and Exit Function, so basically pointless.