如何在VBA的立即窗口中打印二维数组?是否存在任何通用方法来完成此操作?如果可以将数组的每一行绘制成一行,则可以解决此问题,因为只需为每行循环执行此代码即可。请注意保留HTML标记。
如果这是为了调试的目的,在程序执行过程中编写宏以查看数组内容不太方便。这甚至可能会导致问题。
为了在程序执行过程中进行调试,您需要一种无需编码即可在所有VB项目中使用的方法来窥视数组的内容。
在此示例中,“aLookList”是变体数组。 “Value2”下的值来自一个范围。
另一个答案建议使用监视窗格。这与我的答案类似,但发布者没有解释您可以通过将数组名称添加到监视中来窥视整个数组(所有单元格)。然后,深入节点。与监视窗口相比,本地窗格的优势在于您不必手动将数组添加到窗格中,因为它已经存在。所以这需要更少的努力。
我为大家的参考制作了一个简单的循环:
Sub WriteArrayToImmediateWindow(arrSubA As Variant)
Dim rowString As String
Dim iSubA As Long
Dim jSubA As Long
rowString = ""
Debug.Print
Debug.Print
Debug.Print "The array is: "
For iSubA = 1 To UBound(arrSubA, 1)
rowString = arrSubA(iSubA, 1)
For jSubA = 2 To UBound(arrSubA, 2)
rowString = rowString & "," & arrSubA(iSubA, jSubA)
Next jSubA
Debug.Print rowString
Next iSubA
End Sub
不,您需要:
这个方法将一个数组输出到即时调试窗口。它会自动调整列的宽度并在右侧列上加上轮廓,使得在同一列中比较数字更加容易。
Public Sub Array2DToImmediate(ByVal arr As Variant, _
Optional ByVal spaces_between_columns As Long = 2, _
Optional ByVal NrOfColsToOutlineLeft As Long = 2)
'Prints a 2D-array of values to a table (with same sized column widhts) in the immmediate window
'Each character in the Immediate window of VB Editor (CTRL+G to show) has the same pixel width,
'thus giving the option to output a proper looking 2D-array (a table with variable string lenghts).
Dim i As Long, j As Long
Dim arrMaxLenPerCol() As Long
Dim str As String
Dim maxLength As Long: maxLength = 198 * 1021& 'capacity of Immediate window is about 200 lines of 1021 characters per line.
'determine max stringlength per column
ReDim arrMaxLenPerCol(UBound(arr, 1))
For i = LBound(arr, 1) To UBound(arr, 1)
For j = LBound(arr, 2) To UBound(arr, 2)
arrMaxLenPerCol(i) = IIf(Len(arr(i, j)) > arrMaxLenPerCol(i), Len(arr(i, j)), arrMaxLenPerCol(i))
Next j
Next i
'build table
For j = LBound(arr, 2) To UBound(arr, 2)
For i = LBound(arr, 1) To UBound(arr, 1)
'outline left --> value & spaces & column_spaces
If i < NrOfColsToOutlineLeft Then
On Error Resume Next
str = str & arr(i, j) & space$((arrMaxLenPerCol(i) - Len(arr(i, j)) + spaces_between_columns) * 1)
'last column to outline left --> value & spaces
ElseIf i = NrOfColsToOutlineLeft Then
On Error Resume Next
str = str & arr(i, j) & space$((arrMaxLenPerCol(i) - Len(arr(i, j))) * 1)
'outline right --> spaces & column_spaces & value
Else 'i > NrOfColsToOutlineLeft Then
On Error Resume Next
str = str & space$((arrMaxLenPerCol(i) - Len(arr(i, j)) + spaces_between_columns) * 1) & arr(i, j)
End If
Next i
str = str & vbNewLine
If Len(str) > maxLength Then GoTo theEnd
Next j
theEnd:
'capacity of Immediate window is about 200 lines of 1021 characters per line.
If Len(str) > maxLength Then str = Left(str, maxLength) & vbNewLine & " - Table to large for Immediate window"
Debug.Print str
End Sub
用于例如输出的子程序:
Sub testArray2DToImmediate()
Dim ar() As Variant
ReDim ar(1 To 5, 1 To 4)
ar(1, 1) = "ID": ar(2, 1) = "Name": ar(3, 1) = "Values1": ar(4, 1) = "Values2": ar(5, 1) = "Values3"
ar(1, 2) = 1: ar(2, 2) = "Example where only": ar(3, 2) = FormatNumber(123 * 123#, 2): ar(4, 2) = 89: ar(5, 2) = "Wow"
ar(1, 3) = 2: ar(2, 3) = "first 2 columns are": ar(3, 3) = FormatNumber(234 * 234#, 2): ar(4, 3) = 456789: ar(5, 3) = "Nice"
ar(1, 4) = 3: ar(2, 4) = "outlined Left": ar(3, 4) = FormatNumber(567 * 567#, 2): ar(4, 4) = 123456789: ar(5, 4) = "Table":
Array2DToImmediate ar
End Sub
示例输出:
ID Name Values1 Values2 Values3
1 Example where only 15.129,00 89 Wow
2 first 2 columns are 54.756,00 456789 Nice
3 outlined Left 321.489,00 123456789 Table
'Define 2d array
arr = [ {"A",1; "B",2; "C",3 } ]: _
For r = LBound(arr, 1) To UBound(arr, 1): _
For c = LBound(arr, 2) To UBound(arr, 2): _
Debug.Print arr(r, c): _
Next c: _
Next
debug.print
和 :
之间加上分号可以防止在列之间换行。
(2) 在 next c
和 next r
之间再加上一个 print
可以强制在行之间换行。
(3) 将 next c
改为 next r
可以帮助紧张的人。
(4) 在即时窗口中不需要使用 debug.
。将以上内容放在一行中,代码如下:For r = LBound(arr, 1) To UBound(arr, 1) : For c = LBound(arr, 2) To UBound(arr, 2) : Print arr(r, c) ; : Next c : Print : Next r
。同时,我必须记住使用 : _
技巧来提高可读性。 - northern-bradleyName Age
A 10
B 20
C 30
D 40
E 50
F 60
G 70
Sub Array_Demo()
Dim arr() As String
Dim i As Integer, j As Integer
' fill the array with strings
Last = Range("A" & Rows.Count).End(xlUp).Row
ReDim arr(Last, 1)
For i = 0 To Last - 1
arr(i, 0) = Cells(i + 1, 1).Value
arr(i, 1) = Cells(i + 1, 2).Value
Next
' only one loop to display its contents !!!
Dim v As Variant
For Each v In arr
Debug.Print v
Next
End Sub
Name
A
B
C
D
E
F
G
Age
10
20
30
40
50
60
70
Sub test()
'declare the 2D array
Dim intA(0 To 2, 0 To 3) As Integer
'populate the array
intA(0, 0) = 45: intA(0, 1) = 50: intA(0, 2) = 55: intA(0, 3) = 60
intA(1, 0) = 65: intA(1, 1) = 70: intA(1, 2) = 75: intA(1, 3) = 80
intA(2, 0) = 85: intA(2, 1) = 90: intA(2, 2) = 95: intA(2, 3) = 100
Debug.Print Print2DArrayToImmediate(intA)
' <<<<<<<<<< Or >>>>>>>>>>
Dim myarr()
myarr = Range("A1:C5").Value
Debug.Print Print2DArrayToImmediate(myarr)
End Sub
Function Print2DArrayToImmediate(arr)
Dim arrStr As String, x As Long, y As Long
arrStr = ""
For i = LBound(arr, 1) To UBound(arr, 1)
If LBound(arr, 1) = 0 Then i = i + 1
arrStr = arrStr & vbCrLf
arrStr = arrStr & Join(Application.Index(arr, i, 0), vbTab & vbTab)
Next i
Print2DArrayToImmediate = arrStr
End Function
http://wiki.yaslaw.info/doku.php/vba/functions/print_r/index
print_r(array(1,2,3,array("A","B")))
<Variant()> (
[0] => <Integer> 1
[1] => <Integer> 2
[2] => <Integer> 3
[3] => <Variant()> (
[0] => <String> 'A'
[1] => <String> 'B'
)
)
print_r([{1,2;3,4}])
<Variant()> (
[1.1] => <Double> 1
[1.2] => <Double> 2
[2.1] => <Double> 3
[2.2] => <Double> 4
)