Assigning a value to variable(nameOfVariable)

Question

Is it possible to use something like variable(nameOfVariable) = myValue

What I'm doing :

On of my XLA Add-In macro uses a lot of module-level variables ; many being Public and 256 being Const. Part of the Public ones are some pseudo-Const being udpated once in a while from the content of a (network-accessible) config.init file.

I thus need to assign value to a bunch of Public variables, at the start of the macro or when the user launches some specific procedures.

The content of the config.init file is very simple :

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

I'm currently setting theses variables using this Initialisation procedure :

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

But this means I need to list every nameOfVariable in the Select Case statement of that procedure, even if the instruction assigning the value is stricly identical (= Split(TextLine, ",")(1)).

I'd like to replace the Select Case statement with something like :

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

so that I don't ever need to modify anything if I choose to add a new variable in the init file (new one or one currently declared as a Const).

Is it possible in VBA ?

A dictionary is one option: https://stackoverflow.com/questions/915317/does-vba-have-dictionary-structure although assigning individually to say members of a type or class allows some type safety and prevents future spelling errors. — Alex K., Mar 05 '18 at 14:02
to dimension global variables is simple, please see guidance below:https://stackoverflow.com/questions/2722146/how-do-i-declare-a-global-variable-in-vba — jimmy8ball, Mar 05 '18 at 14:03
@jimmy8ball. I don't see how it's help and if you're being sarcastic. I'm currently assigning some values I get from a config.init file to global variables. You can thus assume I know how to declare a variable in VBA... — Tibo, Mar 05 '18 at 14:44

score 2 · Answer 1 · answered Mar 05 '18 at 14:39

Maybe the following approach fits your needs. Rewrite your file to an INI-file like that

Then create a class module name it CApp and add the following code

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

And in a standard module add the following code

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

Thank for your answer. It however seems quite complex for what I’m trying to achieve. I'm trying to simplify the code by not having to list every possible nameOfVariable in the `Select Case` statement. ---- With your solution, I would have to list every variables multiple times (in the `Property Get`, `Property Let`, `lLen =...`, `Me.gVAR1 =` instructions), add multiple Lib references, and VBA would have to access the config.init file dozens of times (since I'm setting dozens of variables). ---- How would that be more efficient (code wise and performance wise) compared to my current code? — Tibo, Mar 05 '18 at 15:02
Yes, you have to write the class in such a way that everything is included. But in this way the class is also prepared to write the INI file as well. Moreover you have everything in one place and if you need to add another variable it is a matter of minutes. — Storax, Mar 05 '18 at 15:10
PS Why do you need the values for your global variables in an extra file? Why can't you initialise them in the code or define them as constants? — Storax, Mar 05 '18 at 15:16
I'm using more than 200 variables, many of them being `Const`. But I want some to be udpated each time a user start the macro or launch some procedures, so that I can modify parameters (address of file on the network, technical properties, user rights...) on the fly form my PC, without re-destributing the `XLAM Add-in` to every user. ---- For example, I can disable specific modules/procedures or change ressources' network address at any time, which would be impossible with `Const` variables hardcoded inside the `Add-In`. — Tibo, Mar 05 '18 at 15:26
Ok, then it's certainly a good approach to have a kind on INI file. Buf if you want to maintain these numbers of variables in a way you can manage it I would suggest to put it in a class as I did. I do not really see performance issues reading the file with an API function. It is some work but IMHO it will pay off in the long run. You can also have different sections in the INI file to group you paramteres if needed. It's up to you which way you want to go. — Storax, Mar 05 '18 at 15:33
For my specific question, which aims at **not having to modify the initialisation procedure at all** when adding a new variable in the .init file, I'll probably not implement your solution (since it would mean doubling the number of variables and tripling the number of lines to add for each new variable). But many thanks for your answers ; you're raising interesting points (writting the INI file...) and I do appreciate your detailled post. — Tibo, Mar 05 '18 at 15:50
Right now I've got another idea using the registry and and INI file but not quite sure if it's any better — Storax, Mar 05 '18 at 15:51

Kostas K. · Accepted Answer · 2018-03-06T07:28:02.460

Consider a Dictionary object (as per @Alex K. comment) where the keys will be the variable names.

A simple Settings class example.

The VB_PredeclaredId set to True to act as a global default instance and the Item has been set as the default member.

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

Adding items to the dictionary. This could be done when reading the config.init file.

Sub Add()

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

    PrintSettings
End Sub

In your case would be something like this:

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

Retrieving items by providing the name:

Settings("Name2")

To test the output:

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

Output:

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

Assigning a value to variable(nameOfVariable)

What I'm doing :

2 Answers2

Linked