VBA: Function array, ReDim gives invalid ReDim

Question

I am using a Function to make an array. The input is also an array. When running it gives me an invalid ReDim compile error. Before this was run in a sub routine and the ReDim worked well but now I changed it in a Function and it gives the invalid ReDim compile error. What am I missing here?

Thanks in advance! Amir

Public Function bmhussel(filemx As Variant)

rijaantal = UBound(filemx, 1)
kolomaantal = UBound(filemx, 2)


ReDim bmhussel(1 To rijaantal + 1, 1 To kolomaantal + 1)

For i = 1 To rijaantal
    bmhussel(i, 1) = filemx(i, 1)
    bmhussel(i, 2) = filemx(i, 3)
    bmhussel(i, 3) = filemx(i, 5)
    bmhussel(i, 4) = filemx(i, 28)
    bmhussel(i, 5) = bucket(filemx(i, 28)) 'buckets maken
next i

End Function

Answer 1

Welkom op Stack overflow.

As said you cannot redim the function itself. Therefore use a temporary variable and in the end transfer its content to your function:

Public Function bmhussel(filemx As Variant) as Variant

Dim rijaantal As Long
Dim kolomaantal As Long
Dim tmpArray as Variant

rijaantal = UBound(filemx, 1)
kolomaantal = UBound(filemx, 2)

ReDim tmpArray (1 To rijaantal + 1, 1 To kolomaantal + 1)

For i = 1 To rijaantal
    tmpArray(i, 1) = filemx(i, 1)
    tmpArray(i, 2) = filemx(i, 3)
    tmpArray(i, 3) = filemx(i, 5)
    tmpArray(i, 4) = filemx(i, 28)
    tmpArray(i, 5) = bucket(filemx(i, 28)) 'buckets maken
next i

bmhussel = tmpArray

End Function

Answer 2

bmhussel is the name of your function and not the name of a variable. You cannot Redim your function.

ReDim bmhussel(1 To rijaantal + 1, 1 To kolomaantal + 1)