Environment Variable

Hi All,

Is there a way from within VBA to declare an environment variable?

Or do I have to declare the kernal32 function "SetEnvironmentVariable"?

Thanks

Andy

You are correct, you do need to use an API.

You should also note that Excel seems to load all variables at startup, and
the Environ command retrieves the value from this memory store. So you need
to use the GetEnvironmentVariable to read your new value.

This should demonstrate that

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


--
HTH

Bob Phillips

"Andibevan" <Andibevan@notehere-hotmail.com> wrote in message
news:uwN7eiBmFHA.2060@tk2msftngp13.phx.gbl...
> Hi All,
>
> Is there a way from within VBA to declare an environment variable?
>
> Or do I have to declare the kernal32 function "SetEnvironmentVariable"?
>
> Thanks
>
> Andy
>
>

Thanks Bob - I didn't know about using GetEnvironmentVariable.

What does the function TrimNull do? I can't work it out.

Ta

Andi



"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:e1Fd55BmFHA.3900@TK2MSFTNGP09.phx.gbl...
> You are correct, you do need to use an API.
>
> You should also note that Excel seems to load all variables at startup,
and
> the Environ command retrieves the value from this memory store. So you
need
> to use the GetEnvironmentVariable to read your new value.
>
> This should demonstrate that
>
> 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
>
>
> --
> HTH
>
> Bob Phillips
>
> "Andibevan" <Andibevan@notehere-hotmail.com> wrote in message
> news:uwN7eiBmFHA.2060@tk2msftngp13.phx.gbl...
> > Hi All,
> >
> > Is there a way from within VBA to declare an environment variable?
> >
> > Or do I have to declare the kernal32 function "SetEnvironmentVariable"?
> >
> > Thanks
> >
> > Andy
> >
> >
>
>

Hi Andi,

It is a generic routine to tidy up strings from APIs, which use null to
designate the end of a string. So this routine just looks for the first
null, and extracts all characters to the left of that.

--
HTH

Bob Phillips

"Andibevan" <Andibevan@notehere-hotmail.com> wrote in message
news:%23CI5sXCmFHA.3656@TK2MSFTNGP09.phx.gbl...
> Thanks Bob - I didn't know about using GetEnvironmentVariable.
>
> What does the function TrimNull do? I can't work it out.
>
> Ta
>
> Andi
>
>
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:e1Fd55BmFHA.3900@TK2MSFTNGP09.phx.gbl...
> > You are correct, you do need to use an API.
> >
> > You should also note that Excel seems to load all variables at startup,
> and
> > the Environ command retrieves the value from this memory store. So you
> need
> > to use the GetEnvironmentVariable to read your new value.
> >
> > This should demonstrate that
> >
> > 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
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Andibevan" <Andibevan@notehere-hotmail.com> wrote in message
> > news:uwN7eiBmFHA.2060@tk2msftngp13.phx.gbl...
> > > Hi All,
> > >
> > > Is there a way from within VBA to declare an environment variable?
> > >
> > > Or do I have to declare the kernal32 function
"SetEnvironmentVariable"?
> > >
> > > Thanks
> > >
> > > Andy
> > >
> > >
> >
> >
>
>

I have tried the method you suggested and when the code runs as expected,
but when I type SET from the command prompt the variable is not listed.

I am trying to create a variable that would be listed when you type SET.


"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:uOC8biCmFHA.420@TK2MSFTNGP09.phx.gbl...
> Hi Andi,
>
> It is a generic routine to tidy up strings from APIs, which use null to
> designate the end of a string. So this routine just looks for the first
> null, and extracts all characters to the left of that.
>
> --
> HTH
>
> Bob Phillips
>
> "Andibevan" <Andibevan@notehere-hotmail.com> wrote in message
> news:%23CI5sXCmFHA.3656@TK2MSFTNGP09.phx.gbl...
> > Thanks Bob - I didn't know about using GetEnvironmentVariable.
> >
> > What does the function TrimNull do? I can't work it out.
> >
> > Ta
> >
> > Andi
> >
> >
> >
> > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> > news:e1Fd55BmFHA.3900@TK2MSFTNGP09.phx.gbl...
> > > You are correct, you do need to use an API.
> > >
> > > You should also note that Excel seems to load all variables at
startup,
> > and
> > > the Environ command retrieves the value from this memory store. So you
> > need
> > > to use the GetEnvironmentVariable to read your new value.
> > >
> > > This should demonstrate that
> > >
> > > 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
> > >
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > "Andibevan" <Andibevan@notehere-hotmail.com> wrote in message
> > > news:uwN7eiBmFHA.2060@tk2msftngp13.phx.gbl...
> > > > Hi All,
> > > >
> > > > Is there a way from within VBA to declare an environment variable?
> > > >
> > > > Or do I have to declare the kernal32 function
> "SetEnvironmentVariable"?
> > > >
> > > > Thanks
> > > >
> > > > Andy
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Andi,

It is only set for that session.

If you do Shell "CMD.EXE" immediately after the SetEnvironmentVariable, and
type Set you will see it

--
HTH

Bob Phillips

"Andibevan" <Andibevan@notehere-hotmail.com> wrote in message
news:%23d1h$sCmFHA.3380@TK2MSFTNGP12.phx.gbl...
> I have tried the method you suggested and when the code runs as expected,
> but when I type SET from the command prompt the variable is not listed.
>
> I am trying to create a variable that would be listed when you type SET.
>
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:uOC8biCmFHA.420@TK2MSFTNGP09.phx.gbl...
> > Hi Andi,
> >
> > It is a generic routine to tidy up strings from APIs, which use null to
> > designate the end of a string. So this routine just looks for the first
> > null, and extracts all characters to the left of that.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Andibevan" <Andibevan@notehere-hotmail.com> wrote in message
> > news:%23CI5sXCmFHA.3656@TK2MSFTNGP09.phx.gbl...
> > > Thanks Bob - I didn't know about using GetEnvironmentVariable.
> > >
> > > What does the function TrimNull do? I can't work it out.
> > >
> > > Ta
> > >
> > > Andi
> > >
> > >
> > >
> > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> > > news:e1Fd55BmFHA.3900@TK2MSFTNGP09.phx.gbl...
> > > > You are correct, you do need to use an API.
> > > >
> > > > You should also note that Excel seems to load all variables at
> startup,
> > > and
> > > > the Environ command retrieves the value from this memory store. So
you
> > > need
> > > > to use the GetEnvironmentVariable to read your new value.
> > > >
> > > > This should demonstrate that
> > > >
> > > > 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
> > > >
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > "Andibevan" <Andibevan@notehere-hotmail.com> wrote in message
> > > > news:uwN7eiBmFHA.2060@tk2msftngp13.phx.gbl...
> > > > > Hi All,
> > > > >
> > > > > Is there a way from within VBA to declare an environment variable?
> > > > >
> > > > > Or do I have to declare the kernal32 function
> > "SetEnvironmentVariable"?
> > > > >
> > > > > Thanks
> > > > >
> > > > > Andy
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>