VBA操作IE时readyState=4无限循环偶发问题求助

Hey there, let's tackle that frustrating intermittent loop issue you're facing with your VBA script accessing city-data.com. First, let's recap your code (I filled in the missing URL part for clarity):

Dim IE As Object
Dim pageaddress As String
Dim city As String
Dim state As String

city = "Chicago"
state = "Illinois"

Set IE = CreateObject("internetExplorer.Application")
IE.Visible = True
pageaddress = "http://www.city-data.com/city/" & city & "-" & state & ".html"
IE.Navigate pageaddress

' Original loop that sometimes gets stuck
Do While IE.ReadyState <> 4
    DoEvents
Loop

Why This Happens Intermittently #

The readyState=4 only means the DOM has loaded, but modern sites like city-data often run asynchronous JavaScript to load content after the initial page load. Here are the most likely culprits:

• Unfinished JS Execution: Even with readyState=4, the page might still be fetching data or rendering elements via JS, leaving IE in a "busy" state that your original loop doesn't check.
• Anti-Scraping Measures: The site might have updated its anti-bot logic, which occasionally triggers delays or hangs that your script doesn't handle.
• Network/IE Quirks: Intermittent network blips or IE's internal caching/security settings can cause unexpected state mismatches.

Solutions to Fix the Loop #

1. Improve the Wait Logic (Add Busy Check + Timeout)

The biggest flaw in the original code is not checking IE.Busy and not having a timeout. Replace your loop with a robust wait function that handles both:

Sub WaitForIE(IE As Object, Optional TimeoutSeconds As Integer = 30)
    Dim startTime As Date
    startTime = Now
    
    ' Wait until IE is not busy AND readyState is complete
    Do While IE.Busy Or IE.ReadyState <> 4
        DoEvents ' Let Windows process other events
        
        ' Break loop if timeout is reached
        If DateDiff("s", startTime, Now) > TimeoutSeconds Then
            MsgBox "Page load timed out after " & TimeoutSeconds & " seconds", vbExclamation
            Exit Do
        End If
    Loop
    
    ' Add a short delay to ensure JS finishes rendering content
    Application.Wait Now + TimeValue("00:00:02")
End Sub

Then update your main code to use this function:

Dim IE As Object
Dim pageaddress As String
Dim city As String
Dim state As String

city = "Chicago"
state = "Illinois"

Set IE = CreateObject("internetExplorer.Application")
IE.Visible = True
' Give IE a moment to initialize before navigating
Application.Wait Now + TimeValue("00:00:01")

pageaddress = "http://www.city-data.com/city/" & city & "-" & state & ".html"
IE.Navigate pageaddress

' Use the improved wait function
WaitForIE IE

' Your subsequent code to interact with the page goes here...

' Clean up properly
IE.Quit
Set IE = Nothing

2. Adjust IE Settings

Occasionally, IE's security settings or pop-up blockers can interfere with page loading:

• Go to IE's Internet Options > Security and add city-data.com to the Trusted Sites list.
• Disable the pop-up blocker (or add an exception for the site) since unexpected pop-ups can hang the script.

3. Switch to XMLHTTP (If Visual Interaction Isn't Needed)

If you don't need to see the IE window or interact with page elements, using MSXML2.XMLHTTP is more stable and faster than controlling IE. Here's a quick example:

Dim xmlHttp As Object
Dim pageSource As String

Set xmlHttp = CreateObject("MSXML2.XMLHTTP.6.0")
xmlHttp.Open "GET", "http://www.city-data.com/city/Chicago-Illinois.html", False
xmlHttp.send

If xmlHttp.Status = 200 Then
    pageSource = xmlHttp.responseText
    ' Parse the HTML content here using methods like Split or regex
Else
    MsgBox "Failed to load page. Status code: " & xmlHttp.Status
End If

Set xmlHttp = Nothing

Final Notes #

If the issue still happens occasionally, check for:

• Outdated IE versions (IE11 is the last supported version; older ones have more quirks).
• Browser extensions (like ad blockers) that might interfere with page loading in IE.
• Anti-bot checks that might trigger occasionally—you might need to add a longer delay to let these processes complete.

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