カテゴリー: ちょっと処理

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

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

コードコピー

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

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

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

コードコピー

'5秒間一時停止
Application.Wait (DateAdd("s", 5, Now))

コードコピー

Dim 開始時間 As Double
Const 停止時間 As Double = 0.5

'待ち時間の開始時間の取得
開始時間 = Timer
'開始時間から停止時間数値を超えるまで無限ループ
Do Until Timer - 開始時間 >= 停止時間
DoEvents
Loop

処理を一時停止するには「Application.Wait」メソッドか「Timer」関数を応用するかのどちらかを使用します

それぞれに利点があるので、解説します

コード解説（Application.Wait使用）

こちらはこの処理を一時停止させることを目的としたメソッドです
引数が必須で1つ、指定するのは時刻データ（Time）です

時刻データの引数になるので、日付データは含まれないため日をまたぐ場合は別途処理が必要になりますが、一時停止という観点からそれはほぼないと思います

そして、引数の時間に達したときにこの関数は「True」を返すとともに処理を開始します
つまり、どれだけ停止させるというよりは、いつ再開させるという考え方です

目的のメソッドであるため、1行で実装できる点が便利です
ただ、この停止時間中はExcelの操作は一切行えない状態になります
バックグラウンド処理は実行されるようですが、Excelはフリーズしたような印象を受ける形になります
その認識の無い人が遭遇した場合、タスクキルしてしまうかもしれません

その辺の注意点さえ気にしていれば、停止処理はこれで十分です

コード解説（Timer関数使用）

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は描画処理も実行しますのでここが重要になる場合もあります

それぞれの使い分けとSleep関数について

上記のように利点が異なります

使い分けとしては
・簡便なコーディング、開始時刻の指定、停止中に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標準コードのみで実装できる記事コードで十分です

コードコピー

'タイマーのスタート
Dim 処理開始 As Double
処理開始 = Timer

'処理時間の取得
Debug.Print Timer - 処理開始

処理時間の計測にはTimer関数を使用します

この関数は0時からの経過時間を返す関数で、引数はありません
これは時間を返す関数ですが実際のデータは小数点値を含むSingle型で返されます

ミリ秒までの時間計算ができるため処理時間の計測に向いています（Macでは秒刻みの計測しかできないようです）
ちなみに、Single型で返されますがちゃんとそれ以下の範囲も存在しているため変数の型はDouble型で取得させて計算したほうが精度が高くなります

コード解説

「Dim 処理開始 As Double」
「処理開始 = Timer」
1行目で変数の宣言、上記にもあるように型はDouble型を使用してください
2行目で変数に開始前の時間を代入することで、計測の開始時間とします
この2行目のコードまでに時間計測を行わない処理があれば、その後にこのコードは移動してください

「Debug.Print Timer – 処理開始」
ここで取得していた変数の時間と現在の時間を計算して、差の時間で処理時間を計測することが出来ます
出力はイミディエイト画面に出力されます、メッセージボックスだと処理が停止してそのメッセージを終了させるまでも計測の時間に含まれるので向いていません

処理時間計測の重要性

基本的に処理時間の計算を使う場面は、処理を作成しその処理がどれぐらい時間をかかるかというのを計算するというよりは実際に作った処理の時間が思ったよりも長かったという場合にどこで処理の時間が長くかかっているかを調べる場合に使用します

処理時間の改善に関しては、原因がさまざまあり単純にExcelの画面更新を抑止すればいいとか、再計算を無くせばいいというものではありません
実際計測してみると、画面更新の抑止が意味が無かったりする場合もあります

一定の処理ごとの時間計算をゼロからきっちり計算させる場合は時間計算をした時点で一度変数をリセットする必要があります
これは単純に2行目のコードをもう一度実行するだけでいいです

そこから得た処理の方法と手順に改善の余地がないかをWeb等の情報で検討してみてください、過去に偉人たちがすでに解決してくれていることばかりのはずなので
ただ、ブックを開くなどどうしようもない処理もあるのでその場合はブックを処理前に開いておくとか方法を検討します

また上記にもあるようにメッセージボックスで処理が一時中断するものがある場合は、そこの時間は考慮しないようにうまくこのコードを配置してください

