Excel VBA，Http.ResponseText 运行时错误 91

Question

使用 Excel 2010 与 VBA 7.0

代码在 this video一直工作到 10:01。当执行该行时
Dim Resp As String: Resp = Http.ResponseText
我得到一个 Runtime error 91 Object variable or With block variable not set .

问题:如何解决此错误消息？

Private Sub btnRefresh_Click()
 Dim W As Worksheet: Set W = ActiveSheet
 Dim Last As Integer: Last = W.Range("A100").End(xlUp).Row
 Dim Symbols As String
 Dim i As Integer
 Dim URL As String
 Dim Http As WinHttpRequest
 If Last = 1 Then Exit Sub

 For i = 2 To Last
   Symbols = Symbols & W.Range("A" & i).Value & "+"
 Next i

 Symbols = Left(Symbols, Len(Symbols) - 1)
 URL = "http://finance.yahoo.com/d/quotes.csv?s=" & Symbols & "&f=snl1hg"
 Http = New WinHttpRequest
 Http.Open "GET", URL, False
 Http.Send

 Dim Resp As String: Resp = Http.ResponseText
  Debug.Print Symbols
 Debug.Print URL
End Sub

Answer 1

解决这个问题的两种方法。

1) 设置对 Microsoft WinHttp 5.1 库的引用(VBA 中的工具-> 引用工具栏)并修复以下代码行:

改变

Dim Http As WinHttpRequest     'Incorrect
Dim Http As New WinHttpRequest 'Correct

删除

Http = New WinHttpRequest      'Incorrect

2)您也可以使用单独的对象执行此操作，这将避免在 VBA 中设置库引用:

Private Sub btnRefresh_Click()
  Dim W As Worksheet: Set W = ActiveSheet
  Dim Last As Integer: Last = W.Range("A100").End(xlUp).Row
  Dim Symbols As String
  Dim i As Integer
  Dim URL As String

  If Last = 1 Then Exit Sub

  For i = 2 To Last
    Symbols = Symbols & W.Range("A" & i).Value & "+"
  Next i

  Symbols = Left(Symbols, Len(Symbols) - 1)

  URL = "http://finance.yahoo.com/d/quotes.csv?s=" & Symbols & "&f=snl1hg"

  ' Create object here
  Dim HTTP As Object
  Set HTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
  HTTP.Open "GET", URL
  HTTP.Send

  'Test Response
  MsgBox HTTP.ResponseText

End Sub