excel 在嵌套循环中完成第一次迭代后立即跳回到Sub的开头

68bkxrlz  于 2023-02-14  发布在  其他
关注(0)|答案(1)|浏览(130)

非常新的VBA编码。我插入了一个文本框(Active Control X)。编写了一个代码来从MS Access数据库导入数据并将该数据保存到一个数组。后来我试图在文本框中打印该数组以供用户查看。但每次我的代码进入For循环的嵌套部分时,sub的运行迭代跳回到代码的开始。比多次运行的代码会进行多个SQL查询和excel崩溃。我不确定为什么代码跳回到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

在运行j的第一次迭代之后,代码跳回到代码的开头。我希望它正常运行,但不确定错误是什么

pengsaosao

pengsaosao1#

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

相关问题