strange problem with public variable

[Gary Keramidas]

maybe someone can tell me if this is a bad idea, too.

i use a public variable to set the path and set it in the workbook open module
to keep from setting the path in 12 different modules.
Public fPath As String is in a standard module

If UCase(Environ("username")) = "GARYK" Then
fPath = ThisWorkbook.Path & "\Blends\"
Else
fPath = "\\Customers\Blends\"
End If
is in the workbook open module

i noticed i was losing the path, it was resetting to an empty string. what
caused it was going to the module with the public variables, making a change and
recompiling. even if i just deleted a space, it still reset the variable,

so my question is: is this a bad idea and will this ever reset during the course
of normal operation for my client? they never enter the vbe.

 

[Dave Peterson]

It depends on what your code does.

If you have any End statements (not "end if", "end Function", "end sub", "end
with" and the like), your variables will be reset.

Personally, I think adding an initialization routine wouldn't be a bad idea.

In a general module:

Public VarsAreInitialized As Boolean
Public fPath As String
'and as many more as you need

Sub InitializeVars()
If UCase(Environ("username")) = "GARYK" Then
fPath = ThisWorkbook.Path & "\Blends\"
Else
fPath = "\\Customers\Blends\"
End If
varsareinitialized = true
end sub

Then in your workbook_open event, replace the if/then/else code with Call
statement.

Call InitializeVars

And before you use any of these variables in any routine:

If varsareinitialized = false then
call initializevars
end if

It may save you some problems later. And makes changing and testing easier,
too.

 

[Gary Keramidas]

thanks for the idea, dave.

--


Gary

It depends on what your code does.

If you have any End statements (not "end if", "end Function", "end sub", "end
with" and the like), your variables will be reset.

Personally, I think adding an initialization routine wouldn't be a bad idea.

In a general module:

Public VarsAreInitialized As Boolean
Public fPath As String
'and as many more as you need

Sub InitializeVars()
If UCase(Environ("username")) = "GARYK" Then
fPath = ThisWorkbook.Path & "\Blends\"
Else
fPath = "\\Customers\Blends\"
End If
varsareinitialized = true
end sub

Then in your workbook_open event, replace the if/then/else code with Call
statement.

Call InitializeVars

And before you use any of these variables in any routine:

If varsareinitialized = false then
call initializevars
end if

It may save you some problems later. And makes changing and testing easier,
too.