title: How to fill-up cells within a Excel worksheet from a VBA function?
body: <p>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.</p>
  
  <p>I tried with such a function:</p>
  
  <pre><code>Function FillHere()
    Dim rngCaller As Range
    Set rngCaller = Application.Caller
    rngCaller.Cells(1, 1) = "HELLO"
    rngCaller.Cells(1, 2) = "WORLD"
  End Function
  </code></pre>
  
  <p>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):</p>
  
  <pre><code>Function FillHere()
      Dim rngCaller As Range
      Cells(1, 1) = "HELLO"
      Cells(1, 2) = "WORLD"
  End Function
  </code></pre>
  
  <p>This is not working neither. <strong>But it works if I start this function from VBA using F5!</strong> It seems it's not possible to modify anything on the spreadsheet while calling a function... some libraries do that though... </p>
  
  <p>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).</p>
  
  <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.</p>
  
  <p>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.</p>