User Defined Function In Array Formula

[Jaafar Tribak]

Hi,

I have this simple UDF that takes a worksheet index number and returns the worksheet name :

Function SheetFromIndex(index As Long) As String

    SheetFromIndex = ThisWorkbook.Worksheets(index).Name

End Function

The idea is to enter the following formula array in a range (say range A1:A3 ) and return the corresponding worksheet names in each cell ... obviously, that doesn't work and returns #Value error
{=SheetFromIndex(ROW())}

{=SheetFromIndex(ROW(A1:A3))} didn't work either.

Thanks for your help.

 

[MARK858]

Hi Jafaar, do you mean the below?

Book1
	A
1	Sheet1
2	Sheet2
3	Sheet3
Sheet3

Cell Formulas
Range	Formula
A1:A3	A1	=SheetFromIndex(ROWS($A$1:$A1))

 

[Jaafar Tribak]

Hi Jafaar, do you mean the below?

Book1
	A
1	Sheet1
2	Sheet2
3	Sheet3
Sheet3

Cell Formulas
Range	Formula
A1:A3	A1	=SheetFromIndex(ROWS($A$1:$A1))

Yes. but with an array formula .

Thanks MARK858

 

[FormR]

Hi, I think your UDF would need changing to do that, something like this for example.

Function SheetFromIndex(index As Variant) As Variant
Dim i As Long, j As Long, v() As String
If VarType(index) = vbArray + vbVariant Then
    j = UBound(index)
    ReDim v(1 To j)
    For i = 1 To j
        v(i) = ThisWorkbook.Worksheets(i).Name
    Next i
    SheetFromIndex = IIf(Application.Caller.Rows.Count > 1, Application.Transpose(v), v)
Else
    SheetFromIndex = ThisWorkbook.Worksheets(index).Name
End If
End Function

Book2
	A	B	C
1	Sheet1	Sheet1	Sheet2
2	Sheet2	Sheet2
3	Sheet3	Sheet3
Sheet1

Cell Formulas
Range	Formula
A1:A3	A1	=SheetFromIndex(ROW(A1:A3))
B1:B3	B1	=SheetFromIndex(ROW())
C1	C1	=SheetFromIndex(2)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Jaafar Tribak]

Thanks FormR,

That will sure work but the reason I wanted this in the first place was to avoid having to iterate the worksheets in the vba UDF .

I am looking at solving this with the UDF as is in my initial post (ie: without looping) and entered in the worksheet range as an array formula.

Regards.

 

[FormR]

Hi Jaafar, I'm not sure you can to be honest, but maybe someone else knows for sure.

 

[Jaafar Tribak]

Hi Jaafar, I'm not sure you can to be honest, but maybe someone else knows for sure.

Ok FormR- Thanks very much for your help.

Thought this was going to be easier.

 

[FormR]

the reason I wanted this in the first place was to avoid having to iterate the worksheets in the vba UDF .

Hi Jaafar, just for clarity, in these examples, the UDF is not iterating though the worksheets, rather it's iterating through the cells the function has been entered in, each time processing the relative row number in the index parameter.

 

[Jon von der Heyden]

Hi Jaafar

Do you know of a cunning way to convert a collection to an array without looping through each item in the collection?

 

[RoryA]

FWIW, for this particular purpose, the XLM function GET.WORKBOOK(1) would be simpler, as it returns an array of sheet names.