Request for error handling tips

[dan dungan]

Hi James or anyone,

I'm attempting to understand error handling.

I copied this from James into a test worksheet and it produced runtime
error 1004, method range of object failed on Range(rgStr).Select

I used the procedure on a worksheet with data in range a1:h23.

Sub Sel()
Dim rgStr As String, c As Range
For Each c In Range("a1:h23")
If c <> "" Then
If rgStr = "" Then
rgStr = c.Address
Else
rgStr = rgStr & "," & c.Address
End If
End If
Next c
Range(rgStr).Select
End Sub

With my limited knowledge I think this means there is no more data to
process in my range so the procedure produced the error. I figure the
procedure needs error handling.

I added On Error GoTo error_handler1 after the dim statement and
error_handler1: after Range(rgStr).Select.

The procedure still produces the same error dialog.

It seems to me that even if my error handler worked, I would lose the
values held in rgStr.

It also seems that since this procedure is returning values it should
be a function.

Please help clarify my understanding.

Dan Dungan

 

[Chip Pearson]

As written, the code will raise an error if there is no data in A1:H23
because the test C <> "" is never true and therefore rgStr remains an empty
string. At the end of the code, in the line

Range(rgStr).Select

will blow up because rgStr is empty. You can delete that line of code and
replace it with

If rgStr = vbNullString Then
Debug.Print "No Cells Selected"
Else
Debug.Print Range(rgStr).Address
Range(rgStr).Select
End If

I added On Error GoTo error_handler1 after the dim statement and
error_handler1: after Range(rgStr).Select.

That should work if error trapping is properly enabled. In VBA, go to the
Tools menu, choose Options, then General tab, and makes sure the "Error
Trapping" is set to "Break in class module", NOT "Break on all errors".

It seems to me that even if my error handler worked, I would lose the
values held in rgStr.

The only time your error handler will kick in is if rgStr is an empty
string, a condition you can handle with code instead of error trapping, as
shown above.

It also seems that since this procedure is returning values it should
be a function.

What makes you think it is returning values? It isn't. If it were, it would
have a line of code to set the return value, like

Sel = rgStr

and in this case, you're right, it would need to be a Function. But it isn't
returning any values.

You might find this web page useful:
http://www.cpearson.com/Excel/ErrorHandling.htm


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Jon Peltier]

I don't know if this causes your problem, but you should change the code
slightly to

For Each c In Range("a1:h23").Cells
If c <> "" Then.Value

although I tried your code as is, and it selected the cells in A1:H23 that
contained 1 or more characters.

- Jon