Array function (UDF) only sets first cell in a row

Asked Nov 25 '22 at 09:47

Active Nov 25 '22 at 09:47

Viewed 22 times

I need to output to multiple cells in a row from an UDF and I tried this solution:

Function tester()
    tester = Array("a", "b", "c")
End Function

However, only the first cell contains "a".

asked Nov 25 '22 at 09:47

Alexander Zeitler

2

If you do not have a version of Excel with dynamic arrays (2021 or 365), then you will have to array enter the formula into as many cells as required. – Rory Nov 25 '22 at 09:50
2

Either that or use something along the lines of `=INDEX(tester(),COLUMN(A1))` – JvdV Nov 25 '22 at 09:54
@Rory what does "array enter" mean? – Alexander Zeitler Nov 25 '22 at 09:55
2

Select three cells in a row, type `=tester()` and then press Ctrl+Shift+Enter not just Enter. – Rory Nov 25 '22 at 09:59
1

@JvdV Thanks, the `INDEX` version did work. But this will call my function multiple times, right? – Alexander Zeitler Nov 25 '22 at 10:04
Yes it would, but given the probability you don't know how many elements are in the array it seem logical. – JvdV Nov 25 '22 at 10:05

0 Answers0