Filling an excel range from a vb function

[lightdreamer]

Ok guys, so i've found tons of threads that explain how to fill an excel range from a sub in vb, but none that explains how to do so from a function

Suppose the following function (even if what i have to work with is much more complicated...):

Option Base 1
Function fill(v As Range) As Double
    
    Dim a(5, 1) As Variant
    
    For i = 1 To 5
        a(i, 1) = i
    Next i
    
    v.Resize(5, 1).Value = a
    
    fill=0

End Function

So, this little program takes the range v, and i want to fill it with values from vector a. It doesn't work, it returns only #VALUE.
Someone knows why, and more important how to make it work?

[WoF]

I think you have to do a loop. I'm not sure if you can assign a vector (as you say, which is a two-dimensional array in VB slang) directly to a range of cells.

[aio]

I am not quite sure of your intentions. But the most glaring error I can see are

1 - you assign an entire array of 'a' to a single range (which may be a single cell or a group of cells). You must assign individual array elements to individual cells in a range.

2 - as a function, it must return a value. You assigned 0 to your function name. In effect whatever you do and no matter if the assignment is correct, the function is useless because it will always return a zero (0).

[WoF]

The function issue is no matter here, I think.
Some programming philosophies do not distinguish between a function and a procedure. A function which returns zero is the same as a procedure which returns nothing at all. It serves it's function anyway. In VB6 we can call a function in the same way as a procedure when we do not require the return value. This shows colorful what a procedure is to be. If we need the return value, we use it and use the function in an expression. If we do not need the return value, we use the function like a statement. VB6 allows this perfectly. In fact the Sub is not required at all, being only a function with no return value.

The array issue, however, is the issue here definitely. As you say, aio, each element has to be assigned individually. You cannot assign a range of cells to a 2-dimensional array in one single instruction.

[viperbyte]

Here's an example of a function call and it's corresponding function definition that I use in a current project that I'm working on.

Worksheets("TempSheet").Cells(RowPos, 1).Cells.Value = GetNextServer


Private Function GetNextServer() As String
GetNextServer = "(s) " & Worksheets("Servers").Cells(EmpProc, 1).Cells.Value & " " & Worksheets("Servers").Cells(EmpProc, 2).Cells.Value
End Function

Nothing tricky about using a function. You just use the function name as if it were the value to assign to a cell. That's basicaly to answer your question about how to use a function in an excel environment. But like Aio says you have to assign to a particular cell. But your function as it's currently displayed won't produce the results you want. Once you have your function returning a valid value you can use it as described. But if you're question is more about how to deal with your particular function then that's a whole different matter.

[WoF]

The original question is this (correct me if I'm wrong):
Can I have a two-dimensional array like a(5,1) and put the values of this array into a range of cells, all in one go like Range(5,1) = a.
I think the answer is no. You have to da aloop where you put individual values into individual cells.

For i=0 to 5
  For j = 0 to 1
    Cell(i,j) = a(i,j)
  Next
Next

This only being pseudocode to illustrate what I mean. I think the addressing syntax of individual cells of an excel sheet is a little different.