カテゴリー: ExcelVBA

ExcelVBAに関する記事

Initializeイベント

Initializeイベントはメモリ上に読み込みが完了した時点で発生するイベントです。起動時の注意点など

「Initialize」イベントは、UserFormがメモリ上に読み込まれたら発生するイベントです
表示される前に発生するイベントで、引数はありません

フォームの初期化処理で使用します

Loadステートメントを実行すると、このイベントが発生します
Showメソッドを実行した場合は、このイベントが発生後にActivateイベントが発生します

また、メモリ上に読み込みが終了しているので、UserFormの操作及び各コントロール全ての操作が可能な状態になります

表示前の処理になるので、このイベントに作成した処理が時間のかかるものであった場合は上記の各読み込みコード実行前に起動の明示をしていないとユーザーにフリーズさせているような誤解を与える可能性があります

そういった場合は、あえてActivateイベントに処理を入れてフォームを表示させておいてもいいかもしれません

フォームの起動条件の作成について

このイベント中にフォームを終了させるとエラーが発生します
フォームの起動条件の確認には、読み込みプロシージャかActivateイベントで行い終了させるようにしてください

ただ、この処理では少し問題がある場合があります
その状況と解決方法は以下の記事で解説しています

Endステートメントの有効利用

Zoomプロパティ

Zoomプロパティは、各コントロールの表示倍率の設定です。使用時の注意点も

'100%表示にする
UserForm1.Zoom = 100

「Zoom」プロパティは、フォーム内の表示倍率の設定です
初期値は100になっています
これは%表示と考えてもらっていいです

設定できる範囲が「10～400」の間に決まっています
元々の10分の1から4倍の大きさにまで、拡大縮小が出来るようになっています

Zoomが100の時の表示
初期値100の表示状態
Zoomが50の時の表示
50に設定した表示状態
Zoomが200の時の表示
200に設定した表示状態

以上の画像の様に、同じフォームの同じサイズのコントロールでも大きな違いが生まれます
なにより、見やすさを向上させるためのプロパティと思ってください

表示の乱れについて

このプロパティの難点が文字列がコントロールの表示領域からはみ出してしまうことがあることです

Zoomの数値によって文字列がはみ出している
labelの文字列がはみ出している状態

画像を確認してください、これは元は上記の画像と同じものです
赤枠内のlabelコントロールの表示文字列の最後の「n」がはみ出して消えてしまっています
これはコントロールのサイズの単位と文字列のサイズの単位が違うため、Zoomプロパティの数値を1ずつ動かしていくと必ずどこかで発生します
文字サイズが影響するため、文字の種類によっても差があります

なので、自動調整で文字列がはみ出す場合はいかんともしがたいです

このプロパティを主に使用するのは、フォームのサイズ調整に合わせて、各コントロールの表示も整える場合に使用します
フォームの自動サイズ調整については以下の記事で解説しています

ユーザーフォームを解像度に合わせてサイズ調整

解説にもありますが、いかんせん表示が乱れることがあるのが困っちゃう
困っちゃうのであんまり普段は使わんですねぇ

Tagプロパティ

Tagプロパティは追加情報を設定するプロパティです。設定するコード

'フォームの追加情報の設定
UserForm1.Tag = "追加情報"

「Tag」プロパティは追加情報を設定できます

これ自体は、設定してもなんの意味もないプロパティです
なんの意味もないということは、他に影響を与えないと言い換えれます

これはそのプロパティ値で、処理を行う為に使用します

タグという名前の通り、なんらかの判定に使用します
例えば、このプロパティに「グループA」という文字列を入力しておいて、そのグループごとに処理を実行するようなことを行えます

また、処理中の一時的なデータの保持にも使用できます
加工前の文字列をプロパティに代入しておき、加工後の文字列を出力後、Tagプロパティから加工前の文字列を取得する、というようなことも出来ます

使用方法は多岐にわたりますので、いろいろな使用方法を考えてみてください