あと、0時からの経過時間なので日をまたぐ計算を行う場合は、一度日をまたぐ前に数値を保持しておく必要があります
そんなに長い時間の計測をするかどうかは分かりませんが・・・

コードコピー

'アクティブブックをオブジェクト変数に代入
Dim ブック As Workbook: Set ブック = ActiveWorkbook

'オブジェクトを開放
Set ブック = Nothing

コードコピー

'アクティブシートをオブジェクト変数に代入
Dim シート As Worksheet: Set シート = ActiveSheet

'オブジェクトを開放
Set シート = Nothing

コードコピー

'アクティブセルをオブジェクト変数に代入
Dim セル As Range: Set セル = ActiveCell

'オブジェクトを開放
Set セル = Nothing

オブジェクト変数にそれぞれの代入と処理後に使用する解放のコードです

この処理では、アクティブなものを代入する形になっているので処理中の動的なオブジェクトへの対応が可能になります

また、オブジェクトは変数の型がそれぞれちゃんとあり
それらを使用することで、インテリセンス入力が出来るようになるので代入オブジェクトの型が確定している場合は型宣言もしっかり行います

「ブック.」と入力すれば使用可能なメソッドとプロパティが表示されます
他のシートとセルも同じです

一応、全てのオブジェクトはobject型に代入することは可能です

ブックやシートの代入は、主には新規作成した物をを代入することが多いかもしれません

ブックの新規作成を行うと、そのブックがアクティブブックになるのでこのコードを使用して、新規作成ブックを簡単に操作することが可能になります
シートの新規作成も同じ使い方になります

セルの代入は別ブック・シートのセルを代入させることが多いです
別ブックやシートのセルはその上位のオブジェクト名から指定する必要があるので変数を使用しない場合、コードが非常に長くなってしまい可読性が低下します

セルもシートも同じですが、アクティブを代入するだけで上位のオブジェクトも含めて代入されるので、ブック間を移動する処理であれば非常に便利で分かり易くなります

こんな感じのものが実際に代入されています
コード　「Set セル = ActiveCell」
実際　「Set セル = ActiveWorkbook.ActiveSheet.ActiveCell」

この例文の「セル」の2文字だけで済むのがいかに簡潔になっているかが分かると思います（使用時は分かり易い変数名に変更してください）

最後に変数の解放はしっかり行ってください
上記にあるように、「ブック」という変数にはブックの情報すべてが変数に代入されています
シート情報もその中のセル全ての情報が入っているわけです
これをメモリ上に保持しているわけですから、もう使用しないのに保持しておくことはメモリを無駄に消費している、ということがなんとなくイメージできると思います

なので、1つだけなら処理速度に影響ないし、とは考えずに解放は行ってください

コードコピー

'Excelを最大化して、そのサイズに合わせてフォームの大きさも変更
Application.WindowState = xlMaximized
Const 高さ割合 As Double = 0.8
Me.Zoom = Me.Zoom * ((Application.Height * 高さ割合) / Me.Height)
Me.Width = Me.Width * ((Application.Height * 高さ割合) / Me.Height)
Me.Height = Me.Height * ((Application.Height * 高さ割合) / Me.Height)

↑Initializeイベント内に貼り付け

コードコピー

Private Sub UserForm_Initialize()

'Excelを最大化して、そのサイズに合わせてフォームの大きさも変更
Application.WindowState = xlMaximized
Const 高さ割合 As Double = 0.8
Me.Zoom = Me.Zoom * ((Application.Height * 高さ割合) / Me.Height)
Me.Width = Me.Width * ((Application.Height * 高さ割合) / Me.Height)
Me.Height = Me.Height * ((Application.Height * 高さ割合) / Me.Height)

End Sub

↑イベント未作成時の場合、モジュール内に貼り付け

ユーザーフォームと解像度

ユーザーフォームをある程度作成するようになって時間が経ってくると、気づくことがあります
それが、PC画面の解像度の違いによってフォームがちっちゃなってしまうことです

逆に、解像度の高いPCで作ったフォームが低いPCではみ出してしまったり妙にでかくて不格好になってしまうこともあります

