给变量(nameOfVariable)赋值

Question

给变量(nameOfVariable)赋值

vbaexcelvariables

3

是否可以使用类似变量名(nameOfVariable) = myValue的方式？

我正在做的事情：

我的一个XLA Add-In宏使用了很多模块级变量；其中许多是Public，256个是Const。其中一些伪常量是不时从（可通过网络访问的）config.init文件的内容更新的。

因此，我需要在宏启动或用户启动某些特定过程时将值分配给一组公共变量。

config.init 文件的内容非常简单：

nameOfVariable1,value1
nameOfVariable2,value2
nameOfVariable3,value3
...

目前我是使用以下初始化过程来设置这些变量:

Do While Not EOF(1)              ' Loop until end of config.init

    Line Input #fnum, TextLine   ' Read line into variable Textline
    myNameOfVariable = Split(TextLine, ",")(0)

    Select Case myNameOfVariable
    Case "nameOfVariable1"
        nameOfVariable1 = Split(TextLine, ",")(1)
    Case "nameOfVariable2"
        nameOfVariable2 = Split(TextLine, ",")(1)
    Case "nameOfVariable3"
        nameOfVariable3 = Split(TextLine, ",")(1)
    ...

Loop

但这意味着我需要在该过程的Select Case语句中列出每个变量名，即使分配值的指令完全相同(= Split(TextLine, ",")(1))。

我想用类似下面的内容替换Select Case语句:

Do While Not EOF(1)              ' Loop until end of config.init

    Line Input #fnum, TextLine   ' Read line into variable.
    nameOfVariable = Split(TextLine, ",")(0)

    If thisvariableexist(nameOfVariable) then
        variable(nameOfVariable) = Split(TextLine, ",")(1)
    End if

Loop

为了使我在初始化文件中添加新变量（新的或当前声明为 Const 的变量）时，不必修改任何内容，这是否可能在 VBA 中实现？

- Tibo

3

一个选项是使用字典：https://dev59.com/b3NA5IYBdhLWcg3wjOre。但是，逐个分配给类型或类的成员可以提供一些类型安全性并防止将来出现拼写错误。 - Alex K.

将变量声明为全局变量很简单，请参考以下指南：https://dev59.com/cHE85IYBdhLWcg3wikO- - jimmy8ball

@jimmy8ball。我不明白这有什么帮助，如果你是在讽刺我。我目前正在将一些从config.init文件中获取的值分配给全局变量。因此，您可以假设我知道如何在VBA中声明变量... - Tibo

2个回答

1

考虑一个字典对象（根据@Alex K.的评论），其中键将是变量名。

一个简单的设置类示例。 VB_PredeclaredId设置为True，以充当全局默认实例，并将Item设置为默认成员。

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "Settings"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Private m_settings As Object

Public Property Get Item(ByVal Name As String) As String
Attribute Item.VB_UserMemId = 0
    Item = m_settings(Name)
End Property
Public Property Let Item(ByVal Name As String, ByVal Value As String)
    m_settings(Name) = Value
End Property

'For testing - can omit
Public Function Names() As Variant
    Names = m_settings.Keys
End Function

Private Sub Class_Initialize()
    Set m_settings = CreateObject("Scripting.Dictionary")
End Sub

向字典中添加条目。这可以在读取config.init文件时完成。

Sub Add()

    Settings("Name1") = "Value1"
    Settings("Name2") = "Value2"
    Settings("Name3") = "Value3"

    PrintSettings
End Sub

在您的情况下，可能会像这样：

。

Dim Values As Variant

Do While Not EOF(1)              ' Loop until end of config.init

    Line Input #fnum, TextLine   ' Read line into variable.
    Values = Split(TextLine, ",")

    Settings(Values(0)) = Values(1)
Loop

通过提供名称检索项目：

Settings("Name2")

测试输出：

Sub PrintSettings()
    Dim n As Variant
    For Each n In Settings.Names()
        Debug.Print "Name: " & n & ", Value: " & Settings(n)
    Next
End Sub

输出：

'Name: Name1, Value: Value1
'Name: Name2, Value: Value2
'Name: Name3, Value: Value3

- Kostas K.

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Storax · Accepted Answer

也许以下方法适合您的需求。将您的文件重写为类似于INI文件的格式，如下所示：

然后创建一个名为CApp的类模块，并添加以下代码：

Option Explicit

Private Declare Function GetPrivateProfileString Lib "kernel32" Alias "GetPrivateProfileStringA" _
                                                 (ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpDefault As String, _
                                                  ByVal lpReturnedString As String, ByVal nSize As Long, ByVal lpFileName As String) As Long

Private Declare Function WritePrivateProfileString Lib "kernel32" Alias "WritePrivateProfileStringA" _
                                                   (ByVal lpApplicationName As String, ByVal lpKeyName As Any, _
                                                    ByVal lpString As Any, ByVal lpFileName As String) As Long

Const gsINIFILENAME As String = ".. full file name of config.ini ..."


Private m_VAR1 As String
Private m_VAR2 As String
Private m_VAR3 As String
Private m_VAR4 As String

Private Const msSEC As String = "Variables"
Private Const msVAR1 As String = "gVAR1"
Private Const msVAR2 As String = "gVAR2"
Private Const msVAR3 As String = "gVAR3"
Private Const msVAR4 As String = "gVAR4"

Public Property Get gVAR1() As String
    gVAR1 = m_VAR1
End Property
Public Property Let gVAR1(ByVal sVAR1 As String)
    m_VAR1 = sVAR1
End Property
Public Property Get gVAR2() As String
    gVAR2 = m_VAR2
End Property
Public Property Let gVAR2(ByVal sVAR2 As String)
    m_VAR2 = sVAR2
End Property

Public Property Get gVAR3() As String
    gVAR3 = m_VAR3
End Property
Public Property Let gVAR3(ByVal sVAR3 As String)
    m_VAR3 = sVAR3
End Property

Public Property Get gVAR4() As String
    gVAR4 = m_VAR4
End Property
Public Property Let gVAR4(ByVal sVAR4 As String)
    m_VAR4 = sVAR4
End Property


Private Sub Class_Initialize()

Dim sReturn As String * 255
Dim lLen As Long

    lLen = GetPrivateProfileString(msSEC, msVAR1, "", sReturn, 255, gsINIFILENAME)
    Me.gVAR1 = Left(sReturn, lLen)

    lLen = GetPrivateProfileString(msSEC, msVAR2, "", sReturn, 255, gsINIFILENAME)
    Me.gVAR2 = Left$(sReturn, lLen)

    lLen = GetPrivateProfileString(msSEC, msVAR3, "", sReturn, 255, gsINIFILENAME)
    Me.gVAR3 = Left$(sReturn, lLen)

    lLen = GetPrivateProfileString(msSEC, msVAR4, "", sReturn, 255, gsINIFILENAME)
    Me.gVAR4 = Left$(sReturn, lLen)


End Sub

在标准模块中添加以下代码。

Option Explicit

Public gApp As CApp

Sub Auto_Open()

'Read ini file

    Set gApp = New CApp
    With gApp
        Debug.Print .gVAR1, .gVAR2, .gVAR3, .gVAR4
    End With  

End Sub