IsReadyプロパティ

IsReadyプロパティは、ドライブの使用可能状態を返します。その状態を取得するコード

'Eドライブの使用可能状態を取得
Debug.Print bhFSO.GetDrive("E").IsReady

「IsReady」プロパティは、ドライブが使用可能な状態にあるかを調べるプロパティです

使用可能であれば「True」、使用不可であれば「False」が指定されます

これはUSBメモリーなどの着脱を行うドライブに対して、データのやり取りが可能な状態かを調べるのに使用します
そのため、これはドライブオブジェクトのプロパティになりますので、まずGetDriveメソッドを使用してドライブを取得します
その後、そのドライブオブジェクトのIsReadyプロパティを調べます

例コードでは1行で実行していますが、他にもドライブに対して処理を行うのであれば、GetDriveメソッドでオブジェクト変数に代入させるほうが良いです

ただ、使用状態を知りたいだけならこのコードで十分です

使用可能な状態とは、ドライブが接続されてデータの通信が可能になった状態です

ドライブを取り外す場合に、基本的には「取り外し」という操作を行います
この操作を行った段階ではまだ、物理的には取り外していません
なので、DriveExistsメソッドではTrueが返される状態です
ですがデータのやり取りは出来ない状態なので、IsReadyプロパティはFalseになります

物理的に取り外すと、DriveExistsメソッドでもFalseが返されるようになります

ドライブが存在しないときは、このプロパティを取得しようとするとエラーが発生します
つまり、DriveExistsメソッドとIsReadyプロパティの2つともがTrueを返したときにそのドライブとのデータのやり取りが可能になる、ということです

FileExists・FolderExists・DriveExistsメソッド

FileExists・FolderExists・DriveExistsメソッドは、ドライブ・フォルダ・ファイルの存在確認をするメソッドです

'ファイルの存在確認
Debug.Print bhFSO.FileExists("ファイルの絶対パス")
'フォルダの存在確認
Debug.Print bhFSO.FolderExists("フォルダの絶対パス")
'ドライブの存在確認
Debug.Print bhFSO.DriveExists("C")

FileSystemObjectを使用して、存在確認を行うメソッドです
「FileExists」メソッドはファイル
「FolderExists」メソッドはフォルダ
「DriveExists」メソッドはドライブ
それぞれの存在確認に、各メソッドを使用します

メソッドは全て、存在すれば「True」、存在しなければ「False」を返します

FileExistsメソッド

Debug.Print bhFSO.FileExists("ファイルの絶対パス")

FileExistsメソッドは、ファイルの存在確認を行うメソッドです

引数には拡張子までを含めた絶対パスを指定します
これは単体のファイルの存在確認のため、ワイルドカードは使用できません

指定の名前のファイルが存在していれば、処理を行うような場合に使用します
存在確認の中でも一番使用頻度が高いメソッドと思います

FolderExistsメソッド

Debug.Print bhFSO.FolderExists("フォルダの絶対パス")

FolderExistsメソッドは、フォルダの存在確認を行うメソッドです

引数にはフォルダ名を含む絶対パスを指定します

またパス指定であれば、ドライブの存在確認も行えます
「C:」のように「:」を付けて指定します
専用のメソッドがあるので、意味はありませんが一応可能です

フォルダは上書きされないので、あまり存在確認は必要ありません

DriveExistsメソッド

Debug.Print bhFSO.DriveExists("C")

DriveExistsメソッドは、ドライブの存在確認を行うメソッドです

引数にはドライブ名を指定します、小文字でも実行可能ですがなるべくドライブ名と同じ大文字で指定するほうがいいです

また、パスでも指定は可能です
「C:」というように指定しても確認を行えますが、メソッド的に意味はありませんので、ドライブ名で指定します

基本的には着脱されるドライブに対して使用します
USBメモリーなどのものです、これをPCで使用されているかを調べて存在すればそのドライブにデータを移動させたりする処理に使用します

