FC2ブログ

環境変数を使ってVBAの動作を外部から制御(安全に停止)してみる

非常に時間のかかるループ処理を途中で止めたくなることってありますよね
でも、エスケープやブレークキーでVBAをとめるのは、ユーザーにVBAソースを晒すことになり2次被害の心配もある
さらには、時間がかかる処理であれば、中断と再開ができなければそれまでの処理が無駄になってしまう


〇VBAの動作に中断と再開ができればすばらしい
例えば、非常に件数の多いレコードを入力して集計するプログラムがあったとする
集計作業はメモリー上の変数に確保するのはあたりまえですが
集計の中断によりこの変数をワークシートに保存できれば、再開も容易になる

流れ的には
1.前回処理による集計結果のワークシートから変数に値をセット
2.前回処理で保存された入力レコードのキーなり件数なりで入力再開レコード位置決め
3.集計ループ
4.集計結果をワークシートに保存
5.オプションとして、新規集計処理用にワークシートの初期化

上記の集計ループを抜ける条件に、入力ファイルの終了だけでなく、外部から何らかの指示があったことを検知できれば中断が可能


〇外部から何らかの指示をするには
いろいろ考えてみましたが、思い出したのが環境変数
σ(゚∀゚)ワタシはMS-DOS3.1時代のプログラマでしたので
雑誌記事のバッチ道場とかも好きでよく読んでいました

〇Windows10でもどっこい生きてる環境変数
今ではほとんど目にすることのない環境変数ですが、おためし確認ということで
メニューからコマンドプロンプトを起動し
setと入力しエンターキーを押すと環境変数が表示されます

〇環境変数を使った制御の仕様
環境変数の詳細はネットを検索してください
今回は
種類:User
変数名:ExcelCtrl
変数の値:1 2 3....なんでもいいです

エクセルVBAでループするロジックを作成
ループの最後に環境変数ExcelCtrlの値を取得、および環境変数ExcelCtrl自身を削除する処理を設定します
取得された値により動作を制御=空文字("")でなければループを抜ける

環境変数ExcelCtrlをエクセル以外から設定
今回はお手軽にVBSから設定してみます

なお、今回作成するVBAにおける環境変数の値の取得では以下の点に注意してください
指定した環境変数名が定義されていない、または定義されていても値が設定されていない場合
取得される値は空文字("")となります

VBAの処理の流れを複雑に変更するなどの場合は環境変数の値でコントロールすべきですが
ループを抜ける判断だけであれば空文字以外になったことがわかれば十分ですね

〇環境変数を追加、値設定のVBS
ExcelCtrl.VBS メモ帳に張り付けてこのファイル名で保存

Set objShell = WScript.CreateObject("WScript.Shell") 'シェルを取得
Set colEnv = objShell.Environment("User") '環境変数の種類をUserにする
colEnv.Item("ExcelCtrl") = "Exit" '環境変数名ExcelCtrlに値Exitを設定 既存なら上書き存在しなければ追加される
Set colEnv = Nothing
Set objShel = Nothing


〇環境変数を確認するVBA
Option Explicit

'---------------------------
Sub test()
Dim strENV As String

strENV = ""
Do
    DoEvents
    strENV = CHKENV("User", "ExcelCtrl")
Loop Until strENV <> ""

AppActivate Application.Caption
MsgBox strENV
End Sub

'---------------------------
Private Function CHKENV(ENVType As String, ENVName As String) As String
'環境変数の値を取得
'指定した環境変数名が定義されていない、または定義されていても値が設定されていない場合
'取得される値は空文字("")となります
Dim objWSH As Object
Set objWSH = CreateObject("WScript.Shell")

'環境変数の種類(ENVType)にはSystemやUserがありますが、通常はUserを利用します
'指定した環境変数名(ENVName)の値を取得
CHKENV = objWSH.Environment(ENVType).Item(ENVName)

'不要になった環境変数は念のため削除
If CHKENV <> "" Then
    objWSH.Environment(ENVType).Remove (ENVName)
End If

Set objWSH = Nothing
End Function


以上をVBAの標準モジュールに貼り付けtestを実行するとループが開始されます
次にExcelCtrl.VBSを実行するとExcelのループが終了しVBSで設定したExitが表示されるはずです
※重要:ループ処理の中に必ずDoEventsを入れてください。

〇おまけ
環境変数は各プログラムの起動時に取得しているため、既に起動中のプログラムには反映されません。(このVBAではCHKENV関数の中で毎回環境変数の取り直しをしています)
例えば、先のVBSの実行前にコマンドプロンプトを起動し、set ExcelCtrlで環境変数を確認すると未定義状態ですが
VBSを実行した後に別のコマンドプロンプトを起動し、set ExcelCtrlで環境変数を確認するとExitが返ってくる
でも、先に開いていたコマンドプロンプトもう一度環境変数を確認しても未定義状態のままです
動作確認でコマンドプロンプトを利用するときは注意してください


さて
どうでしょう
なんとなく「面倒な中断処理」がたった数行のプログラムで実現できたのでは?

それではみなさんまた来週
( ̄ー ̄)/~~ジャ
関連記事
スポンサーサイト



コメントの投稿

非公開コメント

おもちゃ箱
Count from 2010/01/14

にほんブログ村 バイクブログ ドゥカティへ


カレンダー
11 | 2019/12 | 01
1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31 - - - -
最新記事
最新コメント
カテゴリ
月別アーカイブ
リンク
検索フォーム
RSSリンクの表示
ブロとも申請フォーム

この人とブロともになる

全記事表示リンク

全ての記事を表示する