VBAで小さなことから業務改善
ExcelVBAに関する記事
ShowModalプロパティは、フォーム表示中にExcelや別のフォームの操作を可能にするかを設定します
「ShowModal」プロパティはUserFormを表示している間に、UserForm以外の操作を可能にするかを設定できます
処理中に変更することはできませんので、コードはありません
Trueに設定すると、他の操作は不可となります
Falseに設定すると、他の操作が可能となります
この設定は、UserForm単体で処理を実行する場合以外に使用します
入力補助などでもワークシート操作を可能にしたり、複数のUserFormを同時に使用する場合に使用します
Trueに設定している場合は、フォームを終了するか非表示状態にすることで他の操作を可能な状態に戻すことが出来ます
なお、Excelの操作は出来ませんがVBEの操作は可能です
上記にもあるように、この設定はデザインウィンドウで行います
処理の途中で変化させることはできません
Showメソッドを実行する際に、引数に「vbModeless」を指定することでプロパティのFalseと同じ設定にして起動することが可能です
その際の処理の動きに注意点がありますので、以下の記事で解説しています
Addressプロパティでセル範囲のアドレス文字列を取得できます。R1C1形式での取得コード
'絶対参照アドレスを取得 Debug.Print Selection.Address(, , xlR1C1)
'A1からの相対参照アドレスを取得 Debug.Print Selection.Address(False, False, xlR1C1)
'B1からの相対参照アドレスを取得
Debug.Print Selection.Address(False, False, xlR1C1, , Range("B1"))
'ブック名とシートまで含めて取得 Debug.Print Selection.Address(False, False, xlR1C1, True)
指定のセル範囲アドレスを取得するには「Address」プロパティを使用します
アドレスは文字列で返され、引数の設定により取得内容が変化します
A1セルでアドレスを取得する動きです
Debug.Print Selection.Address(, , xlR1C1)
通常のR1C1形式の取得方法です
ここでは「R1C1」が返されます
Debug.Print Selection.Address(False, False, xlR1C1)
1つ目の引数が行の設定で、2つ目の引数が列の設定になります
Falseを指定することで、相対参照になります
相対参照で取得しますが、A1からの相対参照になるので「RC」が返されます
これは「R0C0」の事になりますが、0は表示しないのでアルファベットだけになっています
Debug.Print Selection.Address(False, False, xlR1C1, , Range("B1"))
相対参照で起点のセルをB1セルに指定して取得しますので「RC[-1]」が返されます
5つ目の引数をRangeで指定したセルを起点にします
ここではB1になるので、B1からA1へは列がマイナス1になるのでCが-1になっています
この5つ目の引数はR1C1形式でのみ使用可能です
Debug.Print Selection.Address(False, False, xlR1C1, True)
4つ目の引数をTrueに指定すると、ブック名とシート名を含めて取得しますので、「[Wordpress.xlsm]Sheet1!RC」が返されます
ブック名やシート名をそれぞれ省くことはできません
必要な場合は別の処理を組む必要があります
この画像ではSelectionの起点セルがB1になっています
3つ目以外のコードではアドレス取得の起点セルがA1からになっています
3つ目のコードでは起点セルがB1になっているので、返されている「RC:R[4]C」の「RC」は上の処理と同じアドレスですが、実際にはB1になっています
参照設定ならではの動きです
R1C1形式は、パッと見ただけでどのセルを指しているか分かりにくいのですが、相対参照で起点セルまで変更するともはや意味不明なほど分かりにくいですね
でもそれは、人間の脳での話でパソコン的には分かり易い表現なのでアドレスをユーザーに提示する訳ではない場合は有効な取得法ではあります
関連の記事
Addressプロパティは、選択範囲のセルアドレスを取得します。A1形式でのアドレスの取得コード
'絶対参照アドレスを取得 Debug.Print Selection.Address
'相対参照アドレスを取得 Debug.Print Selection.Address(False, False)
'ブック名とシートまで含めて取得 Debug.Print Selection.Address(False, False, , True)
指定のセル範囲アドレスを取得するには「Address」プロパティを使用します
アドレスは文字列で返され、引数の設定により取得内容が変化します
単一のセルアドレスの取得の動きです
Selection.Address
このコードでは絶対参照のアドレスが返されます
「$A$1」というように「$」の付いた形式になります
Selection.Address(False, False)
このコードでは絶対参照の「$」が外れた相対参照のアドレスが返されます
引数の1つ目と2つ目を共にFalseに指定します
1つ目の引数が行の設定で、2つ目の引数が列の設定になります
1つ目の引数のみTrueにすると「A$1」というようなアドレスが返されます
Selection.Address(False, False, , True)
このコードではブック名とシート名までを含めたアドレスが返されます
4つ目の引数をTrueに指定します
別ブックのアドレスをリンクさせる際に簡単にアドレスが取得できます
ブック名を省くことは出来ないので、ブック名が必要ない場合は文字列を加工するか、シート名を取得して結合させます
範囲選択した状態でも取得は可能で、「:」により結合されたセル範囲アドレスが取得されます
基本的にSelectionなどを使用してアドレスを取得しますので、アドレスからセル選択をするという動きの処理を作ることはほとんどありません
選択セルが指定セルアドレスと一致するかの条件分岐やワークシート関数をセルに入力するに使用します
関連の記事
Witheventsキーワードを使用すると、変数オブジェクトでイベントの作成が可能になります。最強説があります
イベント処理において、「Withevents」キーワードは最強です
身も蓋も後述していきますが、マジ最強伝説です
このキーワードは変数の宣言時に使用します
Dim WithEvents エクセル As Excel.Application
このコードの様に変数名の前に挿入します
このコードではExcelを代入する変数の宣言になります
このコードに関しては以下の記事に詳しく解説しています
Excelのイベントを使用する場合には必須のキーワードです
なので、主に使用される場面もこの使用場面になります
ですが、このキーワード最強伝説はイベント処理の対象を動的に変更できる点です
「UserFormでコントロールの動的新規作成は行ったはいいけど、それに処理が組み込めないなら意味無いやん」
「使いましょう、Withevents」
「ワークシートに埋め込みのコントロール、チェックボックスなどを大量に作成したけど全てのコントロールのイベントに同じ処理を作成すると、作成も大変だし、修正があったときはもっと大変やん」
「使いましょうよ、Withevents」
「シートイベントで、見積書などの入力補助処理を作成しました
原紙からコピー追加でシートが増えていきますが、シートイベントも同時にコピペされるので作成は問題ありませんでした、でも修正があって、シート作りすぎてて大変やん」
「もう使ったら、ええやん！Withevents」
くどいので、これ以上はあげませんが
要はイベントが複製される場合、処理中に作成したオブジェクトに対してイベントを作成する場合、これらに使用することができます
また、プロは嫌うかもしれませんが、通常のシートイベントなどをUserFormモジュールにまとめて作成することもできます
処理をまとめることは、メリットもデメリットもあります
メリットの方が大きい場合もあるので利用できます
記事上部のリンクにある「Excelイベントの作成方法」記事内で解説していますが、コードを入力するだけでイベントの作成が可能なのもいいよね
変数を解放すればイベントが発生しなくなるので、UserFormのコントロール系であればイベント無効を応用的に作成できるのもいいよね
やっぱイベント処理において、Witheventsキーワード最強です
ただ、このキーワードで作成出来るイベントはExcelイベント以外は全てのイベントを作成できない点には注意が必要です
作成出来るイベントの中で処理を考えましょう
そうです、全て完璧な存在より少し欠点があるくらいが愛嬌もあるというやつなので、これを弱点とは捉えなくていいんです
コーヒーにチョコレート！
からあげと餃子にビール！
悟空とベジータにポタラ！
イベント処理にWithevents！みんな最強や！
Excelイベントの作成・使用方法についてと使用時コード。イベントの中でも特殊なものです
'Excelのアプリを代入する変数、イベント作成が可能 Dim WithEvents エクセル As Excel.Application
'変数とExcelの紐付け（イベントが発生する） Set エクセル = Excel.Application
'紐付けの解除（イベントは発生しない） Set エクセル = Nothing
Excelイベントを作成するには、Excel自体をまずオブジェクトとして作成を行う必要があります
これには、1つ目のコードをオブジェクトモジュール内の最上部に挿入するだけで可能です
'Excelのアプリを代入する変数、イベント作成が可能 Dim WithEvents エクセル As Excel.Application
このコードを入れたモジュール内では、オブジェクトボックスに変数名が表示されます
それがExcelのオブジェクトになります
これは処理を実行しなくても、この変数名のオブジェクトは使用可能です
コードを入力した時点で使用可能になっています
ここで使用しているWithEventsキーワードは、変数でイベントの作成を行うことが出来るようにするステートメントです
このステートメントは、オブジェクトモジュール内でしか使用できません
オブジェクトモジュールとは、要は標準モジュール以外の全てになります
コーディング時点でエラーが発生するので、エラーが発生しないモジュールに移動してもらえばいいです
基本的には、ThisWorkbookかUserForm内に作成すればいいです
元々はクラスモジュールで、コントロールなどを動的作成する際に使用するもので、多くはクラスモジュールで使用されますが、このステートメント自体がクラスモジュール内限定のものでは無いので、使用するモジュール内で良いと思います
イベントの作成に関しては、通常のイベント作成と同様に行えます
イベント名も引数も全て自動的に作成されます
画像の様にオブジェクトボックスから選んだ時点で既定のイベントが作成されますので、違うイベントを作成したい場合は通常通りです
さらに、もう一つ作業が必要になります
それが紐付け作業です
'変数とExcelの紐付け（イベントが発生する） Set エクセル = Excel.Application
このコードを実行する事で、変数のExcelとコードを実行したExcelが紐付けられます
これ以降、この紐付けたExcelで作成したイベントが実行されるようになります
基本的には、WorkbookイベントのOpenイベントであったり、UserFormのInitializeイベントの初期イベントで実行しておきます
動きは後述します
'紐付けの解除（イベントは発生しない） Set エクセル = Nothing
そして、この紐付けを解除するには変数を初期化します
このコードを実行する事で、以降はExcelのイベントは発生しなくなります
オブジェクト変数なので、この解除（解放）までをセットで使用するようにしてください
基本的には、WorkbookのCloseイベントやUserFormのTerminateイベントの終了イベントで実行するようにします
上記のコードで紐付けが完了したら、イベントが発生するようになります
実際に動きを確認してみましょう
Private Sub エクセル_NewWorkbook(ByVal Wb As Workbook) MsgBox Wb.Name End Sub ------------------------------------------------------------------------- Private Sub Workbook_Activate() '変数とExcelの紐付け（イベントが発生する） Set エクセル = Excel.Application End Sub ------------------------------------------------------------------------- Private Sub Workbook_Deactivate() '紐付けの解除（イベントは発生しない） Set エクセル = Nothing End Sub
今回の例では上記のようなコードを作成しています
Workbookイベントで、アクティブになったら紐付けして、非アクティブになったら紐付けを解除します
このブックでしか、イベントが発生しないような作りです
イベントは、新規ブック作成時に発生するイベントです
内容はそのブック名をメッセージ表示します
まずは、新規作成によりイベントが発生している動きです
コード実行ブックで、新規作成を行う事で新規作成された「Book1」の名前がメッセージに表示されます
次に、そのBook1でさらに新規作成を行った時の動きです
ここではイベントが発生していません
元々コードの実行されていたブックが、Book1作成時点で非アクティブになったためExcelの紐付けが解除されたためです
最後に、また元のブックに戻って再度新規作成を実行します
今回はメッセージが表示されました
元のブックがアクティブ状態になったことで、また紐付けが行われたため
イベントが発生するようになりました
今回はBook3のメッセージが表示されました
上記の通りに、Excelのイベントを作成して実行できます
例で挙げたような、新規作成ブックの名前を表示する処理は非常に単純な処理ですが、これをイベントを使用せずにする場合はいろいろな事を考慮して作成する必要があり面倒です
イベントを使用すれば、作成したブックが引数としてオブジェクトに取得されるため、その後の新規作成ブックに対する処理が非常に簡単に行えるようになります
また、このExcelイベントの強力な利点は、Excel自体のイベントプロシージャの作成をマクロブックに行うことで、そのマクロブックを開いて紐付けをしておけば、マクロ無しの標準ブックであってもイベント処理を実行することが出来る点です
もちろんイベントではなく、通常の処理であればブックを指定すれば可能です
会社の規定などにより、マクロ有効ブックが作成できない場合があります
とはいえ、入力処理などが面倒でイベント処理で楽にできればええなぁと感じることはあります
そんな時に、このExcelイベントの出番になります
これは同じExcelアプリで開くブック全てに対して有効なイベントなので、シートのセルをダブルクリックするイベントなどを標準形式保存のマクロ無しブックでも実行する事が出来るようになります
SheetBeforeDoubleClickイベントは、シートのセルをダブルクリックすると発生するイベントです
このイベントに処理を作成すれば、Excelで開いているブック全てに同じイベント処理を実行する事ができます
WorkbookOpenイベントを利用すれば、ブックのOpenイベントも必要ありません
考えれば考えるほど、応用力があるイベントになります
あんなことや、こんなこと・・・えぇっ！？そんなことまでっ！！？
とは、まさにこのことです
ただ、イベント特有の注意点が便利なほど重要になってきます
全てのブックに適用されて、なおかつ紐付けが解除されるまで無条件で発動し続けるイベントになるので非常に要注意です
基本的には、このイベントでしか実現できない処理にだけ使用します
Workbook以下のイベントで対応できるものを作成する事だけは止めておきましょう
関連の記事
イベントとは、自動的に発生するプロシージャの事です。それの作成方法と使用時の注意点について
イベントとはワークシートやセル、ブックやユーザーフォームのコントロールなどのオブジェクトに存在する自動的に発生するプロシージャのことです
Workbookオブジェクトに対するイベントの作成方法です
まずはコードウィンドウ上部にあるオブジェクトボックスから選択を行います
この時に、既定のイベントが自動的に作成されます
このイベントが不要な場合は、後のイベント追加後に削除してかまいません
イベント作成には、作成したいオブジェクトのコード内に入力カーソルを置いた状態にします
すると、オブジェクトの選択ボックスにそのオブジェクトが指定されます
その状態で右にあるプロシージャボックスから指定のイベントを選択することで作成が可能です
画像にもあるようにこの時に、このオブジェクト名とイベント名、その引数までを自動的に作成してくれます
ちなみに、引数名は変更可能ですが絶対に変更しないでください
非常にコードが分かりづらくなります
基本的にイベントの作成は上記の様にVBEの画面で、全て自動的に作成されます
サイト内コードをコピペすると、オブジェクト名を修正しないといけなくなり面倒だと思います
なのでサイトの趣旨とは異なってしまいますが、コピー用コードの記載はせずにイベントの説明を行なっていきます
なので複数のイベントをまとめて記事にしていますので探しにくい場合ももしかしたらあるかもしれませんがご容赦ください
イベント全般で言える注意点は2つあります
・イベントは無意識下に実行されるプロシージャのため、ユーザーの行動次第で想定外のイベントが実行されてしまう可能性がある
まず、自動的に発生するという特性から非常に利点の多いイベントです
意識的にボタンをクリックしたりする必要がなく、ユーザービリティ向上には欠かせない要素です
ただ、意識して行うようなイベントであれば構いませんが、無意識に実行されるイベントもある点には注意してください
例えば、ダブルクリックすると発生するイベントはほぼユーザーが意識して行うイベントと思って良いと思います
対して、再計算やシート・ブックなどの選択変更ではユーザーの意思とは関係なく実行される可能性のあるイベントです
作成者側で想定していたタイミングでユーザーがそのイベントを発生させるとは限らない、ということは念頭に置いておいたほうが良いです
・プロシージャの実行中であってもイベントは発生する
イベントはイベントプロシージャでも通常のプロシージャでも、実行中にそのイベントが発生するとイベントプロシージャが割込みで発生します
セルのChangeイベントが作られたシートで、プロシージャの処理でセルの値を変更した場合、その変更した瞬間にセルのChangeイベントが実行されます
その後、元のプロシージャに戻ってきて処理が継続します
そして継続後にもセルの変更処理を行なっていた場合、その全てにおいてChangeイベントは実行されます
それが仕様としていれば構いませんが、違う場合は要注意です
その場合には、Excelでのイベントの発生を抑止する必要があります
以下の記事にその解説があります
イベントは非常に便利なのですが多用は禁物です
過去にイベントを多用していた頃、一つのChangeイベントが複数のイベントを連鎖的に発生させる状態になってしまっていました
そのせいで、一つのChangeイベントの処理変更を行う際に関係するイベント全てに対して修正が必要になり非常に大変な思いをしました
もしボタンなどで実行が問題ない場合は、イベントに頼らずボタンを作成し処理を実行するにしてください
後々機能追加などの処理変更の際にイベントが悪さをせずに済みます
Excelのイベントというのは、初期状態では上記の作成方法では作成できません
これはオブジェクトボックスにExcelが表示されないためです
記事中で解説しているのは、Workbookに対するイベント作成です
これはブックのことになるので、Excelで開いているファイルの1つでしかないということです
それに対してExcelとはアプリそのものになります、これがVBEで初期状態では表示できないため少し特殊なものになります
表示させる作業が必要になります、以下の記事で解説しています
Hiddenプロパティを使用して、行・列の非表示設定を行うコード。再表示についても解説します
'選択列の非表示 Selection.EntireColumn.Hidden = True
'選択列の表示 Selection.EntireColumn.Hidden = False
↑列の設定
'選択行の非表示 Selection.EntireRow.Hidden = True
'選択行の表示 Selection.EntireRow.Hidden = False
↑行の設定
'シート全体の行列を表示 Cells.EntireColumn.Hidden = False Cells.EntireRow.Hidden = False
↑シート全体の一括設定
行・列の表示・非表示の設定には「Hidden」プロパティを使用します
Trueに指定すると非表示、Falseを指定すると表示されます
このプロパティはRangeオブジェクトのプロパティになりますが、指定するにはRangeオブジェクトではエラーが発生します
「Range(“A:A”).Hidden = True」
この様にRangeオブジェクトでA列を指定するとエラーが発生します
必ず行や列を対象にする必要があります
以下の記事に指定セルの行や列をオブジェクトとして扱う方法があります
または、RowsプロパティかColumnsプロパティでも指定することが可能です
いずれにせよ、対象とするのがセル範囲ではなく行列にする必要があるという事なので、Entire～～プロパティを使用するのが便利です
再表示については、ワークシートでの操作と少し違います
ワークシートの操作では、C列を非表示にした場合、B～D列を範囲選択して右クリックメニューから再表示を選択します
この様にC列を包括する形で指定することで再表示できます
これに対して、VBAでは非表示の行列のセルが選択可能です
非表示とは、実際には幅が「0」に指定されているだけでそこに存在はしています
なので、C列を非表示にしていても「Range(“C1”).Select」でC1を選択状態にできますので、この状態で例コードの選択セル列を表示すれば再表示が可能です
また、特定の行列を指定する必要がなければシート全体を一括で表示させる5つ目のコードを使用してもいいです
あと、上記にもあるように幅が「0」に設定されているだけなので、その数値を変更する事でも再表示が可能です
以下の記事にあるメソッドを実行しても再表示されます
ちなみに、この記事のメソッドはセルに対しても行えるのでRangeオブジェクトでも実行可能です
Hiddenプロパティとコードの作りは似ていますが、全く別の形になっています
Names.Deleteメソッドで名前定義を削除できます。ブックとシート両方の削除コード
'ブック範囲の名前定義を削除する
ActiveWorkbook.Names("作成名前定義１").Delete
↑ブック適用範囲
'シート範囲の名前定義を削除する
ActiveWorkbook.Worksheets("Sheet1").Names("作成名前定義１").Delete
↑シート適用範囲
名前定義の削除には「Names(“定義名”).Delete」メソッドを使用します
ブック適用とシート適用ではメソッドの対象が変わります
「ActiveWorkbook.Names(“作成名前定義１”).Delete」
ブック適用範囲の名前定義を削除する場合に使用します
ブックに対してメソッドを使用します
「Worksheets(“Sheet1”).Names(“作成名前定義１”).Delete」
シート範囲の場合はシートの指定から削除を行います
名前が一意であれば指定無しでも削除は実行できますが、複数の同じ名前を使用していた場合エラーが発生しますので、シート範囲の名前定義はシート指定で削除を行います
また、NamesオブジェクトなのでItemプロパティがありますので、名前を指定しなくてもIndex番号でも指定は可能です
ただ、名前を指定せずに扱うことは処理上あまり使用することは少ないと思います
名前定義したセルの使用方法について。入力とGotoメソッドによる移動コード
'ブック範囲の名前定義セルに入力
Range("作成名前定義１") = 123
'ブック範囲の名前定義セルに移動 Application.Goto "作成名前定義１"
↑ブック適用範囲
'シート範囲の名前定義セルに入力
Worksheets("Sheet1").Range("作成名前定義１") = 123
'シート範囲の名前定義セルに移動
Application.Goto Worksheets("Sheet1").Range("作成名前定義１")
↑シート適用範囲
名前定義したセルはその名前文字列で以降は指定が可能になります
その際には、Rangeの引数には「”」で名前文字列を囲んで指定します
それぞれの名前定義については以下の記事を確認してください
「Range(“作成名前定義１”) = 123」
ブック適用範囲ではシートの指定が必要なく、アクティブブックに対しての処理になるので、シンプルにRangeオブジェクトの引数に名前文字列を指定して入力を行います
「Application.Goto “作成名前定義１”」
移動には、シートの移動をおこなえるGotoメソッドを使用します
「Worksheets(“Sheet1”).Range(“作成名前定義１”) = 123」
シート適用範囲の場合には、シートの指定から行う必要があります
通常のセル範囲と同じ使用方法になります
「Application.Goto Worksheets(“Sheet1”).Range(“作成名前定義１”)」
移動に関しては、同じくシート移動の出来るGotoメソッドを使用します
指定には、入力と同じようにシートから指定を行う必要があります
Gotoメソッドはシート間の移動までを行うためのコードであるため、シート移動の必要ないアクティブシートでの名前定義セルへの移動に関しては通常のセル選択に使用するSelectメソッドで可能です
Range("作成名前定義１").Select
上記のコードでは、名前定義されたセルを選択状態にします
しかし、別のシートであった場合エラーが発生します
入力に関しては、アクティブシートの相違には問題ありませんが選択状態にする場合は注意する必要があります
Names.Addメソッドはシート範囲の名前定義を作成変更するメソッドです。
'アクティブシートの選択範囲で名前定義 ActiveSheet.Names.Add Name:="作成名前定義１", RefersTo:="=" & Selection.Address
↑アクティブシートの場合
'指定シートの指定範囲で名前定義
Worksheets("Sheet1").Names.Add Name:="作成名前定義１", RefersTo:="=$A$1:$A$10"
↑アクティブ以外のシートの場合
シート範囲での名前定義をするには「Names.Add」メソッドをシート指定して使用します
指定した名前の名前定義が存在しない場合は新規作成され、存在する場合はセル範囲が更新されます
シート範囲で名前定義を行うと、その名前定義をシート内でしか使用できません
別のシートから参照するには、シートの指定から行う必要があります
「ActiveSheet.Names.Add Name:=”作成名前定義１”, RefersTo:=”=” & Selection.Address」
アクティブシートの選択範囲を名前定義します
引数「Name」は名前定義の名前文字列
引数「RefersTo」にはセルのアドレス文字列を指定します、セル範囲そのものではないのでRangeのAddressプロパティを使用します
アクティブシートの選択範囲なので、動的に名前定義する際に使用します
「Worksheets(“Sheet1″).Names.Add Name:=”作成名前定義１”, RefersTo:=”=$A$1:$A$10″」
引数の解説は上記と同じです
今回違う点が、シートを指定しているのと「RefersTo」にアドレス文字列が指定されているところです
この様に指定することで、アクティブシート以外のシートを指定することが可能です
また、シートを適用範囲とするメソッドなのでアドレス指定にはシートの指定は必要ありません、省略すると対象シートが指定されます
シート範囲の名前定義とは、別のシートからは参照できないようにシート内でのみ有効な名前定義になります
これを使用する場面は、同じ目的の名前定義を複数作成することで管理がめんどうになるのを防ぐ意味で使用します
シートごとに違う名前を指定する必要があるブック範囲ではコードの作成にも利便性が無いので、同じ名前の指定をしておけばシートを切り替えるだけで同じ名前定義を使用することができます
名前定義したセル範囲を指定する際には、別シートであればシートからの指定が必要になる点は注意が必要です
関連の記事