VB edit not responding (Excel 2000 SP-3)

Topic

New Reply

I’m trying to debug some VB code in the VB editor and it’s refusing to step through the code. When I press F8 the cursor moves to the next line of code and highlights in yellow as I expect, but the code is not executed. When I click on the run button, it doesn’t.
The code is stepping out of a loop prematurely but never at the same record and I’m trying to find out why.
Can there be a relationship between the behaviour of VBE and the code or is there something wrong with my installation?

Reply | Quote

Replies

What are the indications that it doesn’t execute?

Add a line like:
Msgbox “test”

In the code and run it. Does the msgbox popup? If so the code is running. Perhaps the screen is not updating when you step through
(do you have a line:
Application.screenupdating = false
in the code)

Steve

Reply | Quote

Steve,
I have the VBE window restored so I can see the spreadsheet behind it. One of the lines of code is supposed to Activate a different sheet and it does not.
Screen updating is not off. The macro is running fine up to some indeterminate point, so I am watching it do its thing.
There is no user-defined function as suggested by Jan.

Reply | Quote

Perhaps you could attach a (zipped) copy of the workbook. Remove any sensitive information from the copy.

Reply | Quote

Hans,
Thank you for your interest. The original file has a great deal of information I cannot share, so I have made changes that will hopefully not mask the problem.

Reply | Quote

What macro is exhibiting the problem?

BTW,

Avoid all of the screen flicker by using

Application.Screenupdating=False
at the start
and
Application.Screenupdating=True
at the end of your macro.

Reply | Quote

BrokerFeesPartTwo is the problem.
In some cases it is reassuring to the user to see the macro do its thing. I use the ScreenUpdating on longer macros and after the user is familiar with it.

Reply | Quote

Could you be more specific about where the problem occurs (which loop) and what is the “sympton” of the code “not working”

Steve

Reply | Quote

Steve,
I don’t think I can be specific. The original post describes the problem. There is no particular point in the code at which the problem occurs , but it’s during the “For Each cell In FirmRange” loop that the code just seems to cease functioning.

Reply | Quote

You use “On Error Resume Next” twice in the code, when you want to test if a folder exists. You don’t have “On Error GoTo ” or “On Error GoTo 0” after that, so errors in the rest of the code will go unchecked. You should restore error handling before the line beginning with “wbkFees.SaveAs”.

Reply | Quote

Hans,
Your error-handling advice is well-taken. I will try that.

Reply | Quote

Hans,
Your error-handling advice is well-taken. I will try that.

Reply | Quote

Hans,
Following your post about my error-trapping I reviewed and revised my process and moved the folder checking out of the loop. Now that other errors are no longer ignored, a new problem has been revealed! On the 20th record, the advanced filter fails. Have I overloaded a stack somewhere or something? Do you have the time or patience to look at my revised code?

Reply | Quote

Could you tell us what you are running and what line gives the error and what are the values of the variabls in the line of code.

Steve

Reply | Quote

Steve,
The macro is “BrokerFeesPartTwo” and it’s breaking at line 158 with “AdvancedFilter method of Range class failed”
Thanks

Reply | Quote

What is the line of code? (ie the TEXT of the line)
Do you mean this line?
Range(“Advances”).AdvancedFilter Action:=xlFilterCopy, _

if so, What is the value of of “Firm” at this time?
I can’t seem to get your example file to get to this point to check it out, since I seem to be missing a “required file” on the “Z-Drive”
(can you modify the code to fill any required variables without using this file for demo purposes?)
Also what dates do you choose (just wondering if the date range make a difference, when I try to replicate your error)

Steve

Reply | Quote

