Consulting

Results 1 to 16 of 16

Thread: How to create a public array in class module?

  1. #1

    How to create a public array in class module?

    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".

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,080
    Location
    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
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,601
    Location
    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
    
    
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,080
    Location
    Quote Originally Posted by Paul_Hossler View Post
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,601
    Location
    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
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,080
    Location
    Quote Originally Posted by Paul_Hossler View Post
    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 View Post
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,601
    Location
    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
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,080
    Location
    Quote Originally Posted by Paul_Hossler View Post
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  9. #9
    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?
    Attached Files Attached Files

  10. #10
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,080
    Location
    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
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  11. #11
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,080
    Location
    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
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  12. #12
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,601
    Location
    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
    Attached Files Attached Files
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  13. #13
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,080
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  14. #14
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,601
    Location
    +1
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  15. #15
    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

  16. #16
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,601
    Location
    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
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •