PDA

View Full Version : Problem With Stepping Thru VBA Code



Cyberdude
07-05-2006, 10:32 AM
This is not a new problem ? in fact I can?t remember when it didn?t occur in Excel 2003.
It occurs when I?m stepping through code in a VBA macro by pressing the F8 key repeatedly. It executes as expected line by line until I reach a certain point, then it switches to a function I have in Personal.xls and continues line by line through that function?s code. At the end of the function, then it goes into a loop by starting execution of the function again line by line. Note that a reference to that function is NEVER made within the macro being tested. Curiously, this is a repeatable error that always occurs in the same location in the macro being tested.

In the current macro that I?ve been testing, the statement that triggers the branch to the function is:

Case Small3: Range("AQ68") = .Offset(0, -5)

Other nearly identical ?Case? statements execute as expected without the branch. Nothing in this statement is similar to locations in other macros that I had problems with. The problem does not occur in all macros, and it occurs in other unrelated workbooks. Note that I can run the macro successfully by pressing the F5 key. This has been true of other macros that had the problem. Also, when the branch occurs, it ALWAYS goes to the same function in Personal.xls.

Has anyone else had a similar problem? (Weird ? very weird) :bug:

OBP
07-05-2006, 11:47 AM
Have you got any error trapping?

mvidas
07-05-2006, 01:37 PM
What is the function doing that it goes to?
Sounds to me like when you're assigning a value to the sheet (as demonstated in your sample line), it is causing the sheet to be calculated, and the function is then recalculated too. Does your function have an application.volatile line at the top of it or something? Is that function even being called at all in the workbook?
Matt

XLGibbs
07-05-2006, 06:05 PM
I agree with mvidas...a volatile calculation is occuring when the the cells' value is changing, calling the function in personal.xls

FYI, it is also happening when you press F5, you are just not seeing the iterations through each calculation since it happens seamlessly (but it IS happening when the code runs normally regardless)

Perhaps disabling events or calculation on the outside of your Select Case routine would disable the issue during that sequence.

Cyberdude
07-08-2006, 04:10 PM
Thanx for the suggestions, guys. I'll do some more experimenting.

Cyberdude
07-08-2006, 04:40 PM
I hate to admit it, but I tend to forget about using Application.Volatile(False). As I understand it, I should be using "False" at the beginning of all my UDF's unless I have a good reason not to. Is that a correct statement?

It seems that forgetting to do that could in some macros significantly slow down execution time with no benefit. Whew!