I have about 40 pages worth of code that checks a collection of data files
for various complex exception conditions. It is giving a false alarm on one
of them, but only when I let it run. When I step through the code as it
processes the data row that gets flagged as an error when the code runs
"freely", that data row does not get flagged.

How can I debug the code when it doesn't work the same way when I'm watching
it as when I'm not watching it?

Gregg Roberts

sounds like you have your code in a sheet module.

Look for references like

Range("A1")

this refers to the activesheet when stepping through your code. When
running your code, it refers to the sheet that contains the code. Put in
explicit references

activesheet.Range("A1")
or
Me.Range("A1")

so there are no assumptions as to what is referred to .

--
Regards,
Tom Ogilvy


"Gregg Roberts" wrote:

I have about 40 pages worth of code that checks a collection of data files
for various complex exception conditions. It is giving a false alarm on one
of them, but only when I let it run. When I step through the code as it
processes the data row that gets flagged as an error when the code runs
"freely", that data row does not get flagged.

How can I debug the code when it doesn't work the same way when I'm watching
it as when I'm not watching it?

Gregg Roberts

Gregg, it could be that the system events are bottlenecking and variables
don't contain the proper values at a given time. Can you describe the error
and the types of technologies and objects that are in play? For example,
what format are your data files, are they truly in a Collection, etc.
Sometimes something simple like DoEvents or a Wait statement will allow the
system to "unwind" to ensure things are happening in the proper order.

Regards,
Bill


"Gregg Roberts" wrote:

I have about 40 pages worth of code that checks a collection of data files
for various complex exception conditions. It is giving a false alarm on one
of them, but only when I let it run. When I step through the code as it
processes the data row that gets flagged as an error when the code runs
"freely", that data row does not get flagged.

How can I debug the code when it doesn't work the same way when I'm watching
it as when I'm not watching it?

Gregg Roberts

Try using Debug.Print in your code to "print" Immediate window what is
going on as your code runs. Example
If SomeCondition Then
Debug.Print "SomeCondition = " & YourCell
'Rest of your Code
End if

You can do this at all the calculation points along the way if needed,
I put the Text of the if statement in there so I can see what data is
being returned in the immediate window, otherwise you only get the
values you ask for.

Charles Chickering

Gregg Roberts wrote:
I have about 40 pages worth of code that checks a collection of data files
for various complex exception conditions. It is giving a false alarm on one
of them, but only when I let it run. When I step through the code as it
processes the data row that gets flagged as an error when the code runs
"freely", that data row does not get flagged.

How can I debug the code when it doesn't work the same way when I'm watching
it as when I'm not watching it?

Gregg Roberts

[Anyone else having trouble getting the page to come back with content in the
main pane after logging in?]

Thanks to all for your quick replies. Responses below.

Look for references like
Range("A1")
this refers to the activesheet when stepping through your code. When
running your code, it refers to the sheet that contains the code. Put in
explicit references

I have a mixture of explicit and implicit references, but this code was
copied (then modified) from another sub that works fine. And, it works as
expected when stepping through without the problem you allude to. I've always
wondered why the problem you're alluding to doesn't happen all the time, but
it doesn't. Everything else works, mousing over the variables shows the
expected values, etc. However, I'll try what you suggest.

Same goes for the response about possible bottlenecking. There are almost as
many opportunities for that as there are lines of code, yet this is the only
place it's happening that I know of. I'm in Excel 2003, the data files are
CSV, but I save them as Excel after opening them and in some cases doing a
little sorting.

I'm also going to try the debug.print suggestion.

Here's the sub where the false alarm is occurring. The ErrText is getting
populated when it shouldn't. (It gets to "" after the only code line where it
gets written to an error file.)

----------------

Sub CheckCISEligibility()

Dim CEFindCell As Range
Dim curCEFileRow As Long

CFELIGTableFile.Activate
Range("B1").Select

<See, Tom, right there is an implicit reference, but it works even when I
step through...

