Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Request for error handling tips
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Request for error handling tips
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) "dan dungan" wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Request for error handling tips
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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "dan dungan" wrote in message oups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error Handling - On Error GoTo doesn't trap error successfully | Excel Programming | |||
Error handling with a handling routine | Excel Programming | |||
Error Handling | Excel Programming | |||
SQL.REQUEST #N/A error | Excel Programming | |||
Error Checking Tips | Excel Programming |