Steve,
The macro breaks at the line, “Range(“Range(“Advances”).AdvancedFilter Action:=xlFilterCopy, _” etc.
The value of “Firm” is different each time through the loop – see “Firm” column in Advances column G.
I have removed te code that requires network drive mappings so that it now looks for and creates folders on the C drive.
I have assigned values to the date variables.
It still breaks at a different spot each time. I wonder if the code is somehow out-running Excel’s refresh rate.
Modified code attached.

Reply | Quote

I do not get any VB or excel errors. Your code actually “crashes” xl97 on my machine.

Personally, I would try to “revamp” the code to not do all the selecting and activating. I would create worksheet objects and explicit define the sheets that ranges should reference.

The code will speed up and there should be less problems

Steve

Reply | Quote

I do not get any VB or excel errors. Your code actually “crashes” xl97 on my machine.

Personally, I would try to “revamp” the code to not do all the selecting and activating. I would create worksheet objects and explicit define the sheets that ranges should reference.

The code will speed up and there should be less problems

Steve

Reply | Quote

Your problem appears to be caused by a bug in Excel (several versions). It is most likely a memory leak in the Advanced Filter method. Try the code below and see if it does what you want. It seems to work on my system, and I think it does the same thing that your code does.

Long piece of code (almost 5,000 characters) moved to attachment by HansV

Reply | Quote

Legare,
Thank you so much for taking the time to SOLVE my problem. So often the problems or questions posted here must require fairly simple answers, so it takes someone special to look beyond the obvious. I was sure my problem had to be more than coding because of the other symptoms. Initially, VBE was not acting normally. As I gradually commented-out bits of code to zero-in on the cause, other symptoms appeared. After a code break, when I clicked END instead of DEBUG, Excel would hang and I’d have to use Task Manager to kill it. I was about to use a time delay to see if that would help.
I am writing this before I have taken a good look at your code because I wanted to let you know how much I appreciate your effort. But it appears you have written code to replace the advanced filter method.
When you say “It is most likely a memory leak in the Advanced Filter method”, are you suggesting we avoid the advance filter in a loop? Are you aware of a KB article about this? Is there any way I can help get the word out about this?

Reply | Quote

Something in your loop appears to be causing a memory leak, and I am guessing that it is the advanced filter. I have no way to prove it, and I am not aware of any KB article. I would suggest avoiding the use of advanced filter in a loop, but since I can’t prove that this is the source of the problem that is just a suggestion.

Reply | Quote

Something in your loop appears to be causing a memory leak, and I am guessing that it is the advanced filter. I have no way to prove it, and I am not aware of any KB article. I would suggest avoiding the use of advanced filter in a loop, but since I can’t prove that this is the source of the problem that is just a suggestion.

Reply | Quote

Legare,
Thank you so much for taking the time to SOLVE my problem. So often the problems or questions posted here must require fairly simple answers, so it takes someone special to look beyond the obvious. I was sure my problem had to be more than coding because of the other symptoms. Initially, VBE was not acting normally. As I gradually commented-out bits of code to zero-in on the cause, other symptoms appeared. After a code break, when I clicked END instead of DEBUG, Excel would hang and I’d have to use Task Manager to kill it. I was about to use a time delay to see if that would help.
I am writing this before I have taken a good look at your code because I wanted to let you know how much I appreciate your effort. But it appears you have written code to replace the advanced filter method.
When you say “It is most likely a memory leak in the Advanced Filter method”, are you suggesting we avoid the advance filter in a loop? Are you aware of a KB article about this? Is there any way I can help get the word out about this?

Reply | Quote

Your problem appears to be caused by a bug in Excel (several versions). It is most likely a memory leak in the Advanced Filter method. Try the code below and see if it does what you want. It seems to work on my system, and I think it does the same thing that your code does.

Long piece of code (almost 5,000 characters) moved to attachment by HansV

Reply | Quote

Steve,
The macro breaks at the line, “Range(“Range(“Advances”).AdvancedFilter Action:=xlFilterCopy, _” etc.
The value of “Firm” is different each time through the loop – see “Firm” column in Advances column G.
I have removed te code that requires network drive mappings so that it now looks for and creates folders on the C drive.
I have assigned values to the date variables.
It still breaks at a different spot each time. I wonder if the code is somehow out-running Excel’s refresh rate.
Modified code attached.

Reply | Quote

What is the line of code? (ie the TEXT of the line)
Do you mean this line?
Range(“Advances”).AdvancedFilter Action:=xlFilterCopy, _

if so, What is the value of of “Firm” at this time?
I can’t seem to get your example file to get to this point to check it out, since I seem to be missing a “required file” on the “Z-Drive”
(can you modify the code to fill any required variables without using this file for demo purposes?)
Also what dates do you choose (just wondering if the date range make a difference, when I try to replicate your error)

Steve

Reply | Quote

Steve,
The macro is “BrokerFeesPartTwo” and it’s breaking at line 158 with “AdvancedFilter method of Range class failed”
Thanks

Reply | Quote

Could you tell us what you are running and what line gives the error and what are the values of the variabls in the line of code.

Steve

Reply | Quote

Hans,
Following your post about my error-trapping I reviewed and revised my process and moved the folder checking out of the loop. Now that other errors are no longer ignored, a new problem has been revealed! On the 20th record, the advanced filter fails. Have I overloaded a stack somewhere or something? Do you have the time or patience to look at my revised code?

Reply | Quote

You use “On Error Resume Next” twice in the code, when you want to test if a folder exists. You don’t have “On Error GoTo ” or “On Error GoTo 0” after that, so errors in the rest of the code will go unchecked. You should restore error handling before the line beginning with “wbkFees.SaveAs”.

Reply | Quote

Steve,
I don’t think I can be specific. The original post describes the problem. There is no particular point in the code at which the problem occurs , but it’s during the “For Each cell In FirmRange” loop that the code just seems to cease functioning.

Reply | Quote

Could you be more specific about where the problem occurs (which loop) and what is the “sympton” of the code “not working”

Steve

Reply | Quote

BrokerFeesPartTwo is the problem.
In some cases it is reassuring to the user to see the macro do its thing. I use the ScreenUpdating on longer macros and after the user is familiar with it.

Reply | Quote

What macro is exhibiting the problem?

BTW,

Avoid all of the screen flicker by using

Application.Screenupdating=False
at the start
and
Application.Screenupdating=True
at the end of your macro.

Reply | Quote

Hans,
Thank you for your interest. The original file has a great deal of information I cannot share, so I have made changes that will hopefully not mask the problem.

Reply | Quote

Perhaps you could attach a (zipped) copy of the workbook. Remove any sensitive information from the copy.

Reply | Quote

Steve,
I have the VBE window restored so I can see the spreadsheet behind it. One of the lines of code is supposed to Activate a different sheet and it does not.
Screen updating is not off. The macro is running fine up to some indeterminate point, so I am watching it do its thing.
There is no user-defined function as suggested by Jan.

Reply | Quote

I have seen this happen sometimes, especially inside user defined function containing an error, called from a worksheet cell.

Reply | Quote

I have seen this happen sometimes, especially inside user defined function containing an error, called from a worksheet cell.

Reply | Quote