Excel VBA Dim multiple variables based on a list

[DutchKevin]

Hello,
For my code I face a problem that should hopefully be solvable in a better way than what i now have.
Suppose I have a list of approx 12 column names (Drinks, Transport, Country etc) . Let's assume the word Name is the unique identifier here.
From that list I need to Dim the following variables
NameCol as Integer
NameRange as Range
NameMessage as String
NameCounter as Long
NameErrorCounter as Long

This example alone has 5 x 12 = 60 Dim's
My actual code is a bit more elaborate than this example. It quickly becomes quite big.

Is there a way I can loop through my list of names and that a subcode does 5 x Dim with each name?
And if a new Name is added to my list the whole code would simply accept it and do what is intended.
Does it declare my required variables? Can I then use these variables?

e.g. DrinksCol, DrinksRange, DrinksMessage, DrinksCounter, DrinksErrorCounter, TransportCol, TransportRange, TransportMessage etc etc

Thanks for thinking with me in this one

 

[RobP]

Hi, what about Dimensioning an Array of 5*12 in one line ? Then you can store all your variables inside it.

Quite a good article here for you : Excel VBA Array - The Complete Guide - Excel Macro Mastery

Hope it helps
Rob

 

[NateSC]

As RobP mentioned, an Array could be useful if you can keep the locations straight.

Another option would be to establish a Class variable. It has been a while since I used them, but you would setup a class module:

'Inside Class Modules - I renamed it as myVar
Public myCol As Integer
Public myRange As Range
Public myMessage As String
Public myCounter As Long
Public myErrorCounter As Long

Then you can declare and set your variables up in another module:

Sub testMyClass()

Dim Name As myVar 'or whatever you named the Class
Dim Drinks As myVar

    Name.myCol = 1
    Name.myCounter = 5
    Name.myMessage = "Name Test"
    
    Drinks.myCol = 4
    Drinks.myCounter = 8
    Drinks.myMessage = "Drink Test"

End Sub

If all the variable structures are similar, it can help simplify situations like yours without having to remember exactly where in an Array you put the data you want. You just directly reference the variable and the container, i.e. "Name.mycol". If you go this route, try some quick searches for "VBA Class Module".

You can also export that Class and import into another macro if you want to use it in another workbook/macro.

 

[DutchKevin]

thanks for your comments and feedback.
I'll study it and see how it goes.

 

[DutchKevin]

Thanks once more!
I've tested both and decided to continue my work with VBA Dictionary with Class Module etc.
Interesting stuff, learning a lot recently
Issue closed