なお、ドライブは存在が確認できても使用できる状態とは限りません
使用できる状態を調べるにはIsReadyプロパティを使用します

IsReadyプロパティ

StartUpPositionプロパティ

StartUpPositionプロパティでフォームの初期表示位置を設定できます

「StartUpPosition」プロパティは、UserFormが表示される初期位置の設定です
基本的には初期表示位置なのでデザインウィンドウで設定します

「0 – 手動」は、初期位置を設定しません。この場合LeftプロパティとTopプロパティの設定値により表示位置が調整されます。それらのプロパティを使用して任意の位置に表示するにはこの設定にしておく必要があります

「1 – オーナーフォームの中央」は、Excelのウィンドウの中央に表示されます。Excelの位置によって表示位置が変更されます。マルチディスプレイで表示する場合に便利な設定です

「2 – 画面の中央」は、そのままの意味で画面の中央に表示します

「3 – Windows の既定値」は、画面の左上にぴったりくっついた場所に表示されます。Leftプロパティなどで位置調整を行っていない「手動」と同じ場所に表示されますので、あまり使いません
また、Leftプロパティなどを設定していても左上の表示位置は変わりません

フォームの初期表示位置設定の位置図
フォームの初期表示位置図

この画像が、各設定で表示される場所です
ピンクがフォームで、設定により場所が変わります

緑色がExcelのウィンドウです、UserFormのオーナーは基本Excelなのでその中央になるので、Excelの位置によって変化します

グレーが画面全体のウィンドウです、その中央に画面の中央に設定したフォームです
初期設定では、この位置設定になっています

コードで変更する場合

Private Sub UserForm_Initialize()

'フォームの初期表示を手動で表示する
Me.StartUpPosition = 0

End Sub

上記のコードを使用すれば、表示位置を変更して表示させられます
ですが上記にもあるように、初期表示位置なのであまり動的に変更する場面が少ないと思いますのでここで解説します

このプロパティは、初期表示位置なので表示されてから設定を変更しても意味はありませんので、表示前に設定を変更する必要があります

なので、メモリ上に読み込まれて表示前に発生するInitializeイベントに記載します

またリファレンスにもあるような、メモリ上に読み込みをしてプロパティを変更してから表示させる方法もあります

ただ、このコードの注意点として手動とWindowsの既定値の設定が切り替わりません
自分の環境のExcel2010でしか試していませんが、デザイン画面で手動にして、コードでWindowsの既定値の設定にしても手動のままです
逆も同様でした、それ以外の設定には切り替わります

まあ、使用場面の少なさからあまり影響は無いと思いますが・・・

画面の表示位置について

画面の表示位置とは、案外重要な要素です

入力補助などのフォームで、フォームの起動を繰り返すような場合に毎回表示位置をタイトルバーをドラッグして移動させるのは面倒です
特にマルチディスプレイを使用している場合に、毎回メインからサブへ移動させるというような動きは非常に面倒です

なので、最終的に完成したときにはこのプロパティから表示位置を調整するようにします

なお、表示位置を保存して前回と同じ場所に表示する処理は以下の記事で解説しています

フォームを前回と同じ場所に表示する

レジストリのデータを削除

レジストリに保存したデータを削除するコード（DeleteSettingステートメント）

'レジストリの値の削除
DeleteSetting "ファイル名", "モジュール名", "データ名"
'レジストリのsection以下削除
DeleteSetting "ファイル名", "モジュール名"
'レジストリのappname以下削除
DeleteSetting "ファイル名"

レジストリに保存したデータを削除するには「DeleteSetting」ステートメントを使用します

ステートメントの書式

引数（太字は必須引数）
DeleteSetting appname, section, key

引数の指定によって削除される階層が変化します
引数と階層は以下の記事で解説しています

レジストリにデータを保存

VBAのリファレンスでは、2つ目の引数「section」は必須となっていますが、実際には省略しても実行されます

削除する範囲と必要性

指定した箇所以下が全て削除されます

