VBA Problem With Coding Updates

[djt76010]

I have written the following code to test the efficiency of 3 different user definded functions and for some reason it is not performing as expected. When I slightly modify the code it has somehow accessing the original unedited code. I originally had the variable iptr go from 1 To 5000 to make sure it was working and now I have updated the upper limit of the loop to 1000000 but it is still only going up to 5000. When I step into the code to try and see why it is not working I get an "Expression not defined in context" error message on the Watch for both the I and iptr variables and the code performs as if the upper limit has not been changed from 5000. Any thoughts as to why this is happening?

Sub Macro1()

Dim dtstart As Date
Dim iptr As Long
Dim I As Integer

For I = 1 To 10
    dtstart = Now()
    For iptr = 1 To 1000000
       Sheets("Sheet1").Cells(1, 1).Value = iptr
    Next iptr
    Sheets("Sheet1").Cells(I, 13).Value = Now() - dtstart
Next I

End Sub

 

[Jerry Sullivan]

This code worked as expected for me (although I let it run to 10,000 instead of 1 Million).

Is it possible you have some code elsewhere that is interfering (in Personal.xlsb, an add-on, or some event code in your current workbook)?

Did you set a Breakpoint for your Watch? If so, where did you set it in your code?

 

[djt76010]

No, I set up that file specifically to test a couple of different coding options and it was working, but a couple of times today it was ignoring coding changes. I even tried running it on a different computer but that didn't change things either. I kept on editing the code and saving changes eventually the code would work correctly, but unfortunately not before overwriting the data that I was hoping to collect. I did not run it with a breakpoint I just stepped into the code.

I did eventually get the code to work and am now saving the data elsewhere between runs, but I just found it troublesome that Excel would be able to act contrary to what shows up in the code. I had a feeling that this would be a problem that wouldn't be easily replicated, but I was just hoping someone else had experienced something like it.

Thanks for looking into it for me.

 

[Jerry Sullivan]

Last thought on this...

You mentioned you use this for testing UDF's.
Did you have any UDF's being used in the worksheet when you were having the problems?

 

[djt76010]

Yes. Each time I ran the macro I had 1 of 3 UDF's in the worksheet to test the run time. When stepping through the code the UDF's appeared to be working correctly even though the references to the Macro1 watches on I and iptr were showing up as "Expression not defined in context".