在VBA中使用自定义数据类型 [英] Use of Custom Data Types in VBA

问题描述

我想在VBA for Excel中创建自定义数据类型。让我们把这个数据类型叫做truck。每个卡车都有以下属性：

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:

NumberOfAxles (this is an integer)
AxleWeights (this is an array of doubles)
AxleSpacings (this is an array of doubles)

数据类型卡车（卡车（1），卡车（2）...等），并读取/写入上面列出的属性。

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?

：

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)

非常感谢！

推荐答案

可以：

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

这篇关于在VBA中使用自定义数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！