Hi Rob,

That is exactly what we use it for, dev and prod.

What problem exactly are you getting with the SetEnvironmentVariable API? Is
it that trying to read it with Environ in the same NT/XP session does return
a value? If that is the case, I think this is because Excel seems to load
the environment variables at start-up, and Environ gets the values locally.
So any changes that you make are not reflected in Environ. And as
SetEnvironmentVariable only sets the variable for the current session, this
stymies you.

You can get around it though by using GetEnvironmentVariable to read it as
this will read any variables in the current session. This code snippet
should demonstrate it, GetEnvironmentVariable returns the newly set value,
Environ doesn't

Option Explicit

Private Declare Function GetEnvironmentVariable Lib "kernel32" _
Alias "GetEnvironmentVariableA" _
(ByVal lpName As String, _
ByVal lpBuffer As String, _
ByVal nSize As Long) As Long

Private Declare Function SetEnvironmentVariable Lib "kernel32" _
Alias "SetEnvironmentVariableA" _
(ByVal lpName As String, _
ByVal lpValue As String) As Long

Sub xx()
SetEnvironmentVariable "Rob", "Nuzie!"
MsgBox Environ("Rob")
MsgBox GetEnvironmentVar("Rob")
End Sub


Function GetEnvironmentVar(Name As String) As String
GetEnvironmentVar = String(255, 0)
GetEnvironmentVariable Name, GetEnvironmentVar, Len(GetEnvironmentVar)
GetEnvironmentVar = TrimNull(GetEnvironmentVar)
End Function

Private Function TrimNull(item As String)
Dim iPos As Long
iPos = InStr(item, vbNullChar)
TrimNull = IIf(iPos 0, Left$(item, iPos - 1), item)
End Function

If you want it for another session, you will need to write it to the
registry.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Rob van Gelder" wrote in message
...
Good suggestion.

I actually thought to myself, "great idea, I can use environ for quickly
switching between test and prod for my development".

After a 5 minute search, I can find no easy way to write out environment
variables (other than hitting the SetEnvironmentVariable API - and I'm
having trouble with that too!)

Have you got some code to share?


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Bob Phillips" wrote in message
...
Another way is to use an environment variable rather than a file, and
test
it with Environ.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Rob van Gelder" wrote in
message
...
Two ways:

1.
Set a breakpoint at the End Sub then from the immediate window: Cancel
=
False

2.
If you're doing this regularly, you may want an external flag.

For example
Instead of
Cancel = True
Try
If Dir("C:\DebugMode.txt") = "" Then Cancel = True

Then create DebugMode.txt whenever you plan to do some development.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Shilps" wrote in message
...
Hi,
I have a very tricky situation. I want to disble the save option of
the
workbook. So I wrote this code

Private Sub Workbook_BeforeClose(Cancel as Boolean)

Application.DisplayAlerts = False
ThisWorkbook.Saved = True
ThisWorkbook.Close
Application.DisplayAlerts = true

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel as Boolean)

Application.DisplayAlerts = False
Cancel = True
Application.DisplayAlerts = true

End Sub

Now the problem is the moment I write this code, it is activated
before
saving and this code is not saved at all. How do I save this code? My
objective is that the user should not be able to save any changes in
the
workbook.
TIA
Shilps