Understand that part, but what about my original question? In the code below
aryPartDes(36) and aryPartDes(37) look like this in the Locals Window:
aryPartDes(36)(1,1), aryPartDes(36)(1,2), aryPartDes(36)(1,3),
aryPartDes(36)(1,4).
But aryPartDes(35) looks like this aryPartDes(36)(1), aryPartDes(36)(2),
aryPartDes(36)(3), aryPartDes(36)(4). Is it possible for me to define the (
aryPartDes(35) ) array indicated below with "" like a 2D Array like
aryPartDes(36) and (37)?
I need to do this so my For...Next Loop works properly below. I get an
Error Subscript Out of Range.
This is what I have:
' in standard module
Type Sign
aryPartDes(1 To 37) As Variant
aryPartQty(1 To 37) As Double
End Type
Sub Test()
Dim MySign As Sign
With MySign
' onsite service techinician, do not mark up
If chkServicePlan Then
.aryPartDes(35) = Array("", "Ad Tech On Site Service Techinician", "ea.", CDbl(tbxOnSiteServiceTechCost))
.aryPartQty(35) = 1
Else
.aryPartDes(35) = Empty
.aryPartQty(35) = 0
End If
' software, do mark up
.aryPartDes(36) = PartInfo("Software")
.aryPartQty(36) = 1
' freight charges for modules, do not mark up
.aryPartDes(37) = PartInfo("ModuleShipRate")
.aryPartQty(37) = .ModuleCount * Val(tbxQuantity)
' sum items not to be marked up
Total.NoMarkUpItems = 0
For i = 35 To UBound(.aryPartQty)
If .aryPartQty(i) 0 Then
ERROR Total.NoMarkUpItems = Total.NoMarkUpItems + .aryPartQty(i) *
..aryPartDes(i)(1, 4)
End If
Next i
End With
End Sub
--
Cheers,
Ryan
"Chip Pearson" wrote:
Sign is my User Defined Type.
You need to declare a variable as type Sign. E.g.,
Dim MySign As Sign
You can the use MySign in your With statement.
Side Question: Why can't I use this syntax: aryPartDes(1 To 37) As Range?
Excel throws an error.
With a properly declared Type you can. For example:
Type Sign
Value As Long
Text As String
Ranges() As Range
End Type
Sub AAA()
Dim N As Long
Dim MySign As Sign
With MySign
.Value = 123
.Text = "abc"
ReDim .Ranges(1 To 10)
For N = 1 To 10
Set .Ranges(N) = Cells(N, 1)
Next N
For N = 1 To 10
Debug.Print .Ranges(N).Address
Next N
End With
End Sub
The code user ReDim to allocate the Ranges array. You can also hard
code the bounds within the Type iteselft:
Type Sign
Value As Long
Text As String
Ranges(1 To 10) As Range
End Type
With this, you'll need to get rid of the ReDim in the proc AAA.
Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
On Wed, 10 Dec 2008 11:12:01 -0800, RyanH
wrote:
Your are right. I apologize, I was being lazy. Hopefully this will explain
things better.
Sign is my User Defined Type.
Type Sign
aryPartDes(1 To 37) As Variant
aryPartQty(1 To 37) As Double
End Type
Side Question: Why can't I use this syntax: aryPartDes(1 To 37) As Range?
Excel throws an error.
With Sign
' onsite service techinician, do not mark up
If chkServicePlan Then
.aryPartDes(35) = Array("", "Ad Tech On Site Service
Techinician", "ea.", CDbl(tbxOnSiteServiceTechCost))
.aryPartQty(35) = 1
Else
.aryPartDes(35) = Empty
.aryPartQty(35) = 0
End If
' software, do mark up
.aryPartDes(36) = PartInfo("Software")
.aryPartQty(36) = 1
' freight charges for modules, do not mark up
.aryPartDes(37) = PartInfo("ModuleShipRate")
.aryPartQty(37) = .ModuleCount * Val(tbxQuantity)
' sum items not to be marked up
Total.NoMarkUpItems = 0
For i = 35 To UBound(.aryPartQty)
If .aryPartQty(i) 0 Then
Total.NoMarkUpItems = Total.NoMarkUpItems + .aryPartQty(i) *
.aryPartDes(i)(1, 4)
End If
Next i
End With ' end Sign With