Excel VBA - Array solution

  • Thread starter Thread starter thesteelmaker
  • Start date Start date
T

thesteelmaker

How do i create an array of multiple types.

I can create an array of integers as below:

Sub Array_Example()
Dim MyArray(2) As Integer
MyArray(0) = 100
MyArray(1) = 300
MyArray(2) = 500
End Sub

What i want is an array that holds the following:

quantity As Integer
item As String
unitCost As Double

MyArray() = quantity, item, unitCost

Thank
 
Hi thesteelmaker,

You can do this several different ways. One way is to use a 2-dimensional
array of type Variant. This will allow for multiple "fields" of data of
differing data types in each "row". Here's a quick example:

Sub test()
Dim vData() As Variant
Dim n1 As Integer
Dim n2 As Integer

ReDim vData(0 To 2, 0 To 2) As Variant

vData(0, 0) = 1
vData(0, 1) = 2
vData(0, 2) = 3
vData(1, 0) = "Item 1"
vData(1, 1) = "Item 2"
vData(1, 2) = "Item 3"
vData(2, 0) = 24.95
vData(2, 1) = 34.95
vData(2, 2) = 44.95

For n1 = LBound(vData, 2) To UBound(vData, 2)
For n2 = LBound(vData, 1) To UBound(vData, 1)
Debug.Print vData(n2, n1)
Next n2
Next n1
End Sub

Another (better, IMO) option is to use a user-defined Type. Doing this
makes your code a lot more readable and allows you to organize your data in
an a more understandable manner. Here's an example of doing it this way:

Private Type muInventory
Quantity As Long
Item As String
UnitCost As Double
End Type

Sub test2()
Dim uInv() As muInventory
Dim n1 As Integer

ReDim uInv(0 To 2) As muInventory

With uInv(0)
.Quantity = 1
.Item = "Item 1"
.UnitCost = 24.95
End With
With uInv(1)
.Quantity = 2
.Item = "Item 2"
.UnitCost = 34.95
End With
With uInv(2)
.Quantity = 3
.Item = "Item 3"
.UnitCost = 44.95
End With

For n1 = LBound(uInv) To UBound(uInv)
With uInv(n1)
Debug.Print "Item: " & .Item & ", Quantity: " _
& .Quantity & ", UnitCost: " & Format$(.UnitCost, _
"$#,##0.00")
End With
Next n1
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
Use a type "Variant" for your array:

Sub Array_Example()
Dim MyArray(2) As Variant
Dim quantity As Integer
Dim item As String
Dim unitCost As Double

quantity = 2
item = "fruitcake"
unitCost = 10.99

MyArray(0) = quantity
MyArray(1) = item
MyArray(2) = unitCost

MsgBox MyArray(0) & " " & MyArray(1) & "s will cost you $" &
VBA.Str(MyArray(2))
End Sub

Stephen
 
Back
Top