VBA小技の使い方

• ドラッグ＆ドロップでファイル名取得の使い方



• プログレスバークラスの使い方

エクセルVBAでファイルを指定して何らかの処理をしようとする時、そのファイル
選択の為のユーザーインターフェイスとしては通常は既存の、

GetOpenFileNameメソッド

などでダイアログを開いて指定してもらうような作り方をします。

これはこれで非常に便利であり有用な手段ではありますが、使う側からすると時と
して不便を感じる場合があります。指定するファイルの階層が深かったりすると結
構面倒であり、フォルダに隠し属性が設定されていると表示できなかったりします。

Windowsの通常のアプリケーションでは大抵、ダイアログでのファイル指定の他に

Ｄ＆Ｄ（ドラッグアンドドロップ）

に対応していますが、エクセルVBAには基本的にはその機能はありません。

しかし、VBS（VBScript）では標準でというか簡単にD&Dしたファイルのフルパスを
取得する手段が用意されています。Argumentsプロパティがそれで、

WScript.Arguments

たったこれだけの記述で取得できます。それを紹介したのが小技集の標記項目です。

さて前置きはこのくらいにして具体的な「使い方」をご説明しましょう。^d^


まず、VBSについて。

VBSとは何か、についてはネットで検索すればヒット数は5万とある（かな^^;）で
しょうから詳しい説明はしませんが簡単に言ってWindowsに標準で装備されている

簡易プログラム言語

といったところです。

メモ帳でさらさらと書いたテキストファイルを、拡張子「.vbs」で保存すれば出来
上がりです。簡単に使えますが使い込むとかなり奥が深いです。

私の「小技集」の中でVBSを利用したものも幾つかありますが、それらはみなこの
ように単純なテキストファイルにその内容をコピペして、拡張子「.vbs」で保存
すれば利用可能と言うわけです。

（※この時、「拡張子を変更すると、ファイルが使えなくなる可能性があります。
　　変更しますか。」
　　というような警告が表示されたら、大丈夫ですので「はい」をクリックして
　　ください。）

では最初に簡単な使用例から。

次の手順どおりにしてください。
１．小技集からコードをコピーし、メモ帳などで新規テキストファイルを作成、
　そこへペーストし、拡張子を「.vbs」（フォルダ、ファイル名は何でも可）
　として保存する。

２．エクセルを起動し新規ブックが開いた状態にする。

３．適当な幾つかのファイルをまとめて選択して、１．の.vbsファイルにD&Dする。

すると２．のブックのSheet1のA1から下方向にD&Dしたファイルのフルパスが表示
されたはずです。簡単でしょう。^d^


次にコードに説明を付け加えてみましょう。

Dim Exl　　　'開いているエクセルブック（Bookとかの方が良かったか^^;）
Dim rngDest　'書き込み先のセル
Dim Arg　　　'D&Dで受け取った引数のコレクション
Dim I

Set Exl=Nothing
Set Arg=WScript.Arguments　'D&Dで受け取った引数のコレクションを取得

Set Exl=GetObject(,"Excel.Application")　'アクティブブック取得

With Exl　'ここであえてWithの必要は無いがコードの拡張に備えて
    Set rngDest=.Worksheets(1).Range("A1")　'書き込み先
    For I=0 To Arg.Count - 1　　　　　　　　'コレクションのインデックスは０からなので
        rngDest.Value=Arg(I)　　　　　　　　'書き込み先セルへパスの書き込み
        Set rngDest=rngDest.Offset(1)　　　'書き込み先を一行下へ移動
    Next
End With

Set Exl=Nothing
Set rngDest=Nothing

短いコードですが今見直すと色々と当時の格闘の後が見えます。^^;
（変数名のExlとか、最初にSet Exl=Nothingを入れたり、With Exlとしたり。）


最後は使い方です。

使い方といっても別に難しいことはありません。既にシート上にフルパスを取得出
来ているわけですからそこから配列に取り込むだけです。

Dim VV
VV=ActiveWorkbook.Worksheets(1).Range("A1").CurrentRegion.Value

これで、
VV(1,1)に一つ目のフルパス、
VV(2,1)に二つ目のフルパス、
・
・
が取得できます。

