Dr. Demento
Well-known Member
- Joined
- Nov 2, 2010
- Messages
- 618
- Office Version
- 2019
- 2016
- Platform
- Windows
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:
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.
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:
VBA Code:
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.