Make an Array 2-dimensional in VBA

[RyanH]

I fill an array with 4 celled ranges. The 4th cell in the Range is a price
of a part. I then use a For...Next Loop to sum those prices. I need the
array that I indicated with ">>>>" to be two dimensional (or
..aryPartDes(35)(1,4)) because I am getting a "Subscript out of Range" on the
line indicated "ERROR>>>". I think because the current array is viewed like
this .aryPartDes(35)(4) thus it can,t find the price. Any ideas?

' NON MARKUP ITEMS

' onsite service techinician, do not mark up
If chkServicePlan Then.aryPartQty(35) = 1
Else
.aryPartDes(35) = ""
.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

 

[Rick Rothstein]

You show a "dot" in front of .aryPartDesc and .aryPartQty... what object to
those dots reference back to? In other words, exactly what are
..aryPartDes(35) and .aryPartQty(35) that you are assigning and Array to one
of them and a numeric value to the other? Also, what do you think is in
..aryPartQty that you refer to it in this statement...

For i = 35 To UBound(aryPartQty)

without its dot? Perhaps if you showed us all your (relevant) code, what you
are trying to do would make more sense to us (maybe even including a
description would help).

 

[RyanH]

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
--
Cheers,
Ryan

You show a "dot" in front of .aryPartDesc and .aryPartQty... what object to
those dots reference back to? In other words, exactly what are
..aryPartDes(35) and .aryPartQty(35) that you are assigning and Array to one
of them and a numeric value to the other? Also, what do you think is in
..aryPartQty that you refer to it in this statement...

For i = 35 To UBound(aryPartQty)

without its dot? Perhaps if you showed us all your (relevant) code, what you
are trying to do would make more sense to us (maybe even including a
description would help).

 

[Chip Pearson]

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)

 

[RyanH]

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

 

[Chip Pearson]

The problem in the line
Total.NoMarkUpItems = Total.NoMarkUpItems + .aryPartQty(i) *
.aryPartDes(i)(1, 4)

because .aryPartDes(i) contains a single-dimensional array (created
with the Array function), and therefore you can't use the (1,4).
Choose either item 1 or 4 whichever is relevant. E.g,.

Total.NoMarkUpItems = Total.NoMarkUpItems + .aryPartQty(i) *
.aryPartDes(i)(1)

'or
Total.NoMarkUpItems = Total.NoMarkUpItems + .aryPartQty(i) *
.aryPartDes(i)(4)

Using a stripped out version of you code, the Locals window properly
reports
..aryPartDes(35)(3) = what should be there

It does not display as
..aryPartDes(35)(1,3)

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[RyanH]

Good Morning Chip! Because all the the array element are 2D other than (35),
I was wanting to know if there is a way to use the Array function to make it
2D? If not, I guess I can use what is below. PartInfo is a UDF that returns
a range of 4 cells, the 4th cell being a price of that part.

' onsite service techinician, do not mark up
If chkServicePlan Then
.aryPartDes(35) = PartInfo("ServiceTech")
.aryPartDes(35)(1, 4) = CDbl(tbxOnSiteServiceTechCost)
.aryPartQty(35) = 1
Else
.aryPartDes(35) = Empty
.aryPartQty(35) = 0
End If

 

[Rick Rothstein]

Because all the the array element are 2D other than (35), I was wanting

to know if there is a way to use the Array function to make it 2D?

I haven't been following this thread nor the logic behind your code;
however, I did want to address this question for you. There is a way to do
what you ask... sort of. This will only work with variables declared as
Variant (which is the case for your question), but the syntax will be a
little funny. Here is an example for a normal Variant variable, but you can
apply the technique to your array of Variants as well (you will just have an
extra set of parentheses for the Variant array index in addition to the ones
shown below). Note, that while not necessary, I have assumed a "pure"
multi-dimensional array in which there are the same number of columns in
each row.

Sub Test()
Dim Row As Long
Dim Col As Long
Dim V As Variant
'
' Assign a 3 by 4 array to Variant variable V
'
V = Array(Array(1, 2, 3), Array("A", "B", "C"), _
Array(5, 6, 7), Array("D", "E", "F"))
'
' Let's see the result of that assignment; and notice
' the double parentheses method of referencing the
' multi-dimensional array - V(Row)(Col)
'
Debug.Print "Row Col Value"
For Row = LBound(V) To UBound(V)
For Col = LBound(V(LBound(V))) To UBound(V(UBound(V)))
Debug.Print " " & Row & " " & Col & " " & V(Row)(Col) & _
" " & "V(" & Row & ")(" & Col & ")"
Next
Next
End Sub