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... and it should fill 3 ranges with 3 different arrays...):


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?

A similar code for a sub (without input, selecting a range inside the sub and filling it) works...

[GTO]

Greetings lightdreamer,

I hope I do not get beyond myself here, but it appears to me that you are trying to pass an arg back to the calling procedure. This can be done, but how are you calling the function?

Please show a short example calling the function.

Mark

[lightdreamer]

well, what i want it to do is to return 0 in the cell where i call the function, and return the array in the selected range.
the calling would be fill("a1:a10").

Anyway, the problem exists even in the following code, where i don't select anything:


Function fill() As Double

Dim a(5, 1) As Variant

For i = 1 To 5
a(i, 1) = i
Next i

Range("a1:a20").Resize(5, 1).Value = a
fill = 0
End Function


So my question is: why i can't change the value of a range inside a function?

[GTO]

well, what i want it to do is to return 0 in the cell where i call the function,....

Do you mean you are trying to call the function from a worksheet (A User-defined function)?

If so, maybe XLD or Malcom or someone can correct me, but I don't think that's going to happen. AFAIK, a worksheet function (including a UDF) cannot change properties (including .Value) of other cells.

Mark

[Jan Karel Pieterse]

Normally, you cannot affect content of other cells from a UDF called from a worksheet cell.

But.

It can be done, using a couple of events, see attached for an example.

[SamT]

lightdreamer,

Are you asking how to put the values of an array into a range

Dim SomeArray as array(n)
Dim SomeRange AS Range

SomeRange = SomeArray


OR:
Are you asking how to use a UDF in a cell?

Where A1.Value = 2 and B1.Value = 3, Then

Function AddTwoCells(Num1, Num2) As Long
AddTwoCells = Num1 + Num2
End Function


Then if C3.Formula = AddTwoCells(A1, B1)
C3.Value = 5

SamT

[lightdreamer]

My problem was to find a way to modify some selected cell values with values from an array that was computed from a dll. I've found a way to do it with the array formulas. It's less dynamic (for i have to choose columns that are near each other) but it works.

For whom may be interested, if you do something like this:




Function fill() As Variant

Dim a(5) As Double
Dim b(5) As Double
Dim c(5) As Double
Dim myoutput(5, 3) As Variant

'define vectors
For i = 1 To 5
a(i) = i
b(i) = i * i
c(i) = Sqr(i)
Next i

'define the output
For i = 1 To 5
myoutput(i, 1) = a(i)
myoutput(i, 2) = b(i)
myoutput(i, 3) = c(i)
Next i


fill = myoutput


End Function


and than call the function as an array function (ctrl+shift+enter), for example in cells a1:c5, you'll see the values you want.

As I've said, it's not as general as i'd like (you can't select three distinct columns), but it'll do. At least for now. I hope.

[Bob Phillips]

As has been mentioned, you cannot write to a range of cell from within a function, but you can have a function that applies to multiple cells.

You need to select all of the target cells, and then array enter the formula, so that all of them are processing that same formula action. Here is an example, first enter this UDF, then put any value in B1 and select A1:A3 and type in the formula =myUDF(B1), and hit Ctrl-Shift-Enter



Function myUDF(basecell As Range)
Dim ary As Variant
Dim i As Long

ReDim ary(1 To 3)
For i = 1 To 3

ary(i) = basecell * Rnd()
Next i

myUDF = Application.Transpose(ary)
End Function