VBA调用PowerShell传递变量实现Windows 10通知弹窗失败排查

问题情况 #

原本基于参考代码可以正常实现Windows 10通知弹窗，但尝试在PowerShell命令中传递VBA变量sMessage时，程序无报错却无法弹出通知。

原正常运行的VBA代码 #

Public Sub Notify_Test()

Dim WsShell     As Object: Set WsShell = CreateObject("WScript.Shell")
Dim strCommand  As String

strCommand = "powershell.exe -Command " & Chr(34) & "& { "
strCommand = strCommand & "[reflection.assembly]::loadwithpartialname('System.Windows.Forms')"
strCommand = strCommand & "; [reflection.assembly]::loadwithpartialname('System.Drawing')"
strCommand = strCommand & "; $notify = new-object system.windows.forms.notifyicon"
strCommand = strCommand & "; $notify.icon = [System.Drawing.SystemIcons]::Information"
strCommand = strCommand & "; $notify.visible = $true"
strCommand = strCommand & "; $notify.showballoontip(10,'New Chat!','You have received New Chat!',[system.windows.forms.tooltipicon]::None)"
strCommand = strCommand & " }" & Chr(34)
Shell strCommand, vbHide
End Sub

出错的尝试代码 #

#    '" & sMessage & "'
 strCommand = strCommand & "; $notify.showballoontip(10,'New Chat!','" & sMessage & "',[system.windows.forms.tooltipicon]::None)"

问题根源 #

经排查，问题出在变量sMessage未做预处理：如果变量中包含撇号（单引号），会破坏PowerShell命令中用单引号包裹的字符串结构，导致命令解析失败，进而无法弹出通知。

修复方案 #

在传递变量前，对sMessage中的单引号进行转义处理——在VBA中将单引号替换为两个连续的单引号（PowerShell中，单引号字符串内的两个单引号会被解析为一个单引号）。

示例修复代码：

' 先处理变量中的单引号
sMessage = Replace(sMessage, "'", "''")

' 再拼接命令
strCommand = strCommand & "; $notify.showballoontip(10,'New Chat!','" & sMessage & "',[system.windows.forms.tooltipicon]::None)"

内容的提问来源于stack exchange，提问作者Grmn