通过反射解析VBA模块函数参数 [英] Parsing VBA Module function parameters via reflection

问题描述

在 Excel 中，我有一个名为 MyModule 的 VBA 模块，它具有这样的功能

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

在运行时，我知道我有一个名为 MyModule 的模块，我知道我有一个名为 MyFunction 的函数，但我想动态检查参数名称 Param1 和 Param2

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

在我的世界 (C#) 中，这称为反射.我可以在这里使用类似的概念来获取参数名称还是我对 VBA 期望过高?

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?

推荐答案

在这个 CodeProject 中有一个很好的分析 http://www.codeproject.com/Articles/164036/Reflection-in-VBA-a-CreateObject-function-for-VBA

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

提取:

VBA 不支持反射，这是一个 OOP 概念，它有很多方面，包括在不知道的情况下实例化类的能力它在设计时的类型.反射提供了极大的灵活性VBA 令人痛苦地缺席(至少对于我们这些喜欢使用VBA 用于超出通常预期的范围).

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:

示例

例如，如果我想创建一个 Excel Application 对象的实例而不必在代码中显式声明类型(使用 Set xlApp = New Excel.Application)，我可以使用以下反射级对象创建来实现代码:

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

还有:

您可以使用 assylias 方法来查询您自己的代码(使用正则表达式或其他一些解析来挑选出部分您感兴趣的代码):

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

这篇文章中有一些有趣的线索:遍历对象浏览器VBA

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

这篇关于通过反射解析VBA模块函数参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！