英文:
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 & ...
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论