VBA: Summing a matrix

Question

Why doesn't this function work?

Type =funtest(2.1) in Excel and it'll give me #VALUE!.

Public Function funtest(a As Double) As Double

Dim z, j, i As Integer
Dim matrix(3, 3, 3) As Double

For z = 0 To 3 Step 1
For j = 0 To 3 Step 1
For i = 0 To 3 Step 1

matrix(z, j, i) = a

Next i, j, z

funtest = Application.WorksheetFunction.Sum(matrix)

End Function

Solution

WorksheetFunction.Sum will work with either a range or a 2 dimentional array. It errors because you are passing it a 3 dimensional array.

So, this works

Public Function funtest(a As Double) As Double
    Dim z As Long, j As Long, i As Long
    Dim matrix() As Double

    ReDim matrix(0 To 3, 0 To 4)
    For j = LBound(matrix, 1) To UBound(matrix, 1)
    For i = LBound(matrix, 2) To UBound(matrix, 2)
        matrix(j, i) = a
    Next i, j

    funtest = Application.WorksheetFunction.Sum(matrix)
End Function

Note I have modified your declarations slighly, see note at end of answer.

To sum higher dimensional arrays you will need to do some looping.

One option (which may or may not suit your overal requirements) is to declare your array slightly differently, as a so called Jagged Array.

Public Function funtest2(a As Double) As Double
    Dim z As Long, j As Long, i As Long
    Dim matrix() As Variant
    Dim InnerMatrix(0 To 4, 0 To 4) As Double

    ' Dimension Jagged Array
    ReDim matrix(0 To 4)
    For i = LBound(matrix, 1) To UBound(matrix, 1)
        matrix(i) = InnerMatrix
    Next

    'Load Data into matrix
    For z = LBound(matrix) To UBound(matrix)
    For j = LBound(matrix(z), 1) To UBound(matrix(z), 1)
    For i = LBound(matrix(z), 2) To UBound(matrix(z), 2)
        matrix(z)(j, i) = a
    Next i, j, z

    ' Sum matrix
    For z = LBound(matrix) To UBound(matrix)
        funtest2 = funtest2 + Application.WorksheetFunction.Sum(matrix(z))
    Next
End Function

This is an array of 2 dimensional arrays. The Sum is then applied to each of the inner arrays in turn. This way, at least you are only looping one dimension and not all three.

Note on Dim and Integer
You must specify all As Type's, otherwise variables default to Variant
In your code z and j will be Variants

Also, using Integer rather than Long is actually counter productive on a 32 bit OS: Long's will be slightly faster.