Excel グラフ自動作成が遅い VBA

質問

いつもお世話になっております。

今回は、大量のグラフを作る際に動作が異常に遅いため質問させて頂きました。
http://okwave.jp/qa/q8384381.html

下記リンクにあるように、グラフを作成しています。
Function GlaphSetting() 'グラフ色などの設定
Dim C As ChartObject
On Error Resume Next

For Each C In ActiveSheet.ChartObjects
C.Chart.Axes(xlCategory).TickLabels.Orientation = xlVertical '縦書き調整
C.Chart.ChartGroups.GapWidth = 70 '幅を70に
C.Chart.ChartArea.Font.Size = 9 '全部のフォントサイズ
C.Chart.PlotArea.Interior.ColorIndex = xlNone 'プロットエリア色なし
C.Chart.PlotArea.Border.ColorIndex = xlNone '枠線なし
C.Chart.Axes(xlValue).HasMajorGridlines = False 'メモリ見えなくする
C.Chart.Axes(xlValue).MinimumScale = 0 'メモリ最小値
Next
End Function

実行すると、データ量により物凄く遅くなります。
（グラフの数は14、項目数52）※今のところの最大量

実行すると、5分程度はかかるでしょうか・・・

ためしにメッセージボックスを使い、
ストップウォッチで計ってみました。

データ処理　　　　　：2秒
その他の処理　　　：2秒
グラフ作成処理　　：1分12秒
グラフの編集　　　 ：4秒

でした。
グラフの作成処理をどうにか早くできないでしょうか？

回答よろしくお願い致します。

回答No.3

mitarashi · ベストアンサー

#1です。
古いWinXPのノートを引っ張り出してやってみました。
PentiumM 1.3GHzで2003年発売のものです。xl2000です。
結論から言うと再現されました。

DataLabelを生かしたとき。
グラフ作成 146.841sec
グラフ修飾 3.635sec

DataLabel設定をコメントアウトしたとき
グラフ作成 3.815sec
グラフ修飾 0.521sec

.DataLabel.Text = myRange.Columns(3).Cells(i).Valueのところをセルアクセスでなく、Variant配列に入れてやってみましたが、1秒強くらい速くなっただけで焼け石に水でした。
申し訳ありませんが、昔のシステムにとっては重い処理らしいので時間がかかるのは仕方がないとしか言えません。

なお、今となってはあまり役に立たない情報ですが、xl2000では
.ApplyDataLabels ShowValue:=True
がエラーとなり、単に
.ApplyDataLabels
とする必要がありました。

お礼

回答ありがとうございます！

わざわざ確認作業までして頂き、ありがとうございます。

今はWindows7の2.6GHzを使用していますが、
やはり仕方の無いことなのですね…

ありがとうございました！

回答No.2

mitarashi

#1です
言葉足らずでした。
「カメラ」機能を用いた別のワークブックを同時に開いているだけでも、VBAの実行が遅くなる事があるそうです。
ご参考まで。

回答No.1

mitarashi

１．下記のコードを試してみました。
http://okwave.jp/qa/q8384381.html
GraphMakeの最後に、GlaphSettingも実行させて、それぞれの段階でWindowsAPIのGetTickCountで計測しています。

乱数で、Max100要素のデータを26個分生成して試験しました。試験データ作成コードは後に載せます。
当方の環境はCore i5-3470 CPU 3.20GHz、xl2010です。

グラフ作成　4.446 秒　←　分の間違いではありません、秒です
グラフ設定　0.031秒

最初に Application.ScreenUpdating = False、最後にApplication.ScreenUpdating = Trueを入れた場合
グラフ作成　0.39 秒
グラフ設定　0.016 秒
ただ、Application.ScreenUpdating = Trueにした後の画面描画に数秒かかります。

という訳でたとえ数年前のPCであっても数分もかかるとは思えません。
「カメラ」機能を用いていると、VBAの実行が著しく遅くなる現象が発生するそうですが、該当しませんか？
あるいは、膨大な数式が入っていて、ソーティング後に再計算がかかって時間を食ったりしていませんか？
ご検討下さい。

２．リンク先のコードで気付いた点
・C列でのtargetRangeのソーティングは、下記の一行で良いと思います。
targetRange.Sort key1:=targetRange.Range("C1"), order1:=xlAscending
・For Each myArea In dataRange.SpecialCells(xlCellTypeConstants).Areasのループ中で、graphColumnsを変化させておかしな事になりませんか。

３．試験データ生成コード
Sub makeTestData()
Dim i As Long, j As Long, dataCount As Long
Dim mycell As Range
Dim myCollection As Collection
Dim pickUp As Long

'Collectionにアルファベット大文字26文字を収納
Set myCollection = New Collection
For i = 1 To 26
myCollection.Add Item:=Chr(64 + i)
Next i

Randomize Time
ThisWorkbook.Sheets(1).Cells.Clear
Set mycell = ThisWorkbook.Sheets(1).Range("A1")
For i = 1 To 26
'
pickUp = Int(Rnd() * myCollection.Count) + 1
mycell.Value = myCollection(pickUp)
myCollection.Remove pickUp
Set mycell = mycell.Offset(1, 0)
dataCount = Int(100 * Rnd()) + 1
For j = 1 To dataCount
mycell.Value = j
mycell.Offset(0, 1).Value = 20 * Rnd
mycell.Offset(0, 2).Value = Chr(65 + Int(Rnd() * 26))
Set mycell = mycell.Offset(1, 0)
Next j
Set mycell = mycell.Offset(1, 0)
Next i
End Sub

補足

回答ありがとうございます！

カメラ機能というものは、使ったことがありません。
（使い方すら知りません）

Application.ScreenUpdating=False
に設定してあります。

もうリンクの所は削除してしまったのですが、
グラフを作る際に
'======================--
Sub makeGraph(myRange As Range, myChartObj As ChartObject)
Dim mySeries As Series
Dim i As Long

With myChartObj.Chart
Set mySeries = .SeriesCollection.NewSeries
mySeries.XValues = myRange.Columns(1)
mySeries.Values = myRange.Columns(2)
.ChartType = xlColumnClustered
.HasTitle = True
.HasLegend = False
.ChartTitle.Text = myRange.Cells(1).Item(0).Value
.ApplyDataLabels ShowValue:=True
End With
For i = 1 To mySeries.Points.Count
With mySeries.Points(i)
.HasDataLabel = True
.DataLabel.Text = myRange.Columns(3).Cells(i).Value
End With
Next i
End Sub
'======================--
（内側上にデータラベルはあとから追加しました。）

上記の方法を使っていましたが、
For i = 1 To mySeries.Points.Count
With mySeries.Points(i)
.HasDataLabel = True
.DataLabel.Text = myRange.Columns(3).Cells(i).Value
End With
Next i
上記部分がとても時間がかかっている事がわかりました。
これをコメントアウトすると、
時間が4分の1以下になり、重くならずにすみました。

上記部分でやりたいことは、
targetrangeの3列目部分を
データラベルとして表示したいのです。

どう変更したら、時間がかからなくなるでしょうか？