How to fill-up cells within a Excel worksheet from a VBA function?

Question

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.

I tried with such a function:

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

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

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

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

Your current approach assumes that there are always at least two cells selected, horizontally. The function fails if they are selected vertically, for example. May I ask what you are actually trying to do with the function? This info might be helpful. — Tomalak
This function would return an "unknown" size (both in rows and columns) of data. By example it could be a SQL request again a table in a DB. It would return the name of the columns + the associated rows. — TigrouMeow
So basically, starting from the cell the user is currently in, you want to fill some data down and left? — Tomalak
Exactly! Like any of these libraries that provide functions to get data from any kind of sources. — TigrouMeow
Just write it as a normal VBA function, called via Tools > Macros and use Application.ActiveCell to determine where to put the results — barrowc

JDunkerley JDunkerley · Accepted Answer · 2009-05-14T09:06:09

You will need to do this in two steps:

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

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

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.

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

How to fill-up cells within a Excel worksheet from a VBA function?

4 Answers