Excel用Visual Basic函数返回链接HTTP状态的#VALUE!报错解决咨询

错误原因 #

你遇到的#VALUE!报错主要来自两点：

1. WinHttpRequest的Open方法默认开启异步请求，Excel工作表自定义函数不支持异步运行逻辑，调用时直接触发运行时错误
2. 缺少必要的请求配置项，遇到超时、重定向场景时也会直接触发异常返回错误值

修改后可用代码 #

Public Function CheckURL(url As String)
    Dim request As Object
    Set request = CreateObject("WinHttp.WinHttpRequest.5.1")
    On Error GoTo haveError
    With request
        ' 第三个参数设为False强制同步请求，适配工作表函数运行规则
        .Open "HEAD", url, False
        ' 设置超时时间，单位毫秒，避免无响应卡死Excel
        .SetTimeouts 5000, 5000, 5000, 5000
        ' 开启自动跟随重定向，3xx状态可正确跳转获取最终状态
        .Option(6) = True
        .Send
        CheckURL = .Status
    End With
    ' 正常运行结束释放对象
    Set request = Nothing
    Exit Function
haveError:
    CheckURL = Err.Description
    ' 报错时也释放对象
    Set request = Nothing
End Function

注意事项 #

• 部分网站会拦截HEAD请求，如果遇到大面积报错，可以把.Open的第一个参数HEAD改成GET即可，缺点是请求耗时会略长
• 该函数只能在启用宏的Excel文件（.xlsm格式）中运行，且需要开启宏权限
• 首次运行时如果提示ActiveX控件创建失败，需要在系统控制面板开启WinHTTP服务支持

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