如何填写了细胞从VBA函数Excel工作表中？ [英] How to fill-up cells within a Excel worksheet from a VBA function?

问题描述

我只想填写了细胞从VBA函数我的小号preadsheet。举例，我想在一个单元格中键入= FillHere（），并在结果我都会有一些细胞填充了一些数据。

I simply want to fill-up cells in my spreadsheet from a VBA function. By example, I would like to type =FillHere() in a cell, and in result I will have a few cells filled-up with some data.

我试着用这样的功能：

Function FillHere()
  Dim rngCaller As Range
  Set rngCaller = Application.Caller
  rngCaller.Cells(1, 1) = "HELLO"
  rngCaller.Cells(1, 2) = "WORLD"
End Function

据只要我尝试修改的范围内打破。然后，我想这（即使它不是真正的我正在寻找的行为）：

It breaks as soon as I try to modify the range. Then I tried this (even it's not really the behavior I'm looking for):

Function FillHere()
    Dim rngCaller As Range
    Cells(1, 1) = "HELLO"
    Cells(1, 2) = "WORLD"
End Function

这是不工作也没有。 但是，它的工作原理，如果我使用F5！看来这是不可能修改任何的S preadsheet同时调用一个函数......一些图书馆做，虽然从VBA启动此功能...

This is not working neither. But it works if I start this function from VBA using F5! It seems it's not possible to modify anything on the spreadsheet while calling a function... some libraries do that though...

我也试过（其实这是我的第一个想法），从函数返回一个数组。问题是，我只在阵列中获得的第一个元素（也就是意味着在左上角+ F2 + CTRL-SHIFT-ENTER选择具有公式整个区域的把戏，但是这意味着用户需要知道通过预先数组的大小）。

I also tried (in fact it was my first idea) to return a array from the function. The problem is that I only get the first element in the array (there is a trick that implies to select a whole area with the formula at the top left corner + F2 + CTRL-SHIFT-ENTER, but that means the user needs to know by advance the size of the array).

我真的坚持了这个问题。我不是最终的最终用户，所以我需要的东西很容易使用，具有pferably，$ P $，任何参数都没有。

I'm really stuck with this problem. I'm not the final end-user so I need something very easy to use, with, preferably, no argument at all.

PS：我很抱歉，我问过这个问题了，但我没有在那个时候注册的，似乎我不能参与到其他线程了

PS: I'm sorry I asked this question already, but I wasn't registered at that time and it seems that I can't participate to the other thread anymore.

推荐答案

您需要做这两个步骤：

更改您的模块是这样的：

Change your module to be something like:

Dim lastCall As Variant
Dim lastOutput() As Variant

Function FillHere()
    Dim outputArray() As Variant
    ReDim outputArray(1 To 1, 1 To 2)
    outputArray(1, 1) = "HELLO"
    outputArray(1, 2) = "WORLD"

    lastOutput = outputArray
    Set lastCall = Application.Caller

    FillHere = outputArray(1, 1)
End Function

Public Sub WriteBack()
    If IsEmpty(lastCall) Then Exit Sub
    If lastCall Is Nothing Then Exit Sub

    For i = 1 To UBound(lastOutput, 1)
        For j = 1 To UBound(lastOutput, 2)
            If (i <> 1 Or j <> 1) Then
                lastCall.Cells(i, j).Value = lastOutput(i, j)
            End If
        Next
    Next

    Set lastCall = Nothing
End Sub

然后为了调用子进入的ThisWorkbook区VBA和添加类似：

Then in order to call the Sub go into the ThisWorkbook area in VBA and add something like:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    Call WriteBack
End Sub

这样做是返回左上单元格的值，然后计算后完成填充休息。我写这个问题的方法它假定只有一个FillHere功能将在同一时间被调用。如果你想有多个的重新计算其在同一时间，那么你就需要一个更复杂的一套全局变量。

What this does is return the value of the topleft cell and then after calculation completes populates the rest. The way I wrote this it assumes only one FillHere function will be called at a time. If you want to have multiple ones which recalculate at the same time then you will need a more complicated set of global variables.

提醒一句的是，这不会在意，当它填充其他单元格是什么覆盖。

One word of warning is that this will not care what it overwrites when it populates the other cells.

编辑：
如果你想这样做，在一个XLA应用范围的基础上。在code代表的ThisWorkbook区域应该是这样的：

If you want to do this on a Application wide basis in an XLA. The code for the ThisWorkbook area should be something like:

Private WithEvents App As Application

Private Sub App_SheetCalculate(ByVal Sh As Object)
    Call WriteBack
End Sub

Private Sub Workbook_Open()
    Set App = Application
End Sub

这将连线了应用层计算。

This will wire up the Application Level calculation.

这篇关于如何填写了细胞从VBA函数Excel工作表中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！