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