VBA Code Sqequence Getting Re-directed

[EmpDeity]

I am probably using the wrong terminology, but here is what is happening. I have a DO loop that is taking data from one sheet and data from an SQL call and outputing it to another sheet. On the 6th time through (and I am watching with the debugger stepping through each line of code) the code jumps from the normal lines of code to one of my UDFs. The thread stays in the UDF and eventually I have to restart Excel.

The code snippet is included below. I am including the entire first part of the DO loop since there are some SQL calls that I believe are causing the issue. Note the commented line near the end. This is the line where the thread jumps off to the UDF. If I comment this line, then the thread jumps the the UDF on the next line.

Do While Worksheets("Bucket").Cells(j + 4, 1).Value <> ""
j = j + 1
FindPartFlag = 0
buf = Worksheets("Bucket").Cells(j + 3, 3).Value
sqlQuery = "SELECT Count(" & Module3.DBPart & ") FROM sheet1 Where " & Module3.DBPart & "='" & Worksheets("Bucket").Cells(j + 3, 3).Value & "'"
On Error Resume Next
rs.Open sqlQuery
FindPartFlag = rs(0)
rs.Close
If FindPartFlag > 0 Then
sqlQuery = "SELECT * FROM sheet1 WHERE Field1 = '" & Worksheets("Bucket").Cells(j + 3, 3).Value & "'"
On Error Resume Next
rs.Open sqlQuery

Worksheets("NFG").Cells(j + 1, 1).Value = LCase(rs(1))
buf1 = LCase(rs(17))
If Trim(LCase(rs(17))) = "v" Then
Worksheets("NFG").Cells(j + 1, 1).Font.Color = vbGreen
Else
Worksheets("NFG").Cells(j + 1, 1).Font.Color = vbRed
End If
Worksheets("NFG").Cells(j + 1, 2).Value = Worksheets("Bucket").Cells(j + 3, 2).Value
buf1 = Module3.get_gap_k("n")
'Worksheets("NFG").Cells(j + 1, 3).Value = Worksheets("Bucket").Cells(j + 3, 7).Value
Worksheets("NFG").Cells(j + 1, 4).Value = rs(3)
(here are more lines of the same kind of output to Worksheets("NFG")...., then the end of the loop)

I can include the UDF too, it is only about a dozen lines of code. But right now, I don't think the issue is what is in the UDF, only that the thread is being directed there.

Hopefully I have described this problem properly.

Thanks for any help

Pat

 

[Joe4]

The UDF is probably being called because a recalculation is being triggered.
You can try setting the calculation method to manual to see if that prevents it from being called while the procedure is running.

Just put these line at the beginning of your procedure above (turning off Screen Updating will make your code run faster):

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

Then put these lines at the very end of your procedure:

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

Note though, if you are getting stuck in the UDF and unable to exit, that could also indicate an issue with how that UDF is written. It might be wise to update that to handle errors or unexpected occurrences.

 

[EmpDeity]

I always hate being the bearer of bade new, but that did not work. Below is the updated UDF to include the four lines of suggested code.

Public Function get_gap_k(g_flag As String) As Double
Application.Volatile True
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim i, j As Integer
Do While Worksheets("Gap Materials").Cells(i + 2, 1) <> ""
i = i + 1
Module3.G_Tag(i) = Trim(LCase(Worksheets("Gap Materials").Cells(i + 1, 1)))
Module3.G_K(i) = Trim(LCase(Worksheets("Gap Materials").Cells(i + 1, 3)))
Module3.G_Derate(i) = Trim(LCase(Worksheets("Gap Materials").Cells(i + 1, 4)))
Loop
For j = 1 To i
If LCase(Trim(g_flag)) = LCase(Trim(Module3.G_Tag(j))) Then
get_gap_k = Module3.G_K(j) * Module3.G_Derate(j)
Exit Function
End If
Next j
get_gap_k = 999
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Function


So now I am just starting to consider that you wanted to four lines of code in the "main" code and not the UDF. Clearly I have put them in the UDF. did I get it wrong?

 

[Joe4]

So now I am just starting to consider that you wanted to four lines of code in the "main" code and not the UDF. Clearly I have put them in the UDF. did I get it wrong?

Yes, I meant to put them in the MAIN code.

My comment regarding the UDF was separate of that recommendation.

 

[EmpDeity]

Yes, that seems to have fixed the problem.

Many thanks.

 

[Joe4]

Excellent!

You are welcome.