vba - working outside sub routines

J

jackb

New Member
Joined
May 22, 2002
Messages
6
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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
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...
Code: 
Public yourvariablename as variant(etc)
Hope this is what you're after. Dave
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Code: 
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.
 
Upvote 0
MartinK said:
Code: 
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.
Click to expand...

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!
 
Upvote 0
i thought that you said it cant be redeclared? but didnt you do that?
Click to expand...
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.

Code: 
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
 
Upvote 0
See "Understanding Scope & Visibility" in the VBA helpfile.

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

Smitty
 
Upvote 0
pennysaver said:
See "Understanding Scope & Visibility" in the VBA helpfile.

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

Smitty
Click to expand...

:laugh: 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? :p no way to just use coding?? do users on other comps need to do this?
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Question Question
VBA: Pass Args back to Sub from PVT Sub
Replies
4
Views
164
Skyybot
Skyybot
JeffGrant
  • Solved
When is a workbook too big?
2
Replies
11
Views
2K
Michael M
Michael M
K
  • Solved
Previously working very complex VBA now stopped
Replies
7
Views
532
Alex Blakenburg
A
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
G
  • Solved
Solution/workaround for dynamically created UserForms resetting project on customization
2 3
Replies
22
Views
3K
RoryA
RoryA

Forum statistics

Threads
1,223,912
Messages
6,175,344
Members
452,638
Latest member
Oluwabukunmi

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top