vba - working outside sub routines

[jackb]

Hi there,

I'm trying to run a bit of code in vba which works with arrays and suchlike and am having trouble managing it all in one sub routine.

Accordingly, I was wondering if anyone knew the correct language/structure for diming arrays, string etc at module level (at present as soon as I go to a different sub routine the dimmed variables become unspecified).

Hope this makes sense to someone,

Thanks

J

 

[NdNoviceHlp]

If you're referring to a variable being available to all subs within a module place the variable dim statement at the top of the module. If you want the variable available to the whole project place at the top of a module...

Public yourvariablename as variant(etc)

Hope this is what you're after. Dave

 

[Paperclipper]

hi dave, i tried that , and it didnt work? says that "invalid attribute in sub or function".. i want to take the path of the executing workbook and pass into other workbooks to use... dimming a var public should be the key?

Sub testpub()
Public pub As String
pub = ThisWorkbook.Path
MsgBox ThisWorkbook.Path
MsgBox pub
Application.Run "Contracts 2003.xls'!ExtractData"
End Sub

 

[MartinK]

I think you must set a reference to the other workbook in VB Editor => Tools => references. Browse for the book, confirm.

Shared variables must be declared as Public at the very beginning of a module.

martin

 

[Paperclipper]

not sure what you mean at beginning of module?

thanks!

 

[MartinK]

Option Explicit
Public strMyVar As String 'this is a global one

Sub blahblah()
Dim strMyVar As String 'this is a local one
Msg strMyVar
'etc., etc.
End Sub

(declared BEFORE any sub in the module, must not be re-declared once again within the Sub, otherwise the Public one loses its global scope)

In my example, the two strMyVar vars are actually TWO independent variables. One global, one used only within the Sub. The global one is invisible for this routine.

 

[Paperclipper]

Option Explicit
Public strMyVar As String 'this is a global one

Sub blahblah()
Dim strMyVar As String 'this is a local one
Msg strMyVar
'etc., etc.
End Sub

(declared BEFORE any sub in the module, must not be re-declared once again within the Sub, otherwise the Public one loses its global scope)

In my example, the two strMyVar vars are actually TWO independent variables. One global, one used only within the Sub. The global one is invisible for this routine.

i was reading the help and it talks about standard module and class module?? wherea re those?
what's option explicit?
i thought that you said it cant be redeclared? but didnt you do that?

so you mean that i have to type the public line as the first line within a module? or just before the sub?
thanks!

 

[MartinK]

i thought that you said it cant be redeclared? but didnt you do that?

Yes I did, but only as a demonstration that it is possible though not recommended as you mix up the two variables with the same name easily eh.

Option Explicit is not needed, but I recommend you check "Require Variable Declaration" in VBA Tools=> options => Editor. It sapiently forces you to declare each variable.

We are speaking about normal modules, not class modules now.
Yes, declarations of public variables must be as first lines in the module.

Dim strString As String 'is a variable seen by all procedures in this module
Public strString2 As String 'is a variable seen by all modules and also from
    other workbooks provided they are opened and referenced to in 
    VBA=>Tools=>References.

Sub bnbbn()
    MsgBox strString
    MsgBox strString2
End Sub

Martin

 

[Smitty]

See "Understanding Scope & Visibility" in the VBA helpfile.

It explains in more detail than I could the difference between defining procedure levels.

Smitty

 

[Paperclipper]

See "Understanding Scope & Visibility" in the VBA helpfile.

It explains in more detail than I could the difference between defining procedure levels.

Smitty

smitty to the rescue...

wow! thanks smitty! that's exactly the explanation i needed... (though i noticed a typo in the example code! and bad instrcution there too - you have to exe the first before exe the second procedure..) such a novice, i didnt even know what i was looking for.. and that was what kept my previous attempts at using public/static vars in even one workbook from working!

thanks martin and others' posts, i finally got the clue that there existed a "declarations" section!

although, now that i know the deal with sharing a var/value btwn books... it seems awful cumbersome to have to click on tools and reference each time? and they are not even descriptive... just a vba project for each file open... i have 20 open! how can i see which is the right one? no way to just use coding?? do users on other comps need to do this?