この状況に対応するには、フォームの大きさを解像度に合わせて変更してあげる必要があります

以前考えていた方法は、作成したフォームの大きさを元に調整していましたがその方法だとコード上にその数値を入力する必要もあり、ワイド画面かどうかでも修正が必要だったのですこし面倒でした

そこで今回考えたのが、今回の例コードです
これはExcelの高さを元に計算するので、ワイド画面かどうかの判断は必要なくなりました

ただ、一つどうしようもないのは文字のはみ出しのZoomプロパティの調整関係ですが、これはこのプロパティで調整する以上はあきらめるしかありません
これの完璧な調整はコマンドそれぞれに大きさの調整を行っていく必要があります

コードで自動サイズ調整

今では少し懐かしいサイズの解像度の【800×600】の画像です
当初このサイズ感で作成したので、同じ解像度ではなんの問題もありませんでした

しかし、解像度の上昇により同じサイズであった場合画像のように小さくなってしまいます
Excelとの大きさを比較してもらうと分かり易いと思いますが、これでは文字が見えづらく、みんなPC画面をにらみつけるようになってしまいます

フォームの見栄えも、みんなの印象も悪くなる前に何とかしたほうが良さそうなので例コードを使用してみます

この画像がコードを実行してサイズが自動調整された大きさです
実行前の画像と比べてみるとサイズがかなり大きくなっているのが分かると思います

こちらのコードは、Excelのサイズに自動調整されるので作成時の大きさはほぼ意味がなくなります

これは、作成時の解像度でのコード実行時の動きです
この様に作成したときの解像度であってもサイズが自動調整されます

コードの解説

Application.WindowState = xlMaximized

これは、Excelウィンドウのサイズを最大化に設定します
Excelウィンドウサイズの高さで調整するので、これが無いと画面の大きさに等しくならないのでここで設定を入れておきます

もし、最大化することがはばかられる場合は事前にサイズの設定を取得しておき高さを取得してから戻せばいいです

WinAPIを使えば解像度の取得は出来るので、ここは必要なくなりますがこの1行で準備できるなら楽ですよね

Const 高さ割合 As Double = 0.8

ここでExcelウィンドウの高さに対するフォームの高さを定数で指定します

この「0.8」が割合の数値ですので、Excelの高さの80%という意味になります

ここの数値を任意に変更してください、この設定値次第ではZoomプロパティでエラーが発生する可能性があるのでその際は調整してください
Double型なので、もっと細かい数値設定は可能ですが「1」を超えないようにしてください、画面からはみだしますね

Me.Zoom = Me.Zoom * ((Application.Height * 高さ割合) / Me.Height)

ここからフォームの表示設定を行っています

ここのオブジェクト名「Me」とは、このコードの記載されたユーザーフォームが指定されるもので、ActiveCellのような感じの使い方です

Excelウィンドウのの高さに定数「高さ割合」を積算します
ここでは「0.8」を積算する形になります
その高さとフォームの高さを除算して割合を出します

この時、画像の例とは逆にフォームがExcel本体より大きすぎた場合は、100%未満になってフォームのサイズが小さくなります

Me.Width = Me.Width * ((Application.Height * 高さ割合) / Me.Height)

次にフォームの横幅を調整します
Zoomプロパティと同じように計算を行います

なお、高さを元に調整しているのでZoomとWidthは入れ替わっても問題ありませんが、最後にHeightは調整するようにしてください

Me.Height = Me.Height * ((Application.Height * 高さ割合) / Me.Height)

最後に高さの調整です
計算自体は全て同じですが、ここで計算式に入っているHeightプロパティに代入するのでこれ以降は同じ計算は行えません

またコードを見て分かるように、割合の計算式は3行とも同じものなので変数や定数を使用して割合数値を代入して計算させてもいいです
例コードではコピペ行数を減らすために使用していません

最後にこのコードはユーザーフォームの「Initialize」イベント内に作成してください、起動してから変更する場合は位置調整が必要になる場合があるので面倒です
また、UserFormの「StartUpPosition」には画面の中央に設定をしておくと変更後のサイズで自動的に中央に配置してくれます

