这就需要在VBA来填充数据结构 [英] Data structure which needs to be filled in VBA
问题描述
我需要从二维数组到一个类型的数组结构加载一些数据。
I need to load some data from an 2D array to an type array structure.
我喜欢的类型结构如下所示:
My type structure looks like the following:
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
其中需要从一个二维数组填充数据
which needs to be filled with data from a 2D array.
在code我用来填充LocationArray如下:
The code I use to fill LocationArray is as follows:
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
我想知道是否有任何方法或code,我可以用它来使code,我用它来补LocationArray更容易?
I was wondering if there is any method or code I could use to make the code which I use to fill "LocationArray" easier?
*注意:我所有的变量声明和code正在工作。我只是要求,以我目前使用的更好或更容易的方法。
帮助将非常AP preciated。
*Note all my variables are declared and code is working. I am just asking for better or easier method to the one I am currently using. Help will be much appreciated.
推荐答案
在一些思考
创建一个类模块,并将其命名为位置
create a Class module and name it Location
这是code类模块中使用
this is the code to use in the class module
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
这是一个标准的模块1进行测试(注意注释部分的)
this is a standard Module1 for testing (note the commented section)
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
这篇关于这就需要在VBA来填充数据结构的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!