为什么这段代码没有用数组填充我的组合框

Question

Sub Auto_Open()
NumElements = Range(.Range("D6"), .Range("D" & .Rows.Count).End(xlUp))
Dim arr(), i As Long
Redim arr(1 to NumElements,1 to 2)
With Worksheets(5)
For i=1 to NumElements
arr(i,1)=cells(4+i,4)     ' column D
arr(i,2)=cells(4+i,5)     ' column E
Next 
Me.ComboBox1 = arr
End With
End Sub

我想做的是创建一个组合框，该组合框将由不同工作表中的两列组成的数组填充。

更新：

Sub Auto_Open()
With Worksheets(5)
NumElements = Range(.Range("D6"), .Range("D" & .Rows.Count).End(xlUp))
Dim arr(), i As Long
Redim arr(1 to NumElements,1 to 2)
For i=1 to NumElements
arr(i,1)=cells(4+i,4)     ' column D
arr(i,2)=cells(4+i,5)     ' column E
Next 
Me.Combobox = arr
End With
End Sub

更新2:

Sub Auto_Open()
With Worksheets(5)
Dim erg As Range: Set erg = .Range(.Range("D6"), .Range("D" & .Rows.Count).End(xlUp)).Resize(, 2)
Dim arr As Variant: arr = erg.Value
Worksheets(1).ComboBox1 = arr
End With
End Sub

我现在得到了这个，但它告诉我在组合框行上有一个类型不匹配。

Answer 1

用多列范围填充组合框

Option Explicit
Sub Auto_Open()
With Worksheets(5)
Const cCount As Long = 2
Dim erg As Range: Set erg = .Range("D6", .Range("D" & .Rows.Count) _
.End(xlUp)).Resize(, cCount)
Me.ComboBox1.ColumnCount = cCount
Me.ComboBox1.List = erg.Value
End With
End Sub