how to declare Public statement

[bianliaw]

How to use public statement for an array that we can use in any procedure/function ?.

Thanks.

 

[Dashley]

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]

My program like this.

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]

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]

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]

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

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

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 [url=http://www.vbaexpress.

 

[bianliaw]

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]

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]

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]

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 [url=http://www.vbaexpress.

 

[xlbo]

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