Excel VBA: Using Public variables and multiple modules

[DJWheezyWeez]

Hi,

I've been using Dim x as String variables and I've been playing around with Public instead and I'm having a problem making one work.

In my testing, I've gotten this to work starting from a brand new book with two brand new modules:
Module1:
Public testVar As String
Sub testone()
testVar = "testing testing one two"
End Sub

Module2:
Sub testtwo()
ActiveCell.FormulaR1C1 = testVar
End Sub

When I run the testtwo macro, it fills in "testing testing one two" into the currently active cell. My next attempt at playing around is dealing with opening an existing workbook (as part of a much larger macro).

In my testing, again with a brand new workbook and two new modules, this is what I have but it's not working:
Module1:
Public filePath As String
Sub fpone()
filePath = "W:\IT\Zach\Sales Journal\Testing\"
End Sub

Module2:
Sub fptwo()
Workbooks.Open Filename:=filePath & "Files\Toms_Variance_Setup.xlsm"
End Sub

This time, when I run the fptwo macro, I get an error saying it can't find the 'Files\Toms_Variance_Setup.xlsm' file since it's not using the filePath variable string in the file path. the fptwo macro DOES work if I put the filePath variable inside it instead of fpone.

I'm wondering why does this work in my first example but not the second?
Will this work with some tweaking or is this just not possible?

-DJWW

 

[SkipVought]

hi,

Don't know why this would happen.

I used YOUR CODE, substituting a suitable path and workbook name.

Ran fpone
Ran fptwo, which opend my wb.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DJWheezyWeez]

Very odd... I tried it again quickly (as my day is ending) and still wasn't able to get it to run. I thought maybe it was the .xlsm extension and tried a .xls file with no luck. Might it be the version of Excel? I'm using 2010.

I can play around with other shorter file paths or a path on C: instead of W:. Obviously something must be different if I can't run it but you can.

 

[SkipVought]

what happens if you add this statement

Sub fptwo()[b]
fpone[/b]
Workbooks.Open Filename:=filePath & "Files\Toms_Variance_Setup.xlsm"
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DJWheezyWeez]

That worked for me. I'm still not sure why it didn't work before but it does now. Thanks

 

[SkipVought]

Had you actuall run fpone before you ran fptwo?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Mrall]

I have had problems with public variables working in more than one module for some reason. So as an altertive I store them in a cell in a hidden sheet. It works always.

 

[SkipVought]

There is no reason why public variables would not work, unless your method is improper!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[combo]

Sometimes the vba running project is reset, in the vbe options ("general" tab) you can tick "Notify Before State Loss" to be warned in this case. It's a local setting and can be used for testing. You can also use the "Watch" window to see what is going on.

combo