Consulting

Results 1 to 6 of 6

Thread: Solved: Array as a Public Const

  1. #1
    VBAX Regular
    Joined
    Jun 2007
    Location
    Moscow
    Posts
    12
    Location

    Red face Solved: Array as a Public Const

    Hello everyone,

    I have a simple question that I can't deal with by myself.
    I couldn't find if it was solved earlier (>500 search results - it's easier to read all the forum), so don't blame me if it really was.

    I want to make a Public Const of an array of a fixed size. Something like
    Public Const A(10) = ... and then declare somehow each of 10 entries in A().

    Reason: I need a list of strings, avaliable from anywhere in the project. I have many worksheets; of them are special ones ("Final Report", "input data"), others are looped through for varoius actions. I need constants with their names. Now I have only two "special" sheets, but I want to make my code "easy-to-scale", so an array avaliable from anywhere in the project would be terrific...

    Thank you!! ^_^
    Sincerly Yours,
    千歳みどり, FRM

    Member of:
    www.riskofficer.ru, dom.bankir.ru, www.animeforum.ru

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    I don't think so, you need to declare it as a public variant and load it in a procedure

    [vba]

    Public A As Variant

    Sub Proc()

    A= Array ("Sheet1", "Sheet2")

    End SUb

    [/vba]
    ____________________________________________
    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 Regular
    Joined
    Jun 2007
    Location
    Moscow
    Posts
    12
    Location
    Quote Originally Posted by xld
    I don't think so, you need to declare it as a public variant and load it in a procedure
    Thank you. I've solved the issue by means of a special module and a function:

    Module T1:

    [VBA]
    Public SL(), Sn
    Function SheetName(i)
    Sn = 2
    ReDim SL(Sn)
    SL(1) = "Sheet1"
    SL(2) = "Sheet2"
    SheetName = SL(i)
    End Function
    [/VBA]

    Now I can access this list, as well as its length, from anywhere in my code:

    [VBA]
    Sub zz()
    Debug.Print VBAProject.T1.SheetName(1)
    Debug.Print VBAProject.T1.Sn
    End Sub
    [/VBA]
    Sincerly Yours,
    千歳みどり, FRM

    Member of:
    www.riskofficer.ru, dom.bankir.ru, www.animeforum.ru

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    In other words, you declared it as a public variant and loaded it in a procedure.
    ____________________________________________
    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 Regular
    Joined
    Jun 2007
    Location
    Moscow
    Posts
    12
    Location
    Oh, I cannot make a public array of non-fixed size, so I have to do my T1 module as follows:
    [VBA]
    Public Sn
    Dim SL()

    Function SheetName(i)
    SheetName = SL(i)
    End Function

    Sub Activate()
    Sn = 2
    ReDim SL(Sn)
    SL(1) = "Sheet1"
    SL(2) = "Sheet2"
    End Sub
    [/VBA]
    Now everything is the way I want it to be, thanks for assistance. All I have to do is change the contents "Activate" sub, and no more code changes are needed elsewhere if I have some new sheet to take care of. ^_^
    Sincerly Yours,
    千歳みどり, FRM

    Member of:
    www.riskofficer.ru, dom.bankir.ru, www.animeforum.ru

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    I didn't say a Public array, I said a public variant, which is what you did.
    ____________________________________________
    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

Posting Permissions

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