Can I set the name of variable with textbox? VBA Excel

Question

Can I set the name of the variable with TextBox (Vba Excel)? I have to input new group of products on the way that I write the name of the group in TextBox and click command button. The code have to take the String from Textbox an set this string as name of new created array.

Answer 1

I just want to create a new variable (or array) at runtime.

It is believed that this is not possible. But it is. This is not documented anywhere (at least I have not read it anywhere).

Logic:

We will simply program The VBA Editor at runtime to create new array. You can read more about programming the VBA editor at Chip's website Programming The VBA Editor

Note

1. Ensure you have set a reference to Microsoft Visual Basic For Applications Extensibility 5.3
2. I am not doing any error handling. Feel free to incorporate that in your code.

In the below code we will create an array MyArray and dimension it to say a size of 5

Code

Option Explicit

Const vbext_ct_StdModule As Integer = 1

Sub Sample()
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
    Dim CodeMod As VBIDE.CodeModule
    Dim LineNum As Long

    Set VBProj = ThisWorkbook.VBProject
    Set VBComp = VBProj.VBComponents.Add(vbext_ct_StdModule)

    '~~> Create new module called MySpecialModule
    '~~> It it exists then you will get an error
    '~~> Either delete it and create new one or use error handling
    VBComp.Name = "MySpecialModule"

    Set CodeMod = VBComp.CodeModule

    With CodeMod
        LineNum = .CountOfLines + 1
        .InsertLines LineNum, "Public MyArray() As String"
        LineNum = LineNum + 1
        .InsertLines LineNum, "Public Sub InitArray()"
        LineNum = LineNum + 1
        .InsertLines LineNum, "    Redim MyArray (1 to 5)"
        LineNum = LineNum + 1
        .InsertLines LineNum, "End Sub"
    End With

    '~~> Initialize newly created Array
    initializeArray
End Sub

'~~> Run the procedure to initialize the newly created array
Sub initializeArray()
    InitArray

    Debug.Print UBound(MyArray)
End Sub

When you run the code, a new module is created and some code is injected in that module. That code is then again called at runtime. The below code doesn't exists when you run the code.

Public MyArray() As String

Public Sub InitArray()
    ReDim MyArray(1 To 5)
End Sub

Answer 2

In general it is not advisable to do that. You could however program your own classe with would then hold your GroupName and Data. I usually use collections as data containers opposed to arrays. A class MyClass would then look like this:

Option Explicit

Private Type TModel
    Data As Collection
    GroupName as string
End Type

Private this As TModel

Public Property Get GroupName() As Collection
    Set GroupName= this.GroupName
End Property
Public Property Let GroupName(ByVal Value As Collection)
    Set this.GroupName= Value
End Property
Public Property Get Data() As Collection
    Set Data = this.Data
End Property
Public Property Let Data(ByVal Value As Collection)
    Set this.Data = Value
End Property

Private Sub Class_Initialize()
Set this.Data = New Collection
End Sub

This looks pretty complicated but actually is very simple. You have a encapsulation using the Private Type TModel. There you store the data. Using Property Get and Property Let you can then get and let your property of the class. This may be used like this in a Module.

Sub TestMyClass()
Dim MClass as New MyClass
Dim GName as String
Dim Data as New Collection

Set Data=GetData()
GName=GetName()

Set MClass.Data=Data
Set MClass.GroupName=GName

Debug.Print MClass.GroupName ' Prints Groupname
End Sub

Like this every Variable of the type MyClass has a name and data in it.