K
K Anderson
I am having the following issue: I have a worksheet with a not-too-exotic
routine in the Worksheet_Change Event. When I make a change to the worksheet,
the event fires correctly. That is, I can step through the lines of code one
at a time and watch variables and do whatever else the debugger permits. The
problem is that it's not executing the code. That is, I step through a line
that says, say, 'Sheets("Sheet2").Cells(1,1).Value = 10', and--while the
debugger steps to the next line--the actual worksheet in the background did
not change. It does this for every single command. It's keeping track of
variables and navigation correctly--it's just not doing anything to the
worksheet. The worksheet is not protected, and--as already mentioned--the
event is firing, so there is no problem with the Application.EnableEvents
property either. It is simply proceeding to the next instruction without
having executed the one I stepped through.
A few notes...
(1) The problem is not consistent. That is, the error's occurrence seems to
be a function of how the Change event is triggered. If I change a value in a
Data Validation-type dropdown box, I will almost certainly get the problem.
If I enter a value in a cell, I get the issue about 75% of the time. What's
more, as I'm entering data on the worksheet, it sometimes "catches up." That
is, if it failed to execute correctly while changing a dropdown, and then I
proceed to enter a bunch more data anyway, it may at some point while I'm
entering the data, finish executing the routine it was supposed to have done
when I changed the dropdown.
(2) I had a number of cells (say, 25 to 100) with a formula using a UDF. The
function calculated correctly and pulled exactly what I needed it to.
However, I've noticed that if I have the code plug these values directly into
the cells without using a UDF, (i.e., if there's no cells on the worksheet
using a UDF) then I do not get the error. Period. This makes no sense to me,
vis-a-vis: if the problem is that the UDF is taking up too much space in
memory or the like, I'd expect that the machine would continue to calculate
before letting the debugger pass to the next line. Also, it shouldn't take
another 30 cells of data entry before it "catches up". Moreover, that
wouldn't explain why it only fails some of the time.
(3) When stepping through the routine (when it's not executing anything I'm
stepping through) it comes to one certain line and then quits. The line is
simply a '.Interior.Color = 1005423' line changing a color on a
FormatCondition. The rest of the instructions it treats like they don't exist
except that it steps through them; but for some reason it treats this one
like I would expect it to treat 'Exit Sub.'
(4) I'm using Excel 2007 if that makes any difference.
(5) Just an observation: while it's stepping from one command to the next,
the status bar next to the zoom slider changes to "Calculate: (4)
Processor(s):" or something similar. That's what gave me the idea to remove
the UDFs.
(6) Last but not least, I have been unable to repilcate this problem on a
new workbook. That tells me there is something relevant happening in my real
workbook that I'm not recognizing.
All in all, my sheet is working thanks to my removal of the udf usage in the
worksheet. My questions are: does anyone have an idea what is causing this?
And what can I do in the future to avoid this, short of never using a udf
again?
Thanks in advance for any and all help,
K
routine in the Worksheet_Change Event. When I make a change to the worksheet,
the event fires correctly. That is, I can step through the lines of code one
at a time and watch variables and do whatever else the debugger permits. The
problem is that it's not executing the code. That is, I step through a line
that says, say, 'Sheets("Sheet2").Cells(1,1).Value = 10', and--while the
debugger steps to the next line--the actual worksheet in the background did
not change. It does this for every single command. It's keeping track of
variables and navigation correctly--it's just not doing anything to the
worksheet. The worksheet is not protected, and--as already mentioned--the
event is firing, so there is no problem with the Application.EnableEvents
property either. It is simply proceeding to the next instruction without
having executed the one I stepped through.
A few notes...
(1) The problem is not consistent. That is, the error's occurrence seems to
be a function of how the Change event is triggered. If I change a value in a
Data Validation-type dropdown box, I will almost certainly get the problem.
If I enter a value in a cell, I get the issue about 75% of the time. What's
more, as I'm entering data on the worksheet, it sometimes "catches up." That
is, if it failed to execute correctly while changing a dropdown, and then I
proceed to enter a bunch more data anyway, it may at some point while I'm
entering the data, finish executing the routine it was supposed to have done
when I changed the dropdown.
(2) I had a number of cells (say, 25 to 100) with a formula using a UDF. The
function calculated correctly and pulled exactly what I needed it to.
However, I've noticed that if I have the code plug these values directly into
the cells without using a UDF, (i.e., if there's no cells on the worksheet
using a UDF) then I do not get the error. Period. This makes no sense to me,
vis-a-vis: if the problem is that the UDF is taking up too much space in
memory or the like, I'd expect that the machine would continue to calculate
before letting the debugger pass to the next line. Also, it shouldn't take
another 30 cells of data entry before it "catches up". Moreover, that
wouldn't explain why it only fails some of the time.
(3) When stepping through the routine (when it's not executing anything I'm
stepping through) it comes to one certain line and then quits. The line is
simply a '.Interior.Color = 1005423' line changing a color on a
FormatCondition. The rest of the instructions it treats like they don't exist
except that it steps through them; but for some reason it treats this one
like I would expect it to treat 'Exit Sub.'
(4) I'm using Excel 2007 if that makes any difference.
(5) Just an observation: while it's stepping from one command to the next,
the status bar next to the zoom slider changes to "Calculate: (4)
Processor(s):" or something similar. That's what gave me the idea to remove
the UDFs.
(6) Last but not least, I have been unable to repilcate this problem on a
new workbook. That tells me there is something relevant happening in my real
workbook that I'm not recognizing.
All in all, my sheet is working thanks to my removal of the udf usage in the
worksheet. My questions are: does anyone have an idea what is causing this?
And what can I do in the future to avoid this, short of never using a udf
again?
Thanks in advance for any and all help,
K