Reload environment variables without restarting Excel?

user_3111149 0
Viewed 557

I use the function Environ() to get environment variables from a running Excel instance. When a new variable is defined in the system, Excel won't know it until it doesn't get restarted.

My question is: is there any way I can get the new value of the environment variables without need to restart Excel?

The test case is:

  • Windows search / System / Advanced system settings / Environment variables
  • Open Microsoft Excel
  • Define a new user variable, say TESTING, with value Whatever

enter image description here

  • Run the following macro:

    Sub test()

    MsgBox "TESTING:" & Environ("TESTING")

End Sub

... the variable is empty:

enter image description here

  • Restart Microsoft Excel
  • Re-run the same macro again: the variable is now loaded.

enter image description here

environment-variables vba excel
2 Answers
Sub Test()

    MsgBox CreateObject("WScript.Shell").Environment("system").Item("testing")

End Sub
user_21657590

You need to subclass your excel window and intercept wm_settingchange messages that are sent from the control panel environment thing and setx.

This is VB.Net - lparam contains a pointer to a string saying Environment. It's up to you to work out what has changed.

Console.writeline(Marshal.PtrToStringUni(lparam))

Back to VB6/VBA

Here some code that hooks and unhooks and a winproc. You get Excel's hWnd from excel.application hWnd property.

Public Sub Hook()
   lpPrevWndProc = SetWindowLong(EditNote.gRtfHwnd, GWL_WNDPROC, _
   AddressOf gWindowProc)
End Sub

Public Sub Unhook()
   Dim temp As Long
   temp = SetWindowLong(EditNote.gRtfHwnd, GWL_WNDPROC, lpPrevWndProc)
End Sub

Public Function gWindowProc(ByVal hwnd As Long, ByVal Msg As Long, _
                 ByVal wParam As Long, ByVal lParam As Long) As Long
   If Msg = WM_CONTEXTMENU Then
        If EditNote.mnuViewEditContextMenu.Checked Then EditNote.PopupMenu EditNote.mnuEdit
'        gWindowProc = CallWindowProc(lpPrevWndProc, hWnd, Msg, wParam, _
         lParam)
   Else ' Send all other messages to the default message handler
      gWindowProc = CallWindowProc(lpPrevWndProc, hwnd, Msg, wParam, _
         lParam)
   End If
End Function

Each application gets a copy of it's parent's environment memory block. A program cannot access another program's memory - so it's one way. Only Windows Explorer listens to this message. So an updated variable is only available in Windows Explorer and any new program started by explorer. Note: CMD does not listen for this.

user_109145910
Related