While I can’t help with the VBA aspects, my question is around the resources that you want the VBA code to automatically assign. If you have a resource sheet with 25 resources and a task needs 10 resources, which 10 are to be assigned? Which leads me to believe you may be using this approach to look more at over all schedule duration rather than using it to assign very specific named resources. If this is the case, consider using the concept of a resource pool. In your resource sheet, you could define 1 resource with a Max Units value equal to 100% x the total number of resources. If you were to have 25 resources, you could set the Max Units to 2500%. Then, rather than trying to dynamically assign up to 25 resources to a given task with the appropriate number of hours on each, you could assign 1 resource with an appropriate Assignment Units value to simulate the number of resources. For example, if the task needed 10 resources, the Assignment Units could be set to 1000% to simulate 10 assigned resources. The resulting task duration will then reflect 10 resources doing the work.
The primary down side to this approach is that resource leveling will most likely not see resource leveling issues at a resource level, but at the pool level and will not delay tasks or assume resources are over allocated until they exceed the resource Max Units value.
I don’t know if this alternative approach may help, but if it does, it may be much easier to load the Assignment Units value into one assigned resource than to write VBA code that would need to dynamically add/subtract assigned resources.
Just a thought.