Passing and Receiving an Array from Function

Question

I have made a bunch of 2D arrays in Excel, and I have written a function to put the same data in a copy of each. I'm not quite sure I'm getting the fiddly parts of the syntax correct.

The function is called 'Fill', the old array 'Old' and the new one 'New'. I'm using the name 'Block' for the passing-between variable name.

So, the line in my code is:

New = Fill(Block:=Old())

And the first line of my function is:

Function Fill(Block() As Variant) As Variant

This gives me a type mismatch error of the 'Old' array, saying it is expecting an array. Leads me to think the function is OK, and waiting for the correct type, but not receiving it.

What am I missing?

Answer 1

It has been a while since I did VBA programming, but I would think the following is more likely to be correct:

NewArray = Fill(OldArray)

Function Fill(Block As Variant) As Variant

  Fill = Block

End Function

Answer 2

My original code gives me a type mismatch error of the 'Old' array, saying it is expecting an array. Leads me to think the function is OK, and waiting for the correct type, but not receiving it.

Your suggestion goves me error 13, also a type mismatch, I'm afraid. Aren;t you trying to pass 'Old' and a string there?

Answer 3

Here are some notes on why you may have got the error you did. If a function expects a particular type, you must declare the variable as that type.

Sub FillThis()
'Declare OldArray as Variant '
'because that is what the function '
'requires. '
Dim OldArray As Variant
'Similarly ...'
Dim StringPart As String

    'ByRef, so the variable will be '
    'changed by the function. '
    'Note that ByRef is the default. '
    Fill OldArray

    For i = 0 To 4
      Debug.Print OldArray(i)
    Next

    StringPart = "Part 1"
    GetString StringPart
    Debug.Print StringPart
End Sub

'In this example, Fill is not being returned, '
'so there is no need to declare it as anything '
Function Fill(ByRef OldArray As Variant)
'The Array must be dimensioned '
ReDim OldArray(4)

    For i = 0 To 4
        OldArray(i) = i + 1
    Next

End Function

Function GetString(ByRef StringPart As String)
    StringPart = StringPart & " Add a Bit"
End Function