arrays - 需要用VBA填充的数据结构

Question

我需要将一些数据从二维数组加载到类型数组结构中。

我的类型结构如下所示:

Public Type LocationType
  LocationID As String
  Description As String
  ZoneID As String
  IsEmpty As Boolean
  LastPalletArrivedTime As Date
  PalletID As String
  Sequence As Long
End Type

我声明如下:

Dim LocationArray() As LocationType

需要用二维数组中的数据填充。

我用来填充LocationArray的代码如下:

For x = 1 To UBound(TextFileLine())
    LocationArray(x).Description = SplitTextLines(x, 0)
    LocationArray(x).LocationID = SplitTextLines(x, 1)
    LocationArray(x).Sequence = SplitTextLines(x, 2)
    LocationArray(x).ZoneID = SplitTextLines(x, 3)
    LocationArray(x).PalletID = SplitTextLines(x, 4)
    LocationArray(x).LastPalletArrivedTime = SplitTextLines(x, 5)
    LocationArray(x).IsEmpty = SplitTextLines(x, 6)
Next x

我想知道是否有任何方法或代码可以用来使我用来填充“LocationArray”的代码更容易？

*注意我的所有变量都已声明并且代码正在运行。我只是要求比我目前使用的方法更好或更简单的方法。我们将非常感谢您的帮助。

Answer 1

经过一番思考

创建一个类模块并将其命名为Location

这是在类模块中使用的代码

Option Explicit

Public LocationID As String
Public Description As String
Public ZoneID As String
Public IsntEmpty As Boolean
Public LastPalletArrivedTime As Date
Public PalletID As String
Public Sequence As Long

Public Property Let Item(index As Long, value As Variant)
    Select Case index
        Case 1
             LocationID = value
        Case 2
             Description = value
        Case 3
             ZoneID = value
        Case 4
             IsntEmpty = value
        Case 5
             LastPalletArrivedTime = value
        Case 6
             PalletID = value
        Case 7
             Sequence = value
    End Select
End Property

Public Property Get Item(index As Long) As Variant
    Select Case index
        Case 1
             Item = LocationID
        Case 2
             Item = Description
        Case 3
             Item = ZoneID
        Case 4
             Item = IsntEmpty
        Case 5
             Item = LastPalletArrivedTime
        Case 6
             Item = PalletID
        Case 7
             Item = Sequence
    End Select
End Property

这是用于测试的标准 Module1(注意注释部分)

Option Explicit

Sub Main()

    Dim myLoc As Location
    Set myLoc = New Location

    Dim i As Long

    '////////////////
    ' SAMPLE filling

    For i = 1 To 7

        ' covering BOOLEAN
        If i = 4 Then
            myLoc.Item(i) = False

        ' covering DATE
        ElseIf i = 5 Then
            myLoc.Item(i) = Now

        ' covering LONG
        ElseIf i = 7 Then
            myLoc.Item(i) = i

        ' convering STRING
        Else
            myLoc.Item(i) = CStr("property " & i)

        End If
    Next i

   '///////////
   ' PRINTING

    For i = 1 To 7
        Debug.Print "property:" & i, myLoc.Item(i)
    Next i

    '/////////////////
    ' pay attention
    ' this is what you could do

    ' this section is commented as Im unable to test it
    ' but this should work for you


'    create a collection

'    Dim c As Collection
'    Set c = New Collection
'
'    Dim x As Long
'    For x = 1 To UBound(TextFileLine())
'        Dim loc As Location
'        Set loc = New Location
'
'        For i = 1 To 7
'            loc.Item(i) = SplitTextLines(x, i - 1)
'        Next i
'
'        c.Add loc
'    Next x
'
'    ' now if you wanted to retrieve the data
'    ' you iterate over the collection
'
'    For i = 1 To c.Count
'        For j = 1 To 7
'            Debug.Print c.Item(i).Item(j)
'        Next j
'    Next c


End Sub