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
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