Filling an Excel range from a VB function

Question

0.00/5 (No votes)

See more:

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...):

VB

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?

What I want it to do is return 0 in the cell where I call the function, and fill the range I selected. I even tried, to no avail, the following:

VB

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 can't I change the value of a range inside a function? And how can I make it do so?

Posted 4-Mar-10 23:28pm

lightdreamer83

Updated 5-Mar-10 11:06am

Smitha Nishant

v2

Add a Solution

3 solutions

Solution 3

I wanna thank both of you for your answers. Thanks to you, i've found a way around my problem ^^

Posted 6-Mar-10 1:54am

lightdreamer83

Updated 6-Mar-10 12:48pm

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

William Winner · Accepted Answer · 2010-03-05T12:30:00

So I've been playing around with this for about the last hour and it doesn't seem like you can change cell contents from within a formula.

Personally, I don't understand what you're trying to do with that sample code. Why in the world would you take the user's selected range, and then resize it to only 5 cells?

I wrote the following function within a new module in a new excel workbook.

VB

Function FillIn()
    Range("A1").Value = 1
    FillIn = 1
End Function

This function works perfectly if I call it from a button click, but doesn't work if I type "=FillIn()" within a cell.

I have no idea why, but it seems that if you use a function as a formula, something within the cell that is calling it disallows changing any of the other cells. You can get other cell's values, but if you try to change them, it throws an error.

I think you'll have to figure out another way to do what you're wanting.

Dan Mos · Accepted Answer · 2010-03-05T12:23:00

Solution 1

The simplest way is to call the Sub that modifies the datas from the function.

As for why it doesn't work is because it is not meant to modify values
or styles/formatting. The main purpose of the Function is to create custom functions that you can call from Excel.
Like =MySum(A1);

Posted 5-Mar-10 12:23pm

Dan Mos