Public variable question

[RhinoNeil]

I have a project with several modules. I have created several Public variable to track items across all the modules.
When I re-run the macro, the public variables keeps their values from the last run and increases rather than starting from zero as they should.
I now set all the public variables to zero at the start of the macro after declaring them but what I want to know is, as my list of public variables grows, is there a simpler way to rest all public variables at the end of the macro?
Thanks for any help.

 

[Richard Schollar]

You could include all the variables as properties of a custom class and just instantiate a new instance of the class at each macro run (so you will start off with 'clean' variables):

'class module CMyVariables
 
Private myVar1 As Long
Private myVar2 As Long
Private myVar3 As Long
 
'standard module
 
Public myVars As CMyVariables
 
Sub SomeSub()
 
Set myVars = New CMyVariables
 
myVars.myVar1 = 100
 
myVars.myVar2 = 200
 
'etc etc

 

[RhinoNeil]

Thats a nice idea. Would also put my Public variables in one easily accessable place.

Neat

Thanks!!!

 

[RhinoNeil]

Just trasnferred all my Public variables to a class module but when I run the macro it comes up with an error that arrays are not allowed to be declared as public in a class module.

A lot of my public variables are arrays holding a lot of data (1000+ elements) so I cannot set up variables for each element.

Any ideas how I get round or what am I doing wrong?

 

[RhinoNeil]

Looking at the Help with the error message it says -

Although a procedure can't return an array, it can return a Variant that contains an array. To simulate a Public array in a class module, use a set of Property procedures that accept and return a Variant containing an array.

I have no idea how to set a Property procedure.

Can anybody help me do it?

 

[Richard Schollar]

Apologies, I have been busy. See below:

'Class module cMyArray
Private myVarArray As Variant

Property Let myArray(v As Variant)
    myVarArray = v
End Property
Property Get myArray()
    myArray = myVarArray
End Property
 
 
'standard module:
 
Option Explicit
Dim myclass As cMyArray

Sub test()
Dim v As Variant
Set myclass = New cMyArray
v = Range("A1:B10").Value
myclass.myArray = v
MsgBox myclass.myArray(1, 1)
End Sub