What is the correct way to dynamically set environment variables before running Excel?

Question

I have a bunch of Excel addins which are configured by an environment variable. Normally these are set in the registry on a system-wide basis. When I want change the value non-dynamically I go to:

my-computer -> properties -> advanced -> environment variables -> System variables

I have verified that when I set an environment variable here it seems to be picked up by Excel exactly as we might expect.

However, I also need to set environment variables dynamically, for example to make my Excel addin use an alternative configuration file. I'd expect to be able to do something like this:

set MYADDIN_CONFIG_FILE=path_to_config.xml
<path to excel>\excel.exe

Unfortunately when I do this it's as if the changes to the environment variables are completely ignored. I'm guessing that Excel needs to be told somehow notice environment variables which have been set form the command line?

I've verified that the above technique fails using the VB "Env" function.

FYI: Excel 2003 on Windows XP 32bit.

Answer 1

I made a simple test (Excel 2003 SP2 on Windows XP Pro SP3).

I wrote a VBA function:

Public Function GetEnv(name As String) As String
  GetEnv = Environ(name)
End Function

Then I typed:

=GetEnv("TEMP")
=GetEnv("JAVA_HOME")
=GetEnv("TestSO")

The latter being not defined, the cell remains empty.

Now, I go to command line and type:

set TestSO=This is a test for SO
"C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE"

and I load the previous sheet. Uh, result is unchanged, even after hitting F9, but if I click on the formula and hit Return, the result is updated... (I am a bit rusty on Excel/VBA usage...)
Anyway, it does display the correct environment variables set on the fly in the current console before running Excel. As expected (common Windows behavior).