CODEWITHSUNDEEP
excelvba
László Poroszkai
László Poroszkai

Reputation: 13

Excel VBA On Error error

I am rather new at programming, and while learning Python also started experimenting with Excel VBA. I have an issue with the last one.

I have some large Excel sheets and tried to validate that data in specific columns matches data on another sheet in certain columns as they will be supposed to relate to each other by these values (and will be connected by a third value). To make this a bit more difficult, both of these columns may contain more than one value separated by "|". So, I have split these values in a list and I try to iterate through them to make sure all these values are set correctly, the connection will work fine.

All is fine as long as all is fine :) I have however an issue where there are two values in one of those columns and only one in the other. I would like this discrepancy to be noted on a sheet and then proceed to the next item.

The way that seemed to be applicable for me is to use "On Error GoTo ErrHandler", then note error on another sheet, and then user Resume to proceed.

Here is what I came up with:

            For h = 0 To UBound(Split1())
                 For j = 1 To GetMaxRow("SpecificSheet", A)
                     On Error GoTo ErrHandler:
                        If Sheets("SpecificSheet").Cells(j, 1).Value = Split1(h) And Sheets("SpecificSheet").Cells(j, 2).Value = Split2(h) Then
                                DependencyOk = DependencyOk + 1
                        End If
                    Next j
            Next h
ErrProceed:

Also ErrHandler is:

ErrHandler:
        Sheets("Issues").Cells(x, 1) = "IssueDescription"
    GoTo ErrProceed

It stops at line 2 with Subscript out of range for Split2(h) rather than moving on to ErrHandler and then ErrProceed. I have the feeling this must be something very obvious but I am just unable to get this working, and I am not able to find other way (like a try/except) in Excel VBA.

UPDATE:

Trying to clarify things a bit. The root of the issue is, that the Split2 list is shorter than Split1 - which is an issue with the input data and I'd like to capture this. I get the Split values from cells, where the values are separated by "|" characters:

    CellValue = Sheets("SomeSheet").Cells(RowNumber, ColumNumber)
    CellValueSplit() = Split(CellValue, "|")

And then iterate as:

   For h = 0 To UBound(Split1())

So as Split1 moves on to the for example 3rd value, Split2 throws error and script stops. The best I was able to do so far was, that I let it proceed with the loop, but as this is a rather large sheet, it will fill the same error report ca. 200k times in this case, which I'd like to avoid. So I'd prefer it to proceed from after this loop once it hits out of range error, and proceed examining the next value.

Thank you for your help so far and in advance!

Upvotes: 0

Views: 429

Answers (3)

Moxtheox
Moxtheox

Reputation: 99

You have an issue with your syntax. The proper Error statement syntax is:

On Error GoTo <string>
On Error Resume Next
On Error GoTo 0

When using On Error GoTo <string> there is no ":" at the end. The ":" doesn't come into play until you create the target location. Example:

On Error GoTo Here
'// ---- Do something ---- //
Here:
'// ---- Handle the error ---- //

If you use On Error Resume Next, then you're telling the machine to ignore errors and proceed on to the next line of code.

When you useOn Error Return To 0, VBA will reset its error handling back to default. It's a good habit when using On Error Resume Next to insert On Error Return To 0 as soon as you no longer need it. On Error Resume Next has a real potential to break your code and make it behave strangely. Not to mention debugging can be a real nightmare. Check out the VBA manual from Microsoft for a more detailed explanation.

Finally, if your question is answered, you should mark it as answered.

Upvotes: 1

L&#225;szl&#243; Poroszkai
L&#225;szl&#243; Poroszkai

Reputation: 13

Actually I have just found the issue. It was caused by a ":" left after an If statement a few rows earlier. I still don't really understand what it did, but I suggest not to reproduce it :)

Upvotes: 0

Scott Holtzman
Scott Holtzman

Reputation: 27249

The short and quick version is that VBA Error Handling Routine's only handle errors in the actual code execution, they do not fire when conditions expressed by the code are not met.

In your case, you do not need any error handling at all. In most cases it is actually best to avoid On Error GoTo .... There are cases where it's inevitable, but they are rare.

Try this IF THEN ELSE block:

If Sheets("SpecificSheet").Cells(j, 1).Value = Split1(h) And Sheets("SpecificSheet").Cells(j, 2).Value = Split2(h) Then
    DependencyOk = DependencyOk + 1
Else
    Sheets("Issues").Cells(x, 1) = "IssueDescription"
End If

Upvotes: 0

Related Questions