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.

 

[Jon von der Heyden]

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

That's cunning! I thought I'd give it a go, and perhaps try use with ExceuteExcel4Macro so to create an array-enabled function. Only in my case it returns a string with the name of the 1st sheet only, in spite of having 3 sheets (same behaviour when used in a name).

 

[RoryA]

It returns a 1D array so you'd have to add a TRANSPOSE function if you wanted it in a column.

 

[Jon von der Heyden]

That's cool, thanks Rory. So for Jaafar it could be:

Create a new name, in my case called Sheets
Refers to: =GET.WORKBOOK(1)

And then in A1:A3, as array:
=INDEX(sheets,0,ROW())

@RoryA : ExecuteExcel4MAcro("GET.WORKBOOK(1)") returns a String, not a String Array. Isn't that odd?
{Edit}: To elaborate, returning name of the 1st sheet, not a comma separated string of all sheets in the collection.

 

[RoryA]

As far as I know, ExecuteExcel4Macro has only ever been capable of returning single values, not arrays.

 

[Jon von der Heyden]

As far as I know, ExecuteExcel4Macro has only ever been capable of returning single values, not arrays.

Pah! Dead end then. ?‍

 

[Jaafar Tribak]

@RoryA
Thanks- that"s a cool way of getting the sheet names . I never knew about it.

@Jon von der Heyden
Same here, I thought ExecuteExcel4Macro("Get.Workbook(1)") would return a string array just like Get.Workbook(1) did via an excel Name.

I am just going to have to use another method.

Thanks everyone for answering.

 

[Rick Rothstein]

I think the underlying problem is that Sheets and Worksheets are collections and I think the only way to get at a collection's elements is individually.

 

[Jon von der Heyden]

HI Jaafar

You can return an array of the sheets if you Evaluate the name containing GET.WORKBOOK(1).

The issue is that if you are using a UDF, the UDF will not create the name for you (assuming it doesn't exist). You would need to either ensure that the name exists, or find a cunning way for the UDF to create it (using a time procedure trigger).

But assuming the name exists then this would work as an array formula (I have used a name called Sheets):

Public Function SheetFromIndex(index As Variant) As Variant
    SheetFromIndex = Application.index(ActiveSheet.Evaluate("=Sheets"), 1, index)
End Function

 

[Jaafar Tribak]

HI Jaafar

You can return an array of the sheets if you Evaluate the name containing GET.WORKBOOK(1).

The issue is that if you are using a UDF, the UDF will not create the name for you (assuming it doesn't exist). You would need to either ensure that the name exists, or find a cunning way for the UDF to create it (using a time procedure trigger).

But assuming the name exists then this would work as an array formula (I have used a name called Sheets):

Public Function SheetFromIndex(index As Variant) As Variant
    SheetFromIndex = Application.index(ActiveSheet.Evaluate("=Sheets"), 1, index)
End Function

Hi Jon,

Yes- I did think of using a windows timer (SetTimer API) inside the UDF for creating the name on the fly as follows :

Names.Add "SheetNames", "=REPLACE(GET.WORKBOOK(1),1,FIND(" & """]""" & ",GET.WORKBOOK(1))," & """""" & ")"

but this becomes unstable when the UDF must be applied to more than one cell.

Your function works fine assuming the name exists already... I'll probably go with that.

Thanks vey much for your help and interest Jon.

 

[YasserKhalil]

@
Jon von der Heyden

How can the UDF be used as I tried it but I got NAME error .. I am sure of the udf name on the worksheet..