投稿者: tanakadaigoro

配列データを一括出力する

配列データを作成してから、セルへ一括出力するコード

'配列データをセル範囲に一括出力
Range("A2:C3") = bh配列データ

配列データを一括で出力するには、指定セル範囲に配列変数名を代入するだけで可能です

配列データはメモリ上での操作で取得・加工を行うため、その部分は少し難しい内容になりますが、出力は非常に簡単にできますし、セルの更新自体も1回で済むので処理が高速になります

実際の使用例

Dim bh配列データ(99) As Long, bhインデックス番号 As Long

For bhインデックス番号 = 0 To 99
bh配列データ(bhインデックス番号) = Range("A1").Offset(0, bhインデックス番号) * 1.1
Next bhインデックス番号

Range(Range("A2"), Range("A2").Offset(0, 99)) = bh配列データ

配列データを計算をしてから代入していき、計算が終わってから一括で出力します

こうすることで、1セルずつ処理を実行していくよりも遥かに早く出来ます
例コードでは、1行目の100セルに10%上乗せした数値を計算して出力しています
ようするに、税抜き金額から税込み金額を計算して出力している形です

まずは、コード実行前と後の結果画像を確認します

配列データの一括処理実行前
コード実行前

コード実行前の状態です
100セルを表示することはできませんので、SUM関数で合計金額を表示します
D3には、計算結果が正しいかを分かり易くするために、答えを先に表示しています

この状態でコードを実行すると、2行目に計算結果金額が出力されます

配列データの一括処理後
コード実行後

コード実行後の状態です
100セル全ての計算を行い、セルへの入力は1回のみなので瞬時に終わります
金額が答えのセルと一致していることも確認してください

ループ処理により1セルずつ行っていった場合、自分の環境では10倍速度が違いました
セルの個数が増えれば、その幅はどんどん大きくなっていきます

このように、ループ処理で計算を行っていけば出来るような処理でも配列を使用すると非常に高速に処理できることがわかります

コードの解説

Dim bh配列データ(99) As Long, bhインデックス番号 As Long

まず配列データの変数宣言です
ここでは静的配列として宣言しています、個数が確定している場合は静的配列の方が便利です

静的配列と動的配列の違いと宣言方法に関しては以下の記事を確認してください

配列変数の宣言（静的）
配列変数の宣言（動的）
For bhインデックス番号 = 0 To 99

Forループの開始コードです
要素数分ループを実行します

bh配列データ(bhインデックス番号) = Range("A1").Offset(0, bhインデックス番号) * 1.1

左辺は、配列データのインデックス番号をForループの変数値で指定しています
右辺は、A1セルからForループの変数値分Offsetで移動しながら、そのセル数値に「1.1」を乗算した数値を代入しています

ここで、実際の計算が行われていますが、セルへの出力は行われていないため
このループ処理自体は非常に高速に終了します

Next bhインデックス番号

Forループの終了コードです、次のインデックス番号に移動します

Range(Range("A2"), Range("A2").Offset(0, 99)) = bh配列データ

ここで一括出力を行っています

配列で一括代入する場合は、要素数と同じセル範囲を指定する必要があります
なので、ここではA2セルからOffsetを使用して99セル右に移動したところまでを範囲指定しています

ここで、要素数未満であった場合はその選択範囲分データが出力され
超えていた場合は「＃N/A」が出力されます

またセル範囲は連続している必要があります、連続していない場合はその範囲ごとにインデックス番号が最初から入力されます
そのあたりについては以下の記事で対策と合わせて解説しています

配列データを連続していないセルに一括出力する

後は、配列変数名だけでデータ全ての指定が可能です

ダイアログボックスのキャンセル判定

ダイアログボックスを表示後にユーザーがキャンセルをしたかを判定するコード

'ファイル選択ダイアログでキャンセルした場合に分岐する
If Application.FileDialog(msoFileDialogFilePicker).Show = -1 Then
MsgBox Application.FileDialog(msoFileDialogFilePicker).SelectedItems(1)
Else
MsgBox "キャンセル実行"
End If

ダイアログボックスを表示した際に、指定の操作を行わなかった場合（キャンセルを行った場合）にそのまま処理を継続はできません
取得するアイテムが存在しないためエラーになります

その場合はキャンセルされた場合の処理分岐を作成する必要があります
それが、例コードになります

If Application.FileDialog(msoFileDialogFilePicker).Show = -1 Then

まず最初のIF条件文の箇所です
これはダイアログの表示と同時に、戻り値を判定基準にしています

ダイアログは選択されると「-1」を返します

