从VB函数填充Excel范围 [英] Filling an Excel range from a VB function

问题描述

好的，所以我发现了很多线程来解释如何从VB中的子对象填充Excel范围，但是没有一个线程可以从函数中解释如何做到这一点.

假设以下功能(即使我必须使用的功能要复杂得多...):

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

因此，这个小程序的取值范围为v，我想用向量a的值填充它.它不起作用，仅返回#VALUE.
有人知道为什么，更重要的是如何使其工作?

我要它执行的操作是在调用函数的单元格中返回0，并填充我选择的范围.我什至尝试了以下尝试，但无济于事:

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:

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?

推荐答案

所以我已经玩了大约一个小时了，看起来好像不像你可以从公式中更改单元格内容.

就我个人而言，我不理解您要如何使用该示例代码.为什么在世界上，您会选择用户选择的范围，然后将其调整为仅5个单元格的大小?

我在新的excel工作簿的新模块中编写了以下函数.

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.

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

如果我通过单击按钮调用此函数，则该函数可以完美运行，但是如果我在单元格中键入"= FillIn()"，该函数将不起作用.

我不知道为什么，但是似乎如果您使用函数作为公式，则调用它的单元格中的某些内容将不允许更改任何其他单元格.您可以获取其他单元格的值，但是如果尝试更改它们的值，则会引发错误.

我认为您必须找出另一种方法来完成您想要的事情.

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.

最简单的方法是调用Sub，该Sub修改了函数中的数据. />
至于为什么它不起作用是因为它并不意味着要修改值
或样式/格式.函数的主要目的是创建可从Excel调用的自定义函数.
像=MySum(A1);

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

一样，我要感谢你们两个人的回答.多亏了您，我找到了解决问题的方法^^

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

这篇关于从VB函数填充Excel范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！