如何在Excel中显示操作系统环境变量？

I have an application written in Excel plus a bunch of C++ / Python addins.

我有一个用Excel编写的应用程序加上一堆C ++ / Python插件。

The location of the various config files used by the addins is determined at startup time by a number of environment variables. I'd like to debug a problem related to these environment variables by the most direct possible means: Can I simply type in an Excel formula which will cause an environment variable to display in the worksheet?

addins使用的各种配置文件的位置在启动时由许多环境变量确定。我想通过最直接的方法调试与这些环境变量相关的问题:我可以简单地输入一个Excel公式,这将导致环境变量显示在工作表中吗?

Let me give you an example:

让我给你举个例子:

I have an environment variable called "MYADDIN_XML_CONFIG" which contains the path to an XML file used by the MyAddin component. If this environment variable is set incorrectly then MyAddin will fail to run. I'd like to have a single simple function which takes the string "MYADDIN_XML_CONFIG" as an argument and returns the value of the env-var if it is set. If the environment variable is not set it should return a NONE or some kind of error code.

我有一个名为“MYADDIN_XML_CONFIG”的环境变量,它包含MyAddin组件使用的XML文件的路径。如果此环境变量设置不正确,则MyAddin将无法运行。我想要一个简单的函数,它将字符串“MYADDIN_XML_CONFIG”作为参数,并返回env-var的值(如果已设置)。如果未设置环境变量,则应返回NONE或某种错误代码。

Can this be done?

可以这样做吗?

FYI, MS Excel 2003 on Windows XP.

仅供参考,Windows XP上的MS Excel 2003。

3 个解决方案

#1

I don't know any built-in Excel formula that does this, but you can create a public function in a VBA module:

我不知道执行此操作的任何内置Excel公式,但您可以在VBA模块中创建公共函数:

Public Function Env(Value As Variant) As String
    Env = Environ(Value)
End Function

then use this as a user-defined formula in a worksheet, e.g.

然后将其用作工作表中的用户定义公式,例如

=Env("COMPUTERNAME")

#2

Use the VBA function Environ("MYADDIN_XML_CONFIG").

使用VBA功能Environ(“MYADDIN_XML_CONFIG”)。

#3

Unfortunately the accepted solution won't work if your environment variable changes its value (even if you reopen the Excel workbook, the value of the cell won't change). If you want to have it updated more often you should update cell's value on some event,

遗憾的是,如果环境变量更改其值,则接受的解决方案将不起作用(即使重新打开Excel工作簿,单元格的值也不会更改)。如果您想更频繁地更新它,您应该在某些事件上更新单元格的值,

e.g. Worksheet_Activate (code should be placed in VBAProject->Microsoft Excel Objects->Sheet which should be observed):

例如Worksheet_Activate(代码应该放在VBAProject-> Microsoft Excel Objects-> Sheet中,应该遵守):

Private Sub Worksheet_Activate() 
    Range("a1") = Environ("SOME_ENV_VARIABLE")
End Sub

or Workbook_Open (code should be placed in VBAProject->Microsoft Excel Objects->ThisWorkbook):

或Workbook_Open(代码应放在VBAProject-> Microsoft Excel Objects-> ThisWorkbook中):

Private Sub Workbook_Open()
    Range("a1") = Environ("SOME_ENV_VARIABLE")
End Sub

#1