ＶＢＡの実行速度短縮についてのご相談。

質問

いつもお世話になっております、ご多忙のところ申し訳ありません。

動作環境 Excel 2003 です。

現在業務の中で、「Ａブック（ＶＢＡが組み込まれてる方）とＢブック（統合したいデータ）を重ねて新しいブックを作成する」といった構文を使用しているのですが、

＊－－－－－－－－－－－－－－－－－－－－－－－－－＊

Option Explicit

Dim ConvinedSheet As Worksheet

Public Sub Init()

Set ConvinedSheet = Workbooks.Add.Worksheets(1)
End Sub

Sub AddSheetData(Sh As Worksheet)
Dim DataStartRow As Range
Set DataStartRow = LastUsedDataRow(ConvinedSheet).Offset(1, 0)

Dim DataRange As Range
Sh.Calculate
Set DataRange = Range(Sh.Cells(1, 1), Sh.Cells.SpecialCells(xlCellTypeLastCell))
Dim Col As Range
For Each Col In DataRange.Columns
Col.Copy
Dim PasteTarget As Range
Set PasteTarget = DataStartRow.Cells(1, ColumnNumber(Col.Cells(1).Value))
PasteTarget.PasteSpecial xlPasteValuesAndNumberFormats
Next
DataStartRow.EntireRow.Delete Shift:=xlShiftUp
End Sub

Private Sub test()
Init
AddSheetData ThisWorkbook.Sheets("Sample1")
AddSheetData ThisWorkbook.Sheets("Sample2")
End Sub

'シートの中で、最後に使われたデータ保管のための行を返す
'1行目は列名が入るので除く
Private Function LastUsedDataRow(Sh As Worksheet) As Range
Dim UsedRow As Range
Set UsedRow = Sh.Cells.SpecialCells(xlCellTypeLastCell).EntireRow
While WorksheetFunction.CountA(UsedRow) = 0 And UsedRow.Row > 1
Set UsedRow = UsedRow.Offset(-1, 0)
Wend
Set LastUsedDataRow = UsedRow
End Function

Private Function ColumnNumber(FieldName As String) As Long
Dim c As Range
For Each c In ConvinedSheet.Rows(1).Cells
If CStr(c.Value) = FieldName Then
ColumnNumber = c.Column
Exit For
ElseIf CStr(c.Value) = "" Then
c.Value = FieldName
ColumnNumber = c.Column
Exit For
End If
Next

End Function

＊－－－－－－－－－－－－－－－－－－－－－－－－－＊

実行した所、少々時間がかかってしまいまして今後の業務に向けて時間を短縮出来ないかなと思いましてこの度のご質問になりました。

ご多忙の所申し訳ありませんが、ご指導の程お願い申し上げます。

回答No.2

neKo_deux · ベストアンサー

一般的な対策だと、

処理開始前に、
Application.ScreenUpdating = False

処理終了後に、
Application.ScreenUpdating = True

として、処理中に余計な画面なんかの更新を止めておく事で、そこそこ改善するハズ。

他には、処理内容は詳細に見ていませんが、Copy、PasetSpecialなんかのメソッドを使用してクリップボードを経由すると、処理が重くなるような気はします。

お礼

処理コードの前にサンプルコードを入れ込んだら計測していた実行速度より速くなりました。
PasetSpecialとクリップボードを経由すると重たくなるんですね・・・、色々考察してみてコードを修正したいと思います。
ありがとうございました！

回答No.3

bardfish

他の方法としては、でバックモードでステップ実行を行い、どこが一番時間がかかるかを調べ、別のコードで実現するよう修正するという手法もあります。

お礼

なるほど。ステップインして考察しておりましたが、処理速度が遅くなった原因を少しだけつかめた気がします。
ご多忙のところありがとうございました。

回答No.1

DIooggooID

大量にデータ処理をする場合、

　Ｅｘｃｅｌ　のオプションで、計算方法を　自動　から　手動　にするだけでも
効果があると思います。

お礼

ご多忙のところありがとうございました。