VBA function that return array and automatically fill in the range based on the array dimension

Question

I created a Function where it return an array it is like: Val(0,0) = "Hello" Val(1,0) = "World"

So my array is 2 dimensional Array. If I put my formula in a specific cell let say "=GetData()" it gives me only 1 value which is the first value in the array. I know when getting an array I should be select a Range then Enter the formula then press "CTRL+SHIFT+ENTER", this actually works.

However, I don't know if there is a possibility that by only selecting 1 cell (not a Range) and enter the formula that return an array, I want to automatically fill the cells based on array dimension, so the user don't need to select a Range and press the CTRL+SHIFT+Enter.

Thank you very much.

FaneDuru FaneDuru · Accepted Answer · 2020-06-21T08:00:03

Excel VBA does not allow to a UDF function to modify the value of another cell. The only thing a UDF is allowed to do is to return values to the cell/cells it is called from.

So, what you try doing is not possible.

In order to obtain what you need, do not use a UDF function! I mean, do not call it from the worksheet.

If you need a workaround, you can try sheet change Event, to target a specific cell (where you change something) and make a code to place the array wherever your code tells it...

An example of doing that, would be the next. You can see that a 1D array can also be used to return in a range:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.address(False, False) <> "G1" Then Exit Sub
  If Target.Value = "Go" Then
    Dim arr(1 To 1, 1 To 2) As Variant, arr2 As Variant
     arr(1, 1) = "Hello": arr(1, 2) = "World"
     arr2 = Split("Hello World", " ")
     Target.Offset(, 1).Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
     Target.Offset(1, 1).Resize(1, UBound(arr2) + 1).Value = arr2
  End If
End Sub

In order to use it, you must write "Go" on the sheet where the above event exists. I would suggest you to validate the cell as list, supplying Go,Nothing and playing with it, will run the code once and not for 'Nothing' list option...

VBA function that return array and automatically fill in the range based on the array dimension

2 Answers