VBAで小さなことから業務改善
Hideメソッドを使用してフォームを非表示にするコード
'「UserForm1」を非表示にする UserForm1.Hide
ユーザーフォームを非表示にするには「Hide」メソッドを使用します
ユーザーフォームの「オブジェクト名」を指定して実行します
オブジェクト名を省略して「Hide」だけを記載するとアクティブなフォームが非表示になります
このコードを実行した時点で、非表示となりますがLoadされていないフォームを指定した場合はLoadのみ行い表示は行いません
つまりLoadステートメントと同じ動きをする形になります
Unloadステートメントでも解放を行うため非表示にはなりますが、ユーザーフォームの入力データはすべて初期状態に戻ってしまいます
処理を実行する際にユーザーフォームの表示が邪魔な場合に、終了させずに入力データを保持したままにしたいときに使用します
非表示状態ですがメモリ上には残ったままです
表示状態に戻すには「Show」メソッドを使用します
（Showメソッドの記事はこちら）
Showメソッドによるフォームの表示と表示方法の違いの注意点
'「UserForm1」を表示する UserForm1.Show
'他操作不可表示 UserForm1.Show vbModal
'他操作可能表示 UserForm1.Show vbModeless
作成したユーザーフォームを表示するには「Show」メソッドを使用します
ユーザーフォームの「オブジェクト名」を指定して実行します
このコード自体はフォームを表示するメソッドです
コード実行時に対象のフォームがLoadされていなければ、Loadが実行されてから表示処理が行われます
表示するメソッドなので、Hideメソッドで非表示になった状態からもこのメソッドで再表示を行います
'他操作不可表示 UserForm1.Show vbModal
'他操作可能表示 UserForm1.Show vbModeless
上記の2つの例コードでは引数を指定しています
ユーザーフォームを表示する際に表示方法の設定を行います
表示の方法とは、ユーザーフォーム表示中はExcelや他のフォームが操作できないようにするかしないかの設定です
ただこの設定に関してはユーザーフォーム自体の「ShowModal」プロパティで設定が可能で、引数を省略してShowメソッドを実行すればその設定が有効になります
vbModal（ShowModalがTrue）を設定した場合の画像です
UserForm2は、この設定になっています
なので、このフォームが表示されている間はExcelや他のフォームの操作が行えません
画像のマウスカーソルを確認してください
セル選択時の十字カーソルに変化せず、矢印カーソルのままです
この状態でクリックしてもビープ音がなり操作は出来ません
vbModeless（ShowModalがFalse）を設定した場合の動きの画像です
この設定では、Excelや他のフォームの操作が可能になります
画像でもワークシートのセル選択を変更できています
Showメソッドでの設定をすることはあまり無いかもしれませんがShowModalプロパティはコード中での変更が出来ないため、初期表示とは違う表示方法を選択したい場合には使えるかもしれません
表示方法の違いで続くコードが継続して実行されるか、一時停止するかが変わります
「vbModal」は、ユーザーフォームが終了するまで、このメソッドの行でプロシージャの実行は一時停止されます（こちらが既定値）
「vbModeless」は、ユーザーフォームが表示された時点でプロシージャの後続の処理も実行されます
UserForm1.Show vbModal MsgBox "" UserForm2.Show vbModeless MsgBox ""
上記のようなコードの組み方をして動作を確認します
UserForm1は操作不可の設定で表示、UserForm2は操作可能な設定で表示します
表示処理の後に、メッセージボックスを表示します
まずは、vbModalでUserForm1が表示されます
そのフォームが終了すると、後続のメッセージボックスの表示処理が実行されます
次に、vbModelessでUserForm2が表示されます
その後のメッセージボックスはフォームが表示されると同時に実行され、フォームより手前に表示されています
さらに処理はそのままEndSubまで実行されます
この時にフォームは表示されたままです
以上の事から、プロシージャでShowメソッドを記載した後になんらかの処理があった場合に注意が必要になります
このメソッドの後続処理が、フォーム表示中に実行するのかフォーム終了後に実行するのかを考慮したうえで作成を行ってください
もちろん後続処理がなければ注意する必要はありません
「vbModal」で表示されたフォームの後に、別のフォームを「vbModeless」で開くことが出来ません
vbModelessでは、Excelと別フォームの操作が許可されます
しかし、1つ目のフォームでは許可されていません
ここでその許可に矛盾が生じるためエラーが発生し起動が出来ません
後に操作が許可されるのは認められません
逆に先に許可されている状態から、許可されない動きは認められます
つまり、1つ目のフォームがvbModelessで表示されてExcel操作が可能な状態です
この後に2つ目のフォームでvbModelで表示された場合にExcel操作が不能になります
当然この状態では、Excelの操作も1つ目のフォームも操作できません
フォームを終了すれば、また操作が可能になります
AddControlイベントはAddメソッドによりコントロールが新規作成された時に発生します
AddControlイベントは、指定のフォームでコードにより新たにコントロールが作成されたときに発生するイベントです
最初にLoadされた時にもコントロールは作成されていますが、ここでは発生しません
Addメソッドを使用してコントロールを追加した際に発生します
引数
AddControl(ByVal Control As MSForms.Control)
Controlは、新規作成されたコントロールです
'フォームにボタンを新規作成
Dim 新ボタン As CommandButton
Set 新ボタン = Me.Controls.Add("Forms.CommandButton.1", "新ボタン")
Set 新ボタン = Nothing
ユーザーフォームでは、フォームにコントロールを作成したときに発生します
上記のコードでコマンドボタンを新規追加作成します
この2行目のコードで、イベントが発生します
引数
AddControl(ByVal Control As MSForms.Control)
Controlは、新規作成されたコントロールです
'フレームにボタンを新規作成
Dim 新ボタン As CommandButton
Set 新ボタン = Me.Frame1.Controls.Add("Forms.CommandButton.1", "新ボタン")
Set 新ボタン = Nothing
こちらのコードではFrameコントロールにコマンドボタンを新規作成します
フォームの方と同じで、2行目のコードでイベントが発生します
引数
AddControl(ByVal Index As Long, ByVal Control As MSForms.Control)
Indexは、追加したコントロールのページのインデックス番号です
Controlは、新規作成されたコントロールです
'フレームにボタンを新規作成
Dim 新ボタン As CommandButton
Set 新ボタン = Me.MultiPage1.Pages(0).Controls.Add("Forms.CommandButton.1", "新ボタン")
Set 新ボタン = Nothing
MultiPageコントロールの1ページ目にコマンドボタンを追加するコードです
ここも2行目のコード実行時にイベントが発生します
似たようなコントロールのTabStripコントロールではコントロールの追加作成は行えませんのでイベントもありません
また、ページの追加作成ではこのイベントは発生しません
Controlには新規作成したコントロールが代入されます
引数の型は「MSForms.Control」となっていますので、コントロールの型全てを含む型になります
なので、この引数でのコーディング時には各コントロールの専用プロパティなどがインテリセンス入力されませんが、使用は出来ます
Activateイベントは、初期表示後と他フォームからの切り替えで発生するイベントです
Activateイベントはアクティブ状態になったときに発生するイベントです
初期状態でいうと、フォームの表示される前に「Initialize」イベントが発生し、表示されて最初にこのイベントが発生します
このイベントは別のフォームからアクティブ状態になったときにも発生しますが、Excelや別のアプリからアクティブ状態になっても発生しません
複数のフォームを並行して使用する際に、このフォームイベントで特定のフォームがアクティブ状態になったら関連するシートやブックに切り替える、といった動きに使えます
複数のフォームを自由に切替するには「ShowModal」プロパティをFalseに設定する必要があります
他によくあるのがフォームを別のアプリ（Excel以外）よりも全面に表示する処理でも使用します
Excel以外のアプリをアクティブ状態にすれば、当然そのアプリが最前面表示されますが、その際にすべてのアプリより全面に表示させることでなんらかの情報をフォームに表示させて他のアプリを操作できます
これにはWinAPIを使用して、アクティブウィンドウとしてユーザーフォームのハンドル値を取得してそれに対して表示処理を設定します
これと同じようにWinAPIを使用して、×ボタンを使用不可にしたりする際にも使用します
このように、このイベントは大きく2通りの目的があるので、状況に合わせて使用してください
ユーザーフォームについての前提解説と、各イベント・メソッド・プロパティの記事一覧
この記事でのUserFormとは、モジュールの事ではなくフォームの事です
UserFormは、ユーザーフォームモジュール1つにつき1つ作成可能で、おおもとのフォームになります
いわゆる、ウィンドウそのものという事になります
このフォームの中に各コントロールを追加して、UserFormを作成していきます（ユーザーフォームの作成に関してはこちら）
UserFormのイベントについて、他のコントロールと違ってUserFormはモジュールに1つしか作成できないため、コントロール名をイベントプロシージャ名に反映しません
常に「UserForm」が指定されます
UserFormのメソッドには、読み込みと終了をさせるメソッドはありません
それらにはステートメントを使用しますので、コードが少し違っています
AutoFitメソッドにより、行と列のサイズを自動調整するコード、特定のセルでの列幅自動調整コード
'選択された行高を自動調整する Selection.EntireRow.AutoFit
'選択された列幅を自動調整する Selection.EntireColumn.AutoFit
'全ての行列の幅を自動調整する Cells.EntireRow.AutoFit Cells.EntireColumn.AutoFit
行高列幅の自動調整には「AutoFit」メソッドを使用します
このメソッドは指定したセル範囲から行か列か自動的に判断して自動調整を行います
対象には行か列を指定する必要があるため、「Range(“A:A”)」と列の指定を行っても構いませんが、列や行でしか実行できないメソッドなので対象を確実に行列にするために「EntireColumn」か「EntireRow」を使用して対象を指定します
この2つのプロパティに関してはこちらで確認してください
また、行列の判定を自動的に行うという性質から両方を同時に指定することが出来ません
シート全体の行と列両方を自動調整する場合は、行と列をそれぞれ自動調整する必要があります
シート全体を指定するので、ここでは「Cells」に対する行列の指定を行っています
'A1だけのデータに合わせてA列の幅を自動調整
Range("A1").Columns.AutoFit
使用する頻度が低そうなので単独の記事ではなく、ここに追加しておきます
上記の自動調整では指定範囲内のセルの内で最大の文字数に合わせて調整が行われます
ただ、その場合だと一つだけ極端に長い文字列があり、それに調整された結果とんでもない列幅になってしまうことがあります
そんな場合に、このコードは利用できます
画像の動きを確認してください
コード実行後に、列幅が調整されてA1のセル内容が全て表示されるように調整されていますが、A2はまだはみ出したままです
A1のデータのみで自動調整されていることが分かります
そして、列幅としか記載していない通りこの特定セルでの調整は行高のサイズ調整を行えません
コード自体の実行は可能ですが、結局最大文字サイズの高さに調整されます
利用場面が少ないことが予想されますがこんなことも出来ると思っていてください
関連の記事
PasteSpecialメソッドを使用して、特定のセルデータを貼り付けるコード、値・書式・数式の3種類
Selection.Copy
'値のみ貼り付け
Range("B1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.Copy
'書式のみ貼り付け
Range("B1").PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Selection.Copy
'数式の貼り付け
Range("B1").PasteSpecial Paste:=xlPasteFormulas
Application.CutCopyMode = False
セルの全てのデータではなく、特定のデータのみを貼り付けするには「PasteSpecial」メソッドを使用します。このメソッドの引数「Paste」の設定により貼り付けるデータを指定できます
「Selection.Copy」
「Application.CutCopyMode = False」
の2行に関しては、セルのコピーに解説があります
（こちらの記事で確認してください）
「Range(“B1”).PasteSpecial Paste:=xlPasteValues」
セルの値のみ貼り付けには引数に「xlPasteValues」を指定します
画像ではB列が貼り付け結果です
A3はSUM関数が入力されたものを貼り付けしていますが、「333」という数値が入っていることを確認してください
なお、この画像ではわかりやすくするために「数式の表示」を有効にしていますので、見えている関数は文字列ではありません
「Range(“B1”).PasteSpecial Paste:=xlPasteFormats」
セルの書式のみ貼り付けには引数に「xlPasteFormats」を指定します
画像ではC列が貼り付け結果です
書式のみを貼り付けを行う際には、元データから表などの範囲全体に適用する事も多いと思います
なので、コピー元は1つのセルを指定して、貼り付け先を範囲指定にすればその範囲全てに同じ設定が貼り付けられます
「Range(“B1”).PasteSpecial Paste:=xlPasteFormulas」
セルの数式を貼り付けするには引数に「xlPasteFormulas」を指定します
画像ではD列が貼り付け結果です
画像ではA3のSUM関数が相対参照で同じセル参照の関数が貼り付けられています
上記にあるように関数の表示をしているので、確認してください
数式のコピーは小計を出したりするときに使う事も多いと思いますが
貼り付け先を範囲指定にすれば、同じ形式の数式が貼り付けられます
この構文では引数にあるセルアドレスは相対参照で更新されます
このメソッドでは貼り付け先が選択状態になりますので、アクティブセルの使用時には注意が必要です
また通常の貼り付けとは違い、このメソッドではセルを対象オブジェクトとして貼り付けを実行する事ができます
通常の貼り付け操作では、Applicationオブジェクトに対するメソッドでした
これは、形式を選択して貼り付けとはセルに対して行うものだからです
図形に値のみの貼り付けは存在しません
とはいえ、コピー先をコピーしてモードの終了という流れは全く同じです
あとこのメソッドは「Copy」メソッドと違い、クリップボードを介さない直接貼り付けはできません
なので、コピー元でCopyメソッドを使用してから、対象のセルに貼り付けを実行する必要があります
引数「Paste」には、他にも設定が可能です
Excelでのセルの貼り付け操作時の「形式を選択して貼り付け」が相当するので、そこで指定できる選択肢は全て引数から指定できます
ユーザーフォームの作成に関するVBEでの操作解説
ユーザーフォームとは、VBAで作成できるExcelとは別のウィンドウを持つ画面のことです
ユーザーフォームを使用することで、ユーザービリティが格段に上昇するため、初心者のうちから使っていきたい機能です
ユーザーフォームは、シートや標準モジュール同様にユーザーフォームモジュールを作成することで処理の作成を行うことが出来ます
作成の方法は、標準モジュールと同様に挿入タブから「ユーザーフォーム」を選択するだけです
選択すると、「UserForm1」という名称のユーザーフォームが作成されます
ウィンドウ自体も自動的に作成され、このウィンドウが画面上に実際に表示されるものになります、原寸大サイズです。
このフォーム上にコントロールを追加するには、ツールボックスという画面から任意のコントロールを選択して行います
基本的には、フォームの編集画面であれば表示されますが、もし表示されていない場合は表示タブの「ツールボックス」を有効にしてください
ツールボックスから追加したいコントロールを選択して、フォーム上でクリックするだけで初期状態のコントロールが作成されます
これは図形の作成要領と同じなので、作成段階でドラッグ操作を行えば大きさの指定も可能ではありますが、特にそこまでしなくてもいいとは思います
図形と同じ要領で、作成したコントロールはドラッグ操作で移動させることも可能です
コントロールには表示させる文字が設定できます
画像では、「ボタン」という文字列に設定しました
ですが、初期状態ではこの文字列に対してコマンドボタンが少し大きいのでサイズの調整を行います
選択枠の□をドラッグしてサイズ調整してもいいのですが、自動調整する方法があります
コントロールの選択枠の右3つの□の内、下2つのどちらかをダブルクリックすることで表示された文字列に合わせて自動的にサイズを調整してくれます
横幅だけでなく高さも調整してくれます
複数のコントロールを作成して、適当に配置していくとどうしてもずれていってしまうことがありますので、その際は整えたいコントロールを複数選択して調整を行います
この際に調整の親となるのが1つだけ選択枠が白い□のコントロールが指定されます
最後に選んだものが親となるので、親にしたいコントロールをうまく選択してください
そのコントロール上で右クリックメニューの整列から任意の設定で整列させることができます
画像の例では、左揃えに指定しています
作成したコントロールに処理を作成するには、コードを作成します
また、ユーザーフォームでは基本的にコントロールのイベントに対して処理を作成していきます
コントロールをダブルクリックすることで、既定のイベントが自動的に作成されます
画像の例では、コマンドボタンのClickイベントが作成されています
コマンドボタンがクリックされたときに発生するイベントです
このイベント名の前にあるのが、コントロールの名前になりますので、イベント作成後にコントロールの名前を変更した場合はここも修正する必要がありますので注意してください
コマンドボタンの既定のイベントはClickイベントでした
これ以外のイベントを作成するには、プロシージャボックスから作成したいイベントを選択します
選択したイベントにオブジェクト名も付与されてイベントプロシージャが自動的に作成されます
こうして1つのコントロールでも、ユーザーの行った動作で別の動きを発生させることが出来ます
少し前後しますが、フォームの大きさや名前など全ての設定はプロパティウィンドウで設定可能です
ここでは初期値の設定となります、ほとんどのプロパティはコード上で変更が可能になるので、フォームを起動してからでもコントロールの色や大きさは変更可能です
ユーザーフォームはExcelとは別のウィンドウを持ちますが、あくまでもExcelの中の機能であることに変わりは無いので、Excelを起動せずにフォームを表示するようなことはできません
またユーザーフォームは複数の同時起動が可能です
同じフォームは再起動されるだけですが、別のフォームを複数表示して大きな処理毎に画面を切り替えていくような作りにもできます
ユーザーフォームはシステム作成レベルのことが出来るにもかかわらず作成するのが非常に簡単です
コントロールの作成も、イベントの追加もマウス操作だけで完了してしまうからです
ユーザーフォームはレベルの高い機能ではありません
初心者でも十分に扱うことの出来る有効な機能なので簡単なことから使っていくようにしてみてください
OSに処理を渡すことで、処理中にExcel操作が可能になるDoEvents関数について
'OSに処理を渡す DoEvents
処理中にOSに処理を渡す関数が「DoEvents」関数です
引数は無く、この関数名のみで使用可能です
このコードは単純に処理の中で実行しても何も起きていないように感じる関数です
この関数は、ループ処理で使用します
ループ処理はCPUへの負荷が強く、大量なループ処理や無限ループではExcelが応答なしの状態になり、描画処理も停止するため、いわゆるフリーズしたように見える状態になります
VBAでは処理実行中に「Ctrl+Break」か「Esc」で処理の一時停止させることができます
しかし、処理の内容によってはその操作が効かなくなる場合がありますし、そもそも本当にフリーズしていれば当然停止不能になります
そういった状況を防ぐために使用するのが、この関数です
この関数をループ処理の内側どこでもいいので入れてあげると、その瞬間だけVBAの処理の為に使用している制御をOS（Windows）に渡しますので、VBAの処理は停止しますがそのタイミングでOSの操作が可能になるためVBEの画面もExcelも操作が可能になります
ただ、上記の停止操作はOSに制御が移っているせいで停止が効きません
ですが、VBEの操作ができるのでループ処理内のどこかでブレイクポイントを設置してあげるとそこで停止します
関数を入れずにループさせた場合、どこかの時点でExcelの描画処理が停止します
その状態でExcelをクリックすると、応答なしの表示がされてしまいフリーズのような画面になります
この関数を使用すると、この描画処理がOS制御でしっかり行ってくれるので描画処理が停止しませんので、VBAが頑張っているところを見せつけられます
ただ、この関数を使用するとOSに制御を渡すため処理時間は大幅に延長してしまいます、まあ別の制御も行ってるんですから当然です
なので、この関数での目的は処理速度ではなく
処理実行中の待ち時間を無駄にしないために使用することになります
また、IE制御などの待機用無限ループでは必須の関数になります
Excelの操作が可能になるため、アクティブセルを操作するような処理では、動作不良を起こす可能性が非常に高いのでそういった処理で使用する場合であったり、Excel以外の全てのアプリも操作が可能になるため、IE制御であれば制御中のIEを終了されてしまったり、WinAPIでアクティブウィンドウのハンドル値を取得しているのが思惑と違うウィンドウになってしまったり、とさまざまなバグを発生させる可能性があります
ユーザーの為に操作を可能にするのであれば、そういった可能性を考慮した処理作成を行いましょう
Application.WaitかTimerを使用して処理を一時停止するコードとそれに関する注意点
'5秒間一時停止
Application.Wait (DateAdd("s", 5, Now))
Dim 開始時間 As Double Const 停止時間 As Double = 0.5 '待ち時間の開始時間の取得 開始時間 = Timer '開始時間から停止時間数値を超えるまで無限ループ Do Until Timer - 開始時間 >= 停止時間 DoEvents Loop
処理を一時停止するには「Application.Wait」メソッドか「Timer」関数を応用するかのどちらかを使用します
それぞれに利点があるので、解説します
こちらはこの処理を一時停止させることを目的としたメソッドです
引数が必須で1つ、指定するのは時刻データ（Time）です
時刻データの引数になるので、日付データは含まれないため日をまたぐ場合は別途処理が必要になりますが、一時停止という観点からそれはほぼないと思います
そして、引数の時間に達したときにこの関数は「True」を返すとともに処理を開始します
つまり、どれだけ停止させるというよりは、いつ再開させるという考え方です
目的のメソッドであるため、1行で実装できる点が便利です
ただ、この停止時間中はExcelの操作は一切行えない状態になります
バックグラウンド処理は実行されるようですが、Excelはフリーズしたような印象を受ける形になります
その認識の無い人が遭遇した場合、タスクキルしてしまうかもしれません
その辺の注意点さえ気にしていれば、停止処理はこれで十分です
Timer関数を使用する場合は、もともと目的の関数ではないので少し煩雑になります
「Dim 開始時間 As Double」
「Const 停止時間 As Double = 0.5」
1行目は開始時間を代入する変数です
2行目は定数を使用して停止時間を指定する箇所で、秒数を指定します
ここでは「0.5」秒が指定されます
「開始時間 = Timer」
停止時間の開始時間を取得しています
「Do Until Timer – 開始時間 >= 停止時間」
「DoEvents」
「Loop」
ループ処理で待機させます
1行目での終了条件が差し引き時間秒数が停止時間秒数を超えているかです
2行目は後述します
3行目でループされます
このループは差し引き時間まで無限にループさせる処理です
実際のループ回数は計り知れませんが、その際に2行目のコードを入れることで「Application.Wait」よりも柔軟な使用が可能になります
と、いうのもApplication.Waitの解説にあるように停止中はExcel自体が停止するためフリーズした状態になります
これを回避するのが「DoEvents」関数です
この関数はVBAがOSに処理を一時的に渡す関数です
この関数を使用することにより、Excelの操作が可能になるのでフリーズしたような動作をすることが無くなります
また、もう一つ重要な点として「描画処理」も行われる点にも優位性があります
例えば停止処理を入れつつループ処理を実行する場合に重要です
デジタル時計を表示させる場合は、停止時間を「1」にして1秒ごとに時刻データを送信し続けます
そうすると、ある時点から描画処理が行われなくなり画面がフリーズしたような状態になり、処理終了後に描画処理が行われて最終結果が表示されます
OSに処理を渡すことで、OSは描画処理も実行しますのでここが重要になる場合もあります
上記のように利点が異なります
使い分けとしては
・簡便なコーディング、開始時刻の指定、停止中にExcelの操作を許可しない場合はApplication.Waitを使用
・1秒未満の停止時間の指定、Excelの操作を可能、描画処理を行う場合はTimer関数を使用
処理を停止する場面はいろいろあると思いますが、停止前と開始後でセルのデータやアクティブブックが入れ替わっていて処理がおかしくなるようなら操作の許可は出来ないわけです
ただ、これに関してはTimer関数のコードからDoEvents関数を削除すれば同じようになります
Application.Waitは停止時間ではなく開始時刻を指定するので、特定の時刻に処理を開始したい場合には優位
Timer関数側ではミリ秒単位の停止時間の指定が可能なので、そこが優位ですね
ただこの2つのコードには大きな問題があります
CPUの消費量が高いことです
Application.Waitメソッドの内部的な動きは分かりませんが、Timer関数では大量にDoループが走っています
タスクマネージャーで確認しましたが、どちらも同じ消費量だったのでこれに関しての優位性はありませんでした
特にDoEvents関数で他の処理も行った場合の影響は大きそうですので、そのあたりは少し頭に入れておいてください
そして、そこでこの問題を解決してくれるのが「Sleep」関数です
これはWinAPI関数の1つです
WinAPIなので、指定の必須の関数宣言を必要としさらにbit環境にも対応しておかないといけないので初心者には少し荷が重いかもしれません
ただ、この関数はその名の通り休ませる関数なのでCPUの消費量は上がりません
WinAPIの理解とCPU占有による弊害が見逃せない状況になったら一度使用を検討してみましょう
そうでなければ、VBA標準コードのみで実装できる記事コードで十分です
関連の記事