Set CEFindCell = ActiveSheet.Range("B2:B65536").Find(CASEFILE_ID,
LookAt:=xlWhole)
If Not CEFindCell Is Nothing Then
curCEFileRow = CEFindCell.Row
Do
If Cells(curCEFileRow, 3) = "1" Then ' ANOTHER IMPLICIT REFERENCE
CIS_Eligible = True
End If
curCEFileRow = curCEFileRow + 1
Loop Until CIS_Eligible Or Cells(curCEFileRow, 2) < Cells(curCEFileRow
- 1, 2)
Else
ErrText = ErrText & "CIS Eligibility not indicated" & Chr(10)
Debug.Print "CASEFILE_ID = " & CASEFILE_ID & ", CIS Eligibility not
indicated"
End If

Set CEFindCell = Nothing

End Sub

My suggestion was based on the information provided. I also said if you had
the code in a sheet module and that implicit references would work when
stepping through. Since you don't say where the code is located, there is no
reason to believe it is in a sheet module and it certainly isn't written as
if it was. After looking at your code, I would suggest you provide all
arguments to the find function. Several of the arguments have persistent
values and could be causing the find operation to fail mysteriously (as you
describe) which then populates your error text which you say is the problem.

Sub CheckCISEligibility()

Dim CEFindCell As Range
Dim curCEFileRow As Long

CFELIGTableFile.Activate
ActivEsheet.Range("B1").Select


Set CEFindCell = Activesheet.Range("B2:B65536" _
).Find(What:=CASEFILE_ID,
LookIn:=xlFormulas, _
LookAt:=xlwhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not CEFindCell Is Nothing Then
curCEFileRow = CEFindCell.Row
Do
If Activesheet.Cells(curCEFileRow, 3) = "1" Then
CIS_Eligible = True
End If
curCEFileRow = curCEFileRow + 1
Loop Until CIS_Eligible Or Activesheet.Cells(curCEFileRow, 2) _
< ActiveSheet.Cells(curCEFileRow - 1, 2)
Else
ErrText = ErrText & "CIS Eligibility not indicated" & Chr(10)
Debug.Print "CASEFILE_ID = " & CASEFILE_ID & ", CIS Eligibility not
indicated"
End If

Set CEFindCell = Nothing

End Sub

This assumes that the CASEFILE_ID is the only thing in the cell - no
unexpected leading or trailing spaces. If that is the case and the ID will
not be a substring of the entry in another cell, you might want to use xlPart
rather than xlWhole. Also, several of your variables seem to be set outside
your procedure, so there is no way to know that they have the values you
would expect. Obviously if CASEFILE_ID didn't hold the correct value it
would be an obvious explanation. Charles suggestion of using Debug is
appropriate, but you already indicated that you stepped through and checked
the values of variables. So that might be redundant.

--
Regards,
Tom Ogilvy


"Gregg Roberts" wrote:

[Anyone else having trouble getting the page to come back with content in the
main pane after logging in?]

Thanks to all for your quick replies. Responses below.

Look for references like
Range("A1")
this refers to the activesheet when stepping through your code. When
running your code, it refers to the sheet that contains the code. Put in
explicit references

I have a mixture of explicit and implicit references, but this code was
copied (then modified) from another sub that works fine. And, it works as
expected when stepping through without the problem you allude to. I've always
wondered why the problem you're alluding to doesn't happen all the time, but
it doesn't. Everything else works, mousing over the variables shows the
expected values, etc. However, I'll try what you suggest.

Same goes for the response about possible bottlenecking. There are almost as
many opportunities for that as there are lines of code, yet this is the only
place it's happening that I know of. I'm in Excel 2003, the data files are
CSV, but I save them as Excel after opening them and in some cases doing a
little sorting.

I'm also going to try the debug.print suggestion.

Here's the sub where the false alarm is occurring. The ErrText is getting
populated when it shouldn't. (It gets to "" after the only code line where it
gets written to an error file.)

----------------

Sub CheckCISEligibility()

Dim CEFindCell As Range
Dim curCEFileRow As Long

CFELIGTableFile.Activate
Range("B1").Select

<See, Tom, right there is an implicit reference, but it works even when I
step through...

Set CEFindCell = ActiveSheet.Range("B2:B65536").Find(CASEFILE_ID,
LookAt:=xlWhole)
If Not CEFindCell Is Nothing Then
curCEFileRow = CEFindCell.Row
Do
If Cells(curCEFileRow, 3) = "1" Then ' ANOTHER IMPLICIT REFERENCE
CIS_Eligible = True
End If
curCEFileRow = curCEFileRow + 1
Loop Until CIS_Eligible Or Cells(curCEFileRow, 2) < Cells(curCEFileRow
- 1, 2)
Else
ErrText = ErrText & "CIS Eligibility not indicated" & Chr(10)
Debug.Print "CASEFILE_ID = " & CASEFILE_ID & ", CIS Eligibility not
indicated"
End If

