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.

What is the code for your function?

jamheadart
– jamheadart

2020-06-21 06:24:17 +00:00
Commented Jun 21, 2020 at 6:24 — jamheadart
– jamheadart, Commented Jun 21, 2020 at 6:24
exceloffthegrid.com/…

Tim Williams
– Tim Williams

2020-06-21 17:21:40 +00:00
Commented Jun 21, 2020 at 17:21 — Tim Williams
– Tim Williams, Commented Jun 21, 2020 at 17:21

FaneDuru · Accepted Answer · 2020-06-22 06:59:13Z

1

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

edited Jun 22, 2020 at 6:59

answered Jun 21, 2020 at 8:00

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Henjoe Over a year ago

Thanks Sir :) Highly appreciated your answer. I might need to change my code to work with it.

FaneDuru Over a year ago

@Henjoe: Glad I could help! But, when somebody here gives a helpfull answer, we here tick the code left side check side check box, ito make it accepted answer. In this way, somebody else searching for a similar issue will know that the answer was helpfull. :)

FaneDuru Over a year ago

@Henjoe: I edited the answer and supplied an example of sheet Change Event use...

Christian Eslabon · Accepted Answer · 2020-08-04 19:58:16Z

0

Use the new =FILTER() function in excel. This will return a 1D or 2D array in excel. No VBA required.

edited Aug 4, 2020 at 19:58

answered Jun 21, 2020 at 7:18

Christian Eslabon

1854 silver badges10 bronze badges

1 Comment

Henjoe Over a year ago

Hello Ian, thank you for your answer. this is not actually what I want. As @fanedure's answer, what I want to do is not possible. Again, thanks for sharing.

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related