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.
2 Answers
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
- Ensure you have set a reference to
Microsoft Visual Basic For Applications Extensibility 5.3 - 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
2 Comments
FunThomas
Hehe, I like that. And then put an
On error resume next when checking if a variable already exists. And of course remove the Module at the end. Debugging is not possible (throws an error) - noone will be able to understand your code...
Siddharth Rout
Such questions inspire me to create blogs on my website. Created a blogpost on Create a new variable/object at runtime :D
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.
I just want to create a new variable (or array) at runtime.You can do that but it is not recommended. Using a Collection/Dictionary is recommended as shown Here