Need a good reference for troubleshooting VBA errors

[Mr_Phil]

Hi, thanks for looking. I have a really hairy workbook with a lot of macros. Most were written in part with the recorder because I am not a programmer. I have one that works several times in a row and then it will hang up and I will have to debug, select the worksheet, then hit the arrow to continue the execution. I really need to buckle down and get smart so I can do more than ask questions. Is there a good web based VBA site that I can learn the basics of troubleshooting at?

 

[My Aswer Is This]

You said:
Is there a good web based VBA site that I can learn the basics of troubleshooting at?

So, I see you have been a member of this forum for nearly 5 years.
Have you not been able to get the help you need here?
I see nothing wrong with this line of code assuming you have a sheet by that name.
And to get help with a script it's always best to post all the code here and not a picture of the code.

 

[Alex Blakenburg]

Paul Kelly from Excel Macro Mastery has a lot of good videos out there, try the one below:
As @My Aswer Is This indicated it may not help you much with your current scenario since the most likely reasons for that line not working are:
• That sheet name is not Valid in the Activeworkbook
• The ActiveWorkbook is not what you think it is.
The fact that it works sometimes and not other times indicates that since your code has not specified which workbook, is defaulting to the ActiveWorkbook which may not be the workbook that it is intended to use.

 

[Mr_Phil]

You said:
Is there a good web based VBA site that I can learn the basics of troubleshooting at?

So, I see you have been a member of this forum for nearly 5 years.
Have you not been able to get the help you need here?
I see nothing wrong with this line of code assuming you have a sheet by that name.
And to get help with a script it's always best to post all the code here and not a picture of the code.

Yes, I have been a member for that long. And yes I have received help. Yes, there is a sheet by that name. As I said, it works until it unexpectedly stops working. Then I debug, select the sheet manually and hit the arrow to continue. In the five years I have been getting help I really haven't learned it from front to back. When I was typing the post showing the code leading up to it I actually got a bit disgusted that I haven't done more to learn it as opposed to asking for handouts. So I rewrote the post to ask for a reference where I can actually learn more and not rely on someone to correct it for me. Like I said in the OP "I really need to buckle down and get smart ..." Thanks for reading. Have an awesome weekend.

 

[Mr_Phil]

Paul Kelly from Excel Macro Mastery has a lot of good videos out there, try the one below:
As @My Aswer Is This indicated it may not help you much with your current scenario since the most likely reasons for that line not working are:
• That sheet name is not Valid in the Activeworkbook
• The ActiveWorkbook is not what you thing it is.
The fact that it works sometimes and not other times indicates that since your code has not specified which workbook, is defaulting to the ActiveWorkbook which may not be the workbook that it is intended to use.

Thank you for the link to the video. I will also fire up google and start looking at the active workbook possibility. This pick sheet opens up, copies from, and pastes into itself from about 8 workbooks. Since the macros are rolled up into a "parent" macro I can almost envision what you said. Thank you for reading and helping out. Have a great weekend.

 

[My Aswer Is This]

Yes, I have been a member for that long. And yes I have received help. Yes, there is a sheet by that name. As I said, it works until it unexpectedly stops working. Then I debug, select the sheet manually and hit the arrow to continue. In the five years I have been getting help I really haven't learned it from front to back. When I was typing the post showing the code leading up to it I actually got a bit disgusted that I haven't done more to learn it as opposed to asking for handouts. So I rewrote the post to ask for a reference where I can actually learn more and not rely on someone to correct it for me. Like I said in the OP "I really need to buckle down and get smart ..." Thanks for reading. Have an awesome weekend.

So, was the video mentioned above helpful and did it solve your issue?
I see nothing wrong with your script, but it must be the sheet name is not correct.

 

[Alex Blakenburg]

Do you know how to use the immediate window (Ctrl+G if it is not visible) ?
When it errors out copy the below into the immediate window (including the question mark) and hit enter and see if it is the workbook that contains that sheet name.

? ActiveWorkbook.Name

 

[kevin9999]

Another reference for debugging techniques is Error Handling & Debugging (Part 1) in Microsoft Excel 365 for Windows

 

[Mr_Phil]

So, was the video mentioned above helpful and did it solve your issue?
I see nothing wrong with your script, but it must be the sheet name is not correct.

The video was helpful. The issue isn't solved because at this moment it is running as expected. But, when it inevitably quits I can use the techniques in the video to try and isolate it. I will be marking the thread as solved/answered as I have a couple of study-able resources now. I almost hope it breaks again so I can figure it out. I appreciate the time and effort you guys put in to helping the "rest of us" if you will. Thank you.

Do you know how to use the immediate window (Ctrl+G if it is not visible) ?
When it errors out copy the below into the immediate window (including the question mark) and hit enter and see if it is the workbook that contains that sheet name.

? ActiveWorkbook.Name

First, thank you for the video link. I did not realise there was a way to see the results until he pointed out the watch/local/immediate windows and the I think I inferred from the video that it was possible to get different results when stepping thru as opposed to a straight run. I would have never thought of that without your help. I truly appreciate the time everyone has taken to help me learn something new. Thank you.

Another reference for debugging techniques is Error Handling & Debugging (Part 1) in Microsoft Excel 365 for Windows

I will be sure to check it out. Thank you for the link. Thank you for taking the time to help out.

 

[Alex Blakenburg]

Its always nice when someone acknowledges all the contributor . Thank you and glad we could help.