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

 

[MartinK]

You can change the project's name:

In VBEditor, you see something like
VBAProject("Book1.xls").

Right-click it, Properties, change name. It is advisable also in order not to get collision of the names.

Cheers

Martin

 

[MartinK]

how can i see which is the right one? no way to just use coding??

As it sounds unless you have 20 books with diferent code it would be more advissable to have just one code in a master book and apply it on your 20 workbooks. It depends.
Martin

 

[Paperclipper]

wow... how do you copy code from one book to another?

i have 1 master.xls, and one book for every year... the code is "mostly" the same... just references different things, for instance, they all have ms queries, but named slightly diff...

hey can you use something like the CreateObject("blahblah.FileSystemObject")
c = fs.fileExists("C:\filename.xls")

or something like that?

http://www.mrexcel.com/board2/viewtopic.php?t=63587&postdays=0&postorder=asc&start=0

 

[Paperclipper]

anyway, i tried to reference, and it wouldnt even give me the empty box this time. it says "ambiguous name detected"!

 

[MartinK]

anyway, i tried to reference, and it wouldnt even give me the empty box this time. it says "ambiguous name detected"!

In VB editor, right click the VBA Project (default name), click Properties and change the name. So it will not collide.

By having just one code I do not mean that you copy it into the workbooks (though this is also possible). What I rather mean is that if the code in all those 20 sheets is identical, it's much better to have one master sheet with the code that works on all the other workbooks in a loop.

E.g. this will open C:\test1.xls and enter today's date in Sheet3, A1. The same with Test2.xls or any other workbook that is in the specified folder and whose name you stack into y.
<pre><SPAN style="color:#00007F">Sub</SPAN> ManipWBs()
<SPAN style="color:#00007F">Dim</SPAN> myPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, y <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> sh <SPAN style="color:#00007F">As</SPAN> Worksheet
<SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
myPath = "C:\"
y = [{"Test1","Test2"}]
<SPAN style="color:#00007F">For</SPAN> i = <SPAN style="color:#00007F">LBound</SPAN> <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>
Workbooks.Open (myPath & y(i) & ".xls")
<SPAN style="color:#00007F">Set</SPAN> sh = ActiveWorkbook.Sheets("Sheet3")
sh.[A1] = Int(Now())
ActiveWorkbook.Close SaveChanges:=<SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">Next</SPAN> i
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></PRE>
Best regards

Martin

 

[Paperclipper]

hmm... that's good idea.. but i am do a get external data query... and the inputs vary from source file to source file (like i would need to adjust teh db file name for each worksheet, and also the queries are slightly off too... ) is this possible? i would need public vars?

too difficult for me!

 

[djforeman]

working with multiple workbooks

Sorry if this is a long post, but I need to give a full explanation of what I'm up to. It's simiilar to previous posts, but a bit different.

The short request is: sharing a variable across workbooks, if there are multiple workbooks open. If this is the wrong place for this post, please point me in the right direction.

I need to access a couple of variables across multiple workbooks, but will NOT be copying/moving worksheet data between them. I have created some buttons and commandbars (in VBA 2003) and want them to be common across a subset of workbooks (let's call them "proper" workbooks) and to make them disappear when the last "proper" workbook exits, so they don't appear when an "ordinary" worksheet is opened, unmless a "proper" workbook has made them visible.

I have designed a "counter" that increments when a "proper" workbook opens and decrements when one closes, but I need to make this counter "global" to the "proper" workbooks. "Public" isn't global enough.

The counter should work for 1 or more "proper" workbooks being open. The functions activated by the buttons already ensure that a "proper" workbook is the active one or they do nothing. This allows for non-Proper workbooks and "proper" workbooks to intermix on the screen.

 

[imnie]

More on VBProject references

I was similarly trying to create a reference to another workbook so I could share the variables with the active workbook. I followed Tools --> References --> then browsed for the project containing the variables I needed. However, I kept getting an error about "name conflicts". Any idea why it would do this....also - is this the best way to share the variables? My current process is this:

Main Workbook runs a series of code
Main Workbook calls another workbook (2nd workbook)
2nd Workbook runs code referencing values of variables in Main Workbook

Thanks for any suggestions.

 

[chrisr01536]

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

Brilliant, really helped me