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