なので、これを条件にすることで選択されたかどうかが判定できます
このコードでいえば、「-1」（選択した）であれば真（True）となります

MsgBox Application.FileDialog(msoFileDialogFilePicker).SelectedItems(1)
Else
MsgBox "キャンセル実行"
End If

残りのコードは、分岐によって処理される部分です

1行目は真の時に実行されるので、選択された状態であることが確認されているので
その指定ファイルの絶対パスをメッセージに表示します

Else以降は偽（False）の場合になるので、キャンセルされた場合に当てはまりますので、メッセージでキャンセルを知らせます

このコードは、ダイアログ全ての種類で有効です
名前を付けて保存のダイアログでも、キャンセルすればこの条件分岐で処理できます

選択個数で分岐

一応、選択個数で判定を行うこともできます
すこし条件文が冗長になるので、ここで解説しておきます

'ファイル選択ダイアログでキャンセルした場合に分岐する
If Application.FileDialog(msoFileDialogFilePicker).SelectedItems.Count >= 1 Then
MsgBox Application.FileDialog(msoFileDialogFilePicker).SelectedItems(1)
Else
MsgBox "キャンセル実行"
End If

データの選択個数を取得するプロパティを使用します

その個数が1以上であれば選択していると判断します
なので、「>= 1」として1以上なら真になるように条件をたてます

どちらの条件分岐でもいいのですが、少し長くなるこちらはあまり意味はないかもしれません
指定したときに「-1」が返されることが分かっていれば、トップのコードでいいですね

関連の記事

FileDialogオブジェクト（ダイアログボックス）

FileDialog.Executeメソッド

FileDialog.Executeメソッドはダイアログで指定した操作を実行します。ファイルを開く・保存の実行コード

'選択したファイルを開く
Application.FileDialog(msoFileDialogOpen).Execute
'保存を実行する
Application.FileDialog(msoFileDialogSaveAs).Execute

ダイアログボックスでの指定を行った後に、ファイルを開くか名前を付けて保存を実行するには「Application.FileDialog().Execute」メソッドを使用します

このコードは単体では意味はありません
事前にユーザーにダイアログで指定を行ってもらう必要があります
そのダイアログの表示に関する解説は以下の記事にあります

ダイアログボックスの表示（FileDialog.Showメソッド）

表示したダイアログの種類と同じものにメソッドを使用すると実行されます

Application.FileDialog(msoFileDialogOpen).Show
Application.FileDialog(msoFileDialogOpen).Execute

この様に、Showメソッドの後に実行する事で操作が実行されます
ただ、Showメソッドでキャンセルを行ったり、開く操作を対象とした以外の行動をしている可能性もありますので、その辺は対応に合わせてプロパティを変更していく必要があります

関連の記事

FileDialogオブジェクト（ダイアログボックス）

FileDialog.SelectedItemsプロパティ

FileDialog.SelectedItemsプロパティは選択したアイテムの絶対パスが格納されます。Countプロパティによる個数取得もあります

'ファイル選択で指定したファイル1つ目の絶対パスを取得
Debug.Print Application.FileDialog(msoFileDialogFilePicker).SelectedItems(1)
'ファイル選択で指定したファイルの個数を取得
Debug.Print Application.FileDialog(msoFileDialogFilePicker).SelectedItems.Count

ダイアログボックスで指定したデータの絶対パスを取得するには、「SelectedItems」プロパティを使用します

このプロパティは、指定したデータの絶対パスが文字列で返されます

また、ダイアログボックスで複数選択していた場合は、引数の数値がインデックス番号になっているので数値を変更することで取得していくことができます

このプロパティには、さらにプロパティがあります
その場合はこのプロパティがオブジェクトに切り替わるわけですが、あまりどうでもいい話です

いくつかありますが、使用するのは「Count」プロパティです

Application.FileDialog(msoFileDialogFilePicker).SelectedItems.Count

このコードではユーザーがダイアログで選択したデータの個数が取得できます

ダイアログの複数選択は個数が可変なので、いくつになるか分かりませんので処理をする際にこのプロパティで個数を取得することで、選択した全てを処理することが出来るようになります

関連の記事

FileDialogオブジェクト（ダイアログボックス）

ダイアログボックスの表示（FileDialog.Showメソッド）

ダイアログの表示コード、種類ごとのコードがあります。ここでは表示のみの処理になります

'ファイル選択のダイアログ表示
Application.FileDialog(msoFileDialogFilePicker).Show
'フォルダ選択のダイアログ表示
Application.FileDialog(msoFileDialogFolderPicker).Show
'ファイルを開くダイアログの表示
Application.FileDialog(msoFileDialogOpen).Show
'名前を付けて保存ダイアログの表示
Application.FileDialog(msoFileDialogSaveAs).Show

