Can I inspect an Excel function signature programmatically in VBA?
excel, vba
Solution
You might consider
- using `Application.Caller` to get a reference to the cell containing the formula, then use that cell's `.Formula` property to get the formula as text.
- You can then parse the formula to get your parameters as text.
I did this when I wanted to pass a 3D range as a parameter to a UDF (so I could make a suite of functions like `COUNTIF` and `SUMIF` that would work on a 3D range like `Sheet1:Sheet99!$A$1:$A$1000`). I found that the UDF would be triggered when a value in the 3D range was changed--but the UDF would have a runtime error as soon as I needed to do anything with the Variant that received the 3D range in the UDF. My workaround was as discussed in the first paragraph--get the formula and parse it to get the 3D range as text.
I tried inserting the code as a block, but was stymied by the formatting requirements. Here is the workbook that uses it on my SkyDrive
Problem
Rather than access all the arguments explicitly by name, is there a way to get a list of function arguments programmatically from within the function? So for this function signature: ``` Function doSomething(Arg1 as String, Arg2 as Range, Optional Arg3 as String): ``` is there, ideally, an object that contains argument names and their metadata (type, optional, default value, etc.)? E.g., the code `Me.Arguments` inside this function would produce a dictionary something like this: ``` { "Arg1": { "Type": String, "Optional": False, "Default": Nothing }, "Arg2": { "Type": Range, "Optional": False, "Default": Nothing }, "Arg1": { "Type": String, "Optional": True, "Default": Nothing } } ``` Thanks.