How to create a public array in class module?

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

Code:

---

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

Code:

---

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

---

But I get the error "invalid outside procedure".

Code:

---

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

---

Code:

---

Sub CallMyClass()
Dim cls As Class1

    Set cls = New Class1

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

End Sub

---

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

Code:

---

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

Code:

---

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



---

Quote:

---

Originally Posted by 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

---

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

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

Quote:

---

Originally Posted by Paul_Hossler

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.

Quote:

---

Originally Posted by Paul_Hossler

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.

Quote:

---

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

Quote:

---

Originally Posted by Paul_Hossler

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.

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

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?

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

Code:

---

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

Code:

---

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

---

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

Code:

---

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

---

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

Code:

---


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

Code:

---

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

---

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.

+1

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

Code:

---

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

Code:

---

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

Not tested

Code:

---


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