Classes

[tiredofit]

I read on these threads that it is advisable to pass variables via class modules rather than declare publically.

This is stored in Module1:

Option Explicit

Public myarray() As Variant

Sub NoClass()

myarray = Range("A1").CurrentRegion.Value
Dim myarrayrows As Long
myarrayrows = UBound(myarray, 1)
Dim newarray() As Variant
ReDim newarray(1 To myarrayrows, 1 To 1) As Variant
Dim Counter As Long

For Counter = 1 To myarrayrows
    newarray(aCounter1) = myarray(Counter, 1)
Next Counter

Range("E1").Resize(myarrayrows, 1).Value = newarray

End Sub

This is stored in a class module, class1:

Option Explicit

Private temparray As Variant

Property Get myarray() As Variant
myarray = temparray
End Property

Property Let myarray(passedarray As Variant)
temparray = passedarray
End Property

This is in module2:

Sub WithClass()

Dim myclass As Class1
Set myclass = New Class1
myclass.myarray = Range("A1").CurrentRegion.Value
Dim myarrayrows As Long
myarrayrows = UBound(myclass.myarray, 1)
Dim newarray() As Variant
ReDim newarray(1 To myarrayrows, 1 To 1) As Variant
Dim CounterAs Long

For Counter = 1 To myarrayrows
    newarray(Counter, 1) = myclass.myarray(Counter, 1)
Next Counter

Range("E1").Resize(myarrayrows, 1).Value = newarray
Set myclass = Nothing

End Sub

Admittedly these programs do literally nothing other than copy what's in column A and pastes it in column E.

To test, fill column A with around 50,000 entries and run Sub NoClass.

Repeat for Sub WithClass.

Sub NoClass takes less than a second to run.

The version with classes is significantly slower. I think it's because it constantly goes to the get property:

For Counter = 1 To myarrayrows
    newarray(Counter, 1) = myclass.myarray(Counter, 1) ' from here it goes to the class module
Next Counter

Can someone please tell me what have I done wrong?

Surely using classes cannot affect the performance by that much?

So how should I amend it to speed it up?

Thanks