Answering my own question from the previous post, here is a short UDF that will return a list of worksheet names, starting with any chosen sheet:

### Categories

### RSS Feed

### Search NewtonExcelBach

### Archives

### Top Posts

- Time in hours and minutes between two dates
- Solving Quadratic, Cubic, Quartic and higher order equations; examples
- Using LINEST for non-linear curve fitting
- Commenting a block of code in VBA
- Using Goal Seek on Multiple Cells
- Using Linest for non-linear curve fitting, examples, hints and warnings
- Contour plots with Excel and Matplotlib
- Weighted Least Squares Regression, using Excel, VBA, Alglib and Python
- Cubic Splines
- Downloads by category

### Recent Comments

Benata on Ana Vidović mindOnion on Drawing a Buckyball in Ex… dougaj4 on Drawing a Buckyball in Ex… mindOnion on Drawing a Buckyball in Ex… mindOnion on Drawing in Excel 7 – Cre… Veronica Davenport on Asymmetric Catenary Function py_UMom spreadsheet… on More on combined shear and ben… Skip on xlwings – dataframes and… dougaj4 on xlwings – dataframes and… More on combined she… on RC Design Functions 9.03; comp… skip on xlwings – dataframes and… skip on mpmath for Excel dougaj4 on mpmath for Excel skip on mpmath for Excel dougaj4 on Composite Beam Spreadsheet Upd…

There’s an alternative approach using defined names with some of those names calling XLM functions. See

http://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/e803e69927d7164e

LikeLike

Hi Doug,

I think I would have spat it in to one cell, with a “,” between names, what happens if I don’t know how many sheets I have?

Is it possible to get a UDF to write data to the windows clipboard? That way you could run the function then paste it onto the sheet – if you see what I mean?

Good stuff

Ross

LikeLike

Ross – you don’t like array functions?

The point of the sheetname function was to get all the sheetnames listed in a column, so writing them to a column array seemed like the obvious way to do it.

I’ve never tried writing to the clipboard before, but looking into it, it seems to have interesting possibilities.

Everything you need to know here: http://www.cpearson.com/excel/Clipboard.aspx

The main thing being you need a reference to the Microsoft Forms 2.0 Object Library to make the code below work (I guess you’ll have that anyway, but I’ve just got a new computer and it didn’t have the reference set):

Function WriteToClip(Textin As String) as string

Dim mText As DataObject

Set mText = New DataObject

mText.SetText Textin

mText.PutInClipboard

WriteToClip = Textin & ” copied to clipboard”

End Function

LikeLike