ExcelVBA – ページ 18 – VBAの部品庫

バインディングについて

FSOなどExcel以外のアプリ操作の使用に際するバインディングについての解説

バインディングとは、通常のVBAコードでは実現が難しかったり、不可能な場合にVBA標準以外のアプリなどを利用して処理を作成する際に、アプリなどのインスタンスを作成する操作のことをいいます

インスタンスの作成とは、アプリを内部的に起動する状態のことです

このバインディングには「事前バインディング」と「実行時バインディング」の2通りの方法があります

事前バインディング

処理を実行する前に行うため、事前バインディングといいます

これは、VBEでの参照設定を行う必要があります

ツールメニューの中にある「参照設定」を選択します

選択すると、参照設定画面が表示されます
この画面の左のリストから、使用したいライブラリファイルにチェックを入れてOKボタンを選択することにより参照設定が完了します

これはPCの環境によってリスト内にあるものも変化しますので、作成環境と使用環境が同じライブラリファイルが無いとエラーになります

また、この設定はVBEの設定であるため他のPCでは都度同様の操作が必要になります
行わないとエラーが発生します

実行時バインディング

処理の実行時に行うものを実行時バインディングといいます

こちらはCreateObject関数を使用してインスタンスの作成を行います
この関数は引数に指定された文字列に対するアプリのインスタンスを作成します

実行時の環境にあわせてバージョンも自動的に指定されるため、作成環境ではないPCでも問題なくバインディングが実行できます

変数の宣言方法

それぞれの実際に使用を始める際のコードが少しだけ違います
最初にインスタンスを代入する変数を宣言します
FileSystemObjectを例にします

・事前バインディング
「Dim bhFSO As FileSystemObject」
Asまでは通常の変数と同じです、変数の型が作成するインスタンスに対応したものが指定できます
これは事前バインディングを行った場合にしか指定できない型です

・実行時バインディング
「Dim bhFSO As Object」
Asまでは同様に同じ、変数の型は固有のものを使用できないためインスタンスを代入できる汎用的な型の「Object」を使用します

インスタンス作成の方法

次に行うのが実際にインスタンスを作成するコードです
上記にもあるようにインスタンスの作成とはアプリを起動することです
これは内部的に起動を行うので、バックグランド処理になります
こちらもFileSystemObjectを例にします

・事前バインディング
「Set bhFSO = New FileSystemObject」
事前バインディングの場合は「New」キーワードを使用してインスタンスの作成を行います
この作成を行った時点で、同時に変数に代入されます

・実行時バインディング
「Set bhFSO = CreateObject(“Scripting.FileSystemObject”)」
実行時バインディングの場合は、CreateObject関数を使用してインスタンスの作成を行います
この関数の引数「Scripting.FileSystemObject」がFileSystemObjectのアプリケーション名になります

こうして、インスタンスの作成を行うことによりそのアプリケーションをVBAから操作することが可能になります

これはExcel以外のアプリを操作する場合に必須の知識になります
IE制御はもちろん、WordやOutlookなどのアプリも同様です

また、Excelのインスタンスを作成することで、現在使用しているExcelとは別のExcelを複数起動することが可能になりExcelデータを内部処理として実行するようなこともできます

それぞれの使い分け

ここまでの話では、環境設定を行う必要が無い実行時バインディングのみ使用すればいいように感じるかもしれません

ですが実際にコーディングを行う際には事前バインディングの方が圧倒的に便利です

事前バインディングを行った場合は、画像のようにインテリセンス入力ができるようになります
これの便利さは、通常のコーディング中でもかなり実感できているはずです

外部アプリの場合はそもそもどんなメソッドやプロパティがあるかさえも分かりませんので、これをいちいち調べたり、スペルミスが無いかを確認しながらコーディングを行う必要がある実行時バインディングに比べると、いかに事前バインディングがコーディング上で優位性があるかが分かります

また、オブジェクトブラウザーで使用可能なメソッドやプロパティが参照可能です

画像の様に、FileSystemObjectクラスにある各メンバー（メソッドやプロパティ）が参照が可能です
ものによりますが、下部にそのメンバーの説明があるためこれである程度どんな操作が可能か分かります

