how to perform function (e.g. Sum, Average) on VB 3-d Array
in [Programming]
|
Prev: Any help with inserting, copying and transferring data.
Next: Excel formula needed to round up a value to a whole number
From: Bernard on 6 May 2010 17:40 Hi there can I use a excel function such as SUM on a 3d array? e.g. Dim array1(10,10,10) Dim av1 as integer av1 = Application.WorksheetFunction.average(array1(1,1,1):array1(10,1,1) debug.print av1 many thanks
From: Rich Locus on 6 May 2010 19:11 If you can't find a function that suits your needs, you can always write your own 3-D sum function as Follows: Option Explicit Option Base 1 Public Sub SumMyArray() Dim intMyArray(2, 3, 4) As Integer Dim lngTheFinalSum As Long intMyArray(1, 1, 1) = 1 intMyArray(1, 1, 2) = 1 intMyArray(1, 1, 3) = 1 intMyArray(1, 1, 4) = 1 intMyArray(1, 2, 1) = 1 intMyArray(1, 2, 2) = 1 intMyArray(1, 2, 3) = 1 intMyArray(1, 2, 4) = 1 intMyArray(1, 3, 1) = 1 intMyArray(1, 3, 2) = 1 intMyArray(1, 3, 3) = 1 intMyArray(1, 3, 4) = 1 intMyArray(2, 1, 1) = 1 intMyArray(2, 1, 2) = 1 intMyArray(2, 1, 3) = 1 intMyArray(2, 1, 4) = 1 intMyArray(2, 2, 1) = 1 intMyArray(2, 2, 2) = 1 intMyArray(2, 2, 3) = 1 intMyArray(2, 2, 4) = 1 intMyArray(2, 3, 1) = 1 intMyArray(2, 3, 2) = 1 intMyArray(2, 3, 3) = 1 intMyArray(2, 3, 4) = 1 lngTheFinalSum = SumArray(intMyArray, 2, 3, 4) MsgBox ("The Final Sum is " & lngTheFinalSum) End Sub Public Function SumArray(intArray, x As Integer, y As Integer, z As Integer) As Long Dim i As Integer Dim j As Integer Dim k As Integer Dim lngSum As Long For i = 1 To x For j = 1 To y For k = 1 To z SumArray = SumArray + intArray(i, j, k) Next k Next j Next i End Function -- Rich Locus Logicwurks, LLC "Bernard" wrote: > Hi there > can I use a excel function such as SUM on a 3d array? > e.g. > > Dim array1(10,10,10) > Dim av1 as integer > av1 = Application.WorksheetFunction.average(array1(1,1,1):array1(10,1,1) > debug.print av1 > > > many thanks >
From: Bernard on 7 May 2010 05:57 thank you Rich and nice idea. However for speed reasons, I'm trying to avoid looping through the Array, so I'm looking for the VB code that allows me to do what can be done in a Sheet i.e. Sum(Range) or Average(Range). I'm dealing with large arrays and lots of calculations so speed is important. any other ideas would be appreciated........... Bernard. "Rich Locus" wrote: > If you can't find a function that suits your needs, you can always write your > own 3-D sum function as Follows: > > Option Explicit > Option Base 1 > Public Sub SumMyArray() > Dim intMyArray(2, 3, 4) As Integer > Dim lngTheFinalSum As Long > > intMyArray(1, 1, 1) = 1 > intMyArray(1, 1, 2) = 1 > intMyArray(1, 1, 3) = 1 > intMyArray(1, 1, 4) = 1 > intMyArray(1, 2, 1) = 1 > intMyArray(1, 2, 2) = 1 > intMyArray(1, 2, 3) = 1 > intMyArray(1, 2, 4) = 1 > intMyArray(1, 3, 1) = 1 > intMyArray(1, 3, 2) = 1 > intMyArray(1, 3, 3) = 1 > intMyArray(1, 3, 4) = 1 > intMyArray(2, 1, 1) = 1 > intMyArray(2, 1, 2) = 1 > intMyArray(2, 1, 3) = 1 > intMyArray(2, 1, 4) = 1 > intMyArray(2, 2, 1) = 1 > intMyArray(2, 2, 2) = 1 > intMyArray(2, 2, 3) = 1 > intMyArray(2, 2, 4) = 1 > intMyArray(2, 3, 1) = 1 > intMyArray(2, 3, 2) = 1 > intMyArray(2, 3, 3) = 1 > intMyArray(2, 3, 4) = 1 > > lngTheFinalSum = SumArray(intMyArray, 2, 3, 4) > > MsgBox ("The Final Sum is " & lngTheFinalSum) > > End Sub > > Public Function SumArray(intArray, x As Integer, y As Integer, z As Integer) > As Long > Dim i As Integer > Dim j As Integer > Dim k As Integer > Dim lngSum As Long > > For i = 1 To x > For j = 1 To y > For k = 1 To z > SumArray = SumArray + intArray(i, j, k) > Next k > Next j > Next i > > End Function > > -- > Rich Locus > Logicwurks, LLC > > > "Bernard" wrote: > > > Hi there > > can I use a excel function such as SUM on a 3d array? > > e.g. > > > > Dim array1(10,10,10) > > Dim av1 as integer > > av1 = Application.WorksheetFunction.average(array1(1,1,1):array1(10,1,1) > > debug.print av1 > > > > > > many thanks > >
From: Rich Locus on 7 May 2010 16:48 Bernard: Apparently you can't do 3-D array formulas in Excel. See this link: http://office.microsoft.com/en-us/excel/HA010872901033.aspx Here's a quote from the page: A quick introduction to arrays and array formulas If you've done even a little programming, you've probably run across the term array. For our purposes, an array is just a collection of items. In Excel, those items can reside in a single row (called a one-dimensional horizontal array), a column (a one-dimensional vertical array), or multiple rows and columns (a two-dimensional array). You can't create three-dimensional arrays or array formulas in Excel. -- Rich Locus Logicwurks, LLC "Bernard" wrote: > thank you Rich and nice idea. > However for speed reasons, I'm trying to avoid looping through the Array, so > I'm looking for the VB code that allows me to do what can be done in a Sheet > i.e. Sum(Range) or Average(Range). I'm dealing with large arrays and lots of > calculations so speed is important. > any other ideas would be appreciated........... > Bernard. > > > "Rich Locus" wrote: > > > If you can't find a function that suits your needs, you can always write your > > own 3-D sum function as Follows: > > > > Option Explicit > > Option Base 1 > > Public Sub SumMyArray() > > Dim intMyArray(2, 3, 4) As Integer > > Dim lngTheFinalSum As Long > > > > intMyArray(1, 1, 1) = 1 > > intMyArray(1, 1, 2) = 1 > > intMyArray(1, 1, 3) = 1 > > intMyArray(1, 1, 4) = 1 > > intMyArray(1, 2, 1) = 1 > > intMyArray(1, 2, 2) = 1 > > intMyArray(1, 2, 3) = 1 > > intMyArray(1, 2, 4) = 1 > > intMyArray(1, 3, 1) = 1 > > intMyArray(1, 3, 2) = 1 > > intMyArray(1, 3, 3) = 1 > > intMyArray(1, 3, 4) = 1 > > intMyArray(2, 1, 1) = 1 > > intMyArray(2, 1, 2) = 1 > > intMyArray(2, 1, 3) = 1 > > intMyArray(2, 1, 4) = 1 > > intMyArray(2, 2, 1) = 1 > > intMyArray(2, 2, 2) = 1 > > intMyArray(2, 2, 3) = 1 > > intMyArray(2, 2, 4) = 1 > > intMyArray(2, 3, 1) = 1 > > intMyArray(2, 3, 2) = 1 > > intMyArray(2, 3, 3) = 1 > > intMyArray(2, 3, 4) = 1 > > > > lngTheFinalSum = SumArray(intMyArray, 2, 3, 4) > > > > MsgBox ("The Final Sum is " & lngTheFinalSum) > > > > End Sub > > > > Public Function SumArray(intArray, x As Integer, y As Integer, z As Integer) > > As Long > > Dim i As Integer > > Dim j As Integer > > Dim k As Integer > > Dim lngSum As Long > > > > For i = 1 To x > > For j = 1 To y > > For k = 1 To z > > SumArray = SumArray + intArray(i, j, k) > > Next k > > Next j > > Next i > > > > End Function > > > > -- > > Rich Locus > > Logicwurks, LLC > > > > > > "Bernard" wrote: > > > > > Hi there > > > can I use a excel function such as SUM on a 3d array? > > > e.g. > > > > > > Dim array1(10,10,10) > > > Dim av1 as integer > > > av1 = Application.WorksheetFunction.average(array1(1,1,1):array1(10,1,1) > > > debug.print av1 > > > > > > > > > many thanks > > >
From: Dana DeLouis on 7 May 2010 19:04
> Apparently you can't do 3-D array formulas in Excel. Just for discussion, another section of the help mentions the following. I know it's not really the same thing thou... Create a 3-D reference to the same cell range on multiple worksheets Learn about a 3-D refererence: You can use a 3-D reference to add up budget allocations ...etc =SUM(Sales:Marketing!B3) = = = = = = = :>) Dana DeLouis On 5/7/2010 4:48 PM, Rich Locus wrote: > Bernard: > > Apparently you can't do 3-D array formulas in Excel. See this link: > http://office.microsoft.com/en-us/excel/HA010872901033.aspx > > Here's a quote from the page: > > A quick introduction to arrays and array formulas > > If you've done even a little programming, you've probably run across the > term array. For our purposes, an array is just a collection of items. In > Excel, those items can reside in a single row (called a one-dimensional > horizontal array), a column (a one-dimensional vertical array), or multiple > rows and columns (a two-dimensional array). You can't create > three-dimensional arrays or array formulas in Excel. -- = = = = = = = HTH :>) Dana DeLouis |