VBAではよく変数を使うのですが、たくさんの値を変数に代入するには、たくさんの変数を用意しなくてはいけません。
ですが、動的配列を使い、配列形式で変数を使うと、たくさんの変数を準備しなくてもOKです。
〇変数とは
まず、変数とは
A = 1 + 1
の「A」のことです。
この場合、A とはなにか？と聞かれたら、
A には “１＋１” の結果が入っているので、「2」です。
変数は箱のようなものなので、そのなかには、色々なものを入れることができます。
Set関数を使えば、計算結果だけでなく、ワークブックやワークシート、セル、といったオブジェクトなんかも代入することができます。
現在のワークブックを変数に入れておきたければ、
Set AWB = ActiveWorkbook
で、以後 AWB と言えば、現在のワークブックのことを言います。
AWB.Activate
これで変数 “AWＢ” にいれた、ワークブックを開くことができます。
もちろん、同じ操作をワークブック名で指定して行うことができますが、
操作したいワークブックがたくさんあったり、毎回、同じ名前とは限らない場合には、それらの処理をワークブック名で行うには、なかなかの根性が必要です。
なので、VBAを使って、たくさんのワークブックを開いて、一つのワークシートに「集約された表」を作りたいときなどには、変数を使えばとても便利です。
〇やりたかったこと
今回、僕が行いたかったことは、
フォルダーに1.xlsといった、数字の名前の入ったファイルがたくさんあり、それらを順に開いていき、情報を一つのファイルにまとめ、順に閉じていく
といったことを行いたかったです。
フォルダーにあるファイルの数は決まっておらず、日々変化します。
〇最初に作ったコード
Sub 集約()
On Error Resume Next
For i = 1 To 31
Workbooks.Open "C:\Users\〇〇\Desktop\" & i & ".xlsm"
Worksheets(1).Select
Cells.Copy
Workbooks(2).Activate
Worksheets(i).Select
Cells.selcet
Selection.PasteSpecial xlPasteValues
Workbooks(i).Close
Next
〇最初のコードの問題点
最初に作ったコードは、for文で1～31の数字を変数に代入していき、
対応したファイルを開き、一番左のシートにある情報を全部コピーして、2番目に開いたファイルにペーストしていくといった内容です。
なぜ2番目かというと、個人用マクロブックを作っている場合は、それが常に1番目に開いたファイルとなるからです。
その後に、開いているワークブックの数を数えて、その数だけ閉じる
といった事をしています。
ワークブックが無い場合はエラーになるので、On Error Resume Next でスルーさせています。
この場合、このプログラムを動かす時は、他のワークブックを開いていると、そこに情報が集約されてしまいます。
どんな時も、“2番目に開いたファイル”に集約してしまうからです。
閉じるときも、3番目に開いたワークブックから先を全て閉じてしまうので、閉じたくないファイルまで閉じてしまうことがあります。
On Error Resume Next でエラーをスルーさせてしまっているので、他のエラーがでても全てスルーしてしまいます。
〇問題を解決するために動的配列で変数をつかう
Sub 集約改良版()
Dim AWB(31)
For i = 1 To 31
Set AWB(i) = ActiveWorkbook
Cells.Copy
Workbooks(2).Activate
Worksheets(i).Select
Cells.selcet
Selection.PasteSpecial xlPasteValues
If Err.Number <> 0 Then
On Error GoTo 0
Exit For
End If
For i = 1 To 31
If IsEmpty(AWB(i)) Then
Exit For
End If
Set AWB(i) = Nothing
Next
〇改良した点
Dim AWB(31)
と関数を宣言することで、31個の部屋があるAWDという変数を作りました。
Set AWB(i) = ActiveWorkbook
とすることで、AWB(i)に順に開いたワークブックを格納させていき、特定させることで
VBAで開いたワークブックと、それ以前に開いたワークブックをわけて処理することができるようになりました。
On Error Resume Next でエラーをスルーさせてしまっていたのを、
On Error GoTo err1
を使うことで、エラーが発生したら、err1の処理に行くようにしました。
err1では、エラーナンバーで0じゃなかったら（エラーが発生したら）for文を抜け出して、On Error GoTo 0で
エラーナンバーを0（リセット）とせよとしました。
For i = 1 To 31
If IsEmpty(AWB(i)) Then
Exit For
End If
とすることで、ワークブックが無かった場合は、for文を抜け出せとすることで、エラー処理を限定しました。
最後に、
AWB(i).Close
とすることで、VBAで開いたファイルだけ閉じるようになりました。
〇まとめ
今回、紹介したVBAのコードは、紹介用に簡易的に作ったもので、実際のはもっと重い処理をしています。
なので、For文の最後に
ActiveWorkbook.Close
といれておけば問題ないのでは？と思われるかもしれません。
まったくそのとおりです。
でも、実際はもっと多くの処理をするので、閉じられなかったのです。
でも、動的配列を使うことで、さらに、それをfor文と組み合わせれば、変数を可変にすることができます。
これで、ワークブックナンバーで処理していたので、プログラムを動かす前に、他のファイルを開いていないかを確認する必要があったのですが、
変数で指定することができるようになり、そういったことを気にする必要がなくなりました。
VBAでワークブック間のコピペをしたい場合、ワークブックを切り替えるたびに、処理時間がかかってしまうのですが、
一度、変数に格納させて、それを一気にペーストさせれば、ワークブック間の行き来を最小限に抑えることができるので、処理時間の短縮もできそうですね。
0 件のコメント:
コメントを投稿