別のサイズ調整方法として

コードコピー

'Excelを最大化して、そのサイズに合わせてフォームの大きさも変更
Application.WindowState = xlMaximized
Me.Zoom = Me.Zoom * (Application.Height / Me.Tag)
Me.Width = Me.Width * (Application.Height / Me.Tag)
Me.Height = Me.Height * (Application.Height / Me.Tag)

コードコピー

Private Sub UserForm_Initialize()

'Excelを最大化して、そのサイズに合わせてフォームの大きさも変更
Application.WindowState = xlMaximized
Me.Zoom = Me.Zoom * (Application.Height / Me.Tag)
Me.Width = Me.Width * (Application.Height / Me.Tag)
Me.Height = Me.Height * (Application.Height / Me.Tag)

End Sub

↑イベント未作成時の場合

別の調整方法として作成時のExcelの高さを保存しておきその数値で新しい環境と比較して自動調整する方法です

こちらは1つ目の方法と違って、作成時の環境の高さの数値を保存しておく必要があるため少し面倒なので少しだけ紹介しておきます
1つ目と違うのは割合の計算式の部分だけなのでそれ以外のコードは1つ目のコードを参照してください

Me.Zoom = Me.Zoom * (Application.Height / Me.Tag)
赤字の部分だけ違っていますが、これはExcelの高さをユーザーフォームのTagプロパティに保存された数値で除算して割合を出しています
このTagプロパティに保存する数値は作成したPC環境でのExcelの高さの数値を入力しておきます

コードコピー

Debug.Print Application.Height

このコードでExcelの高さをイミディエイトに出力できますので、この数値をTagプロパティに入力してください
この数値を基準に割合計算を行います

こちらの方法であれば、作成したときの大きさと同じ解像度の場合にサイズの自動調整が行われないので1つ目の方法で現在の使用環境でうまく文字が収まらないのでどうしようも無い時に使えますが、やはりTagに入力するのと占有されるのが難点ではあります
ですが、もともとこのサイズ調整は解像度がもし違う場合に、その環境に合わせておきたいレベルの話が多いので全くの無駄とは言えません

コードコピー

'アクティブセルのある行のB～D列を選択
Intersect(ActiveCell.EntireRow, Range("B:D")).Select

コードコピー

'アクティブセルのある行のA列を選択
Range("A" & ActiveCell.Row).Select

ActiveCellや指定のセルの行にある列を指定するには、2通りの方法があります

1つ目はIntersectメソッドを使用して、行全体と指定列を重複させて取得する方法です
2つ目はRangeオブジェクトの引数に列文字列を指定して、指定セルの行数数値を取得して指定する方法です

使い分けとしては、複数列を指定する場合はIntersectメソッド
単一セルを指定する場合はRangeオブジェクトを使用する方法を使います

とはいえ、どちらでもどちらの指定方法は可能です
ただ、Rangeオブジェクトの方で複数列を指定する場合は少しコードが煩雑になるので、Intersectメソッドの方が簡潔に記載できます

コード解説

画像の様に、ActiveCellがどの列にあってもその行の指定列を指定することが出来ます

Intersect(ActiveCell.EntireRow, Range("B:D")).Select

こちらでは複数列の範囲指定を行う際に便利な方法です

この例ではB~D列のセルを範囲選択しています
Intersectメソッドを使用しているので、行全体と列全体を引数に指定することで重複する範囲を取得しています

表にデータを追加したときなどに、罫線などの書式設定を一括で行う際によく使用します

Range("A" & ActiveCell.Row).Select

こちらは単体のセルを指定するのに便利な方法です

アクティブ行の指定列の単体セルを簡単に指定できるので、値の入力を行う際に使用します

Range("B" & ActiveCell.Row & ":D" & ActiveCell.Row).Select

なお、Rangeオブジェクトで複数列範囲のセルを指定するには以上のようなコードになります
やはり、煩雑なコードになるのでIntersectメソッドの方が見やすいですね

この2つを駆使すれば、表にデータを追加して書式設定も整えていく処理が相対的に行えるので、使用頻度が結構高いコードです