Programmatically get list of all functions from Function Wizard dialog

vincehardwick

New Member
Joined
Feb 4, 2021
Messages
20
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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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.
 
Upvote 0
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.
 
Upvote 0
You're welcome, sorry I couldn't be of more help.
 
Upvote 0
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.
 
Upvote 0
Just bear in mind that not all worksheet functions are actually exposed on that object.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

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
Back
Top