在 VBA 中使用自定义数据类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12414168/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Use of Custom Data Types in VBA
提问by marillion
I am trying to create a custom data type in VBA for Excel. Let's call this data type "truck". Each truck has the following attributes:
我正在尝试在 VBA for Excel 中创建自定义数据类型。我们称这种数据类型为“卡车”。每辆卡车都具有以下属性：
NumberOfAxles (this is an integer)
AxleWeights (this is an array of doubles)
AxleSpacings (this is an array of doubles)
Can I create many instances of the data type "truck" (truck(1), truck(2)... etc), and read/write the attributes I listed above to that instance?
我可以创建数据类型“truck”的多个实例（truck(1)、truck(2)...等），并将上面列出的属性读/写到该实例中吗？
Example:
例子：
Truck(1).NumberOfAxles = 2
Truck(1).AxleWeights(1) = 15.0
Truck(1).AxleWeights(2) = 30.0
Truck(1).AxleSpacings(1) = 8.0
Truck(2).NumberOfAxles = 3
Truck(2).AxleWeights(1) = 8.0
Truck(2).AxleWeights(2) = 10.0
Truck(2).AxleWeights(3) = 12.0
Truck(2).AxleSpacings(1) = 20.0
Truck(2).AxleSpacings(2) = 4.0
and so on. The syntax above is most possibly wrong, I just wanted to demonstrate the structure I need to come up with.
等等。上面的语法很可能是错误的，我只是想演示我需要提出的结构。
All I am trying to write data to a data structure and call it as necessary such as
我正在尝试将数据写入数据结构并根据需要调用它，例如
Truck(i).NumberOfAxles
Truck(i).AxleWeights(j)
Truck(i).AxleSpacings(j)
Thank you very much!
非常感谢！
回答by Olle Sj?gren
Sure you can:
你当然可以：
Option Explicit
'***** User defined type
Public Type MyType
MyInt As Integer
MyString As String
MyDoubleArr(2) As Double
End Type
'***** Testing MyType as single variable
Public Sub MyFirstSub()
Dim MyVar As MyType
MyVar.MyInt = 2
MyVar.MyString = "cool"
MyVar.MyDoubleArr(0) = 1
MyVar.MyDoubleArr(1) = 2
MyVar.MyDoubleArr(2) = 3
Debug.Print "MyVar: " & MyVar.MyInt & " " & MyVar.MyString & " " & MyVar.MyDoubleArr(0) & " " & MyVar.MyDoubleArr(1) & " " & MyVar.MyDoubleArr(2)
End Sub
'***** Testing MyType as an array
Public Sub MySecondSub()
Dim MyArr(2) As MyType
Dim i As Integer
MyArr(0).MyInt = 31
MyArr(0).MyString = "VBA"
MyArr(0).MyDoubleArr(0) = 1
MyArr(0).MyDoubleArr(1) = 2
MyArr(0).MyDoubleArr(2) = 3
MyArr(1).MyInt = 32
MyArr(1).MyString = "is"
MyArr(1).MyDoubleArr(0) = 11
MyArr(1).MyDoubleArr(1) = 22
MyArr(1).MyDoubleArr(2) = 33
MyArr(2).MyInt = 33
MyArr(2).MyString = "cool"
MyArr(2).MyDoubleArr(0) = 111
MyArr(2).MyDoubleArr(1) = 222
MyArr(2).MyDoubleArr(2) = 333
For i = LBound(MyArr) To UBound(MyArr)
Debug.Print "MyArr: " & MyArr(i).MyString & " " & MyArr(i).MyInt & " " & MyArr(i).MyDoubleArr(0) & " " & MyArr(i).MyDoubleArr(1) & " " & MyArr(i).MyDoubleArr(2)
Next
End Sub
回答by user3357963
It looks like you want to define Truck as a Classwith properties NumberOfAxles, AxleWeights & AxleSpacings.
看起来您想将 Truck 定义为Class具有 NumberOfAxles、AxleWeights 和 AxleSpacings 属性的 a。
This can be defined in a CLASS MODULE(here named clsTrucks)
这可以在CLASS MODULE 中定义（这里命名为clsTrucks）
Option Explicit
Private tID As String
Private tNumberOfAxles As Double
Private tAxleSpacings As Double
Public Property Get truckID() As String
truckID = tID
End Property
Public Property Let truckID(value As String)
tID = value
End Property
Public Property Get truckNumberOfAxles() As Double
truckNumberOfAxles = tNumberOfAxles
End Property
Public Property Let truckNumberOfAxles(value As Double)
tNumberOfAxles = value
End Property
Public Property Get truckAxleSpacings() As Double
truckAxleSpacings = tAxleSpacings
End Property
Public Property Let truckAxleSpacings(value As Double)
tAxleSpacings = value
End Property
then in a MODULEthe following defines a new truck and it's properties and adds it to a collection of trucks and then retrieves the collection.
然后在MODULE 中，以下内容定义了一个新卡车及其属性，并将其添加到卡车集合中，然后检索该集合。
Option Explicit
Public TruckCollection As New Collection
Sub DefineNewTruck()
Dim tempTruck As clsTrucks
Dim i As Long
'Add 5 trucks
For i = 1 To 5
Set tempTruck = New clsTrucks
'Random data
tempTruck.truckID = "Truck" & i
tempTruck.truckAxleSpacings = 13.5 + i
tempTruck.truckNumberOfAxles = 20.5 + i
'tempTruck.truckID is the collection key
TruckCollection.Add tempTruck, tempTruck.truckID
Next i
'retrieve 5 trucks
For i = 1 To 5
'retrieve by collection index
Debug.Print TruckCollection(i).truckAxleSpacings
'retrieve by key
Debug.Print TruckCollection("Truck" & i).truckAxleSpacings
Next i
End Sub
There are several ways of doing this so it really depends on how you intend to use the data as to whether an a class/collection is the best setup or arrays/dictionaries etc.
有几种方法可以做到这一点，因此它实际上取决于您打算如何使用数据来确定类/集合是最佳设置还是数组/字典等。
