0

I am trying to make myself some systematic vba-codes, and have just learned about the existence of Class Modules: The way I understand it, class modules works as defining a set of dimensions(variables) to a object (i.e. "the class"), then it is possible to make several instances of this object/class, with unique info, always within the boundaries of the definitions set for the object/class (through the class module).

So, now I want to make a set of Objects with keyinfo to later then be used in different procedures/subs.

E.g. in my case I want to have some key info for my graphs: E.g. firstly, number of series in the chart which should not be altered in the different codes I am running (e.g. the first 3-5 series are boundaries that should never be filtered out, neither altered). This number is varying with the different charts...

I know I could write this in cells in different sheets etc. and refer to these specifically by Range(<celladress>), but I thought I could give it a try with the class modules too. - I guess there are more advantages of it as I learn more...

When a set of objects are made (i.e. the list of charts with their specifications) my thought is to call the different procedures as:

subname( Array("Chart_11","Chart_12"))

some code turning off/on series, changing apperance based on serie names etc. That code is supposed to use the different class-properties clsChrtKyVar.<chartname>.properties (e.g., .numOfStndSrs beeing one of these properties) as input

Could anybody help me with suggestions on how to make this work? Am I understanding class modules competely wrong? From the picture under, it is clear I am not using the system/syntax correctly (or that the list of objects should be placed in its own module, not in the class module itself), but if the philosphy behind is good, could anyone give some tips?

  • If the philosphy is bad, please also tell and explain!

Example with simple testing

I have already used class modules a bit like in the picture under. The advantage is that if I change the celladress of where I get my input for key-values in the sheet, I only need to change the celladress in the class module, and not all modules as I am only calling to each property in the Class module... Hopefully, that is the way it is intended to be used right?

Class module working as database

Also, the reason I want to use "Class modules" as a database in the way I described above. - If my intended used don't work, the option I rely on is rather having several arrays with 1) graph names, 2)-n) different keyinfo. I would then have to keep the correct number of input in both arrays at all times, and to have things in the exact same order. - Making it little robust (i.e. very fragile) towards including more graphs and removing, changing specific graphs etc.

Hallvard Skrede
  • 13
  • 5
  • Congratulations for not being afraid to give Class Modules a try. Regarding creating an object that stores multiple instances of your class, have you considered using a VBA dictionary? – DecimalTurn Jun 17 '23 at 15:12
  • Hmm, no, I didn't know if it until now. I guess I will have to check it out. But first I think I will store this data in another sheet for now, and just refer to that one. But maybe later, when I have more time, I will look into that. By a glance, it seems to be the thing I have been looking for. – Hallvard Skrede Jun 17 '23 at 16:24

1 Answers1

0

Elaborating on my comment, if you want to have a sort of "database" of class objects that contain important values, you could use a Collection or a Dictionary.

They both have their pros and cons. But Dictionaries are more flexible, so they work for more cases. You can find mutltiple tutorial online about how to use them like this one which is pretty complete.

As a side note, it looks like you only need a class to hold some properties and no method/procedure. In that case, you could also consider using a Type.

DecimalTurn
  • 3,243
  • 3
  • 16
  • 36