Use Variable to name Class Object (Excel VBA)

Question

I'm writing a script to record the various characteristics associated with a ream of material (supplier number, material certificates, etc). A nice way to do this I though would be to create a class object, named by the ream's serial number, with all these associated parameters.

The serial number is a string housed in an array. I'm struggling to find out whether there is some data type that can be assigned the serial number value and used to name the class object:

Dim Obj As String
Obj = Arr_IncomingCann(i, 2)
Dim Obj As New cCannister

I see why this can't work (I'm simply redimensioning "Obj"), but can someone give some advice as to whether what I'm attempting is possible?

Variable names in VBA are static (means they cannot be changed dynamically). You might want to have a look at Dictionaries see Excel VBA Dictionary – A Complete Guide. You can add a new item to the dictionary then and assign a new instance of your class to that item. — Pᴇʜ
– Pᴇʜ, Commented Feb 10, 2020 at 10:07

Pᴇʜ · Accepted Answer · 2020-02-10 10:13:07Z

3

Variable names in VBA are static (means they cannot be changed dynamically). You might want to have a look at Dictionaries see Excel VBA Dictionary – A Complete Guide.

You can add a new item to the dictionary then and assign a new instance of your class to that item.

Example:

Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")

Dim NewClass As New clsMyClass
NewClass.Test = "ABC" 'do something with your class object

dict.Add Key:="your serial", Item:=NewClass
Set NewClass = Nothing 'you can destroy the variable the class object is now in the dictionary.

Then you can access it with

Debug.Print dict("your serial").Test 'will return ABC from your class object.

answered Feb 10, 2020 at 10:13

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Use Variable to name Class Object (Excel VBA)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related