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

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

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

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

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

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
.