Question

I originally had a VBA function that returned a two dimensional variant array:

Public Function SplitIntoCells(some_data As String) As Variant()
End Function

我使用了阵列的辛奈,将其从另一 v功能中称作:

Public Function MyWrapper() as Variant
    MyWrapper = SplitIntoCells("somestring")
End Function

From Excel, if I select a big enough range and then do:

=MyWrapper()

随后是CTRL+SHIFT+ENTER,该数据明显分为每一组。

然而,为了实现自动化,如果我改变我的手法,则:

Public Function MyWrapper()
    ActiveSheet.Range("A1:E20").Select
    Selection.FormulaArray = SplitIntoCells("somestring")
End Function

以上只是工作。在Excel,我看不到任何东西。

我做了什么错误?

最新情况:

就测试而言,如果我稍微修改MyWrapper()如下:

Public Function MyWrapper()
    Dim variant_temp() as Variant
    variant_temp = SplitIntoCells("somestring")
    ActiveSheet.Range("A1:E20").Select
    Selection.FormulaArray = variant_temp
End Function

variant_temp predictably has the 2-d array after returning from SplitIntoCells but the subsequent Selection.FormulaArray still has nothing in it even after the assignment. I am sure I am missing something blindingly obvious.

Answer 1

A VBA user-defined function cannot modify the cell or range it s being called from: it can only return a value.

友情链接