【VBA】処理をまたいでデータを保持する方法
今回は、Excel VBAを使用したプログラムで処理をまたいでデータを保持する方法をシェアします。
例えば、Excel起動後に一度だけ大きなファイルを読み込み、
その後は読み込んだデータを使って（最初に読み込んだインスタンスを使い回して）随時処理をしたい、
といった場合に今回のテクニックが役に立ちます。
概要
今回は個人的にオススメな永続化方法を2つ紹介します。
- 標準モジュールにパブリック変数を宣言して使いまわす方法
標準モジュールで宣言したパブリック変数は、Excelを閉じるまでインスタンスが破棄されることはありません。
この特性を利用する方法です。
手軽に実装できる反面、バグなどによって途中でExcel VBAが強制終了してしまった場合に、
グローバル変数のインスタンスも初期化されてしまう点に注意しましょう。 - ユーザーに見えないシートを作成してそこにデータを格納する方法
個人的には上記よりもオススメな方法です。
Excelシートには3種類の表示スタイルがあります。
「Visible」と「Hidden」と「VeryHidden」です。
Visibleは通常の表示シートです。
Hiddenは、通常の非表示シートです。
VeryHiddenはユーザーが表示することができない隠しシートです。
この「VeryHidden」シートにデータを格納し操作する方法です。
標準モジュールにパブリック変数を宣言して使いまわす方法
それでは、まず1つ目の方法の実装を以下にシェアします。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Option Explicit ' 標準モジュールに宣言したグローバル変数 Public counter As Long ' カウンターに格納されている値を表示する Public Sub PrintCounter() ' カウンターの値を1増加 counter = counter + 1 ' カウンターの値を出力 Debug.Print "カウンターの値 : " & counter End Sub |
この方法は、グローバル変数である「counter」に格納されている値が、
「PrintCounter」が呼ばれる度に増加していきます。
これにより、Excelを開いている間はオブジェクトを使いまわすことができます。
この方法の注意点としては、バグなどによってプログラムが途中で強制終了してしまった場合
（下のような画面が出てきて、ユーザーが「終了(E)」を押下した場合）
グローバル変数の値もリセットされてしまいます。
ユーザーに見えないシートを作成してそこにデータを格納する方法
次に、個人的にオススメな方法を紹介します。
Excelのワークシートは、VBEで見ると以下のように3種類の表示モードを選ぶことができます。
- xlSheetVisible (表示)
- xlSheetHidden (非表示)
- xlSheetVeryHidden (ユーザーが表示に変更できない非表示)
今回は、この「xlSheetVeryHidden」を使用してデータを保持する処理を実装します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
Option Explicit <br>' カウンターに格納されている値を表示する Public Sub Main() ' 隠しシートの値 + 1を隠しシートに設定 SetDataFromVeryHddenSheet GetDataFromVeryHddenSheet + 1 ' 隠しシートの値を出力 Debug.Print "カウンターの値 : " & GetDataFromVeryHddenSheet End Sub ' 隠しシートからデータ取得 Public Function GetDataFromVeryHddenSheet() As Long With VeryHiddenSheet.Range("A1") If .Value = "" Then .Value = 0 End If GetDataFromVeryHddenSheet = CLng(.Value) End With End Function ' 隠しシートにデータを設定 Public Sub SetDataFromVeryHddenSheet(ByRef counter As Long) VeryHiddenSheet.Range("A1").Value = counter End Sub |
上記のプログラムで「VeryHiddenSheet」は、「xlSheetVeryHidden」を設定した
ワークシートオブジェクトです。
このワークシートのA1セルにcounterの情報を設定して、
値を出力しています。
このシートはユーザーが表示することができないので、
ユーザーに変更されることなく、プログラムで自由に操作することができます。
また、この方法だと、プログラムが途中で強制終了された場合でも、
値を保持することができます。
まとめ
今回は、Excel VBAで、処理をまたいでデータを保持する方法をシェアしました。
この他にも、外部ファイルにデータを保存する方法や、
AccessDBに値を保存する方法なども考えられます。
もし、この方法に興味がある場合や、他にもこんなことが知りたい、
などがあれば、是非フォームからメッセージを頂きたく思います。
