Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

bianliaw (IS/IT--Management) (OP)
29 Nov 04 0:00
How to use public statement for an array that we can use in any procedure/function ?.

Thanks.
Dashley (Programmer)
29 Nov 04 0:54
You can put it at the very top of your code in the General area above everything else. This is also where you can declare constants.

PUBLIC Mything as Thing.

You can also create a module called "Global.bas" and put you publics in there along with public subs.
bianliaw (IS/IT--Management) (OP)
29 Nov 04 1:20
My program like this.

CODE

Option Explicit
Public aX(4) as string
aX = array('sheet1','sheet2','sheet3','sheet4','sheet5')
----------------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    For nLoop = 0 To 4
        cX = aX(nLoop)
        Sheets(cX).Visible = xlVeryHidden
    Next   
End Sub

there is an error "invalid outside procedure".
how should I correct this ?.

Thanks.
PHV (MIS)
29 Nov 04 4:28
You may populate your array in the Workbook_Open event procedure.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

xlbo (MIS)
29 Nov 04 5:02
I think what PHV is trying to say is that it errors because there is no data in your array as it has not been populated. You must populate it in a SUB or FUNCTION. You cannot populate it simply by declaring it

In VBE:
F1
"Assign Variable"
read the "writing declaration statements" helpfile

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

TonyJollans (Programmer)
29 Nov 04 6:10
Hi bianliaw,

You cannot populate public arrays outside code procedures. As both PH and Geoff have said you must populate it before you can use it.

An alternative way is to write a function ..

CODE

Public Function aX(Element As Integer)
    aX = Array("sheet1", "sheet2", "sheet3", "sheet4", "sheet5")(Element)
End Function

You may want to include some error checking depending on exactly what you are doing with it.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress

bianliaw (IS/IT--Management) (OP)
29 Nov 04 23:17
Thanks TonyJollans, PH and Geoff

Tony's code can solve it.

In my excel I have workbook's code  and user form's code. I put  public function aX in workbook's code but form's code can not recognize it...

so where should I put public  function aX ?.  or I have to make both in workbook's code and userform's code ?.

TIA.
PHV (MIS)
29 Nov 04 23:21
Put your public function in a standard code module:
right click on ThisWorkbook -> Insert -> Module

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

bianliaw (IS/IT--Management) (OP)
30 Nov 04 1:42
Thanks.. PHV

how about public variable / const, can I put in module then use in workbook's code or use in user form's code ?.

TIA.
TonyJollans (Programmer)
30 Nov 04 2:29
With things like that it's probably easier to try it than to post here, but Yes, public variables in a module should be available everywhere in the project.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress

xlbo (MIS)
30 Nov 04 3:56
either that or read the help file

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close