DeleteSetting "ファイル名", "モジュール名"

つまり、この様にsectionまでの指定を行うとsectionが削除されます
なので値のキーは全て削除されます

DeleteSetting "ファイル名"

appnameのみ指定した場合は、全てのデータを削除します

以下の全データとは言っても、VBAで作成したデータなので、知らないデータや別アプリのデータが削除されるわけではありませんので安心してください

また、このキーより上位のアイテムは本当に必要が無ければ削除したほうが良いと思います

これはエクスプローラーのフォルダ階層と同じ構造です
つまり、フォルダ内にあるファイルを全て削除しても空フォルダがそこに残ったままの状態になっています
空なので、影響はありませんが無駄ファイルであることは間違いありません
キーの値が全て必要ないのであれば、appnameのみで指定して全て削除してしまっていいと思います

レジストリのデータを取得

レジストリのデータを取得するコード（GetSetting関数）

'レジストリの取得
Debug.Print GetSetting("ファイル名", "モジュール名", "データ名", 0)

レジストリのデータを取得するには「GetSetting」関数を使用します

関数の書式

引数（太字は必須引数）
GetSetting(appname, section, key, default)

引数「appname」「section」「key」は、データまでのパス指定です
このあたりの解説は以下の記事で行っています

レジストリにデータを保存

引数「default」は、パスも含めて指定のデータが存在しなかったときに返される値になります
「appname」は存在しているが、「section」が存在していなかった場合もこの引数の値が返される訳です

省略可能で、省略時は「””」（空白文字列）が返されます
データが無くてもエラーが発生しない点には注意が必要かもしれません

レジストリのデータ取得に関して

レジストリにデータを保存する場合は、SaveSettingステートメントでした

こちらは関数です、値の取得を行うため戻り値が存在するためです
なので、こちらのコードでは引数を「()」で囲むのと、代入先を指定する事を忘れないようにしましょう
例コードではイミディエイトに出力しています

また、レジストリの操作は編集・削除は要注意ですが
取得を行うこの関数の使用に関してはそこまで気にしなくても大丈夫です
取得を行うだけで編集は行いません

ただ、見たらあかんやつを見たらあかんので規則は大切に

レジストリにデータを保存

レジストリにデータを作成・保存するコード（SaveSettingステートメント）

'レジストリの保存
SaveSetting "ファイル名", "モジュール名", "データ名", "データ内容"

レジストリにデータを作成・保存するには「SaveSetting」ステートメントを使用します

ステートメントの書式

引数（太字は必須引数）
SaveSetting appname, section, key, setting

引数は全て必須項目となっており、引数「setting」が保存するデータとなります
それまでの3つの引数はデータの名称設定となり、階層構造になっています
全て文字列で指定し、決まった名称もありませんので自由に指定できます

レジストリの階層図
レジストリの階層図

レジストリの各引数の階層図になります
こういった形で階層が引数の順番に並びます

最上位の「VBA専用」のフォルダより上位は編集できません
編集可能な部分が限定的なので、他のVBAで作成されていなければ何もデータは存在しませんので好きに扱っても問題ありません

存在するデータを指定した場合は、既存のデータが上書きされ、データが無ければ新規作成されます

レジストリ使用上の注意点

レジストリには各アプリの重要なデータも含まれています
いくらVBAでは触れないとはいっても、レジストリの編集をしているという事実は変わらないので、基本的に編集する場合は要注意です

また「レジストリを編集していいすか？」と、気軽に確認するのはやめましょう
レジストリを知っている人からすれば、そんな恐ろしいこと許可できひんと言われます

とはいえ、上記にもあるようにVBAのコードで編集する分には問題ありません
元々何もデータが入っていない場所を編集するのでだいじょうぶです
他の場所を編集しようとしてもVBAのコードでは実行できません

ただ、レジストリエディタは簡単に起動が可能で編集もそのエディタで行えます
ここでは触れませんが、Web検索すればすぐに起動方法はでてきます
ですがVBAでの使用範囲内であれば使用しないでください
何かあったときの責任は非常に大きい可能性がありますので

