Programmatically get list of all functions from Function Wizard dialog

vincehardwick

New Member
Joined
Feb 4, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I'm trying to work out a way to programmatically obtain a list of all the function names that appear in the Excel function wizard's 'Select a function' combobox when you select the 'All' category:

1628507888936.png


I need this so that instead of having to manually create and maintain a list of their names on a sheet (which is my only backup plan atm, it would just be onerous and prone to human error), it can be automatically kept up-to-date if/when Microsoft add new functions in later versions. This is in relation to a function I'm putting together to offset cell/range references in formulas, I need to be able to strip out all function names from a formula as one of the steps in reducing a formula to only its cell/range references.

I've found that you can launch the Function Wizard dialog from VBA - via either Range.FunctionWizard or Application.Dialogs(xlDialogFunctionWizard).Show - but the list of functions doesn't appear to be exposed via that method. I've also scoured through all the CommandBar objects and their Controls, and although I can find the Insert...Function button or the formula auditing items, again the list of all function names didn't show up.

Any ideas?

Vince
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,759
Office Version
  1. 365
Platform
  1. Windows
To the best of my knowledge, there is no way to do that & even if it was possible, there is no way of tracking whether new functions have been added.
 

vincehardwick

New Member
Joined
Feb 4, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
To the best of my knowledge, there is no way to do that & even if it was possible, there is no way of tracking whether new functions have been added.
Thanks Fluff - I wouldn't need to know if the list had changed, only what function names are in it at any given moment. I'd be iterating over the list every time, not just when the list had changed compared to some previous time.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,759
Office Version
  1. 365
Platform
  1. Windows
You're welcome, sorry I couldn't be of more help.
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,463
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

One way is by hooking the xlDialogFunctionWizard and then using Microsoft Active Accessibility to read all the entries in the list of functions.

Another way is probably by using the ITypeInfo interface and invoking the GetFuncDesc method.

See if you can adapt the following code: Get list of all Properties and Methods for an object with VBA code alone
Pass the WorksheetFunction Object and look for VbMethod only.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,031
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Just bear in mind that not all worksheet functions are actually exposed on that object.
 

vincehardwick

New Member
Joined
Feb 4, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
One way is by hooking the xlDialogFunctionWizard and then using Microsoft Active Accessibility to read all the entries in the list of functions.

Another way is probably by using the ITypeInfo interface and invoking the GetFuncDesc method.

See if you can adapt the following code: Get list of all Properties and Methods for an object with VBA code alone
Pass the WorksheetFunction Object and look for VbMethod only.
Thanks Jafarr, I might look into that first method you suggested - the second method will only give me the worksheet functions that are accessible via VBA, which isn't all of them - see this article - only those which don't have an equivalent VBA function.
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,463
Office Version
  1. 2016
Platform
  1. Windows
True. The worksheetfunction object doesn't expose all worksheet function, unfortunately.

AFAIK, the only remaining possible workaround is to use the MSAA hack.
I will give it a try and post back if any results.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,491
Messages
5,764,675
Members
425,229
Latest member
Rashid mahmood

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top