How to create a public array in class module?

[alex009988]

So that I've tried to use that code in class module

Public sf()
Dim strf(), strf1(), strf2(), strf3()
strf1 = Array(1, 2, 3)
strf2 = Array(4, 5, 6)
strf3 = Array(6, 7, 9)
strf = Array(strf1, strf2, strf3)
End Sub

and code for a module

Sub s()
Debug.Print strf(2)(2)
End Sub

But I get the error "invalid outside procedure".

[Bob Phillips]

Option Explicit

Public strf As Variant
Dim strf1 As Variant, strf2 As Variant, strf3 As Variant

Public Sub sf()
    strf1 = Array(1, 2, 3)
    strf2 = Array(4, 5, 6)
    strf3 = Array(6, 7, 9)
    strf = Array(strf1, strf2, strf3)
End Sub

Sub CallMyClass()
Dim cls As Class1

    Set cls = New Class1

    cls.sf
    Debug.Print cls.strf(2)(2)

End Sub

[Paul_Hossler]

Probably a matter of personal style, but I wouldn't put public variables in a Class module; I'd use Property Let/Get

Class module Class1

Option Explicit

Dim m_Ary() As Variant

Property Let A(A1 As Variant)
    m_Ary = A1
End Property

Property Get A() As Variant
    A = m_Ary
End Property

Sub DoubleIt()
    Dim i As Long
    
    For i = LBound(m_Ary) To UBound(m_Ary)
        m_Ary(i) = 2 * m_Ary(i)
    Next I

End Sub

Standard module

Option Explicit

Sub test()
    Dim c As Class1
    Dim i As Long
    
    Set c = New Class1
    
    With c
        .A = Array(1, 2, 3, 4)
        .DoubleIt
    
        For i = LBound(.A) To UBound(.A)
            Debug.Print .A(i)
        Next i
        .DoubleIt
    
        For i = LBound(.A) To UBound(.A)
            Debug.Print .A(i)
        Next i
    End With
End Sub

[Bob Phillips]

Probably a matter of personal style, but I wouldn't put public variables in a Class module; I'd use Property Let/Get

A public variable in a class is the same as Property Let/Get, it is just less coding.

[Paul_Hossler]

True, but in my example the properties weren't public

Probably no really good reason to make them properties, just the way I did things, since I oft times do things in the property besides just expose the variable, and I find I do better if I'm consistant

[Bob Phillips]

True, but in my example the properties weren't public

What do you mean, of course they are public, how else do you think you can reference them in the procedure test? Just try explicitly declaring those properties as private and try running test.

Probably no really good reason to make them properties, just the way I did things, since I oft times do things in the property besides just expose the variable, and I find I do better if I'm consistant

I too often do that, but I constantly question myself as to whether I should do that or I should make it a method of the class instead.

[Paul_Hossler]

What do you mean, of course they are public, how else do you think you can reference them in the procedure test? Just try explicitly declaring those properties as private and try running test.

I too often do that, but I constantly question myself as to whether I should do that or I should make it a method of the class instead.

Yes, you're right -- I just don't think of them as Public in the same way as a Standard Module Public variable since they have to instantiated as part of an object -- just me

Depends on situation -- I think of Methods as the class equivalent of a Sub, and Properties as the class equivalent of a Function

So I might have a property FullName defined as m_Last & " " & m_First

[Bob Phillips]

Depends on situation -- I think of Methods as the class equivalent of a Sub, and Properties as the class equivalent of a Function

So I might have a property FullName defined as m_Last & " " & m_First

Funny isn't it, I definitely think of Methods as functions, and properties as, well, properties. To be accurate I really think of methods of being the actions of a class, and so can be serviced by a function or a sub (although I tend to use functions), and properties as the attributes of that class (which is why I get myself in knots when I get a property doing something other than just passing the value of that attribute in or out of the class).

In my coding I rarely use subs, except for event code that has sub as part of the signature, on that premise that every procedure should return a result even if it is only success or failure.

