前回の続きになります。
VBAに必須！？高速化処理
その３までで触れませんでしたが、処理の内容が増えるとそれだけ完了までの時間は長くなります。そのための対策として高速化処理というものや、処理がたくさん増えることによって注意しなければならない点がいくつかありますが、今回は、たった二行で簡単にできる高速化処理について説明いたします。
※注意しなければならない点については、高速化処理も含めて別記事にて再度まとめようかと思います。
処理の最初と最後に以下の２行を付け足すだけです。
Application.ScreenUpdating = False
処理の内容
Application.ScreenUpdating = True
ざっくり説明すると、上の１行で以下の処理に関する表示をオフにして、処理が終わった後に下の１行で処理に関する表示をオンにしています。
あるとないとでは、だいぶ違います。処理の内容が増えれば増えるほど、その恩恵を感じられると思います。
その３以降で取り入れた内容
結合セルの解除
依頼主の方から、「B列とC列が結合しているので結合解除できないか」と新たに依頼がありました。導入は簡単でした。以下コードです。
For i ＝ １ To m
Sheets(i).Columns(“B:C”).Unmerge
シートに関しては、データ元の全てのシートが対象なので、変数でこのように指定しています。”Columns“で列を指定し、”Unmerge“で結合解除を行います。
トータルに関する処理
各データ元のシートには必ず”トータル“という項目があり、右隣のセルにはSUM関数が入っていました。その３までの内容は「項目が一致すれば、対応する値をコピペする」という内容のため、不適切な関数が集約データのシートに入ってしまい、表示値がおかしかったです。
また、”トータル“は各データ元シートの最下行に位置するため、集約データシートも同様に最下行に位置させるのが適当かと思い、依頼主に提案しました。
そこで考えたのが以下の処理内容およびコードになります。
- 張り付けシートの項目名重複削除まで完了したら、”トータル”の項目をいったん削除し、最下行に再度追加する。
- 張り付けシートから集約データシートにコピペする際に、値で貼り付けを行う。
For i ＝ １ To n
If Cells( i , 1 ).Value = ”トータル” Then
Exit For
Cells( i , 1 ).Delete xlShiftUp
End If
Next i
Range(“A1”).End(xlDown).Select
ActiveCell.Offset(1,0).Activate
ActiveCell.value ＝ ”トータル”
Activecell.Borders.LineStyle ＝ xlCountinuous
シート名の行追加
これも自分からの提案になります。シート名の取得ができることが分かったので、１行目にそれぞれの列に対応するシート名を入力していく処理を考えました。
以下、そのコードになります。また別途、変数をｘとして最終列の取得を行ったり、B列以降の列幅自動調整、シート名の中央揃え等も行いました。
For i ＝ ２ To m + １
Cells( 1 , i ) ＝ Worksheets( i – 1 ).Name
Next i
x ＝ Cells( 1 , Columns.Count ).End(xlToLeft).Colmun
For i ＝ ２ To x – 1
Columns( i ).EntireColumn.AutoFit
Next i
Rows( 1 ).HorizontalAlignment ＝ xlCenter
全体に罫線追加
コピペを値で貼り付けにしたことや、セルの結合を解除したことにより、集約データシートの書式に統一感がなくなりました。そこで上述のxで最終列を取得したこともあり、全体に罫線をつけてみることにしました。以下そのコードになります。
Range(Cells( 1 , 1 ) , Cells( n , x )).Borders.LineStyle ＝ xlCountinuous
新たに問題！？A列にも結合セルあり
依頼主も把握しきれてなかったようですが、A列にも結合セルがあったようで、エラーがでて処理が止まってしまうようです。単に上述の結合解除範囲である、B列からC列をA列からC列に変えたら良いとは思いましたが、念のためどのような範囲で結合されているのか確認してもらうためにも以下の処理内容及びコードを考えました。
念のため言っておきますが、これは実際に行われる処理には必要ないので組み込ませていません。
- もし結合が発見されたら、メッセージボックスで表示
- シート名と結合の場所が分かるようにする
- 表示される内容はループの中で蓄積させていき、ループ終了後に表示させる
Dim buf As String
For i ＝ １ To m
For j ＝ １ To 任意の値
If Sheets( i ).Cells( j , 1 ).MergeCells Then
buf ＝ buf ＆ Sheets( i ).Name ＆ ” - ” ＆ Cells( j , 1 ).Address( 0 , 0,) ＆ ” –＞結合されています” ＆ vbCrLf
End If
Next j
Next i
MsgBox buf
重い処理には必須！？ステータスバーについて
処理に時間がかかってしまうと、どうしても「処理が止まっているんじゃ…？」とか「今どのあたりまで処理が進んでいるんだろう？」なんて考えてしまいがちです。自分はサンプルファイル(データ量が少ない)で動作確認をしていましたが、依頼主さんが実際に処理したいファイルは、多くのデータが入っていると聞きました。当然ながら、扱うデータが増えれば処理にかかる時間も長くなります。また自身の経験上、ここまで処理に時間のかかるコードは書いたことはありませんでした。そこで依頼主さんに処理の進捗状況を把握してもらおうと、ステータスバーの表示を導入してみることにしました。
導入に関しては、初めての試みでしたが案外簡単でした。処理に時間のかかりそうなループ文中の最後に１行記述するだけです。以下、そのコードになります。
Application.StatusBar ＝ 表示させたい内容
自分の場合は、表示させたい内容を以下のようにしました。
”シート名 ” ＆ Sheets( j ).Name ＆ ” の処理中”
ちなみに、開いたエクセルファイルの下側にシート名がずらーと並んでいると思いますが、そのすぐ下に表示されます。(要するに左下の隅です。)
まとめ
以上で散らばったデータを必要な状態にまとめるという作業は概ね完成のようです。
この後も必要な処理はあるようなので把握次第、記事にしていこうと思います。