但し、D&Dしたのが一つだけの時はVVは配列にならずVV(1,1)はエラーになります。
そこまで考慮する場合は、

dim r as range
dim vv() as string
dim c as long
set r=ActiveWorkbook.Worksheets(1).Range("A1")　'場所は適宜変更
do until r.value=""
    c=c+1
    redim preserve vv(1 to c)
    vv(c)=r.value
    set r=r.offset(1)
loop

などと、面倒でもループで取得するか、あるいはエラー処理でカバーするかです。

また、シートはユーザーに見えるのはあまりかっこ良くありませんから予め
隠しシートを作っておき、そこへ書き込むようにした方が良いでしょう。

さらに、忘れてならないのは取得した値のリセットです。
例えば初めに５つD&Dして処理し、次に３つD&Dで処理しようとする場合、リセット
をしていないと最初の５つの内の最後の２つは残ったままになってしまいます。

パス取得の専用シートですからリセットは簡単に、

シート.Cells.ClearContents

ぐらいで良いと思います。

ついでに、フルパスからファイル名のみを取り出すには、今回の場合は実在の
ファイルのフルパスですので

Dir(フルパス)

としてしまうのが簡単です。


いかがですか、まあ本格的なＤ＆Ｄが出来るソフトほどのかっこ良さはありません
が、それでも簡単な割には結構実用的なソフトの作成が出来そうな小技でしょう。
(^^ゞ

＊＊＊＊＊　はじめにプログレスバーについての一般的な話　＊＊＊＊＊

プログレスバーとは、アプリケーションのインストール時やファイルコピー時など
処理に時間がかかる時に、ユーザーにその状況を知らせるあの「延びてゆく横棒グラフ」
のことです。Excelではブックオープンや、再計算の時などにステータスバー（画面下）
に表示されたりします。

■■■■■■■□□□70% 残り0:00:25　処理中です...

ExcelVBAでプログラムを書いて、処理待ち時間が長く、自分のプログラムにも
プログレスバー表示できないかなと考えた方もいるのではないかと思います
（だからこそここを見てくれているのだと思いますが）。

さて、ExcelVBAでプログレスバーを使う方法は幾つかあります。
１．既存の出来合いのコントロールを使う方法
２．ユーザーフォーム標準のラベルなどのコントロールで自作する方法
３．ステータスバーを利用する方法

１．が一番簡単に実現できそうな気がしますが実は問題があります。Excelには
　標準では同コントロールは付属していないのです。そのため作成したブックを
　配布する際には、配布先にそのコントロールがあることを確認するか添付して
　あげる必要があります。その配布に関してはさらにライセンスの問題もありま
　す。同コントロールを勝手に添付して配布するとライセンス違反となる場合が
　あります。

２．は面倒ですがExcel標準のコントロールで作成するのですからライセンスの
　問題はありません。しかし、実は１．もそうですがユーザーフォームを使用す
　るとPC負荷が重い、という欠点があります。ただでさえ処理が遅いから「待ち
　表示」をしようというのに、さらに重くなって処理待ち時間が増えたら本末転
　倒です。（どの程度重くなるかは未検証ですが、ユーザーフォームはメモリを
　食うことは確実ですので、そのしわ寄せは間違いなくあります。）

ということで、エクセルで使用するのでしたら断然３．のステータスバーを利用
がお勧めです。画面の真ん中にユーザーフォームを表示するようなカッコよさは
ありませんが、プログレスバー表示による追加負荷は最小限に抑えられ軽快です。

ところで、プログレスバーを使用するためには重要な事柄があります。
それは、プログレスバーは自動的に表示を更新してくれるわけではない、という
事です。
どういうことかというと、プログレスバーの使い方の基本は次のようになるから
です。（既存のコントロールでも自作でもいずれも同じ）

１．処理の最大量を求める（または予想する）
２．処理の途中（ループの途中など）で、その時点の進捗情報をプログレスバー
　に渡す。⇒プログレスバーが表示を更新する。
３．処理の最後まで２．を繰り返す。
４．終了したらプログレスバー表示を消す。

このため、VBAからみて一つの処理を呼び出すだけのものではプログレスバー表示
は意味がありません。最初に０％で次が１００％になるだけですから。
プログレスバー表示が可能な意味のあるものとしては、繰り返し回数の決まった
For Nextループのような処理の場合に限定されます。ご注意下さい。


＊＊＊＊＊　プログレスバーの使い方　＊＊＊＊＊

そんなわけでステータスバーを利用して汎用的に使用できるように作ったてみた
のがプログレスバークラスです。
簡単に作ったものですが意外に本格的に使えます。^d^

それでは具体的な使用方法です。
０．クラスモジュールの作成
　１．新規にクラスモジュールを挿入します。
　２．===== cProgress クラスモジュール =====以下のコードをコピペします。
　　（Option Explicitが２つになってしまうようなら一方を削除してください）
　３．クラスモジュールの名前（オブジェクト名）を「cProgress」と変更する。

１．標準モジュールでの使用
　以下、小技集より単純な例で説明します。

Dim myBar As cProgress     'クラス使用宣言（このプロシージャ内で使うよという準備の宣言）
Dim i as Long

Set myBar = New cProgress  'クラスのインスタンス生成（クラス自体の使用準備）
'プログレスバークラスの設定
With myBar
    .Msg = "お待ち下さい..."  '表示するメッセージです。
    .Length = 10  '□□□の長さです。省略可、省略時は20です。
    .Min = 0      'プログレスバーの最小値です。省略可、省略時は0です。
    .Max = 10     '同最大値です。省略可、省略時は100です。
    .Style = plBarAndPercent   '「%」「残り時間」を表示するしないの指定です。省略可、省略時は「%」表示です。
    .Start        '残り時間表示時、残時間計算のために処理実行開始時間を設定します。
                  '処理開始直前にこのメソッドを実行するようにします。
End With

'処理のループ
For i = myBar.Min To myBar.Max   'ここは普通に 1 To 10 などでもよいです。勿論 .Min と .Max に合わせる必要はあります。
    '処理（１秒待つ、ここに実際のループ内での処理内容を記述します。その際下記の「待ち」は不要です。）
    Application.Wait Now + TimeSerial(0, 0, 1)

    'ここで、現在の進捗（MinからMaxの間の数値）をプログレスバーに設定します。
    'その設定値が何％なのか、残り何秒なのかは自動で計算されますから考慮は不要です。
    myBar.Value = i
Next

'クラスインスタンスの解放（ステータスバーの制御をExcelに戻します。必須です。）
Set myBar = Nothing


実は、上でごちゃごちゃ書きましたが、プログレスバークラスの設定では、最小限.Maxだけ設定すればよいです。
後の設定は必要に応じてして下さい。
つまり本当に必要最小限でサンプルを書くと次のようになります。

Dim myBar As cProgress
Dim i as Long

Set myBar = New cProgress
myBar.Max = 10

For i = 1 To myBar.Max
    Application.Wait Now + TimeSerial(0, 0, 1)
    myBar.Value = i
Next

Set myBar = Nothing

どうです、簡単でしょう。

要は、使用宣言と終了宣言が必要なこと。
BarのMax値を設定し、
ループの途中で、現在値をBar.Valueに渡してやる。

これだけです。
これであなたのプログラムにもちょっとだけ箔が付きますかな。^d^


追加：ループに依らないちょっと変わった使い方

プログレスバー表示は、要は全体のどれほどの進捗かの情報を渡せば良いわけですから、
こんな使い方も出来ます。

例えば独立したプロシージャが３つあり、予め各々処理時間の比率が３：２：４と
分かっているとします。
この場合、.Maxには９(3+2+4)を設定し、
プロシージャ１の実行前に.Value=0
プロシージャ１が終了した時点で.Value=3
プロシージャ２が終了した時点で.Value=5 (3+2)
プロシージャ３が終了した時点で.Value=9 (3+2+4)
という使い方もありです。

また、.Maxの初期値は100ですのでこれをそのまま利用して特に設定はせずに置き
現在何パーセント程度かというのを、プロシージャの適当な箇所で.Valueに設定
するという使い方でも良いでしょう。^d^