[alex009988]

Thank you both for your help. So I wanted to have huge 99x1001 array(matrix). So I had to divide it into two parts 50x1001. Now I get error "procedure too large".
Now my class module has more then 50000 lines. So I've attached a file of that.
and use this module code

Sub s()
Dim st As Class
Set st = New Class
st.sf
Debug.Print st.strf(2)(2)
End Sub

How to make this code workable?

[Bob Phillips]

I would put all of those values in a worksheet and load them in the class, fast and space efficient.

I split the values into 50 columns, one for each array, and then used the following code in the class module

Option Explicit

Public strf As Variant

Dim strf1, strf2, strf3, strf4, strf5, strf6, strf7, strf8, strf9, strf10, _
    strf11, strf12, strf13, strf14, strf15, strf16, strf17, strf18, strf19, strf20, _
    strf21, strf22, strf23, strf24, strf25, strf26, strf27, strf28, strf29, strf30, _
    strf31, strf32, strf33, strf34, strf35, strf36, strf37, strf38, strf39, strf40, _
    strf41, strf42, strf43, strf44, strf45, strf46, strf47, strf48, strf49, strf50
Public Sub sf()

    With ActiveSheet

        strf1 = .Range("A2:A1002")
        strf2 = .Range("B2:B1002")
        strf3 = .Range("C2:C1002")
        strf4 = .Range("D2:D1002")
        strf5 = .Range("E2:E1002")
        strf6 = .Range("F2:F1002")
        strf7 = .Range("G2:G1002")
        strf8 = .Range("H2:H1002")
        strf9 = .Range("I2:I1002")
        strf10 = .Range("J2:J1002")
        strf11 = .Range("K2:K1002")
        strf12 = .Range("L2:L1002")
        strf13 = .Range("M2:M1002")
        strf14 = .Range("N2:N1002")
        strf15 = .Range("O2:O1002")
        strf16 = .Range("P2:P1002")
        strf17 = .Range("Q2:Q1002")
        strf18 = .Range("R2:R1002")
        strf19 = .Range("S2:S1002")
        strf20 = .Range("T2:T1002")
        strf21 = .Range("U2:U1002")
        strf22 = .Range("V2:V1002")
        strf23 = .Range("W2:W1002")
        strf24 = .Range("X2:X1002")
        strf25 = .Range("Y2:Y1002")
        strf26 = .Range("Z2:Z1002")
        strf27 = .Range("AA2:AA1002")
        strf28 = .Range("AB2:AB1002")
        strf29 = .Range("AC2:AC1002")
        strf30 = .Range("AD2:AD1002")
        strf31 = .Range("AE2:AE1002")
        strf32 = .Range("AF2:AF1002")
        strf33 = .Range("AG2:AG1002")
        strf34 = .Range("AH2:AH1002")
        strf35 = .Range("AI2:AI1002")
        strf36 = .Range("AJ2:AJ1002")
        strf37 = .Range("AK2:AK1002")
        strf38 = .Range("AL2:AL1002")
        strf39 = .Range("AM2:AM1002")
        strf40 = .Range("AN2:AN1002")
        strf41 = .Range("AO2:AO1002")
        strf42 = .Range("AP2:AP1002")
        strf43 = .Range("AQ2:AQ1002")
        strf44 = .Range("AR2:AR1002")
        strf45 = .Range("AS2:AS1002")
        strf46 = .Range("AT2:AT1002")
        strf47 = .Range("AU2:AU1002")
        strf48 = .Range("AV2:AV1002")
        strf49 = .Range("AW2:AW1002")
        strf50 = .Range("AX2:AX1002")
    End With

strf = Array(strf1, strf2, strf3, strf4, strf5, strf6, strf7, strf8, strf9, strf10, strf11, strf12, strf13, strf14, strf15, strf16, strf17, strf18, strf19, strf20, strf21, strf22, strf23, strf24, strf25, strf26, strf27, strf28, strf29, strf30, strf31, strf32, strf33, strf34, strf35, strf36, strf37, strf38, strf39, strf40, strf41, strf42, strf43, strf44, strf45, strf46, strf47, strf48, strf49, strf50)
End Sub

