vbareflection

Parsing VBA Module function parameters via reflection

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?

Solution

  • 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