List all elements in a specific VBA object

[BartH]

Hi,

We are able to list for instance all sheets in a workbook:

Sub getSheetsOfWorkbook()
    Dim sh As Worksheet
    Dim i As Integer
    ActiveWorkbook.Sheets.Add
    For Each sh In ActiveWorkbook.Sheets
        i = i + 1
        ActiveSheet.Cells(i, 1) = sh.name
    Next
End Sub

How can I list all worksheetfunctions?

Sub getEachFunction()
    Dim f As WorksheetFunction
    Dim i As Integer
    ActiveWorkbook.Sheets.Add
    For Each f In Application.?
        i = i + 1
        ActiveSheet.Cells(i, 1) = f.name
    Next
End Sub

Grtz BartH

 

[6StringJazzer]

The comparison of Worksheets to WorksheetFunction doesn't work. Worksheets is a Collection. WorksheetFunction is not a Collection. It is a set of methods. There is no way to iterate through the names of all worksheet functions.

 

[BartH]

The comparison of Worksheets to WorksheetFunction doesn't work. Worksheets is a Collection. WorksheetFunction is not a Collection. It is a set of methods. There is no way to iterate through the names of all worksheet functions.

That is disappointing. Can't I somehow load them in a custom object?

 

[6StringJazzer]

What is your ultimate need? What are you trying to do?

Here is a list of the supported functions. You could create your own Collection, dictionary, array, or other data structure with this list. The best way to proceed all depends on your goal.

 

[BartH]

What is your ultimate need? What are you trying to do?

Here is a list of the supported functions. You could create your own Collection, dictionary, array, or other data structure with this list. The best way to proceed all depends on your goal.

Hi 6StringJazzer,
I know of that list, but it is not up to date with the latest added functions (f.i. xlookup).
There are more situations where I would like to be able to list all members of a class,
but in this case, I am working on a method to evaluate and report all steps in formulas that hold values.
So for instance

IF(
searchVal = "myComp",
'List'!$A$1:$B$233,
2,
0)
= 235.66

The red values I have added.

It would have been nice if I could list all available functions (and future new ones), perhaps even with their corresponding arguments.
As there are many formulas, I'd need to make some sort of array to determine which arguments I need to supply a value for.

Grtz BartH