VBA:获取变量的名称 [英] VBA: Get name of variable

问题描述

是否有函数或属性来获取变量名?

类似

msgBox myVariable.name 或

msgBox nameOfVariable(myVariable)

当我用例如 myVariable =无论" ?Google只是给变量引用带来了问题...

解决方案

可能的Class方法如下(注释):

1. 添加课程模块

   在VBA IDE中

   • 单击插入"->类模块"

   • 单击查看"->属性窗口"

   • 输入(名称)属性文本框，然后输入变量"(或您喜欢的任何内容，但与以下步骤保持一致)

   • 在 Class 代码窗格中输入以下代码

      选项显式'声明将附加到此类的每个实例的字段公共名称为String'<-|这将存储您将要设置此类对象的变量的名称变体'<-||的公共价值这将存储与您将要设置此类对象的变量相关联的值声明一种方法"，将对象的值"写入以"name"字段命名的命名范围内Sub WriteRange(可选wb作为变体)'<-|您可以传递要利用其命名范围的工作簿的引用如果为IsMissing(wb)，则设置wb = ActiveWorkbook'<-||如果未传递任何工作簿引用，则假定当前活动的工作簿如果TypeName(wb)="Workbook"，则'<-检查是否传递了正确的工作簿引用)On Error Resume Next'<-防止未分配的命名范围抛出错误wb.Names(name).RefersToRange.value =值'<-|将当前实例的值"字段的值写入以当前实例的名称"字段命名的传递工作簿的命名范围内万一结束子 

2. 在您的代码中利用 Variable 类

   作为利用 Variable 类的三个变量的示例，例如，第一个变量为 String ，第一个变量为 Integer 第2个，第3个 Double 值，在任何模块代码窗格中，输入以下代码:

    选项显式子main()将myVariable1调为变量，将myVariable2调为变量，将myVariable3调为变量'<-|声明类型为"Variable"的变量:选择您想要的任何名称设置myVariable1 = CreateVariable("myVariable1"，这是一个字符串值")'<-将第一个变量设置为其名称(您必须使用与变量相同的名称！)和值(myVariable1将具有`String类型值)设置myVariable2 = CreateVariable("myVariable2"，10)'<-将第二个变量设置为其名称(必须使用与变量相同的名称！)和值(myVariable2将具有整数"类型值)设置myVariable3 = CreateVariable("myVariable3"，0.3)'<-将第3个变量及其名称(必须使用与变量相同的名称！)和值(myVariable3将具有"Double"类型值)进行设置类的利用`WriteRange`方法将对象值写入相应的命名范围:您必须在当前活动的工作簿中设置适当的命名范围myVariable1.WriteRange'<-||这将在当前活动的工作簿的命名范围"myVariable1"中写入字符串"this is a string value"myVariable2.WriteRange'<-|这将在当前活动的工作簿的命名范围"myVariable2"中写入数字"10"myVariable3.WriteRange'<-||这将在当前活动的工作簿的命名范围"myVariable3"中写入数字"0.3"结束子helper函数，用于创建"Variable"类的对象并初始化其"name"和"value"属性函数CreateVariable(名称作为字符串，值作为变量)作为变量'<-|该函数返回`Variable`类的对象设置CreateVariable =新变量'<-||这将创建一个`Variable`类的新对象使用CreateVariable'<-|引用新创建的对象并....name =名称'<-|...设置其`name`属性....value =值'<-|...及其值"属性结束于结束功能 

is there a function or a property to get a variables name?

Something like

msgBox myVariable.name or

msgBox nameOfVariable(myVariable)

that returns "myVariable" when I defined it with e.g. myVariable = "whatever"? Google just brings questions to variable referencing...

解决方案

a possible Class approach would be the following (commented):

1. Add Class Module

   in VBA IDE

   • click Insert-> Class Module

   • click View -> Property Window

   • enter (Name) property textbox and type in "Variable" (or whatever you may prefer but be consistent with in following steps)

   • enter the following code in the Class code pane

     Option Explicit
     
     'declare the fields that will be attached to every instance of this class
     Public name As String '<--| this will store the name of the variable to which you'll set the object of this class
     Public value As Variant '<--| this will store the value associated with the variable to which you'll set the object of this class
     
     'declare a `method` to write the `value` of the object in the named range named after the `name` field 
     Sub WriteRange(Optional wb As Variant) '<--| you can pass the reference of the workbook whose named ranges you want to exploit
        If IsMissing(wb) Then Set wb = ActiveWorkbook '<--| if no workbook reference is passed then the currently active workbook is assumed
        If TypeName(wb) = "Workbook" Then '<-- check for a proper workbook reference being passed)
             On Error Resume Next '<-- to prevent unassigned named range throw an error
             wb.Names(name).RefersToRange.value = value '<--| write the  value of the `value` filed of the current instance in the named range of the passed workbook named after the `name` filed of the current instance
         End If
     End Sub 
     

2. Exploit Variable Class in your code

   as an example of exploiting the Variable class for three variables with, say, a String value for the 1st, an Integer value for the 2nd and a Double value for the 3rd, in any module code pane enter the following code:

       Option Explicit
   
       Sub main()
           Dim myVariable1 As Variable, myVariable2 As Variable, myVariable3 As Variable '<--| declare your variables of type "Variable": choose whatever name you may want
   
           Set myVariable1 = CreateVariable("myVariable1", "this is a string value") '<-- set your 1st variable with its name (you must use the same name as the variable!) and value (myVariable1 will have a `String`type value)
           Set myVariable2 = CreateVariable("myVariable2", 10) '<-- set your 2nd variable with its name (you must use the same name as the variable!) and value (myVariable2 will have a `Integer`type value)
           Set myVariable3 = CreateVariable("myVariable3", 0.3)'<-- set your 3rd variable with its name (you must use the same name as the variable!) and value (myVariable3 will have a `Double` type value)
   
           'exploit `WriteRange` method of your Class to write the object value into the corresponding named range: you must have set proper named ranges in your currently active workbook
           myVariable1.WriteRange '<--| this will write the string "this is a string value" in named range "myVariable1" of your currently active workbook
           myVariable2.WriteRange '<--| this will write the number '10' in named range "myVariable2" of your currently active workbook
           myVariable3.WriteRange '<--| this will write the number '0.3' in named range "myVariable3" of your currently active workbook 
       End Sub
   
       ' helper Function to create an object of `Variable` class and initialize its `name` and `value` properties 
       Function CreateVariable(name As String, value As Variant) As Variable '<--| the function returns an object of `Variable` class
           Set CreateVariable = New Variable '<--| this creates a new object of `Variable` class
           With CreateVariable '<--| refer to the newly created object and ...
               .name = name '<--| ... set its `name` property ...
               .value = value '<--| ... and its `value` property
           End With
       End Function
   

这篇关于VBA:获取变量的名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！