Parsing VBA Module function parameters via reflection

Question

In Excel, I have a VBA module called MyModule which has a function as such

    Public Function MyFunction(Param1 As Range, Param2 As Range) As String
            ' some implementation
    End Function

At runtime, I know that I have a module called MyModule and I know I have a function called MyFunction but I want to inspect dynamically the parameter names Param1 and Param2

In my world (C#), this is called reflection. Can I use similar concepts here to get the parameter name or am I expecting too much from VBA?

Throes

yourWorkbook.VBProject.VBComponents("MyModule").CodeModule should give you access to what you need. – Haemophilic

@Haemophilic this is giving me the whole source code, which is not great, but could help solving my problem. Are you aware of one that can give me the function as an object (similar to C# or JavaScript)? – Throes

I only posted it as a comment because it lacks details, but thinking it would help you get started. I'm sure somebody will post a better answer later. – Haemophilic

Answer 1

There is a good analysis at this CodeProject http://www.codeproject.com/Articles/164036/Reflection-in-VBA-a-CreateObject-function-for-VBA

Extract:

VBA does not support reflection, an OOP concept which has many facets, including the ability to instantiate a class without knowing its type at design time. Reflection offers great flexibility that is agonizingly absent from VBA (at least for those of us who like to use VBA for more than it is typically intended).

There are some reflection-like things possible:

Example

For example, if I wanted to create an instance of an Excel Application object without having to declare the type explicitly in code (using Set xlApp = New Excel.Application), I can do so using the following reflection-level object creation code:

Dim xlApp As Excel.Application 
Set xlApp = CreateObject(,"Excel.Application") 

This is the equivalent of the type specific statement

Set xlApp = New Excel.Application

Also:

You can use assylias approach to interrogate your own code (using regexp or some other parsing to pick out the parts of your code you are interested in):

yourWorkbook.VBProject.VBComponents("MyModule").CodeModule

There are some interesting leads in this post: Iterating through the Object Browser in VBA

Montreal

Is it also possible to get private members (like properties) of a class module instance somehow? – Horrified

Answer 2

Lol, pretty late to this one. This might be useful to someone...

Part of what you want might be addressed with CallByName

I've been coding VBA for many years without knowing about that one!

Bangor