VBA Code Sqequence Getting Re-directed

EmpDeity

New Member
Joined
Mar 9, 2009
Messages
13
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
 
Last edited:

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,139
Office Version
  1. 365
Platform
  1. Windows
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):
Code:
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
Then put these lines at the very end of your procedure:
Code:
    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

New Member
Joined
Mar 9, 2009
Messages
13
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,139
Office Version
  1. 365
Platform
  1. Windows
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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,139
Office Version
  1. 365
Platform
  1. Windows
Excellent!

You are welcome.
 

Forum statistics

Threads
1,148,293
Messages
5,745,921
Members
423,984
Latest member
sayed manzar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top