excel - VBA：For循环在For块外执行代码
问题描述
我有一个大的 For 循环，我用它来创建一个变体数组。当我尝试从我的一个潜艇调用该函数时，我收到“运行时错误'28'：堆栈空间不足”。
函数本身完成时没有错误，但是，我注意到当我在 for 循环之外 print.debug ("complete") 时，它会不断地将 "complete" 打印到即时窗口。我复制并粘贴了一次调用该函数生成的“完成”，它产生了 199 次“完成”打印。
for 循环应该只运行 50 次迭代。它也不应该在 for 块之外的代码上进行迭代（至少我不希望它迭代）。
我担心我无意中创建了一个无限循环。
两折问题：
- 可能导致此错误的代码是否有问题。
- 有什么方法可以表示什么应该在 For 块中，什么应该在它之外？
任何帮助将非常感激。
下面的代码做了什么：
- 创建一个 50 行 x 4 列的多维数组。
- 遍历 for 循环，将月/季度/年分配给第 1 列。
- 分配一个字符串，作为第 2 列中单选按钮的标志
- 为我要在第 3 列中显示的部分分配起始列
- 为要在第 4 列中显示的部分分配结束列。
另一个 sub 调用该函数，该函数取消隐藏从第 0 行的开始列到第 50 行的结束列的所有内容。然后，它只显示数组中月份和单选按钮标志匹配的条目的坐标。
Function a_pane_coords() As Variant
'Will hold the visible column numbers for each month in the inputs pane and returns the result as an array.
Dim months As Variant
'Multidimensional array; 12 rows, 4 columns
'Column 0 = month name; column 1 = radio button flag; column 2 = start_row for month; column 3 = end_row for month.
Dim a_pane_cols(0 To 50, 0 To 3)
Dim in_month_arr As Variant
Dim quarters As Variant 'Will hold an array
Dim start_col As Integer
Dim end_col As Integer
Dim visible_cols As Integer
Dim space_cols As Integer
Dim month_counter As Integer
Dim quarter_counter As Integer
Dim irow As Integer
Dim icol As Integer
'Radio button flags assigned to column 1:
' current month: percent = m_pct; hours = m_hrs; financials = m_fin
' current quarter: percent = q_pct; hours = q_hrs; financials = q_fin
' fiscal year: percent = y_pct; hours = y_hrs; financials = y_fin
'This variable is the number of columns that will be visible for each month section. This should be one less than
' the actual number of visible columns since this is non-inclusive for the first column.
' Visible columns = start_row through start_row + increment. e.g. start_row = 8; end_row = 8 + 3 = 11
visible_cols = 2
'This variable is the number of columns between each month section. When written, there was one column separating
' each month section. This is the iterator that dictates the start column for the next month section therefore, this should be equal to
' the number of spacing columns +1 to get to the next start_col.
space_cols = 2
'Calls the in_month_coords() function in order to find the last column of the month input panes
in_month_arr = in_month_coords()
'Sets the initial value for the end column to the to the last column of the month inputs pane.
end_col = in_month_arr(11, 2)
months = Array("June", "July", "August", "September", "October", "November", "December", "January", "February", "March", "April", "May")
month_counter = 0
quarters = Array("Q1", "Q2", "Q3", "Q4")
quarter_counter = 0
For irow = 0 To 50
If irow < 12 Then 'Rows 0 through 11 are monthly percent allocation panes.
'Sets the first column equal to the month assigned in the months array
a_pane_cols(irow, 0) = months(month_counter)
'Sets the second column to the radio buttons designated above in the comments section
a_pane_cols(irow, 1) = "m_pct"
'Declare start column and assign it to column 1 in the month_cols array
start_col = end_col + space_cols
a_pane_cols(irow, 2) = start_col
'Calculate end_column and assign it to column 2 in the month_cols array
end_col = start_col + visible_cols
a_pane_cols(irow, 3) = end_col
'Increments the month counter in order to move to the next index of the month array on the next loop.
month_counter = month_counter + 1
'Resets the month counter for the next set of month panes.
If month_counter >= 12 Then
month_counter = 0
End If
ElseIf irow >= 12 And irow < 24 Then 'Rows 12 through 23 are hourly allocation panes.
'Sets the first column equal to the month assigned in the months array
a_pane_cols(irow, 0) = months(month_counter)
'Sets the second column to the radio buttons designated above in the comments section
a_pane_cols(irow, 1) = "m_hrs"
'Declare start column and assign it to column 1 in the month_cols array
start_col = end_col + space_cols
a_pane_cols(irow, 2) = start_col
'Calculate end_column and assign it to column 2 in the month_cols array
end_col = start_col + visible_cols
a_pane_cols(irow, 3) = end_col
'Increments the month counter in order to move to the next index of the month array on the next loop.
month_counter = month_counter + 1
'Resets the month counter for the next set of month panes.
If month_counter >= 12 Then
month_counter = 0
End If
ElseIf irow >= 24 And irow < 36 Then 'Rows 24 through 35 are monthly financial panes.
'Sets the first column equal to the month assigned in the months array
a_pane_cols(irow, 0) = months(month_counter)
'Sets the second column to the radio buttons designated above in the comments section
a_pane_cols(irow, 1) = "m_fin"
'Declare start column and assign it to column 1 in the month_cols array
start_col = end_col + space_cols
a_pane_cols(irow, 2) = start_col
'Calculate end_column and assign it to column 2 in the month_cols array
end_col = start_col + visible_cols
a_pane_cols(irow, 3) = end_col
'Increments the month counter in order to move to the next index of the month array on the next loop.
month_counter = month_counter + 1
'Resets the month counter for the next set of month panes.
If month_counter >= 12 Then
month_counter = 0
End If
ElseIf irow >= 36 And irow < 40 Then 'Rows 36 through 39 are quarterly percent panes.
'Sets the first column equal to the month assigned in the months array
a_pane_cols(irow, 0) = quarters(quarter_counter)
'Sets the second column to the radio buttons designated above in the comments section
a_pane_cols(irow, 1) = "q_pct"
'Declare start column and assign it to column 1 in the month_cols array
start_col = end_col + space_cols
a_pane_cols(irow, 2) = start_col
'Calculate end_column and assign it to column 2 in the month_cols array
end_col = start_col + visible_cols
a_pane_cols(irow, 3) = end_col
'Increments the quarter counter in order to move to the next index of the quarters array on the next loop.
quarter_counter = quarter_counter + 1
'Resets the quarter counter for the next set of quarter panes.
If quarter_counter >= 4 Then
quarter_counter = 0
End If
ElseIf irow >= 40 And irow < 44 Then 'Rows 40 through 43 are quarterly hours panes.
'Sets the first column equal to the month assigned in the months array
a_pane_cols(irow, 0) = quarters(quarter_counter)
'Sets the second column to the radio buttons designated above in the comments section
a_pane_cols(irow, 1) = "q_hrs"
'Declare start column and assign it to column 1 in the month_cols array
start_col = end_col + space_cols
a_pane_cols(irow, 2) = start_col
'Calculate end_column and assign it to column 2 in the month_cols array
end_col = start_col + visible_cols
a_pane_cols(irow, 3) = end_col
'Increments the quarter counter in order to move to the next index of the quarters array on the next loop.
quarter_counter = quarter_counter + 1
'Resets the quarter counter for the next set of quarter panes.
If quarter_counter >= 4 Then
quarter_counter = 0
End If
ElseIf irow >= 44 And irow < 48 Then 'Rows 44 through 47 are quarterly financial panes.
'Sets the first column equal to the month assigned in the months array
a_pane_cols(irow, 0) = quarters(quarter_counter)
'Sets the second column to the radio buttons designated above in the comments section
a_pane_cols(irow, 1) = "q_fin"
'Declare start column and assign it to column 1 in the month_cols array
start_col = end_col + space_cols
a_pane_cols(irow, 2) = start_col
'Calculate end_column and assign it to column 2 in the month_cols array
end_col = start_col + visible_cols
a_pane_cols(irow, 3) = end_col
'Increments the quarter counter in order to move to the next index of the quarters array on the next loop.
quarter_counter = quarter_counter + 1
'Resets the quarter counter for the next set of quarter panes.
If quarter_counter >= 4 Then
quarter_counter = 0
End If
ElseIf irow = 48 Then 'Row 48 is an annual percent pane.
'Sets the first column equal to the month assigned in the months array
a_pane_cols(irow, 0) = "FY 2020"
'Sets the second column to the radio buttons designated above in the comments section
a_pane_cols(irow, 1) = "y_pct"
'Declare start column and assign it to column 1 in the month_cols array
start_col = end_col + space_cols
a_pane_cols(irow, 2) = start_col
'Calculate end_column and assign it to column 2 in the month_cols array
end_col = start_col + visible_cols
a_pane_cols(irow, 3) = end_col
ElseIf irow = 49 Then 'Row 49 is an annual hours pane.
'Sets the first column equal to the month assigned in the months array
a_pane_cols(irow, 0) = "FY 2020"
'Sets the second column to the radio buttons designated above in the comments section
a_pane_cols(irow, 1) = "y_hrs"
'Declare start column and assign it to column 1 in the month_cols array
start_col = end_col + space_cols
a_pane_cols(irow, 2) = start_col
'Calculate end_column and assign it to column 2 in the month_cols array
end_col = start_col + visible_cols
a_pane_cols(irow, 3) = end_col
ElseIf irow = 50 Then 'Row 50 is an annual financials pane.
'Sets the first column equal to the month assigned in the months array
a_pane_cols(irow, 0) = "FY 2020"
'Sets the second column to the radio buttons designated above in the comments section
a_pane_cols(irow, 1) = "y_fin"
'Declare start column and assign it to column 1 in the month_cols array
start_col = end_col + space_cols
a_pane_cols(irow, 2) = start_col
'Calculate end_column and assign it to column 2 in the month_cols array
end_col = start_col + visible_cols
a_pane_cols(irow, 3) = end_col
Else
Exit For
End If
''***Uncomment this section to debug***
'' Note: Because of the size of the array, Excel may freeze when printing to the immediate window #vba_sux***'
'Debug.Print (a_pane_cols(irow, 0))
'Debug.Print (a_pane_cols(irow, 1))
'Debug.Print (a_pane_cols(irow, 2))
'Debug.Print (a_pane_cols(irow, 3))
Next irow
Debug.Print ("complete")
a_pane_coords() = a_pane_cols
End Function
解决方案
推荐阅读
- javascript - 在某些单词回来后退出调用 setInterval() 函数的时间
- wonderware - Wonderware Historian 查询中的某些日期时间过滤器不返回任何数据
- python - 使用 asyncio python 超时时取消进程
- processing - 导出到 windows64 失败！尝试导出应用程序时的消息
- ios - Swift 5: Decoding Nested JSON
- c++ - 使用 Qt WebEngineView 在特定页面打开 PDF
- linq - LINQ 代码，按部门计算每个职位和组中的员工性别，并放在矩阵表中
- flutter - Flutter 在关闭对话框上刷新 tabbarview
- node.js - 如何在 NodeJS Express 中将下载的文件发送到客户端？
- mysql - 我在计算我想做的 mySQL 命令时遇到了一些困难