Set CEFindCell = Nothing

End Sub

I have also had issues when I alter the visibility (hidding or grouping) of
rows/columns just before using find. I don't see any reason to think this is
the case here, but it is something to keep in mind while using Find in a
macro.


"Gregg Roberts" wrote:

[Anyone else having trouble getting the page to come back with content in the
main pane after logging in?]

Thanks to all for your quick replies. Responses below.

Look for references like
Range("A1")
this refers to the activesheet when stepping through your code. When
running your code, it refers to the sheet that contains the code. Put in
explicit references

I have a mixture of explicit and implicit references, but this code was
copied (then modified) from another sub that works fine. And, it works as
expected when stepping through without the problem you allude to. I've always
wondered why the problem you're alluding to doesn't happen all the time, but
it doesn't. Everything else works, mousing over the variables shows the
expected values, etc. However, I'll try what you suggest.

Same goes for the response about possible bottlenecking. There are almost as
many opportunities for that as there are lines of code, yet this is the only
place it's happening that I know of. I'm in Excel 2003, the data files are
CSV, but I save them as Excel after opening them and in some cases doing a
little sorting.

I'm also going to try the debug.print suggestion.

Here's the sub where the false alarm is occurring. The ErrText is getting
populated when it shouldn't. (It gets to "" after the only code line where it
gets written to an error file.)

----------------

Sub CheckCISEligibility()

Dim CEFindCell As Range
Dim curCEFileRow As Long

CFELIGTableFile.Activate
Range("B1").Select

<See, Tom, right there is an implicit reference, but it works even when I
step through...

Set CEFindCell = ActiveSheet.Range("B2:B65536").Find(CASEFILE_ID,
LookAt:=xlWhole)
If Not CEFindCell Is Nothing Then
curCEFileRow = CEFindCell.Row
Do
If Cells(curCEFileRow, 3) = "1" Then ' ANOTHER IMPLICIT REFERENCE
CIS_Eligible = True
End If
curCEFileRow = curCEFileRow + 1
Loop Until CIS_Eligible Or Cells(curCEFileRow, 2) < Cells(curCEFileRow
- 1, 2)
Else
ErrText = ErrText & "CIS Eligibility not indicated" & Chr(10)
Debug.Print "CASEFILE_ID = " & CASEFILE_ID & ", CIS Eligibility not
indicated"
End If

Set CEFindCell = Nothing

End Sub

I have also had issues when I alter the visibility (hidding or grouping) of
rows/columns just before using find.

I am now getting a similar issue in a different sub that does not use Find:

Range("K1").Select
Selection.EntireColumn.Insert
Range("K1").Value = "Transfer not closed"
SARow = 2
Do
ErrMsg = ""
If Cells(SARow, 6) = "Unique" Then
If Cells(SARow, 47) = "" Then
Cells(SARow, 11) = "Transfer not closed"
End If
SARow = SARow + 1
Else
StudentsLastRow = 0
While Cells(SARow, 5) = Cells(SARow + StudentsLastRow, 5)
StudentsLastRow = StudentsLastRow + 1 ' When loop stops,
StudentsLastRow equals the number of
' casefiles for the student
Wend
StudentsLastRow = SARow + StudentsLastRow - 1 ' Now it equals the
student's last row number
For Index = SARow To StudentsLastRow
' If any transfer row is not closed, store errmsg
If Cells(Index, 16) = 4 Or _
Cells(Index, 16) = 5 Then
If Cells(Index, 47) = "" Then
ErrMsg = "Transfer not closed"
End If
End If
Next Index
If ErrMsg = "Transfer not closed" Then
' Write error msg to every row for the student
For Index = SARow To StudentsLastRow
Cells(Index, 11) = ErrMsg
Next Index
End If
SARow = StudentsLastRow + 1
End If
Loop Until SARow LastSARow

-------

In this case the code finds no instances of the exception condition when it
runs free, but stepping through on specific row numbers where I know that the
issue exists somehow enables the code to find the problem.

