Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF-event mishap
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 a 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF-event mishap
I don't recognize all of the symptoms you describe, but on occassion I've had
code not update a workbook. One mistake that I've made is not having the workbook with the code be the active workbook. Now I often include ThisWorkbook.activate in a lot of my code. I have, at other times, had completely inexplicable problems. It's been rare, but in those cases I've created a new workbook and copied everything over to it. Odd, but it has helped on more than one occassion. Another possibility is if you're using range names. You can wind up with local range names (local to one sheet) and this could possibly throw off where your updates are occurring. Finally, although I'm guessing you've already checked this, make sure you haven't turned off screen updating. Maybe someone else will actually recognize your symptoms and provide better help. "K Anderson" wrote: 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 a 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
apply cell change event to single column - WorksheetChange Event | Excel Programming | |||
Calculation Mishap | Excel Discussion (Misc queries) | |||
Click event on menu item is lost after first time firing of the event | Excel Programming | |||
How to trap delete row event and hide column event? | Excel Programming | |||
user form-on open event? keydown event? | Excel Programming |