Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make an Array 2-dimensional in VBA
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 .aryPartDes(35) = Array("", "Ad Tech On Site Service Techinician", "ea.", CDbl(tbxOnSiteServiceTechCost)) .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 -- Cheers, Ryan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make an Array 2-dimensional in VBA
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). -- Rick (MVP - Excel) "RyanH" wrote in message ... 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 .aryPartDes(35) = Array("", "Ad Tech On Site Service Techinician", "ea.", CDbl(tbxOnSiteServiceTechCost)) .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 -- Cheers, Ryan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make an Array 2-dimensional in VBA
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 "Rick Rothstein" wrote: 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). -- Rick (MVP - Excel) "RyanH" wrote in message ... 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 .aryPartDes(35) = Array("", "Ad Tech On Site Service Techinician", "ea.", CDbl(tbxOnSiteServiceTechCost)) .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 -- Cheers, Ryan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make an Array 2-dimensional in VBA
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make an Array 2-dimensional in VBA
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make an Array 2-dimensional in VBA
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) On Wed, 10 Dec 2008 12:49:01 -0800, RyanH wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make an Array 2-dimensional in VBA
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 -- Cheers, Ryan "Chip Pearson" wrote: 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) On Wed, 10 Dec 2008 12:49:01 -0800, RyanH wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make an Array 2-dimensional in VBA
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 -- Rick (MVP - Excel) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Export 1-dimensional array values to a two-dimensional table? | Excel Programming | |||
Changing a two-dimensional, one row array to one-dimensional | Excel Programming | |||
Returning an array from a multi-dimensional array | Excel Programming | |||
Mutli-dimensional Array to Single-Dimension Array | Excel Programming | |||
Create One-Dimensional Array from Two-Dimensional Array | Excel Programming |