How to fill cells in excel sheet from VBA function?

Question

I just want to populate cells in my spreadsheet from a VBA function. For example, I would like to enter = FillHere () in a cell, and as a result, I will have several cells filled with some data.

I tried with a function like this:

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

It is interrupted as soon as I try to change the range. Then I tried this (even this is not quite what I am looking for):

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

This does not work. But it works if I run this function from VBA using F5! It seems that it is not possible to edit anything in a spreadsheet when calling a function ... some libraries do this though ...

( ) . , ( , + F2 + CTRL-SHIFT-ENTER, , ).

. , - , , , .

PS: , , , , .

Answer 1

:

- :

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

, Sub, ThisWorkbook VBA - :

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

, , topleft, . , , , FillHere. , .

, , , .

: XLA. ThisWorkbook :

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

.

Answer 2

, , Excel - .

, :

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

, =FillHere() Control + Shift + Enter ( ), .

Answer 3

, , . , Worksheet_Change Worksheet_SelectionChange , .

Answer 4

, : UDF, (, ). Addin, , , , UDF, ( , ) reset .

, - .

( ), , Bloomberg ..