Fill an Array or Collection without knowing the UBound

[RyanH]

I have a workbook that has a userform in it that the sales team uses to quote
prices for the signs we manufacture. The user calls the userform and then
selects all the specifications of the sign by using checkboxes, option boxes,
comboboxes, etc. I am trying to build a "Bill of Material" for the sign that
the sales person quotes. This "Bill of Material" will contain a parts list
of all the parts used to make that sign, along with the part number, part
price, and part description. I have a master list of all the parts in our
inventory on a worksheet named Sheets("Parts List"). It is laid out like so.

A B C
D
Part Number Part Description Units Part Price
EXT0001111 Aluminum Extrusion ft. $5.50

If a particular part is used in the quoted sign I want to capture the values
in Range A on that parts row along with the quantity allocated to that
sign. For example, if PartA is used and is located on Row 100 I want this
stored in an Array or Collection: Range("A100100"), 5. 5 being the
quantity of PartA that is used for the sign.

This is my question. If I use an Array how do I fill it with multiple
Ranges and Quantities without knowing how many parts will be used? Or should
I use a collection? Will the collection hold values or just objects?

Once the Array is filled I then will use a loop to fill those values in my
"Bill of Material" worksheet.

For i = LBound(myArray) to UBound(myArray)

' A-part number, B-part description, C-part unit, D-part cost
Sheets("Bill of Material").Range("A1010").Value = myArray(i)

' quantity of part used
Sheets("Bill of Material").Range("E10").Value = myArray(i+1)
Next i

 

[Jim Thomlinson]

One way would be to create a user defined type. This must be placed in a
standard code module...

Type Material
rngItem As Range
dblQuantity As Double
End Type

You can use it like this...

Sub test()
Dim udtMaterials() As Material

ReDim udtMaterials(0)
udtMaterials(0).dblQuantity = 5
Set udtMaterials(0).rngItem = Range("A100100")

ReDim Preserve udtMaterials(1)
udtMaterials(1).dblQuantity = 6
Set udtMaterials(1).rngItem = Range("A101101")

MsgBox udtMaterials(0).rngItem.Address & vbCrLf &
udtMaterials(0).dblQuantity
MsgBox udtMaterials(1).rngItem.Address & vbCrLf &
udtMaterials(1).dblQuantity

End Sub

 

[Jim Thomlinson]

Sorry... Only the declaration of the UDT needs to be placed in a standard
code module. The rest of the code can reside anywhere as the UDT is
implicitly declared as public (since I did not specify). From re-reading my
post it seems that part is not too clear.

 

[RyanH]

Thanks for the reply Jim. I just want to understand exactly what is going on
here. By setting a user defined type are you creating an Object (Material)
with properties rngItem and dblQuantity, or something else?

Is there a way to configure the code to know what the next index number is,
instead of explicitly assigning the index? For example, I would need
something like this udtMaterial(UBound(udtMaterial) + 1), instead of
udtMaterial(1). Can this be done with an Array? The reason I need to do
this is because sometimes certain parts will be used and sometimes they will
not, depending on what the sales person quotes. This may present a problem
using a loop to list the ranges and quantities on my "Bill of Material"
worksheet. If untMaterials(1) has nothing assigned to it I will probably get
an error, right? For example,

Type Material
rngItem As Range
dblQuantity As Double
End Type

Sub test()

Dim udtMaterials() As Material

If chkPainted = True Then
ReDim udtMaterials(0)
udtMaterials(0).dblQuantity = 5
Set udtMaterials(0).rngItem = Range("A100100")
End IF

If chkMountedOnFrame = True Then
ReDim Preserve udtMaterials(1)
udtMaterials(1).dblQuantity = 6
Set udtMaterials(1).rngItem = Range("A7676")
End If

If cboIllumination.ListIndex = 2 Then
ReDim Preserve udtMaterials(2)
udtMaterials(2).dblQuantity = 6
Set udtMaterials(2).rngItem = Range("A5252")
End If

End Sub

I know I am throwing a lot of questions out there and I appreciate your
response!

 

[Gary Keramidas]

not sure if this will help, but here's a snippet i've used in that past. maybe
someone will find fault with it, but it works for me.




Dim itmArr() As Variant
ReDim itmArr(0)

With ws
Do While j <= endrow And .Range("K" & j).Value <= 40
ReDim Preserve itmArr(0 To z)
itmArr(z) = .Range("A" & j & ":B" & j).value
z = z + 1
j = j + 1
Loop
End With

For w = LBound(itmArr) To UBound(itmArr)

'do something

next

 

[RyanH]

This is a better example of my code. I'm not to sure if the udt is the best
option. What is your opinoin? Should I use an Array instead? Plus I am
getting an error as indicated below: Sub or Function not Defined.

this is in a Standard Module:

Type Material
rngDes As Range
dblQty As Double
curCost As Currency
End Type

this is in a Userform:

Sub cmbCalculate_Click()

'EXTRUSION MATERIAL COST
Dim udtMaterials() As Material

If CheckBox1 = True Then
' TopDoor
ReDim udtMaterials(0)
Error>> Material(0).dblQty = tbxCabSizeWft + tbxCabSizeWins / 12
Set Material(0).rngDes = PartInfo("EXT00011742")
Material(0).curCost = PartCost("EXT00011742")
End If

If CheckBox2 = True Then
' SideDoor
ReDim Preserve udtMaterials(1)
Material(1).dblQty = 2 * (tbxCabSizeHft + tbxCabSizeHins / 12) +
tbxCabSizeWft + tbxCabSizeWins / 12
Set Material(1).rngDes = PartInfo("EXT00011741")
Material(1).curCost = PartCost("EXT00011741")
End If

If CheckBox3 = True Then
' TopFram
ReDim Preserve udtMaterials(2)
Material(2).dblQty = tbxCabSizeWft + tbxCabSizeWins / 12
Set Material(2).rngDes = PartInfo("EXT00011743")
Material(2).curCost = PartCost("EXT00011743")
End If

Dim PartsSum As Currency
Dim i As Long

For i = 0 To Material.curCost.Count
PartsSum = PartsSum + Material(i).curCost
Next i

MsgBox PartsSum

End Sub