「FileExists」はファイルの存在を確認するメソッドですね、VBA標準の「Dir」関数と似たような機能のものです
戻り値が違うので全く同じ機能ではありません

この様にコーディング上にも非常に優位性がある事前バインディングですが、上記にもあるように環境設定が必要な点から考えると実行時バインディングの方が汎用性があります

あまり気にするレベルではありませんが、事前バインディングの方が処理時間が速くなります

結局どちらにもメリットがあるわけです

どちらも利用する方法

最終的にどうしたらええねんって話ですが、どちらのメリットも使用するのが一番です

まず参照設定を行いましょう

そして、変数の宣言は2つ作成しておきます
これは単純に同じ名前の変数で事前と実行時の2つのコードを使用します
ただ、これではエラーになるので実行時の方はコメントアウトしておきます
以下のような形にします

Dim bhFSO As FileSystemObject
'Dim bhFSO As Object

これで、この変数でインテリセンスが使用できます

インスタンスの作成に関しては実行時のものを使用すればいいです
事前バインディングの場合でもCreateObject関数でのインスタンス作成は可能だからです

Set bhFSO = CreateObject("Scripting.FileSystemObject")

これでインテリセンス入力が可能な状態でコーディングできます
処理が完成したら、変数宣言のコメントアウトを入れ替えます
メンテナンスを考慮すれば、あえて削除する必要も無いと思います

使用後の処理について

そして、オブジェクト変数であるため約束事として最後には変数の解放を行いますが、これに関しては解放を行う前にアプリを終了させる必要があるものもありますので解放タイミングは注意が必要です

Set bhFSO = Nothing

コードで解放を行えます
上記の様にアプリを終了させずに解放しただけでは、バックグラウンドでアプリが起動したままになるものもあります

そういったアプリは終了させてから解放を行います
コードで行えなかった場合は、タスクマネージャーからプロセスを強制終了させてください

バインディングについて色々初心者には難しい話が多いですが、中級者になれば必ずExcelのみでは満足いく処理が作成出来なくなってきますのでその段階で導入を考えてください

なお、自分は環境設定を行う前提の処理作成はあまりしたくないので実行時バインディングで処理を完成させるように注意しています

FSOの宣言方法

FSOの基礎知識とバインディングのコード

'実行時バインディングの宣言
Dim bhFSO As Object
Set bhFSO = CreateObject("Scripting.FileSystemObject")

'事前バインディングの宣言
Dim bhFSO As FileSystemObject
Set bhFSO = New FileSystemObject

FSOとは、FileSystemObjectの頭文字を取ったものです
このFileSystemObjectとは、ファイルやフォルダを操作することに特化した機能です
（バインディングについてはこちらの記事内を確認してください）

実行時バインディングで使用する

例コードの1つ目のものは、実行時バインディングなので設定を行う必要はありません
CreateObject関数を使用して、FSOのアプリを作成します
このコードを実行するだけで、FSOの機能を使用することが出来るようになります
ですが、インテリセンス入力やオブジェクトブラウザーでのメソッドなどの確認が行えないためそれらを覚えておかないとコーディングが行えません

事前バインディングでの設定方法

上記の実行時バインディングのデメリットを解消するのが、この事前バインディングです

事前バインディングでは、参照設定というものを行う必要があります
参照設定は、ツールメニューの中にあります

選択すると、参照設定を設定する画面が開きます
このリストの中から「Microsoft Scripting Runtime」にチェックを付けて完了します
これで、FSOの参照設定が完了します
この操作を事前バインディングといいます、この操作を行ったPCでは例コードの2つ目のものが使用可能になります

事前バインディングを行うことにより、インテリセンス入力が可能になります
画像の様にリストにFSO固有の選択肢が表示されるようになるので、コーディングが非常にはかどります

FSOを使用する

ここで作成されたオブジェクト「bhFSO」に対して、処理を実行していきます
このオブジェクト自体がFSOそのものになりますので、これに対してメソッドやプロパティが存在しそれらを操作して使用します

VBA標準コードでもファイルやフォルダの操作は可能ですが、あえてこちらを使用する意味は、何よりオブジェクトとしてファイルやフォルダを操作できる点にあります

