我正在创建一个VBA文件的类对象,其目的是充当范围字典,可以传递单个单元格。 如果此单元格包含在某个范围内,则返回与相应范围键相关联的值。 类名为“rangeDic”。
它正在制作中,因此其功能尚未实现。 以下是代码:
Private zone() As String
Private bounds() As String
Private link As Dictionary
Const ContextId = 33
'Init zone
Private Sub Class_Initialize()
Set link = New Dictionary
ReDim zone(0)
ReDim bounds(0)
End Sub
'properties
Property Get linkDico() As Dictionary
Set linkDico = link
End Property
Property Set linkDico(d As Dictionary)
Set link = d
End Property
Property Get pZone() As String()
pZone = zone
End Property
Property Let pZone(a() As String)
Let zone = a
End Property
'methods
Public Sub findBounds()
Dim elmt As String
Dim i As Integer
Dim temp() As String
i = 1
For Each elmt In zone
ReDim Preserve bounds(i)
temp = Split(elmt, ":")
bounds(i - 1) = temp(0)
bounds(i) = temp(1)
i = i + 2
Next elmt
End Sub
我试图在测试子程序中实例化它,以便在设计中调试。这是代码:
Sub test()
Dim rd As rangeDic
Dim ran() As String
Dim tabs() As Variant
Dim i As Integer
i = 1
With ThisWorkbook.Worksheets("DataRanges")
While .Cells(i, 1).Value <> none
ReDim Preserve ran(i - 1)
ReDim Preserve tabs(i - 1)
ran(i - 1) = .Cells(i, 1).Value
tabs(i - 1) = .Cells(i, 3).Value
i = i + 1
Wend
End With
Set rd = createRangeDic(ran, tabs)
End Sub
Public Function createRangeDic(zones() As String, vals() As Variant) As rangeDic
Dim obje As Object
Dim zonesL As Integer
Dim valsL As Integer
Dim i As Integer
zonesL = UBound(zones) - LBound(zones)
valsL = UBound(vals) - LBound(vals)
If zonesL <> valsL Then
Err.Raise vbObjectError + 5, "", "The key and value arrays are not the same length.", "", ContextId
End If
Set obje = New rangeDic
obje.pZone = zones()
For i = 0 To 5
obje.linkDico.add zones(i), vals(i)
Next i
Set createRangeDic = obje
End Function
请看第2行的
Public Function createRangeDic
。我必须将我的对象声明为“Object”,如果我尝试将其声明为“rangeDic”,Excel会在obje.pZone = zones()
处崩溃。在查看Windows事件日志后,我可以看到一个“错误1000”类型的应用程序未知错误导致崩溃,其中“VB7.DLL”是有问题的包。为什么会这样?我做错了什么吗?感谢您的帮助。编辑:我使用的是Excel 2016。
obje.pZone = zones()
对我来说有点奇怪。如果去掉()
会发生什么? - Vincent Gobje
声明为rangeDic
。但是,我想知道为什么属性不能使用... - MirageHFx = Range("A1:B4")
,即使它应该是x = Range("A1:B4").value
,Excel也能猜出返回一个数组,或Set x = Range("A1:B4")
返回一个范围。特别是在类模块中,变量必须以非常明确的方式声明,以免强迫Excel猜测。根据您添加的参考文献,特定的变量类型可能会对VBA产生困惑。 - FaneDuru