跳回到子循环的开始,一旦完成嵌套循环的第一次迭代。

huangapple go评论59阅读模式
英文:

Keeps jumping back to start of Sub as soon as it completes first iteration in the nested loop

问题

非常新手对VBA编码。我在工作表中插入了一个文本框(Active Control X)。编写了一个代码来从MS Access数据库导入数据并将该数据保存到一个数组中。然后,我尝试将该数组打印到文本框中供用户查看。但每次我的代码进入For循环的嵌套部分时,子程序的运行迭代都会跳回代码的开头。代码然后运行多次,进行多次SQL查询,导致Excel崩溃。我不确定为什么代码会跳回子程序的开头?

私人子 TextBox1_Change()

Dim sQuery As String
Dim ReturnData() As Variant
'清除状态区域中的现有数据
Dim rngClearArea As Range
Dim wsFleetio As Worksheet

Set wsFleetio = ThisWorkbook.Worksheets("Test")
Dim Farm As String

Farm = wsFleetio.Range("B1").Value
'设置rngClearArea = FindTag(wsFleetio, "$Vehicle Status", 2, 0).Resize(1000, 4)
'rngClearArea.ClearContents

'构建查询
sQuery = "SELECT [KillDate], [FarmName], [LoadType] FROM Loads WHERE ([FarmName] = '" & Farm & "' AND [KillDate] >= DateAdd('yyyy', -1, Date()))"

ReturnData = GetMerlinData(sQuery)
Dim leng As Integer
leng = UBound(ReturnData, 2)

Dim FarmData(500, 2) As Variant
Dim m As Integer

For m = 0 To UBound(ReturnData, 2)
    FarmData(m, 0) = ReturnData(0, m)
    FarmData(m, 1) = ReturnData(1, m)
    FarmData(m, 2) = ReturnData(2, m)
Next

Dim i As Long, j As Long

For i = 0 To UBound(ReturnData, 2)
    For j = 0 To 2
        
        TextBox1.Text = TextBox1.Text & FarmData(i, j) & "---"
    
    Next j
    TextBox1.Text = TextBox1.Text & vbCrLf
Next i

End Sub

在运行第一个j的迭代之后,代码会跳回代码的开头。我希望它能正常运行,但不确定出现了什么错误。

英文:

Very new to VBA coding. I inserted a Text Box (Active Control X) in my worksheet. Wrote a code to import data from MS Access database and save that data to an array. Later I am trying to print that array in the text box for user to see. but everytime my code enters the nested part of For loop, the running iteration of sub jumps back to the start of the code. Code than runs for multiple times make multiple SQL queries and excel crashes. I am not sure why code is jumping back to start of the sub?

Private Sub TextBox1_Change()

    Dim sQuery As String
    Dim ReturnData() As Variant
    'Clear existing data in statuses area
    Dim rngClearArea As Range
    Dim wsFleetio As Worksheet
        
    Set wsFleetio = ThisWorkbook.Worksheets("Test")
    Dim Farm As String
    
    Farm = wsFleetio.Range("B1").Value
    'Set rngClearArea = FindTag(wsFleetio, "$Vehicle Status", 2, 0).Resize(1000, 4)
    'rngClearArea.ClearContents
    
    'Build query
    sQuery = "SELECT [KillDate], [FarmName], [LoadType] FROM Loads WHERE ([FarmName] = '" & Farm & "' AND [KillDate] >= DateAdd('yyyy', -1, Date()))"
    
    ReturnData = GetMerlinData(sQuery)
    Dim leng As Integer
    leng = UBound(ReturnData, 2)
    
    Dim FarmData(500, 2) As Variant
    Dim m As Integer
    
    For m = 0 To UBound(ReturnData, 2)
        FarmData(m, 0) = ReturnData(0, m)
        FarmData(m, 1) = ReturnData(1, m)
        FarmData(m, 2) = ReturnData(2, m)
    Next
    
    Dim i As Long, j As Long
    
    For i = 0 To UBound(ReturnData, 2)
        For j = 0 To 2
            
            TextBox1.Text = TextBox1.Text & FarmData(i, j) & "---"
        
        Next j
        TextBox1.Text = TextBox1.Text & vbCrLf
    Next i

End Sub

After running the first iteration of j, code jumps back to start of the code. I want it to run normally but not sure what the error is

答案1

得分: 0

添加第二个文本框,TextBox2,并在循环中使用它:TextBox2.Text = TextBox2.Text & ...

英文:

Add a second TextBox, TextBox2 and use it in the loop: TextBox2.Text = TextBox2.Text & ...

huangapple
  • 本文由 发表于 2023年2月9日 02:19:17
  • 转载请务必保留本文链接:https://go.coder-hub.com/75390119.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定