これに関しては実際に使ってみて動きを確認してもらうのがわかりやすいと思います

また出来ることの幅も大きく変わります

例えばフォルダの削除に関してですが、VBA標準ではフォルダ内にファイルが存在する場合に削除しようとするとエラーになります
削除したい場合は、中のファイルをいちいち全て削除してから行う必要がありますし、エラー処理も必要になります
FSOであれば、問答無用で削除可能です

またFSOはテキストデータの操作も行うことが出来ます
こちらもVBA標準にもありますが、FSOの方がより操作しやすいです

バインディングであったり、オブジェクト変数で扱うことなど初心者の方には理解が難しい内容かもしれませんが、この機能は使えるようになればなるほど便利さが分かるものになっています

敬遠はせずに、少し使ってみるといいですよ

コントロールのZオーダーを調整する

コントロールにはZオーダーが存在します
Zオーダーとは、奥行きのことで手前に表示されるもの、奥に表示されるものを順番に設定することをいいます
これには、書式タブの順序から任意のコマンドで変更設定を行います

1のコントロールを全面に1つずつ移動して、最後に最背面に移動させています

これはExcelの画像などと同じで、背面に持っていけば後に隠れるし、前面に持っていけば前に表示されます
このZオーダーは、デザイン画面時のクリックで選択できる順番でもあるので背面のコントロールがほとんど隠れるような前面コントロールをデザインしてしまうと、背面コントロールが選択しづらくなりますので注意して下さい
選択できない場合は、一旦範囲選択で前面コントロール含めて選択してから、「Ctrl」キーで前面コントロールの選択を解除すればいいです

もしくは、プロパティウィンドウ上部にオブジェクト選択ができるコンボボックスがありますので、そこから選択することも可能です

コントロールを等間隔に並べる

コントロールを均等の間隔に並べるには、ドラッグ操作でもかまいませんが数が多い際は面倒です

そんなときには、書式タブの「左右の間隔」「上下の間隔」で調整を行います
まず間隔がバラバラなら、最初に間隔の削除を行います
この時点でコントロールがピッタリくっつきます
重なっていた場合も離れて、ピッタリになります
この状態で、間隔を広くしていくと完全に同じ間隔でコントロールの位置を調整できます
左右上下どちらも使い方は同じなので、調整したい方向で使い分けてください

自動調整には他にもフォームの中央に配置とボタンの配置がありますが、上記の間隔調整のほうが微調整が出来るのであまり使わないかもしれません

コントロールのグループ化について

上記の見栄えの調整を行ったら、最後にそれらをグループ化することでずれたりすることを防げます、またちょっとした移動なんかの時にも全てのコントロールを選択して引越しをしなくてすむようになります

あるあるで、すべて選択して移動させたつもりがLabelなどが置いてけぼりをくってしまっていることがありますがそういったことが無くなるので1人で不安になることが無くなりそうです
みんなが一斉にどこかに移動して自分だけ取り残されたらとんでもなく不安です、まじで

ただ、いいのか悪いのかサイズ調整をグループ化の枠線で行うと全てのコントロールが合わせてサイズが調整されるので注意が必要です、まあ自分だけでなくみんなが一斉に身長3mになったら気にならないかもしれません、ん？いや・・・

Captionプロパティ

Captionプロパティはコントロール上に表示される文字列の設定です、変更と取得のコード

'表示文字列を変更
UserForm1.Caption = "test"

'表示文字列を取得
Debug.Print UserForm1.Caption

「Caption」プロパティはコントロールに表示される文字列の設定です

プロパティ変更時の各コントロールの表示

手順1
初期設定の文字列です

手順2
全ての文字列を消去した状態

手順3
全てのコントロールに「test」を設定した状態

各コントロールでは、表示される文字列の位置が少しずつ違います

UserFoamでは、タイトルバーのタイトル文字列が変更されます

Labelは表示文字列になるので、このコントロールでは基本のプロパティです

CheckBoxから、ToggleButtonまでの全てはコントロールの表示文字列になるのでボタン名などになります

MultiPageでは、各見出しの文字列が変更されます

Frameでは、フレームを囲む枠線の上辺に表示されます