I extracted the values by copying all of the strf(x) = values line from your class file into the worksheet, starting at A2, and then running this code on it to setup a grid of values

Sub movem()
Dim targetcol As Long
Dim lastrow As Long
Dim lastcol As Long
Dim i As Long

    Application.ScreenUpdating = False

    With ActiveSheet
    
        targetcol = 3
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Range("B1").Value = "strf1"
        .Range("B2").Resize(lastrow - 1).FormulaR1C1 = "=--MID(RC[-1],FIND(""="",RC[-1])+2,99)"
        For i = 1003 To lastrow Step 1001
        
            .Cells(i, "A").Resize(1000, 2).Cut .Cells(2, targetcol)
            targetcol = targetcol + 2
        Next i
        
        lastcol = .Cells(2, .Columns.Count).End(xlToLeft).Column
        With .Range("A2").Resize(1001, lastcol)
        
            .Value = .Value
        End With
        
        For i = lastcol - 1 To 1 Step -2
        
            .Columns(i).Delete
        Next i
        
        lastcol = .Cells(2, .Columns.Count).End(xlToLeft).Column
        .Range("A1").AutoFill Destination:=.Range("A1").Resize(1, lastcol), Type:=xlFillDefault
        .Rows(1).Font.Bold = True
        
        Debug.Print "    With Activesheet"
        Debug.Print ""
        For i = 1 To lastcol
        
            Debug.Print "        " & .Cells(1, i).Value & " = .Range(""" & .Cells(2, i).Resize(1001).Address(False, False) & """)"
        Next i
        Debug.Print "    End With"
    End With

    Application.ScreenUpdating = True
End Sub

[Bob Phillips]

Sorry, missed something in the class module, code should be

Option Explicit

Public strf As Variant

Dim strf1, strf2, strf3, strf4, strf5, strf6, strf7, strf8, strf9, strf10, _
    strf11, strf12, strf13, strf14, strf15, strf16, strf17, strf18, strf19, strf20, _
    strf21, strf22, strf23, strf24, strf25, strf26, strf27, strf28, strf29, strf30, _
    strf31, strf32, strf33, strf34, strf35, strf36, strf37, strf38, strf39, strf40, _
    strf41, strf42, strf43, strf44, strf45, strf46, strf47, strf48, strf49, strf50
Public Sub sf()

    With ActiveSheet

        strf1 = Application.Transpose(.Range("A2:A1002"))
        strf2 = Application.Transpose(.Range("B2:B1002"))
        strf3 = Application.Transpose(.Range("C2:C1002"))
        strf4 = Application.Transpose(.Range("D2:D1002"))
        strf5 = Application.Transpose(.Range("E2:E1002"))
        strf6 = Application.Transpose(.Range("F2:F1002"))
        strf7 = Application.Transpose(.Range("G2:G1002"))
        strf8 = Application.Transpose(.Range("H2:H1002"))
        strf9 = Application.Transpose(.Range("I2:I1002"))
        strf10 = Application.Transpose(.Range("J2:J1002"))
        strf11 = Application.Transpose(.Range("K2:K1002"))
        strf12 = Application.Transpose(.Range("L2:L1002"))
        strf13 = Application.Transpose(.Range("M2:M1002"))
        strf14 = Application.Transpose(.Range("N2:N1002"))
        strf15 = Application.Transpose(.Range("O2:O1002"))
        strf16 = Application.Transpose(.Range("P2:P1002"))
        strf17 = Application.Transpose(.Range("Q2:Q1002"))
        strf18 = Application.Transpose(.Range("R2:R1002"))
        strf19 = Application.Transpose(.Range("S2:S1002"))
        strf20 = Application.Transpose(.Range("T2:T1002"))
        strf21 = Application.Transpose(.Range("U2:U1002"))
        strf22 = Application.Transpose(.Range("V2:V1002"))
        strf23 = Application.Transpose(.Range("W2:W1002"))
        strf24 = Application.Transpose(.Range("X2:X1002"))
        strf25 = Application.Transpose(.Range("Y2:Y1002"))
        strf26 = Application.Transpose(.Range("Z2:Z1002"))
        strf27 = Application.Transpose(.Range("AA2:AA1002"))
        strf28 = Application.Transpose(.Range("AB2:AB1002"))
        strf29 = Application.Transpose(.Range("AC2:AC1002"))
        strf30 = Application.Transpose(.Range("AD2:AD1002"))
        strf31 = Application.Transpose(.Range("AE2:AE1002"))
        strf32 = Application.Transpose(.Range("AF2:AF1002"))
        strf33 = Application.Transpose(.Range("AG2:AG1002"))
        strf34 = Application.Transpose(.Range("AH2:AH1002"))
        strf35 = Application.Transpose(.Range("AI2:AI1002"))
        strf36 = Application.Transpose(.Range("AJ2:AJ1002"))
        strf37 = Application.Transpose(.Range("AK2:AK1002"))
        strf38 = Application.Transpose(.Range("AL2:AL1002"))
        strf39 = Application.Transpose(.Range("AM2:AM1002"))
        strf40 = Application.Transpose(.Range("AN2:AN1002"))
        strf41 = Application.Transpose(.Range("AO2:AO1002"))
        strf42 = Application.Transpose(.Range("AP2:AP1002"))
        strf43 = Application.Transpose(.Range("AQ2:AQ1002"))
        strf44 = Application.Transpose(.Range("AR2:AR1002"))
        strf45 = Application.Transpose(.Range("AS2:AS1002"))
        strf46 = Application.Transpose(.Range("AT2:AT1002"))
        strf47 = Application.Transpose(.Range("AU2:AU1002"))
        strf48 = Application.Transpose(.Range("AV2:AV1002"))
        strf49 = Application.Transpose(.Range("AW2:AW1002"))
        strf50 = Application.Transpose(.Range("AX2:AX1002"))
    End With

strf = Array(strf1, strf2, strf3, strf4, strf5, strf6, strf7, strf8, strf9, strf10, strf11, strf12, strf13, strf14, strf15, strf16, strf17, strf18, strf19, strf20, strf21, strf22, strf23, strf24, strf25, strf26, strf27, strf28, strf29, strf30, strf31, strf32, strf33, strf34, strf35, strf36, strf37, strf38, strf39, strf40, strf41, strf42, strf43, strf44, strf45, strf46, strf47, strf48, strf49, strf50)
End Sub

[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

[Bob Phillips]

What you (the OP) have not told us is what you are actually trying to do, and where those numbers come from. I bet we could come up with a better solution if we knew.

[Paul_Hossler]

+1

[alex009988]

You're right. I think the best way to work with that stuff is to have a worksheet and custom function
This code works for me

Function fstr(a As Integer, m As Integer)
Sheets("sfactors").Visible = False
Sheets("sfactors").Activate
    Set r = Cells(1, 1)
  fstr = Range(r(a, m + 1).Address).Value
End Function

But if I want to do it without hiding the list it dosent work

Function fstr(a As Integer, m As Integer)
'Sheets("sfactors").Visible = False
'Sheets("sfactors").Activate
    Set r = ThisWorkbook.Sheets("sfactors").Cells(1, 1)
  fstr = Range(r(a, m + 1).Address).Value
End Function

What's wrong with the second one? In this case, I had to manually select sfactors list.
Regards, Alex

[Paul_Hossler]

Not tested

Function fstr(a As Integer, m As Integer) As Double

fstr = ThisWorkbook.Sheets("sfactors").Cells(a, m+1).Value

End Function

If you're reading the WS data from inside a macro, you can use the same method w/o a UDF