vba - 从 Excel VBA 检查 Internet 连接

Question

打开 Excel 工作簿时，我需要检查 Internet 连接。为此，我正在尝试以下代码:

Private Declare PtrSafe Function InternetGetConnectedState _
   Lib "wininet.dll" (ByRef dwflags As Long, _
   ByVal dwReserved As Long) As Long

Private Const INTERNET_CONNECTION_MODEM As Long = &H1
Private Const INTERNET_CONNECTION_LAN As Long = &H2
Private Const INTERNET_CONNECTION_PROXY As Long = &H4
Private Const INTERNET_CONNECTION_OFFLINE As Long = &H20
Function IsInternetConnected() As Boolean
    Dim L As Long
    Dim R As Long
    R = InternetGetConnectedState(L, 0&)
    If R = 0 Then
        IsInternetConnected = False
    Else
        If R <= 4 Then
            IsInternetConnected = True
        Else
            IsInternetConnected = False
        End If
    End If
End Function

它有效，但一些用户报告说这破坏了 Excel(它永远卡住了 Excel)。

有没有办法抛出错误或以某种方式纠正此代码以找出问题的原因(防火墙等)？

非常感谢您的帮助!

Answer 1

以下是带有编译器指令的正确导入。

Option Explicit

#If Win64 Then
    Public Flg As LongPtr
    Public Declare PtrSafe Function InternetGetConnectedState _
            Lib "wininet.dll" (lpdwFlags As LongPtr, _
            ByVal dwReserved As Long) As Boolean
#Else
    Public Flg As Long
    Public Declare Function InternetGetConnectedState _
            Lib "wininet.dll" (lpdwFlags As Long, _
            ByVal dwReserved As Long) As Boolean
#End If

Private Const INTERNET_CONNECTION_MODEM As Long = &H1
Private Const INTERNET_CONNECTION_LAN As Long = &H2
Private Const INTERNET_CONNECTION_PROXY As Long = &H4
Private Const INTERNET_CONNECTION_OFFLINE As Long = &H20

Function IsInternetConnected() As Boolean
    Dim R As Long

    R = InternetGetConnectedState(Flg, 0&)

    If Flg >= INTERNET_CONNECTION_OFFLINE Then
        Debug.Print "INTERNET_CONNECTION_OFFLINE"
    End If

    If CBool(R) Then
        IsInternetConnected = True
    Else
        IsInternetConnected = False
    End If
End Function

Sub main()
    Dim mssg As String
    If IsInternetConnected Then
        mssg = "Connected"
    Else
        mssg = "Not connected"
    End If
    MsgBox mssg
End Sub

我相信至少有一个问题是未能提供正确的 var 类型来接收 lpdwFlags。 .我在编译器指令中添加了一个公共(public)变量。 Flg var 从函数接收标志。这些可以根据您的常量逐位解析以确定状态。见 InternetGetConnectedState了解更多信息(以及一整套标志)。

这已经在 32 位和 64 位 xl2010 上进行了测试。