Captionプロパティの使用時の注意点

この文字列を指定すると、実際に使用するユーザーフォーム上で表示される為、使用者がなんの機能を持ったコントロールなのかを理解できるような文字列で指定を行います

コントロールのサイズに対して、多すぎる文字数の場合ははみ出して表示されなくなりますので、処理中で文字列を変更する際は注意が必要です
ユーザーフォームでははみ出す箇所に「…」が表示されてしまうので、そちらも気にして指定してください

このプロパティを使用して、Labelコントロールではユーザーへのメッセージ表示を行うことができます
MsgBox関数では、処理が一時停止してしまう事と履歴が残らないことを考えると内容によっては非常に有効な使い方です

ユーザーフォーム以外の全てのコントロールに言える事ですが、ユーザーフォームのZoomプロパティの設定を変更すると文字がコントロールのサイズと文字のサイズが別の設定になることから、うまくコントロールに入りきらなかったり、ずれたりすることがあります
これは、コントロールと文字のサイズが一体となっているわけではない仕様のためどうしようもありませんので自動的にうまく調整する方法はありません

BorderColorプロパティ

BorderColorプロパティは枠線の色を指定します、設定変更コードがあります

'フォームの枠線色を赤にする
UserForm1.BorderColor = RGB(255, 0, 0)

'フォームの枠線色を取得（非表示でも設定値は取得する）
Debug.Print UserForm1.BorderColor

「BorderColor」プロパティは枠線の色を指定します
（プロパティウィンドウでの色設定はこちらを確認）

このプロパティは枠線の色を整数値で指定を行います
上記のリンク記事にも記載はありますが、色の指定にはRGB関数が便利です
この関数で指定の色を指定すれば、整数値で返してくれます

また、この設定は枠線が表示されていなければ意味はありません
枠線の表示設定はBorderStyleプロパティの設定を変更しておく必要があります

BorderStyleプロパティ

BorderStyleプロパティはコントロールの枠線を実線表示するかの設定です、その切り替えのコード

'実線の枠線を表示
Label1.BorderStyle = fmBorderStyleSingle

'実線の枠線を非表示
Label1.BorderStyle = fmBorderStyleNone

'枠線の表示を取得（0なら非表示、1なら表示）
Debug.Print Label1.BorderStyle

「BorderStyle」プロパティは枠線を実線で表示するかの設定です

このプロパティに「1 – fmBorderStyleSingle」を指定すると枠線を実線で表示します
「0 – fmBorderStyleNone」なら非表示になります

画像の動きを確認してください
このプロパティのあるコントロールが表示されています
分かり易くするため、線の色を赤色に設定しています

ユーザーフォームは、内側の枠内に実線が引かれています

中のコントロールでは、Imageコントロールのみ初期値が実線を表示する設定になるので、最初に枠線が表示されています

それ以外のコントロールでは、初期値が非表示の設定なので、設定を行うと枠線が表示されます
また、このコントロールの設定を行うと別の枠線のプロパティであるSpecialEffectプロパティの設定が解除されてしまいます

手順1と手順3で枠線の表示が変わっているものがいくつかありますが、それらは初期設定ではSpecialEffectプロパティの設定が行われているコントロールです

また、じっくり見てもらうと分かると思いますが、微妙に文字の位置がずれています
これは枠線分表示位置がずれるために発生するずれです

もし、このプロパティをコード中に変更する場合はこのずれを計算してコントロールのサイズを少し余裕を持っておかないと、表示文字がはみ出すことがあります

BackStyleプロパティ

BackStyleプロパティは背景色の透明設定です、それの切り替えのコード

'背景色を不透明にする
CommandButton1.BackStyle = fmBackStyleOpaque

'背景色を透明にする
CommandButton1.BackStyle = fmBackStyleTransparent

'設定値を取得（0なら透明、1なら不透明）
Debug.Print CommandButton1.BackStyle

「BackStyle」プロパティは、コントロールの背景色の透明設定です

この設定値に「0 – fmBackStyleTransparent」を指定すると背景色が透明に指定されます
「1 – fmBackStyleOpaque」を指定すると不透明設定となり、BackColorプロパティに指定された色が表示されます