Again, if there's bottlenecking going on, why doesn't it occur more
consistently, throughout my code?

I'm at SP 2 of Excel 2003. Maybe there's a patch for this, but I wouldn't
know how to search for it.

<rant on
I SO should have gone to the trouble to get direct read-only access to the
database and written these reports in an SQL environment. The learning time
to enhance my SQL knowledge sufficiently would have been nothing next to all
this wild goose/ghost chasing. The need to do it seems ridiculous for such a
supposedly mature product. And how can I or my customers now ever trust the
results of this code? It _sometimes_ finds the error condition and sometimes
doesn't, so we can't even use a complete absence of any cases of the error as
a trouble flag.<rant off

Probably the fastest way to fix this would be to use filtering instead of
direct comparison to find the rows that meet the exception criteria, and
write the exception to every visible row. But that's evading the issue.

Try using Debug.Print in your code to "print" Immediate window what is
going on as your code runs.

The Debug.Print dialog never appears, and the code has stopped falsely
reporting that exception condition in the data being processed. I put it
right after the statement that assigns the exception message to a string
variable.

If I watch the code, again, it doesn't mess up. What is this, quantum
programming? ;-)

I closed and reopened the file with all this code in it, re-ran the code
with the watch still there, deleted the watch and hit F5. After that, the
code ran fine -- no rewriting needed. It's like kicking a cantankerous
machine and having it start working again. Oh well.

Gregg Roberts

does the macro pull data into a query table before running?

--
Regards,
Tom Ogilvy

"Gregg Roberts" wrote in message
...
I have also had issues when I alter the visibility (hidding or grouping)
of
rows/columns just before using find.

I am now getting a similar issue in a different sub that does not use
Find:

Range("K1").Select
Selection.EntireColumn.Insert
Range("K1").Value = "Transfer not closed"
SARow = 2
Do
ErrMsg = ""
If Cells(SARow, 6) = "Unique" Then
If Cells(SARow, 47) = "" Then
Cells(SARow, 11) = "Transfer not closed"
End If
SARow = SARow + 1
Else
StudentsLastRow = 0
While Cells(SARow, 5) = Cells(SARow + StudentsLastRow, 5)
StudentsLastRow = StudentsLastRow + 1 ' When loop stops,
StudentsLastRow equals the number of
' casefiles for the
student
Wend
StudentsLastRow = SARow + StudentsLastRow - 1 ' Now it equals the
student's last row number
For Index = SARow To StudentsLastRow
' If any transfer row is not closed, store errmsg
If Cells(Index, 16) = 4 Or _
Cells(Index, 16) = 5 Then
If Cells(Index, 47) = "" Then
ErrMsg = "Transfer not closed"
End If
End If
Next Index
If ErrMsg = "Transfer not closed" Then
' Write error msg to every row for the student
For Index = SARow To StudentsLastRow
Cells(Index, 11) = ErrMsg
Next Index
End If
SARow = StudentsLastRow + 1
End If
Loop Until SARow LastSARow

-------

In this case the code finds no instances of the exception condition when
it
runs free, but stepping through on specific row numbers where I know that
the
issue exists somehow enables the code to find the problem.

Again, if there's bottlenecking going on, why doesn't it occur more
consistently, throughout my code?

I'm at SP 2 of Excel 2003. Maybe there's a patch for this, but I wouldn't
know how to search for it.

<rant on
I SO should have gone to the trouble to get direct read-only access to the
database and written these reports in an SQL environment. The learning
time
to enhance my SQL knowledge sufficiently would have been nothing next to
all
this wild goose/ghost chasing. The need to do it seems ridiculous for such
a
supposedly mature product. And how can I or my customers now ever trust
the
results of this code? It _sometimes_ finds the error condition and
sometimes
doesn't, so we can't even use a complete absence of any cases of the error
as
a trouble flag.<rant off

Probably the fastest way to fix this would be to use filtering instead of
direct comparison to find the rows that meet the exception criteria, and
write the exception to every visible row. But that's evading the issue.

"Tom Ogilvy" wrote:
does the macro pull data into a query table before running?

No. The database of record is in SQL, but CSV exports are produced nightly
(91 tables) and my code processes 10 of them.