R
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
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