画像ではプロパティウィンドウでの変更の動きになります
項目を選択するプロパティは、プロパティ名をダブルクリックすると設定値を切り替えることが出来ます

また、指定は数値で行っていますので、「0」か「1」のどちらかになります
取得した場合はこの数値が取得されます

BackColorプロパティ

BackColorプロパティはコントロールの背景色の設定、色の設定方法に関しても解説

'フォームの背景色を薄い青紫にする
UserForm1.BackColor = RGB(192, 192, 255)

'フォームの背景色を取得
Debug.Print UserForm1.BackColor

「BackColor」プロパティはオブジェクトの背景色を指定します

プロパティウィンドウでのユーザーフォームの色の設定の仕方

色の設定は通常はプロパティウィンドウで設定を行います
プロパティ値の左にある▼ボタンから、色の選択が可能になります

BackColorのプロパティウィンドウでの設定画像 — プロパティウィンドウでの設定画像

パレットタブにある色が、色の直接の指定になるので基本的にはこちらを使用します
システムタブ側は、Windowsの設定であったり、ユーザーフォームの設定であったりを参照するため意図しない色に指定されることがあります

以前いきなり文字が表示されなくなったという現象に遭遇し、中にちゃんと文字が入っているのに見えない状態でした。結果的にはシステム文字色と背景色が同じになってしまい、見えない状態になっていました

なので、色の指定を行ったほうが安心です

また、パレットの下2行は初期状態では全て白色になっていますが、ここは任意の色が指定できます
色の箇所を右クリックすると、以下の画像にある色の作成画面が表示されます

この画面で色の確認を行いながら作成を行うことが出来ます
追加ボタンで、その色は保存されて同じ場所が作成した色設定になります
これは、PC毎の設定なので別の環境では再現されませんが、色自体は整数値での指定は変わらないのでフォームの色自体はちゃんと表示されます

コード上で変更する場合

色をコード上で変更する場合はRGB関数を使用します
定数の指定もできますが、覚えるのも面倒なのでこの関数で十分です

この関数は指定の色を整数値で返すので、そのままプロパティの設定に代入できます

また取得したプロパティ値は整数値が取得されます
その整数値を他のコントロールに代入すれば、同じ色に設定が可能になります

BackColorプロパティはBackStyleプロパティの設定が「1-fmBackStyleOpaque」（不透明）に指定している必要があります

ユーザーフォームにはBackStyleプロパティはありませんので、常に不透明状態に指定されています

オブジェクト名

Name（オブジェクト名）プロパティは、コントロールの内部的な名称です、その扱い方について

「オブジェクト名」はフォームの名前を指定するプロパティです
同じフォーム上で同じ文字列の設定はできません（後述に詳細あり）

オブジェクト名によるイベント作成時の注意点

このプロパティに設定した文字列が各コントロールの名前になるため、そのイベントを作成する際にはこの文字列が付与されます
以下のような形になります

Private Sub CommandButton1_Click()

End Sub

オブジェクト名「CommandButton1」
そのオブジェクト名のClickイベントを作成した形

コマンドボタンを新規作成した時点での既定の名称はコントロール名に連番が振られます
そのオブジェクト名に「_」（アンダーバー）とイベント名を追記する形です

そして注意するのが、オブジェクト名をイベント作成後に変更した場合です
これは作成時点で自動的に作成されているので、変更は反映されません

イベント作成数が1つ2つであればいいですが、同じ名称のコントロールを軒並み変更してそれにそれぞれイベントがあればそのすべてを修正する必要があります

このオブジェクト名はユーザーに表示されるものではないので、処理を作成後はオブジェクト名の変更は行わないようにしましょう
その設定だけでコードの可読性が劇的に変化することも無いと思います

ユーザーフォームのオブジェクト名について

ユーザーフォームのオブジェクト名は、「Show」メソッドなどのフォームの起動終了に指定する名称になるので、あまり簡素化した名称ではわかりにくくなりますのである程度分かり易い名前にしましょう

ユーザーフォームのみイベント作成時に注意が必要です
ユーザーフォームのみオブジェクト名が反映されず常に「UserForm」が指定されます、以下のような形になります

