VBA - Assign a value to multiple classes at once (

Question

Solution: Just put brackets around Value in the CallByName statement to force evaluation of it. Credit goes to Rory. Cheers!

I'm making a general function to set all the values of one variable in all my classes to a given value. It looks something like:

Sub MobClassSetAll(TargetData As String, Value As Variant)
    For each MobClass in MobCollection
        CallByName MobClass, TargetData, vbLet, Value
    Next MobClass
End Sub

Where MobCollection is a collection of all my similar Mob classes.

The problem is CallByName. Its fourth argument (Args) throws a type mismatch when Value is initialized as a Variant. I've tested this rigorously and even made a post about it here, but I have yet to get an answer.

So my new question is:

How can I change the value of the same variable in multiple similar classes, where the variable to be changed and the new value are passed to a function (without using CallByName)?

Edit: Would overloading the function be a possible solution to this? Just learned about it in class for C++ today and I feel like its possible.

Answer 1

EDITED, to reduce needed helper classes to only one that holds all "main" class shared properties

Since you're already working with classes, you should go on with such a powerful feature

My understanding is that you want to change some properties for ALL objects of the same class, then the best solution is have to properties as Classes themselves, which will allow you to change them in one shot

Of course, as almost always with Classes, the preparatory work is a bit longer but the reward is worth the effort

So

• Shared Property classes

  Set down a Class to hold all the properties to be shared between ALL Mob Class instances

  let's name it after MobShareds and its code would simply be:

  Public prop1 As Variant ' rename "prop1" with whatever name you need
  Public prop2 As Variant '  ""
  Public prop3 As Variant '  ""
  Public prop4 As Variant '  ""
  

• Mob Class

  have your Mob class exploit that shared properties object, so its code would be

  Public props As MobShareds
  

• Now in your main code you have to

  • instantiate one object to hold shared properties

    Dim MobClassShareds As New MobShareds
    

  • set each new Mob instance "shared properties" property to the same object

    MobCollection.Add New Mob
    Set MobCollection(MobCollection.Count).props = MobClassShareds ' set currently added 'Mob' object "shared" properties to the object that holds them
    
    MobCollection.Add New Mob
    Set MobCollection(MobCollection.Count).props = MobClassShareds ' set currently added 'Mob' object "shared" properties to the object that holds them
    
    MobCollection.Add New Mob
    Set MobCollection(MobCollection.Count).props = MobClassShareds ' set currently added 'Mob' object "shared" properties to the object that holds them
    

  • now you're ready to change any wanted shared property across ALL Mob objects with one single statement

    MobClassShareds.prop1 = "a" ' this will set ALL 'Mob' objects property 1 value to "a"
    
    MobClassShared2.Value = 1 ' this will set ALL 'Mob' objects property 2 value to 1
    

A working code to test what above is the following:

• Mob class

  Public props As MobShareds
  

• MobShareds class

  Public prop1 As Variant
  Public prop2 As Variant
  Public prop3 As Variant
  Public prop4 As Variant
  

• Main code

  Option Explicit
  
  Sub main()
      Dim MobClass As Mob
      Dim MobCollection As New Collection
  
      '-------------------
      ' declare and instantiate a variable to hold Mob class shared properties
      Dim MobClassShareds As MobShareds
      Set MobClassShareds = New MobShareds
  
  
      '---------------------
      ' fill your collection of 'Mob' objects and set their "shared properties" property to the same 'MobClassShareds' object
      MobCollection.Add New Mob
      Set MobCollection(MobCollection.Count).props = MobClassShareds ' set currently added 'Mob' object "shared" properties to the object that holds them
  
      MobCollection.Add New Mob
      Set MobCollection(MobCollection.Count).props = MobClassShareds ' set currently added 'Mob' object "shared" properties to the object that holds them
  
      MobCollection.Add New Mob
      Set MobCollection(MobCollection.Count).props = MobClassShareds ' set currently added 'Mob' object "shared" properties to the object that holds them
  
  
      '---------
      ' set shared properties for ALL classes to some string value
      MobClassShareds.prop1 = "a" ' this will set ALL 'Mob' objects property 1 value to "a"
      MobClassShareds.prop2 = "b" ' this will set ALL 'Mob' objects property 2 value to "b"
      MobClassShareds.prop3 = 1 ' this will set ALL 'Mob' objects property 3 value to 1
      MobClassShareds.prop4 = 2 ' this will set ALL 'Mob' objects property 4 value to 2
  
  
      'check what stated right above ist true...
      For Each MobClass In MobCollection
          Debug.Print MobClass.props.prop1
          Debug.Print MobClass.props.prop2
          Debug.Print MobClass.props.prop3
          Debug.Print MobClass.props.prop4
      Next MobClass
  
      '---------
      ' set shared properties for ALL classes to some numeric value
      MobClassShareds.prop1 = 1 ' this will set ALL 'Mob' objects property 1 value to 1
      MobClassShareds.prop2 = 2 ' this will set ALL 'Mob' objects property 2 value to 2
      MobClassShareds.prop3 = "a" ' this will set ALL 'Mob' objects property 3 value to "a"
      MobClassShareds.prop4 = "b" ' this will set ALL 'Mob' objects property 4 value to "b"
      '...
  
      'check what stated right above ist true...
      For Each MobClass In MobCollection
          Debug.Print MobClass.props.prop1
          Debug.Print MobClass.props.prop2
          Debug.Print MobClass.props.prop3
          Debug.Print MobClass.props.prop4
      Next MobClass
  
  End Sub