Structures or an arrays of different data types.

[gogui75]

I an newbie at VBA but I took some short programing classes back at my college days. I am trying to declare an array with different data types and since that seems to be imposible for what I gather then my other option is to declare what I remember as a structure. Can someone help with this. An example will be nice.

Thanks for any help.....

 

[RoryA]

If you declare your array as type variant, then you can put anything in it. Alternatively you can use the TYPE statement:

Type StateData
    CityCode (1 To 100) As Integer    ' Declare a static array.
    County As String * 30
End Type

 

[xld]

Declare it as a variant then you can load whatever you want

Dim ary As Variant


    ReDim ary(1 To 5)
    ary(1) = 17
    ary(2) = "Bob"
    ary(3) = Range("A1:A10")
    ary(4) = UserForm1
    ary(5) = 17 * 121 / 65 + 9.2

 

[gogui75]

If you declare your array as type variant, then you can put anything in it. Alternatively you can use the TYPE statement:

Type StateData
    CityCode (1 To 100) As Integer    ' Declare a static array.
    County As String * 30
End Type

Can you give more details information about this. I don't quite get it. I get that it is a user define data type. But i don't quite get the rest. First element (colunm) is an array of 100 rows and the second col is a string. What does * 30 means? It is this how a fix the size of the string?

More importantly I am having problems when I try to run this. It seems I am placing the code in the wrong place. How do you refer to the values later after is defined or to the cols). If you have a example it will be usefull. Sorry for the bother but I am truly a rookie. thanks,

 

[gogui75]

Declare it as a variant then you can load whatever you want

Dim ary As Variant


    ReDim ary(1 To 5)
    ary(1) = 17
    ary(2) = "Bob"
    ary(3) = Range("A1:A10")
    ary(4) = UserForm1
    ary(5) = 17 * 121 / 65 + 9.2

How you do this in a multidimensional array. The idea is to store a table to later iterate in it considering each row a single entry where i can search or call each element in that row as an individual item?

thanks,

 

[RoryA]

If you are loading data from a worksheet, then just use a Variant:

Dim varData
Dim lngRow as long, lngCol as long
' creates a 2 dimensional range
varData = range("A1:D100").Value
for lngRow = lbound(vardata, 1) to ubound(vardata, 1)
   for lngCol = lbound(vardata, 2) to ubound(vardata, 2)
       ' do something with varData(lngRow, lngCol)
   next lngCol
next lngRow

 

[RoryA]

Here's a sample using types (yes the * 30 made the example a fixed length string):

Public Type employeedata
   Name As String
   Age As Integer
   HireDate As Date
End Type
Sub testType()
   Dim emp As employeedata
   emp.Age = 31
   emp.HireDate = DateSerial(2008, 1, 30)
   emp.Name = "Mr. Pink"
   MsgBox emp.Name
End Sub

HTH

 

[gogui75]

This is cool. Right in the money. What i want is to read from a table in which I will have lets say 4 columms and i want to be able to do do watever by column like search in the whole columm but I also need to say each row is a record so there is a relationship in the index of that row through all the cols. So I need to combine the 2 things you just gave me. So I can have an array or another structure of the structure like you just gave me. Any idea of how to set that up. Again many thanks for the info, I appreciated it.

 

[RoryA]

It doesn't sound like you really need a structure. If you load the data into an array, then given a row number you can simply iterate over all columns for that row. You can use an array of types, but it makes loading the data a little slower as you have to loop through each row of your table and set the relevant properties of the type. For example, here's an array of the employeedata type:

Public Type employeedata
   Name As String
   Age As Integer
   HireDate As Date
End Type
Sub testType()
   Dim emp() As employeedata
   ReDim Preserve emp(1)
   emp(0).Age = 31
   emp(0).HireDate = DateSerial(2008, 1, 30)
   emp(0).Name = "Mr. Pink"
   emp(1).Age = 46
   emp(1).HireDate = DateSerial(2007, 1, 30)
   emp(1).Name = "Mr. White"
   MsgBox emp(0).Name
   MsgBox emp(1).Name
End Sub

 

[gogui75]

I am reading from a table and I need to keep record of multiple matches or finds. How do I say if you find this code in a particular row group that row and all other that have that same match together? I have a bill of materials file that has a weird arrangement. You have all the products lines or details of its components but some products have as components other products that are withing the same file but expresed as a single row and I will like to obtain the full Bill of Materials in terms of the smallest components and no intermediates. Any Ideas? This sound easy in my head but I can not come up with code for this. At the end I will like to concatenate my results to have the same Bill of Materials for a specific product in terms of the smallest materials since I will be sustituing those intermediates for their full details.
An intermediate product can have another intermediate and so on. I already identify a terminating condition for this holy mess and that is if that component it is not listed in the file as a product then, it is expresed in its simple terms....

Any Help will be apreciated.. Thanks