VBAで触れる範囲のデータは全てVBAで取得も作成も削除も行えます
ま、しなくていいことはしないほうがいいですよね

フォームを前回と同じ場所に表示する

フォームの表示位置を保存して、開くたびに同じ場所に表示させるコード

入力補助用のフォームであったり、サブフォームを作成した場合にフォームの表示位置を調整するのは案外重要です

入力補助用であれば、入力範囲に被らないようにユーザーがフォームの位置を移動させることもありますが、表示するたびにその作業を行うのも面倒です

Private Sub UserForm_Initialize()

'セルから位置設定取得
Me.Left = Range("A1")
Me.Top = Range("A2")

End Sub
'-----------------------------------------------------------------------------------------
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

'セルに位置設定を保存
Range("A1") = Me.Left
Range("A2") = Me.Top

End Sub

そんなときに使用するのが、このコードです
これは、UserFormのTerminateイベントにLeftプロパティとTopプロパティを特定のセルに入力しておき、Initializeイベントでその保存値をそれぞれのプロパティに設定する処理です

こうすることで、前回フォームを終了した場所と同じ場所にフォームが表示されるようになる為、表示後にフォームを移動させるような操作が必要なくなります
この処理に使い慣れると、少し便利さが分かります
なお、この設定を有効にするにはStartUpPositionプロパティを手動に設定する必要があります

このLeftプロパティとTopプロパティの設定値は、保存可能な場所に入力する必要があります
一番簡単なのがセルへの入力です、セルであればブックの保存で設定値が保存できるためです

そのセル自体は、設定シートとでも名前の付けた専用のシートに入力します
そのシートは非表示状態にしておけば、ユーザーに編集されることもありません
また、そのセルに名前定義をしておけば参照が簡単に行えます

設定値を個人設定にする

これはブック単位での設定値保存になります
例えば、このブックを複数の人が使用する環境で、個人設定を保存したい場合は個人の特定をする必要があります

もっとも簡単なのは、コンボボックスなどで入力担当者名を選択することです
ただ、ユーザーには少し面倒な操作ではあります、入力補助なので入力する以外にフォームの操作をするのは無駄な作業になりうるからです

もうひとつはPCで特定する方法です
個人ごとにPCがあり、それで特定ができる環境であれば、PC内にその設定値を保存する方法も考えられます

これには2通りの方法が考えられます
PCの特定の箇所にテキストファイルを作成して、そこに設定値を入力保存する方法とレジストリに保存する方法です

特定の箇所にテキストファイルを作成して、読み込みをするにはFSOを使用するのが便利です
さらにPCのローカルフォルダにデータを保存する必要があるため、そのパスを取得するためにWScript.Shellオブジェクトも使用します
ここまで考えると、非常にめんどうな処理になる可能性があります

なので、レジストリの操作が会社で禁止されていなければ、そちらを利用します
そこに表示位置の設定値を保存することで、使用するPCごとに別の設定を保存することが出来るようになります

Private Sub UserForm_Initialize()

'レジストリから位置設定取得
Me.Left = GetSetting("ファイル名", "フォーム位置", "Left", 0)
Me.Top = GetSetting("ファイル名", "フォーム位置", "Top", 0)

End Sub
'-----------------------------------------------------------------------------------------
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

'レジストリに位置設定を保存
SaveSetting "ファイル名", "フォーム位置", "Left", Me.Left
SaveSetting "ファイル名", "フォーム位置", "Top", Me.Top

End Sub

このコードを使用すると、レジストリにデータを保存して表示時にそこからデータを取得します

ユーザーは他の人は別の設定が呼び出されているなんてことを意識すらしないかもしれませんが、そんなことは関係なく、それぞれに設定が別々で行われているのを確認できれば、自分はほくそ笑むことでしょう

関連の記事

レジストリにデータを保存