ダイアログボックスを表示するには、「Application.FileDialog().Show」メソッドを使用します
表示するダイアログの種類を引数に指定してからメソッドを使用します
種類の省略はできません

'ファイル選択のダイアログ表示
Application.FileDialog(msoFileDialogFilePicker).Show
'フォルダ選択のダイアログ表示
Application.FileDialog(msoFileDialogFolderPicker).Show

ファイル・フォルダ選択のダイアログを表示します
「msoFileDialogFilePicker」がファイル選択、「msoFileDialogFolderPicker」がフォルダ選択になります

ファイルの選択時には、ファイル全てが対象になりますのでExcel以外のデータの指定も可能です
また、複数選択が可能でマウスの範囲選択やCtrlキーでの指定が可能です

ここで選択したものは、SelectedItemsプロパティに絶対パスが文字列で取得されますので、以下の記事からプロパティでの動きも確認してください

FileDialog.SelectedItemsプロパティ
'ファイルを開くダイアログの表示
Application.FileDialog(msoFileDialogOpen).Show

ファイルを開くダイアログの表示をします

上記のファイル選択時と同様でExcel以外のファイルの指定も可能です
ただ、あくまでもExcelのメソッドになりますので、開く際はExcelでデータが開かれますので注意が必要です

これ単体ではファイルを開けません、後述します

'名前を付けて保存ダイアログの表示
Application.FileDialog(msoFileDialogSaveAs).Show

名前を付けて保存のダイアログを表示します

この保存対象は、アクティブブックが対象になります
なので、コードブック以外のブックを指定したい場合は事前にそのブックをアクティブ状態にしておきます

上記のファイルを開くダイアログと名前を付けて保存ダイアログで行った指定に関しては、このコードだけでは実行されません

このコードは指定を行うだけで、実際の動作に関しては同じオブジェクトにExecuteメソッドを実行する必要があります
メソッドの解説と動きに関しては以下の記事から確認してください

FileDialog.Executeメソッド

ダイアログボックスのキャンセル

ダイアログボックスで必ずしもユーザーがデータを指定して完了するとは限りません

キャンセルする場合もあります、この場合には条件分岐により処理を分岐する必要があります
以下の記事でダイアログのキャンセル時の条件分岐処理を解説しています

ダイアログボックスのキャンセル判定

関連の記事

FileDialogオブジェクト（ダイアログボックス）

FileDialogオブジェクト（ダイアログボックス）

FileDialogオブジェクトの解説。各メソッドとプロパティの一覧

Excelでダイアログボックスを表示及び操作を行うには、FileDialogオブジェクトを操作します

ダイアログボックスとは、「ファイルを開く」操作で表示される画面のことです

FileDialogオブジェクトでは、ファイルを開く以外にもフォルダの選択も行うことができるため、処理を行う対象フォルダの指定に使用したりします

ここで選択したファイルは、その絶対パスが返されます
そのパスを元にファイル操作を行っていく形になります

また、通常のダイアログボックス同様に複数の選択も可能です
その場合はItemプロパティに取得され、複数選択時はインデックス番号で指定が可能になります

基本的にExcelではドラッグ&ドロップ操作はできません
VBAでもユーザーフォーム間でしかできません
その辺の解説は以下の記事内で行っています

VBAにおける、D&D・マウスホイール操作について

そこで白羽の矢の立ったのが、このダイアログボックスです
ユーザーのファイル選択はここから行ってもらうようにしましょう

FileDialogオブジェクトの操作について

これはオブジェクト操作になります
ダイアログの表示といっても、対象とするものの設定を行う必要がある場合があります
例えば、Excelブックを指定してもらいたいのにWordファイルを選択されてしまうことがあり得ます
これを制限するプロパティを設定する必要があります

この設定は、いつ行うのかというと、表示する前に設定を行います
なので、FileDialogオブジェクトの基本的な操作の流れは以下になります

  1. 指定のダイアログオブジェクトのプロパティを設定
  2. ダイアログを表示して、ユーザー操作
  3. 選択したファイルの絶対パスを取得

この様に表示する前に行うことがあって、表示後にもプロパティから処理を行います
ユーザーフォームの様に事前にプロパティ設定を行い保存しておくようなことは出来ないので、使用するときにコード上で行う必要があります

また、2から3の手順に移る際に、ユーザー操作でキャンセルが実行されていないかを判定しておく必要があります
判定方法については以下の記事を確認してください

