当显示UserForm时，Excel 2010 VBA代码被卡住 [英] Excel 2010 VBA code is stuck when UserForm is shown

问题描述

在后台运行Web查询（使用InternetExplorer对象）时，我创建了一个UserForm作为进度指示器。代码被触发，如下所示。进度指标表格称为Progerss。

  Private Sub Worksheet_Change（ByVal Target As Range）
如果Target。 Row = Range（B2）。Row和Target.Column = Range（B2）。Column Then 
 Progress.Show vbModeless 
 Range（A4：A65535）。ClearContents 
 GetWebData（Range（B2）。Value）
 Progress.Hide 
 End If 
 End Sub 
  

我用这段代码看到的是当单元格B2更改时弹出进度指示器窗体。我也看到列A中的单元格范围被清除，这告诉我，vbModeless正在做我想要的。但是，在GetWebData（）过程的某个地方，事情会挂起来。一旦我手动销毁进度指示器表单，GetWebData（）例程完成，我看到正确的结果。但是如果我将进度指示器显示出来，事情就会无限期地被卡住。

下面的代码显示了GetWebData（）正在做什么。

$



MyURL
Do
DoEvents
循环直到IE.readyState = READYSTATE_COMPLETE
Dim Doc As HTMLDocument
设置Doc = IE.document
Dim Rows As IHTMLElementCollection
Set Rows = Doc.getElementsByClassName（financialTable）。Item（0）.all.tags（tr）

Dim r As Long
r = 0
每行In Rows
Sheet1.Range（A4）。Offset（r，0）.Value = Row.Children.Item（0）.innerText
r = r + 1
下一个

End Sub

任何想法？

解决方案

如果您没有结婚使用Excel Web Query，而您只是想要进度在数据加载时出现的栏，您可以尝试打开URL作为单独的工作簿。如果你这样做，Excel会免费提供一个进度条。

看到我对这个问题的回答：如何将数据进行后处理查询完成后的Excel网络查询？

该方法的折衷是您必须管理处理您自己恢复的数据 - Excel赢得了

我们尝试了与您似乎已经在做的事情非常相似的路线，结束了这条路线。

I've created a UserForm as a progress indicator while a web query (using InternetExplorer object) runs in the background. The code gets triggered as shown below. The progress indicator form is called 'Progerss'.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = Range("B2").Row And Target.Column = Range("B2").Column Then
        Progress.Show vbModeless
        Range("A4:A65535").ClearContents
        GetWebData (Range("B2").Value)
        Progress.Hide
    End If
End Sub

What I see with this code is that the progress indicator form pops up when cell B2 changes. I also see that the range of cells in column A gets cleared which tells me that the vbModeless is doing what I want. But then, somewhere within the GetWebData() procedure, things get hung up. As soon as I manually destroy the progress indicator form, the GetWebData() routine finishes and I see the correct results. But if I leave the progress indicator visible, things just get stuck indefinitely.

The code below shows what GetWebData() is doing.

Private Sub GetWebData(ByVal Symbol As String)
     Dim IE As New InternetExplorer
    'IE.Visible = True
    IE.navigate MyURL
    Do
        DoEvents
    Loop Until IE.readyState = READYSTATE_COMPLETE
    Dim Doc As HTMLDocument
    Set Doc = IE.document
    Dim Rows As IHTMLElementCollection
    Set Rows = Doc.getElementsByClassName("financialTable").Item(0).all.tags("tr")

    Dim r As Long
    r = 0
    For Each Row In Rows
        Sheet1.Range("A4").Offset(r, 0).Value = Row.Children.Item(0).innerText
        r = r + 1
    Next

End Sub

Any thoughts?

解决方案

If you're not married to using Excel Web Query, and you just want a progress bar to appear while your data loads, you might try opening the URL as a separate Workbook instead. If you do, Excel gives you a progress bar for free.

See my answer on this question: How can I post-process the data from an Excel web query when the query is complete?

The tradeoff of that approach is you have to manage processing the data you get back yourself - Excel won't put it in a given destination for you.

We ended up going this route after we tried something pretty similar to what you seem to have been doing.

这篇关于当显示UserForm时，Excel 2010 VBA代码被卡住的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！