Webクエリのダウンロード時間がかかるようになった - 人はエクセルの能力の10%も使っていない？

Webクエリを使い続けていると、3秒程度だったダウンロード時間が1分以上になることがあります。あちこちのサイトを検索してもはっきりした原因はどこにも書いてありませんが、やっと原因を特定できたので情報公開することにします。おそらくこれがファイナルアンサーです。どこのブログにも書いてないので、これが初公開のはず！

500個以上の名前がいつのまにか・・・！？

イミディエイトウィンドウ（CTRL+G)でたまたま ?Thisworkbook.Names.Countとやってみたところ500個以上の数字が表示されました。２個くらいなら名前を定義したけど、500個も定義した覚えがなかったので、?Thisworkbook.Names(1)で中身を表示させてみたら、”=Sheet3!$A$1:$A$136” のような名前が山ほど定義されていました。

明らかに犯人はWebクエリでした。とりあえず、こんなコードを書いて名前を全部消してみたら、あれだけ遅かったWebクエリがなんと３秒でダウンロード完了できるようになったんです。あとは、Webクエリを実行したあとでClearNamesをコールすればいいだけです。

Public Sub ClearNames()

    Dim oName As Name
    For Each oName In ThisWorkbook.Names
        if oName.Name Like "*deleteme*" Then oName.Delete
        DoEvents ' <== 500個以上だとエクセルが応答しない状態になります
    Next oName
End Sub

なぜ名前が残ってしまうのか？

Webクエリのパフォーマンス遅延問題が解決したので、増えていく名前問題は忘れることにしました。ただ、今回ブログの記事を書こうと思ってサンプルのVBAを作っていた時に、この消えない名前の原因も分かりました。

おそらく、ほとんどの人はこんなコードを書いていると思います。QueryTable.Deleteで前回のクエリ結果を消して、ClearContentsでシートをきれいにしてから、Webクエリを実行。

On Error Resume Next
wksTmpSheet.Cells.QueryTable.Delete
wksTmpSheet.Cells.ClearContents

With wksTmpSheet.QueryTables.Add(Connection:="URL;" _ & sURL, Destination:=wksTmpSheet.Range("$A$10"))
  .Name = "deleteme"
  .FieldNames = True
  .RowNumbers = False
  ....
  ....
  .Refresh BackgroundQuery:= False