ダイアログボックスのキャンセル判定

FileDialogのメソッド

  • Execute
    操作を実行する、これをしないと選択しただけになる
  • Show
    ダイアログを表示する、これをしないと意味がないやつ
  • Filters.Add
    ファイル選択時の種別を制限するためのフィルター設定、拡張子で指定する

FileDialogのプロパティ

  • SelectedItems
    選択されたデータの絶対パスが格納されたもの、インデックス番号で指定可能
  • Title
    ダイアログボックスのタイトル部分に表示される文字列の設定
  • AllowMultiSelect
    ファイルの複数選択の許可設定、Falseにすると単一選択になる
  • InitialFileName
    初期表示されるフォルダを指定するためのプロパティ

VBAにおける、D&D・マウスホイール操作について

VBAの弱点、ドラッグ&ドロップとマウスのホイール操作について

VBAでは、ドラッグ&ドロップ（以下、D&D）とマウスのホイール操作が使用できません

D&D操作について

外部ファイルをD&D操作で直接VBAに読み込ませるには、2通りの方法がありますが、いずれも不安要素が大きいです

まず、よくあるのがリストビューコントロールを使用する形です
このリストビューコントロールは、ユーザーフォームの追加コントロールです
「その他のコントロール」から追加して使用することが出来ます
そのコントロールには、D&Dを検知するイベントがあり、そこからデータを操作することが出来るようになります

ただ、このコントロールは追加する操作が必要なのが難点です
そして最大の難点が、新しいExcelではコントロール自体が存在していないことです
これから主流になっていくであろう、64bitのExcelにはありません

永遠に使い続けられる処理を作成するのは幻想ではありますが、可能性ではなく実際に無くなっているコントロールを使い続けるのは少し不安です

なので、このコントロールでの解説は当サイトでは行っていません
別のWebサイトであれば、使用コード・イベントが解説されていますので、検索すればすぐに見つかりますので、そちらにお任せします

もう1つの方法が、WinAPIを使用する方法です

これはSleep関数などのように単体のAPIでサクッと使用できる、というような処理にはならないのでハードルが高いです
APIは便利ですし、それでしか実行できないことは任せますが、ハードルの高いAPIはbit違いによるメンテナンスも考慮すると、こちらも不安が残ります

そこで、本質的な話です

そもそもD&Dを使用したい場面について
これは単純に指定ファイルをシートやフォームに挿入したり処理を実行する際に、「ダイアログを開いて選択して挿入ボタンをクリックする」という操作を省くために使用したいのだと思います

確かに、D&D操作になれていると出来ないと不便に感じてしまいます
しかし、その操作の実現には不安要素が多いのが現実です

そして、この操作の最終目的は何かというと、D&D操作によって指定ファイルを操作することです、決してD&D操作が目的ではありません
なので、ユーザーからの依頼でどうしてもそれが無いと嫌だという状況以外においてはダイアログボックスでの運用をおすすめします
ダイアログボックスについては以下の記事から確認してください

ダイアログボックス（FileDialogオブジェクト）

マウスホイール操作について

ユーザーフォームのリストコントロールなどで上下移動したい場合などにマウスのホイール操作は使用できません

これはVBAでは、このボタン自体が認識されていないためです
ホイールを回す操作だけでなく、押し込む操作も使用できません

これをVBAで実装するにはWinAPIが必要になります
これも上記とほぼ同様なのですが、非常にハードルが上がります

実際、自分は別のWebサイトにあるコードを見てもあまり理解できません
そもそもホイールの概念自体がVBAに存在していないので、そこにその操作を放り込むというのは考えからも非常に困難なことが容易に想像できます

そこで、また本質の話です

このホイール操作で実現したいのは、ホイール操作ではない、という点です
要は、リストなどをマウス操作で簡単に上下移動したいのが目的です

それならば、頑張ってAPIを使用するよりも仕様内で考えてみます

SpinButtonコントロールのMouseMoveイベントを利用します
このコントロールを移動させたいコントロールの隣に配置し、その矢印上にマウスを移動させると、リストなどの選択を上下に移動させるような処理を作成します

これなら、マウスのクリックやドラッグ操作も必要なく簡単に操作できます

結論として

あらゆる言語のあらゆる知識を動員すれば、出来ないことなどなんにもありません

ただ、それには費用対効果をしっかり考える必要があります
効果に対して、費用が掛かりすぎる場合はデメリットの方が大きいので別の方法を考えるか、仕様としてあきらめるかです

勉強のためなら、どんどん突き詰めていくと良いと思いますが、仕事で使用するものであればメンテナンスのことは考えておきたいです

