Why do i get "Argument is not optional" error on simple url call?

Question

My goal is to check the internet connecton in VBA but when i try to call a url with request.open I get the error message "Argument is not optional".

Sadly internet research has no yielded any results.

This is my code:

On Error GoTo NoConnectionErrorHandling
    Dim Request As MSXML2.XMLHTTP60
    Request.Open "http://www.google.com"
    Request.send
    MsgBox Request.Status

It hangs itself in the third line of the shown code. I hope someone can help me as i have very very little experience in VBA yet.

Answer 1

You have to specify the type of the request. It can be "GET", "POST" or something else.

See here for the open Method: https://msdn.microsoft.com/en-us/library/ms757849(v=vs.85).aspx

These are the parameters:

• bstrMethod The HTTP method used to open the connection, such as GET, POST, PUT, or PROPFIND. For XMLHTTP, this parameter is not case-sensitive. The verbs TRACE and TRACK are not allowed when IXMLHTTPRequest is hosted in the browser. What is the difference between POST and GET?

• bstrUrl The requested URL. This can be either an absolute URL, such as "http://Myserver/Mypath/Myfile.asp", or a relative URL, such as "../MyPath/MyFile.asp".

• varAsync[optional]

• bstrUser[optional]

• bstrPassword[optional]

As you see, the method needs at least two parameters (the other 3 are optional) thus 1 is not enough.

You need to declare Request with the keyword New. Thus, something like the following piece works:

Public Sub TestMe()    
    Dim Request As New MSXML2.XMLHTTP60
    Request.Open "GET", "http://www.bbc.com"
    Request.send
    MsgBox Request.Status    
End Sub

---

Whenever you are using libraries outside the standard VBA libraries it is a good idea to do one of the following 2:

• Provide information for the library:

• Use late binding:

---

Dim Request As Object
Set Request = CreateObject("Msxml2.ServerXMLHTTP.6.0")