How to create a public array in class module?

[Paul_Hossler]

1. That's a lot of hard coded numbers to have in a Class. Personally, I'd find 100 individually named arrays (srf1 … srf100) cumbersome to work with

I used Bob's movem sub to put into WS and doubled to get 100 cols, since personally I think that's the best place for 100K+ numbers

2. In a standard module

Option Explicit
Sub test()
    Dim A As clsArray
    
    Set A = New clsArray
    
    With A
    
        Call .Init(Worksheets("Sheet1").Cells(1, 1).CurrentRegion)
        
        MsgBox .ColVector(2)(2)
    
        MsgBox Application.WorksheetFunction.Max(.ColVector(10))
        MsgBox Application.WorksheetFunction.Min(.ColVector(20))
        MsgBox Application.WorksheetFunction.Average(.ColVector(30))
    End With
    Set A = Nothing

End Sub

3. In a class module named clsArray

Option Explicit

Dim m_Array As Range

Private Sub Class_Initialize()
    'if always  the same data range, could go here
    Set m_Array = Nothing
End Sub


Private Sub Class_Terminate()
    Set m_Array = Nothing
End Sub


Sub Init(r As Range)
    
    On Error GoTo Err_Handler
    Set m_Array = r
    Exit Sub
    
Err_Handler:
    
End Sub

Property Get ColVector(n As Long) As Variant
    ColVector = Empty
    
    On Error GoTo Err_Handler
    ColVector = Application.WorksheetFunction.Transpose(m_Array.Columns(n))
    Exit Property
    
Err_Handler:
    
End Property