Private Sub UserForm_Click()

End Sub

オブジェクト名「UserForm1」
そのオブジェクト名のClickイベントを作成した形

コントロールと同じく名称に連番が付くのが既定名です
この既定名でイベントを作成しても、必ず上記のような名称で指定されます

なので、各コントロールとは違いユーザーフォームのオブジェクト名は変更してもイベント処理に影響は無いということです

ただ、Showメソッドなどではオブジェクト名を指定するので全てに影響がない訳ではありません

一意なオブジェクト名の意味

オブジェクト名はコード上で変更が出来ないプロパティです
プロパティウィンドウでのみ変更が可能になっています

イベントの事を考慮すれば当然の処置ですが、動的にコントロールを作成する処理の場合はコード上で名称を指定することが出来ます

またこのコードで追加するコントロールには同じオブジェクト名が指定できます
記事最初に、フォーム上で同じオブジェクト名が使用できないと記載しましたが動的に作成するコントロールは、オブジェクト名で扱うことが無いため指定が可能です
この処理では変数に代入して使用します

ちなみに、このコントロールの追加にはAddメソッドを使用しますが、引数内のオブジェクト名は省略可能で省略した場合は、既定の名称の付け方が適用されます

コーディングについて

このオブジェクト名はインテリセンス入力が有効になります

ユーザーフォームのオブジェクト名はフォーム内のコーディング中であれば構造的に記載する必要は無く、コントロールのオブジェクト名のみで可能です

別のフォームから参照したりする、別モジュールの場合はフォーム名から構造的に記載する必要があります
以下にそれぞれの書き方を記載します

フォームモジュール内であれば、フォーム名は省略可能
「CommandButton1.Caption = “test”」

フォームモジュール外であれば、フォーム名から記載する
「UserForm1.CommandButton1.Caption = “test”」

Captionプロパティは画面に表示される文字列の設定です
この文字をフォームから、もしくは他のモジュールから変更する際には上記のような記載の仕方をします

当然フォームモジュール内でも省略可能というだけなので、2つ目のフォーム名からの指定を行うことは可能です

ユーザーフォームを終了する

Unloadステートメントはフォームを終了するコード、閉じる際の処理についても解説

'コードフォームを終了する
Unload Me

'「UserForm1」を終了する
Unload UserForm1

ユーザーフォームを終了するには「Unload」ステートメントを使用します
このステートメントの引数にはフォームを指定します
1つ目はコードのあるユーザーフォームを終了します
2つ目は指定のフォームを終了します

コードを実行するとメモリ上から解放されます、関連するデータ全てメモリから解放されるので入力されていた内容も全て消去されます

Unloadの注意点

またこのステートメントで注意点として、読み込みされていないフォームを指定した場合はLoadが実行されてからこのUnloadが実行されます

エラーは出ないし、結果的に終了状態ではありますが、起動する無駄が発生するので基本的にはフォームは「Me」オブジェクトを対象にするのが良いと思います

閉じるボタンはいらない

ユーザーフォーム自体の×ボタンで終了することが可能なので、このコードがなくてもフォームを終了することは可能です

なのでこのステートメントを使用した閉じるボタンは基本的に必要ありません
Windowsを使用している環境でウィンドウの×ボタンが使いづらいことも考えにくいです
むしろ、慣れていれば直感的に×ボタンを使用して閉じるボタンを使わない可能性があります

終了処理を作成したい場合は、ボタンの処理で行うのではなく「QueryClose」イベントか「Terminate」イベントに作成します
この2つのイベントはフォームの終了時に発生するイベントです
Unloadステートメントも×ボタンによる終了もこれらのイベントは発生します

なので、このステートメントは処理中の自己フォームの終了かユーザーの操作しない情報表示のフォームを終了させたりする場合に使えます

またWinAPIにより×ボタンを無効にしてそちらから終了できないようにして、閉じるボタンを作成するような処理は完全に無駄な作業なのでしなくていいです

タスクキルや強制終了の方法はいくつかありそれらに対応する必要がでてきますので、それをするくらいなら終了イベントだけで対応したほうがよっぽど楽ですし、ユーザーにとっても有益だと思います

カテゴリー: ExcelVBA