表計算と言えばExcel。でも、表計算アプリケーションとしてドラスティックに登場したのはLotus123。マクロ機能で操作が記憶できるというのも魅力だった。Windows版になって、複数のSheet Tabが利用できるようになった。
123のこの仕様を見て、多数の仕入れ先へのFax発注の合理化を思いついた。しかしながら、それ以前はマクロ機能は123もExcelも独自のものだったのが、ExcelではVBを基本としたVBA(VB for Application)になった。これで勝負あった、と思ってExcelに切り替えた。
ちなみにプレゼンテーションのことをパワーポイントというくらいだが、そもそもはLotus Freelance。
ブラウザにしてもそうだが…… DOS自体も人の作品を買ったものらしいし。
VBAは生産性が高いし、VBが使用できる人には簡単。最初はマクロ記録という操作を記憶してもらう機能が便利。しかし、これは簡単なものならよいが、少し複雑になると遅い。作業を忠実に再生されても時間の無駄である。
処理速度を上げるために、コーディング画面で無駄な工程を削除するとともに、
画面の更新を止めたり、Application Screen Updating = False
発生するイベントを無視したり、Application.EnableEvents = False
自動再計算を止めたり、Application.Calculation = xlCalculationManual
これらは、終了時に明示的にもとに戻しておく。念のため。
これで、記録の まんま再生 みたいなモッサリ感はなくなるが、データ量が多いとチーンと画面が固まったままで、……何待ちなの？ みたいな。
表計算アプリケーションは高機能なのでセルは非常に多くの情報を持っている。 セルは重い。 なので、出来るだけセル参照を避けてメモリを活用するというのが速度向上に効果的。
これには配列とクラス・コレクションが有用。
クラス・コレクションはXMLと感覚が似ている。XMLにメソッドを持たせた感じ？
Memory (VBA)
Array
セルに値を入れていく場合、セル参照すると、値を入れたいだけなのにセルのいろんなものを呼んでしまうみたいで遅い。
単独のセルならCells(1,2).value=123というようにValueを付けて。
(効果はないかもしれないが付けないよりは行儀が良い)
多くの場合、行毎に順次処理していくので、行で連続したセルに値を入れる処理を早くすれば効果的。
入れるべき値は一旦、配列に入れておき、値を入れるときにはセルではなくRangeを対象とすると格段と早くなる。
余計なことをせずに移行する感じ。
with句を使用するのも重要。
Dim PstArray 配列
With tOrder 参照クラス
[Loop]
PstArray = Array(.OrderNo, .Customer, .Chuzansu, .Zaikosu)
Range(Cells(cRow, iClm), Cells(cRow, eClm)) = PstArray
[ /Loop]
End With
Class Collection
定型処理などで複数のBook、Sheetを参照する場合に逐次セル参照すると非常に重い処理になる。参照するSheetのテーブルの内容を一旦メモリに取り込むのが最も効率が高い。
VBAではクラスモジュールが利用できるので、テーブルの項目を元にクラスを作成する。そのコレクションも作成し、読み込んだデータをコレクションに追加しておく。
コレクションに、クラスの要素を一意に検索したり、必要に応じていろいろなメソッド作ればプログラミングの効率も上がる。エクセルは自由度が高いため、インデックスとかには注意が必要。
クラスとコレクションは速いだけではなく、メソッドは便利なので、簡易タイムカードもエクセルのVBAで作成して使っている。
クラス
Private varDateNum As Long
Private varCldKadou As Integer
Private Sub Class_Initialize()
varDateNum = 0
varCldKadou = 0
End Sub
Public Property Get DateNum() As Long
DateNum = varDateNum
End Property
…略…
コレクション
基本的にコレクションにクラスのインスタンスを追加する機能、削除する機能、全件クリアする機能などを作成する。
シートからコレクションに追加する機能のサンプル。
Public Function GetData() As colClds
…略…
Set GetData = Nothing
ClearAllMember 全件クリアのメソッド
…略…
With objXLBook.Worksheets(hogehoge)
.Unprotect
ERow = CLng(Mid(.Cells(IRow, 1).CurrentRegion.Address, 9))
strRange = "A" & CStr(IRow) & ":F" & CStr(ERow)
RangeArray = .Range(strRange).Value一括選択
End With
curRow = 1
For curRow = 1 To ERowメモリを行毎に追加
varDateNum = CLng(RangeArray(curRow, 1)
varCldKadou = CInt(RangeArray(curRow, 6))
Add varDateNum, varCldKadou, CStr(varDateNum)追加のメソッド
Next curRow
Set GetData = Me
…略…