できないことを頑張るより、できることを頑張るほうが精神的にもいいです
そのほうがVBAも楽しめます

結論は、「仕様なのでできません、せやからこの方法でええやん♪」こう言うことにしましょう

Terminateイベント

TerminateイベントはUserFormがメモリ上から解放されると発生するイベントです

「Terminate」イベントはUserFormがメモリ上から解放されると発生します

QueryCloseイベントで終了が許可された場合、その後に発生しますのでフォームの終了処理に向いています

ただ、注意点としてフォームがメモリ上から解放されているため、UserFormの情報が取得できません
サイト記事にもある、前回と同じ場所にフォームを表示する処理で位置を取得する際にはQueryCloseイベントを使用しております
このイベントでは、その位置情報を取得が出来ないためです

なので、このイベントはフォームの情報取得が必要ない場合に使用できます

上記の通りフォームの情報が終了処理に必要な場合はQueryCloseイベントを使用してください
それ以外のブックを終了させるなどの処理なら、このイベントを利用する形で十分だとおもいます

また、リファレンスにもありますが、このイベントはUserFormがメモリ上に読み込まれた後、解放されて初めて実行されます

別記事にEndステートメントで強制的にフォームの起動をキャンセルさせる処理がありますが、その処理の場合はこのイベントはフォーム終了するタイミングであっても発生しません

あと、このイベントは解放されてしまっているので、QueryCloseイベントのように終了をキャンセルするようなことはできません

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

Endステートメントは処理を強制終了させます。数少ない有効利用場面の解説

'全ての処理を強制終了
End

処理を強制的に終了させるには「End」ステートメントを使用します

このステートメントの使用上の注意点があります、以下の記事で解説しています

処理を終了する

基本的にこのコードは強制終了という側面から、あまり多用すべきコードではありません

終了する場合はしっかりプロシージャの最後まで実行するべきです

ですが、それも状況次第で有効に利用できる場合があります

フォームの起動を強制終了

ユーザーフォームを起動させるには、ShowメソッドとLoadステートメントがあります

どちらも、必ずInitializeイベントを発生させます
なのでフォームの初期化処理はこのイベントに作成します

起動条件を設定したフォームの場合は注意する点があります

例えば、入力補助のフォームであれば
作業するシート以外では、フォームは表示される必要はありませんし、場合によってはバグが発生する可能性があります
そもそも使用しないシートでフォームが表示されるのも邪魔です

そんな場合には、フォームの起動時に条件分岐を行い条件に一致しない場合にはフォームを起動しないようにします

そして、通常は問題にならないこの状況が問題になるのが、初期化処理に時間が多少かかる場合です

条件分岐によりフォームを表示させないのに、待ち時間をユーザーに発生させてしまいます
シートを切り替えるたびに、数秒待たされるような状況になったら非常に迷惑です

解決法として、あるのは2通りです
読み込みコード実行前に条件分岐を行うか、Initializeイベントの処理最上部に条件分岐を行うかのどちらかです

If ActiveSheet.Name = "対象" Then
UserForm1.Show
Else: End If

読み込みコード実行前に行うのが通常の方法です
If分岐で条件に一致しなければ、読み込みコードは実行しないようにします

ただ、このコードでも問題はあります
基本的にこういった処理の場合は、シートイベントのActivateイベントで表示処理、Deactivateイベントでフォームを終了させる動きにします

こうしておかないと、表示は条件分岐で対象シートで行えても、シートを切り替えた時にフォームが表示されたままになるためです

そこで問題の発生です

対象シートが複数あった場合、全てのシートに条件分岐を作成する必要があります
少しの修正があっても大変になってしまいます

そこで、フォームのInitializeイベントで一括して条件分岐を行います

If Not ActiveSheet.Name = "対象1" Or ActiveSheet.Name = "対象2" Then
End
Else: End If

Initializeイベントの最上部にこのコードを挿入します
アクティブシートの名前が指定シートでなければ、Endステートメントにより強制終了します
指定シートであれば処理が継続して、多少時間のかかる初期化処理が実行されます

Initializeイベント中に、Unloadステートメントを実行するとエラーが発生しますので、このEndステートメントが効果を発揮します

当然ですが、呼び出し元処理（ShowメソッドやLoadステートメント）も強制終了しますので読み込み処理には他の処理は入れないようにしましょう

使用場面は限られて少ないですが、あると便利な場面もありました

なお、気づいた方もいるかもしれませんが、終了処理に対応してません
これにはイベント最強のWithEventsキーワードを使用します

Initializeイベント

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

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

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

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

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

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

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

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

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

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

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