ExcelVBAテクニック

１．シートへの内容変更時に、各セルに埋め込まれた計算式を実行させない。

　　シートへデータを多くのデータを設定する場合、対象シートに計算式が埋め込まれていると値が変わるたびに計算式が実行されるので、計算式数、設定する回数に比例して処理が遅くなります。
　　そこで処理速度を上げるために、処理中は計算式の実行を手動計算に切り替え処理終了後に計算式の実行を自動計算にするようにします。

Public Function Func_A() As Integer

　 Application.Calculation = xlCalculationManual '手動計算にする。

　'＝いろいろな処理を入れる。＝

　 Application.Calculation = xlCalculationAutomatic '自動計算にする。

End Function

２．シートへの変更内容を全ての処理が終了後に一括反映を行う。

　　シートへのデータを設定する場合に、そのシートを表示しているとシートへのデータ反映が見た目でも分かると思いますが、１セル毎に内容が変更しているのが分かります。その変更セルが多い場合は、１セル毎に内容を反映しているのでセル数に比例して遅くなります。
　　そこで処理速度を上げるために、シートの変更内容をセル全ての変更完了時に一括で変換するようにします。

３．テーブル形式のデータをシートに貼り付ける場合は、一括貼り付けを行う。

　　１次元、２次元のテーブル形式のデータをシートに貼り付ける場合にＦｏｒ文で回して１セル単位でデータを設定するのはデータ量が多いと非常に時間が掛かります。
　　そこで、テーブル形式のデータを一度配列に設定し、シートへ一括貼り付けするようにします。

サンプルコーディング

Public Function Func_A() As Integer
　Dim S_InData() As Variant ''シート貼り付け用領域
　Dim I_EdRow As Integer ''シート貼り付け終了行位置
　Dim I_EdCol As Integer ''シート貼り付け終了列位置
　Const I_StRow = 4; ''シート貼り付け先頭行位置
　Const I_StCol = 1; ''シート貼り付け先頭列位置

　''配列にデータを設定する
　ReDim S_InData(2,3)
　S_InData(1,1) = "AAA"
　S_InData(1,2) = "BBB"
　S_InData(1,3) = "CCC"
　S_InData(2,1) = "DDD"
　S_InData(2,2) = "EEE"
　S_InData(2,3) = "FFF"

　''シートに配列データを貼り付ける
　I_EdRow = UBound(S_InData)
　I_EdCol = UBound(S_InData,2)
　With Worksheets("Sheet1")
　　.Unprotect '保護を解除
　　.Range(.Cells(I_StRow, I_StCol), .Cells(I_EdRow,I_EdCol)).Value = S_InData() 'シートに貼り付け
　　.Protect '保護をかける
　End With

End Function

　　ちなみに縦１列の場合、横１行の場合の配列は以下のようなコーディングになります。

　縦１列、複数行の場合　　　

　ReDim S_InData(3,1)
　S_InData(1,1) = "AAA"
　S_InData(2,1) = "BBB"
　S_InData(3,1) = "CCC"

複数列、横１行の場合　　

　ReDim S_InData(3)
　S_InData(1) = "AAA"
　S_InData(2) = "BBB"
　S_InData(3) = "CCC"

４．「コントロール　ツールボックス」の部品は使わないようにする。

　　ツールバーの種類に「コントロール　ツールボックス」があります。このツールバーにはボタン、コンボボックス、オプションボタン等いろいろな部品が用意されていますが、この部品、容量が大きいようなのです。シートに沢山貼り付けると、起動時のシート描写でけっこう時間が掛かります。私はボタンを６個付けたシートを１１個複製した所、３０秒ぐらい掛かってしまいました。
　　回避策としてツールバーの種類に「フォーム」があります。設定方法はだいぶ異なりますがこの「フォーム」のボタン等の部品は、容量が小さく、シート描写も早いのでだいぶ高速化されると思います。

AAA	BBB	CCC
DDD	EEE	FFF