On Error Resume Next隐藏了以下错误：

If LCase(xmlDoc.getElementsByTagName("valid").Item(0).Text) = "true" Then

需要

If LCase(xmlDoc.getElementsByTagName("ns:valid").Item(0).Text) = "true" Then

这对我很有效：

Sub VATCHECK()
    Dim sURL As String
    Dim sEnv As String
    Dim xmlhttp As New MSXML2.xmlhttp
    Dim xmlDoc As New MSXML2.DOMDocument    'DOMDocument
    Dim sCountryCode As String
    Dim sVATNo As String
    Dim i As Long, ws As Worksheet, els As Object
    
    Set ws = ThisWorkbook.Sheets("VAT") '<<< use a specific sheet
    
'    On Error Resume Next 'don't use this, except for specific expected errors!!!!
'
    ws.Range("D2", Range("D2").End(xlDown)).Clear
    If ws.Cells(Rows.Count, "A").End(xlUp).Row < 2 Then Exit Sub

    For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row

        sURL = "http://ec.europa.eu/taxation_customs/vies/services/checkVatService"
        sCountryCode = ws.Range("B" & i).Value
        sVATNo = ws.Range("C" & i).Value
    
        sEnv = "<soapenv:Envelope xmlns:soapenv=""http://schemas.xmlsoap.org/soap/envelope/""" & _
               " xmlns:urn=""urn:ec.europa.eu:taxud:vies:services:checkVat:types"">"
        sEnv = sEnv & "<soapenv:Header/>"
        sEnv = sEnv & "<soapenv:Body>"
        sEnv = sEnv & "<urn:checkVat>"
        sEnv = sEnv & "<urn:countryCode>" & sCountryCode & "</urn:countryCode>"
        sEnv = sEnv & "<urn:vatNumber>" & sVATNo & "</urn:vatNumber>"
        sEnv = sEnv & "</urn:checkVat>"
        sEnv = sEnv & "</soapenv:Body>"
        sEnv = sEnv & "</soapenv:Envelope>"

        If ws.Range("A" & i).Value = 0 Then
            ws.Range("D" & i).Value = ""
        Else
            With xmlhttp
                .Open "POST", sURL, False
                .setRequestHeader "Content-Type", "text/xml;"
                .Send sEnv
                Set xmlDoc = New MSXML2.DOMDocument
                xmlDoc.LoadXML .responseText
            End With
            
            Set els = xmlDoc.getElementsByTagName("ns2:valid")
            If els.Length > 0 Then 'element is present?
                ws.Range("D" & i).Value = IIf(els(0).Text = "true", _
                                              "Valid VAT number", _
                                              "Invalid VAT number")
            Else
                ws.Range("D" & i).Value = "response error!"
                Debug.Print xmlDoc.xml '<<< check this to see what happened
            End If
        End If
                
    Next i
  
End Sub

我声明我正在处理有效的意大利增值税。
多亏了你的帮助（只是修改了对MSXML2.XMLHTTP60的引用），我成功地让脚本工作了。但是它不再提取地址和公司名称。我该怎么做呢？tks

Sub VATCHECK()
Dim sURL As String
Dim sEnv As String
Dim xmlhttp As New MSXML2.XMLHTTP60 'Dim xmlhttp As New MSXML2.xmlhttp for Microsoft XML, v 6.0
Dim xmlDoc As New MSXML2.DOMDocument60 'DOMDocument
Dim sCountryCode As String
Dim sVATNo As String
Dim i As Long, ws As Worksheet, els As Object

Set ws = ThisWorkbook.Sheets("VAT") '<<< use a specific sheet

ws.Range("D2", Range("D2").End(xlDown)).Clear
If ws.Cells(Rows.Count, "A").End(xlUp).Row < 2 Then Exit Sub

For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row

    sURL = "http://ec.europa.eu/taxation_customs/vies/services/checkVatService"
    sCountryCode = ws.Range("B" & i).Value
    sVATNo = ws.Range("C" & i).Value

    sEnv = "<soapenv:Envelope xmlns:soapenv=""http://schemas.xmlsoap.org/soap/envelope/""" & _
           " xmlns:urn=""urn:ec.europa.eu:taxud:vies:services:checkVat:types"">"
    sEnv = sEnv & "<soapenv:Header/>"
    sEnv = sEnv & "<soapenv:Body>"
    sEnv = sEnv & "<urn:checkVat>"
    sEnv = sEnv & "<urn:countryCode>" & sCountryCode & "</urn:countryCode>"
    sEnv = sEnv & "<urn:vatNumber>" & sVATNo & "</urn:vatNumber>"
    sEnv = sEnv & "</urn:checkVat>"
    sEnv = sEnv & "</soapenv:Body>"
    sEnv = sEnv & "</soapenv:Envelope>"

    If ws.Range("A" & i).Value = 0 Then
        ws.Range("D" & i).Value = ""
    Else
        With xmlhttp
            .Open "POST", sURL, False
            .SetRequestHeader "Content-Type", "text/xml;"
            .Send sEnv
            Set xmlDoc = New MSXML2.DOMDocument60  'DOMDocument
            xmlDoc.LoadXML .responseText
        End With

        
        
        Set els = xmlDoc.getElementsByTagName("ns2:valid")
       If els.Length > 0 Then 'element is present?
            ws.Range("D" & i).Value = IIf(els(0).Text = "true", _
                                          "Valid VAT number", _
                                          "Invalid VAT number")
        Else
            ws.Range("D" & i).Value = "response error!"
            Debug.Print xmlDoc.XML '<<< check this to see what happened
        End If
    End If
            
Next i

结束子组件