Make Array Look Like a Range in VBA

[Ryan H]

I need my VBA Array to look like a range, is this possible? For example, I
have a function (PartInfo) that fills an element of an array (.aryPartDes)
with a range (1 row, 4 columns: total 4 cells) that is related to my user
define type (TG). When I run my For...Loop I get an error (indicated below)
"Subscript Out of Range." How can I make the array I'm trying to assign to
..aryPartDes(2) look like a range so I don't get this error?

Module 1:

' tri-face graphics
Type TG
aryPartDes(1 To 5) As Variant
aryPartQty(1 To 5) As Double
Pieces10 As Double
Pieces10Price As Double
Pieces14 As Double
Pieces14Price As Double
UserPieceLengthFt As Double
End Type

*****************************

Userform Module:

Sub TotalPrice()

Dim Sign As TG ' custom user-defined type

With Sign

.aryPartDes(1) = PartInfo("EXT00011068-126")
.aryPartQty(1) = .Pieces10 * Val(tbxQuantity)

.aryPartDes(2) = Array("User Defined PVC", .UserPieceLengthFt & "' x
6'' PVC", "ea.", CCur(tbxPricePerPiece))
.aryPartQty(2) = .Pieces14 * Val(tbxQuantity)


For i = LBound(.aryPartQty) To UBound(.aryPartQty)
If .aryPartQty(i) <> 0 Then
ERROR>> Total.Material = Total.Material + .aryPartQty(i) *
..aryPartDes(i)(1, 4)
End If
Next i

End Sub

***************************************

Module 2:

Function PartInfo(PartNumber As String) As Variant
' obtain all part information in Part List

SubName = "PartInfo"

Dim lngRow As Long

With Sheets("Parts List")
lngRow = WorksheetFunction.Match(PartNumber, .Range("A:A"), 0)
PartInfo = .Range(.Cells(lngRow, "A"), .Cells(lngRow, "D"))
End With

End Function

 

[Peter T]

I can't recreate your scenario but it looks like you are dealing with an
array of arrays for some reason. Not sure what you mean by "Look like", do
you mean perhaps you want to make an array the same size as a range? If so
try something like this

Sub test()
Dim I As Long, job As Long
Dim rang As Range

Set rang = Range("c1:f15")
With rang
Redid err(1 To .Rows.Count, 1 To .Columns.Count)
End With

For I = 1 To UBound(err)
For job = 1 To UBound(err, 2)
err(I, job) = I * 10 ^ (job - 1)
Next
Next

rang.Value = err
End Sub

The 'err could be one of the array of arrays, if that's really what you are
wanting.

Regards,
Peter T

 

[Peter T]

Over exuberant spell checker

The 'err could be one of the array of arrays, if that's really what you
are wanting.

should read

The 'arr' could be one of the array of arrays, if that's really what you are
wanting.

Peter T

 

[Ryan H]

Thanks for the reply Peter. Sorry for the misunderstanding. Let me try to
explain a different way. My PartInfo Function fills .aryPartDes(1) with the
data that is in a 4 cell range (1 row, 4 columns). The range contains this:

Cells(1,1) = Part Number
Cells(1,2) = Part Description
Cells(1,3) = Unit of Measure
Cells(1,4) = Part Price

thus,

Sign.aryPartDes(1)(1,1) = Part Number
Sign.aryPartDes(1)(1,2) = Part Description
Sign.aryPartDes(1)(1,3) = Unit of Measure
Sign.aryPartDes(1)(1,4) = Part Price

I am assuming Excel recognizes a range as a 2 dimensional array, right? So
what I am wanting to do is manually assign values for Sign.aryPartDes(1) like
this:

Sign.aryPartDes(1)(1, 1) = "User Defined PVC"
Sign.aryPartDes(1)(1, 2) = .UserPieceLengthFt & "' x 6'' PVC"
Sign.aryPartDes(1)(1, 3) = "ea."
Sign.aryPartDes(1)(1, 4) = CCur(tbxPricePerPiece)

How can I do this?

Thanks in Advance!

 

[Peter T]

If the bounds are fixed
Dim arr(1 to 1, 1 to 4)
otherwise
Redim arr(1 to rows, 1 to cols)

aryPartDes(1)= arr

or, if you want each element of aryPartDes to hold the same size array
for i = lbound(aryPartDes) to ubound(aryPartDes)
aryPartDes(i) = arr
next

then
aryPartDes(1)(1,1) = Part Number
etc

Regards,
Peter T