Persistent public variable

[Dr. Demento]

Maybe I'm truly not understanding other answers I've come across but I'm hoping someone can dumb this down for me.

My work uses roaming profiles and therefore Environ$("HOMESHARE") gives me the proper path for My Documents or Downloads. However, when I'm using my work laptop (or copy Personal.xlsb to my personal laptop), Environ$("USERPROFILE") gives the appropriate path.

What I'd like to do is create a persistent public variable that will allow me to assign a string based on which Application.StartupPath is present. In other words:

If Application.StartupPath = workpath then
   sEnviron = Chr(34) & "HOMESHARE" & Chr(34)
ElseIf Application.StartupPath = laptoppath then
   sEnviron = Chr(34) & "USERPROFILE" & Chr(34)
End If

From there, through out my coding, I use Environ$ in various Subs/Functions. I'd like to use sEnviron even though the Sub where Environ has ended. sEnviron would persist as long as Excel was open, regardless of how many subs or functions were run. I'm not talking about passing the variable in a series of subs, but rather once the defining sub ends, having the Public variable always assigned.

Is this even possible? I'm open to pushing the variable to the Personal.xlsb worksheet but I don't know how to define it once and then it's always available.

Thanks y'all.

 

[Jaafar Tribak]

How about defining this function in a satandrd module in your workbook project :

Public Function sEnviron() As String
    If Application.StartupPath = workpath Then
       sEnviron = Chr(34) & "HOMESHARE" & Chr(34)
    ElseIf Application.StartupPath = laptoppath Then
       sEnviron = Chr(34) & "USERPROFILE" & Chr(34)
    End If
End Function

Then you can simply call the above sEnviron function throughout your Subs/Functions .

 

[Dr. Demento]

Oh wow! Thank you, Jaafar. I got so stuck seeing only a single solution.

I love this board!!