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

Question

Hello,

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.

Answer 1

What you're trying to do won't work in Excel - this is by design.

You can do this, though:

Function FillHere()
    Redim outputArray(1 To 1, 1 To 2)
    outputArray(1, 1) = "HELLO"
    outputArray(1, 2) = "WORLD"
    FillHere = outputArray
End Function

If you then select two adjacent cells in your worksheet, enter =FillHere() and press Control+Shift+Enter (to apply as an array formula) then you should see your desired output.

Answer 2

Fundamentally, a function can only affect the cell it is called from. It sounds like you may need to look at using the Worksheet_Change or Worksheet_SelectionChange events to trigger the modification of cells in the intended range.

Answer 3

You can do this indirectly using a 2-stage process: Write your UDF so that it stores data in a sufficiently persistent way (for example global arrrays). then have an Addin that contains application events that fire after each calculation event, looks at any data stored by the UDFs and then rewrites the neccessary cells (with warning messages about overwrite if appropriate) and reset the stored data.

This way the user does not need to have any code in their workbook.

I think (but do not know for sure) that this is the technique used by Bloomberg etc.

Answer 4

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.