Excel - error handling & err.number

[srogers]

I haven't ever dealt with supplying messages when it comes to errors before.

From reading some of the posts it looks like you either let VBA do it or you do it?

In my application I copy data from five workbooks and PasteSpecial it into a target workbook. Apparently the five workbooks have to be open because if even one is closed I get a run-time error '9': Subscript out of range error.

I tried to figure out the error number using a MsgBox and err.number. It says it is zero. I seem to get zero for other errors too (and if I leave that MsgBox line in there it shows a 0 error even if there is not error as far as I can tell).

I don't want the user to get the debug window that takes them into VBA (and that doesn't seem very professional) but I don't really know that should be handling all the errors myself either.

Perhaps it is where I am putting the error handling lines.
Below is some of my code - without the repetitive stuff - in case it helps. Included are the lines where I try to deal with errors - some are commented out as I go back and forth trying one thing or another.

Sub UpdatePeriodXX()
PeriodNum = Application.InputBox("Make sure all the store forecast files are open and then please enter the period number&quot
If PeriodNum = False Then GoTo 2
'''If Err.Number <> 0 Then
''On Error GoTo 3
''3
'''MsgBox (&quot;Please enter a valid period number.&quot
'''GoTo 2
MsgBox (&quot;Error: &quot; & Err.Number)
'''End If
'Store 1
Workbooks(&quot;1forecast.xls&quot.Worksheets(PeriodNum).Range(&quot;Week1_&quot; & PeriodNum).Copy
Workbooks(&quot;PeriodXXInProcess.xls&quot.Worksheets(&quot;WEEKONE&quot.Range(&quot;Week1_1&quot.PasteSpecial Paste:=xlValues

'Store 2
Workbooks(&quot;2forecast.xls&quot.Worksheets(PeriodNum).Range(&quot;Week1_&quot; & PeriodNum).Copy
Workbooks(&quot;PeriodXXInProcess.xls&quot.Worksheets(&quot;WEEKONE&quot.Range(&quot;Week1_2&quot.PasteSpecial Paste:=xlValues

2

End Sub


Thanks for ideas and suggestions.
Sharon

 

[PHV]

In your VBE open the Object Browser and search

 ErrObject (/code] and then feel free to press the F1 key on each member and take a look at the examples.

Hope This Help
PH.

 

[srogers]

I'm looking around in the Object Browser and I'm not sure what to search on. I looked for ErrObject and I see a &quot;Number Property Example&quot; but I don't think there is enough information there.

Maybe I am not searching for the right thing?

Thank you -

 

[PHV]

ErrObject is a class of the VBA library.

Hope This Help
PH.

 

[srogers]

Right, I'm looking at the VBA library but it still seems like you have to know what your doing for that to help. I think I need something a little more comprehensive. Perhaps I will find a a book that talks about placement of code and maybe something about handling certain numbered errors that seem to be common while letting VBA handle the others. Or maybe it is just better to have one general message for all the errors instead of sending the user to the code screen.

Thanks anyway.

 

[PHV]

Have you the VBA help installed with your office stuff ?

 

[TonyJollans]

Hi srogers,

I'm not really sure what you're asking but here's a quick overview of error handling, which is one of the least structured elements of VBA.

You may have one error-handler enabled per procedure (Sub or Function). When an error occurs, the error handler in the current procedure is activated; if there isn't one, the one in the calling procedure is activated, and if there isn't one there either then the one in its caller is activated, etc., etc. There is always a system-controlled enabled error handler at the very highest level (the system) so every error is handled somehow.

An enabled error handler is simply a label within the procedure to which control passes when an error occurs. When an error has occurred the error handler becomes active and remains active until normal code is Resumed. Whilst in an active error handler there is not also an enabled error handler in the same procedure.

To enable (or disable) an error handler you use the On Error statement:

On Error Goto

Line

 ' Enables an error handler at

Line

On Error Goto 0

 ' Disables an error handler

On Error Resume Next

 ' Enables in-line error handling

The Err object only has meaning in an error handling routine. In your case (a) you know the number anyway - it's 9, and (b) you don't have an error handler (although it's hard to tell what you amy have tried with all the commented code.

Hope that helps putting you on the right road.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[srogers]

Hi -
PHV - Yes, thanks, I am able to access the VBA help.

Tony,
I found an article called &quot;Professional Error Handling&quot;

http://www.microsoft.com/officedev/articles/movs204.htm

(apparently written in '98)
that helps. I can see it is very involved though so it may take some time to get things to happen as I want them to.

I thought I knew the error was 9 but I am wondering when I use this line:
MsgBox (&quot;Error: &quot; & Err.Number)
why does it display a zero? That really threw me off.

Thanks for your error handling overview also.
Sharon

 

[TonyJollans]

Hi Sharon,

Err.Number gets set when the error occurs - in your case in one of the Workbooks(.... statements. After the error has occurred, if you had an enabled error handler (i.e. you had previously executed an On Error statement) then control passes to it and then you can do your Msgbox (or whatever) and will have Err.Number = 9 (and other Err Properties as well).

In your code as it stands, you do not have an error when you try to reference the number so it is zero.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[srogers]

Oh! Thanks.