VBA类型 不同工作簿中相同函数不匹配

Question

在不同的工作簿中运行相同的代码时，我在解决类型不匹配时遇到问题。假设工作簿 1 是原始工作簿，工作簿 2 是新工作簿。

工作簿 1 和工作簿 2 具有相同的代码(如下(，其中Listbox_Refresh子调用GetAccountRef()函数。代码在工作簿 1 中运行良好，但在工作簿 2 中类型不匹配，我无法弄清楚原因。

我已经检查了两个工作簿中GetAccountRef()的VarType，它们是不同的。

对于工作簿 1

• 这将按预期生成 8204(vbArray + 变体(：

  Debug.Print VarType(GetAccountRef())
  

• 这将按预期生成 8(字符串(：

  Debug.Print VarType(GetAccountRef(0))
  

对于工作簿 2

• 这将导致 0(空(：

  Debug.Print VarType(GetAccountRef())
  

• 这会导致错误类型不匹配：

  Debug.Print VarType(GetAccountRef(0))
  

我尝试运行的功能是：

Function GetAccountRef() As Variant
On Error Resume Next
Dim Cell As Range
Dim Row_I As Range
Set Row_I = Sheet5.Range("9:9")    '<- ERROR: This range does not contain "Date"
Dim Counter As Integer
Counter = 0
Dim Date_Ref() As Variant

For Each Cell In Row_I
If Cell = "Date" Then
ReDim Preserve Date_Ref(Counter)
Date_Ref(Counter) = Cell.Address
GetAccountRef = Date_Ref
Counter = Counter + 1
End If
Next Cell

On Error GoTo 0
End Function

我正在尝试在For循环中使用此功能，如下所示：

Dim ListedBnk As Variant
For Each ListedBnk In GetAccountRef()
ListedBnk = Replace(ListedBnk, "9", "7")
.ComboBox1.AddItem Range(ListedBnk)
.ComboBox2.AddItem Range(ListedBnk)
Next ListedBnk

谢谢！

Answer 1

你的函数有一些错误。

Function GetAccountRef() As Variant
On Error Resume Next
Dim Cell As Range
Dim Row_I As Range
Set Row_I = Sheet5.Range("9:9")    'TFSA Tracker ONLY
Dim Counter As Integer
Counter = 0
Dim Date_Ref() As Variant

For Each Cell In Row_I
If Cell = "Date" Then
ReDim Preserve Date_Ref(Counter)
Date_Ref(Counter) = Cell.Address
Counter = Counter + 1
End If
Next Cell
GetAccountRef = Date_Ref '<~~ At this moved.
On Error GoTo 0
End Function

和您的工作表模块

Sub test()
Dim ListedBnk As Variant
Dim myArray As Variant
myArray = GetAccountRef
With ActiveSheet
.ComboBox1.Clear
.ComboBox2.Clear
'For Each ListedBnk In GetAccountRef()
For Each ListedBnk In myArray
ListedBnk = Replace(ListedBnk, "9", "7")
.ComboBox1.AddItem Sheet5.Range(ListedBnk)
.ComboBox2.AddItem Sheet5.Range(ListedBnk)
Next ListedBnk
End With
End Sub

Answer 2

发现我的错误，函数在不包含标识符的范围内查找标识符。感谢所有发表评论/解决方案的人！

改进是编写动态标识符，以便在添加行/列时调整范围。

功能：

Function GetAccountRef() As Variant
On Error Resume Next
Dim Cell As Range
Dim Row_I As Range
Set Row_I = Sheet5.Range(**"10:10"**)    '<- previous range("9:9") did not contain the *identifier "Date"*, it was in row 10.
Dim Counter As Integer
Counter = 0
Dim Date_Ref() As Variant

For Each Cell In Row_I
If Cell = "Date" Then
ReDim Preserve Date_Ref(Counter)
Date_Ref(Counter) = Cell.Address
GetAccountRef = Date_Ref
Counter = Counter + 1
End If
Next Cell

On Error GoTo 0
End Function

片子：

With Activesheet
Dim ListedBnk As Variant
For Each ListedBnk In GetAccountRef()
ListedBnk = Range(Replace(ListedBnk, "10", "8"))    '<- Also needs to refer to **Row 10**
.ComboBox1.AddItem ListedBnk
.ComboBox2.AddItem ListedBnk
Next ListedBnk
End With