Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to perform function (e.g. Sum, Average) on VB 3-d Array
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to perform function (e.g. Sum, Average) on VB 3-d Array
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to perform function (e.g. Sum, Average) on VB 3-d Array
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to perform function (e.g. Sum, Average) on VB 3-d Array
Bernard:
Apparently you can't do 3-D array formulas in Excel. See this link: http://office.microsoft.com/en-us/ex...872901033.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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to perform function (e.g. Sum, Average) on VB 3-d Array
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/ex...872901033.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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to perform function (e.g. Sum, Average) on VB 3-d Array
Dana;
I saw that information on 3D sums also. When reading further, I think they mean something different. I believe they are talking about references across multiple worksheets and not 3-D arrays. Yes, the English language can be ambiguous if the author is not careful. -- Rich Locus Logicwurks, LLC "Dana DeLouis" wrote: 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/ex...872901033.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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding or to previously provided Average If Array function | Excel Worksheet Functions | |||
Adding if to Average If Array function | Excel Worksheet Functions | |||
how do I perform a conditional average with exclusions? | Excel Discussion (Misc queries) | |||
Small Array is too big for AVERAGE Function? | Excel Worksheet Functions | |||
perform 1 function, stop, perform different function | Excel Discussion (Misc queries) |