4

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

I declared the following:

Dim LocationArray() As LocationType

which needs to be filled with data from a 2D array.

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

I was wondering if there is any method or code I could use to make the code which I use to fill "LocationArray" easier?

*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.

Community
  • 1
  • 1
John Hopley
  • 331
  • 1
  • 3
  • 13
  • Just to be sure: What does `SplitTextLines(x, 1)` do? – MiVoth Dec 10 '13 at 09:07
  • SplitTextLines() contains data which was read from a text file. for example: SplitTextLines(1,0) = "AisleOne" and SplitTextLines(1,1) ="A1" etc. – John Hopley Dec 10 '13 at 09:15
  • You could use a collection rather than a UDT, that way you would simply need an inner loop – SWa Dec 10 '13 at 10:48
  • It seems like youre trying to build an enumerable type in VBA? is that correct? –  Dec 10 '13 at 10:56
  • @mehow something like that. The LocationArray() is used to store data which is imported with a text file. I have multiple type structures which go through the same process as LocationArray(). Therefore i was wondering for an easier method for storing data to LocationArray() type structure. – John Hopley Dec 10 '13 at 11:08
  • 3
    You can create a class with private properties and add instances to a dictionary or collection. This type of structure is not "easier", but it will encapsulate the code that manages the logic and allow you to execute simple statements like: set obj = new class -> obj.Description = "something" -> Collection.add obj This will result in less spaghetti coding and overall clearer application structure. – html_programmer Dec 10 '13 at 11:12
  • @JohnHopley can you please explain how are you going to use that data of your custom type? What are you going to need to do with it? How are you going to access it? Once we know that we may give you a different approach or workaround the problem. –  Dec 10 '13 at 11:20
  • @mehow I only want to know if there is an easier way to assign text file values to my type structure. I am going to use the type structures in a simulation I'm currently working on. Therefore i don't want to change the type structure, I just want "easier" or shorter method of importing the data. – John Hopley Dec 10 '13 at 11:32
  • 1
    Ok, why don't you wrap it in function called `AddFromLine(x as Variant)`, pass the line to the function and split and add it inside that function. That seems the easiest way.. There is no easier way then what you already have. See [**this**](http://stackoverflow.com/questions/17178461/for-each-class-property-in-excel-vba) –  Dec 10 '13 at 11:49
  • 5
    @JohnHopley Depending on what you want to achieve, I would reconsider moving from a type to a class implementation. What I have in mind when reading your code is what will happen if you want to get the description for a specific location for example; or if you want to know if a specific location exists. With an array you will be forced to loop and apply if then clauses. Collections and dictionaries can search by index, and check if a certain key exists. Type objects cannot be stored in these objects, so you may get stuck on that level. You won't be able to say: give me properties for "Gent" – html_programmer Dec 10 '13 at 11:56
  • 1
    @KimGysen is right, this is related to what I have asked 3 comments up :) –  Dec 10 '13 at 13:08

1 Answers1

2

After some thinking

create a Class module and name it Location

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

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