Sub CmdExe(ByVal CmdStc As Variant, Optional ByVal ShowPrmpt As Long)
    Dim WSH As Object
    Set WSH = CreateObject("WScript.Shell")
    WSH.Run CmdStc, ShowPrmpt, True
End Sub

「テスト用に」の意味だが、大量のファイルを処理するなら、batファイルに書きだした方が効率が良いからである。バッチ処理の方法は「ファイルの暗号化・圧縮②」以降に記載する。

【ツール専用プロシージャ】圧縮コマンドを投げる

次に、設定シートから必要な変数を拾ってきて、投げるだけのコードを書こう。
bat出力を含む、後の拡張性を考えれば、以下のようなものだろう。

Sub ZipFile()
    Call SetHack
    Select Case GetVal_Dft("実行タイプ")
        Case 0
            CmdStc = GetVal_Dft("単体コマンド")
            Call CmdExe(CmdStc)
    End Select
End Sub

god-of-excel.hateblo.jp

これは、以前の記事で紹介したSetHackプロシージャ（固定値の設定）、GetVal_Dft関数（変数取得）などを使っている。
ワークシートを有効活用すれば、ここまでVBAを簡略化できるのだ。

エラーが発生したとき、確認するのは自分ではないかもしれない。いかなる場合もエラーが発生しないように膨大なコードを組むこともまた、エラーの温床となる。スタッフ自身が「ツールの実行時、エラー時、どこを確認すべきかがわかる」状態にしておくことが重要だ。

思いつきだが、ツール化するときは、以前の記事に書いたAddin_Switchのように、ExeFuncのような名前で呼び出し関数を作っても良いと思う。あらかじめCmdExeの内部にOptionalでNameProcとMemoProcを持っておいて、エラートラップをかければ、説明が出てくる。で、実行したければApplication.Run ExeFuncする。自作関数には、変数で説明を持っておくのが、保守性も可読性も維持されやすくおすすめだ。要するに、このままコピペしてもツールとしては分かりづらいから、アレンジして使ってな、と言いたかった。あとで書きなおす。

2019-01-18

Captionのプロシージャを呼び出す汎用ボタン

今回は、「ワークシートのデータベース的利用①設定シート編」で書いた「フォームボタンにSetHackを仕込んで、Captionに実行したいプロシージャ名を記載しておき、SetHack後にプロシージャを呼び出す」とはなんぞや、という話。

god-of-excel.hateblo.jp

事例

ツールの実行時には最初に、以下のような処理をまとめたプロシージャを、必ず実行することが多いだろう。今回はツールの実行時に必ず実行するプロシージャを「Gen」と名付けよう。

シートの描画を止める
定数を宣言する

そして、単純なツール例では、普段は「P0_All」を押下するが、たまに個別に実行したいとき、図のようにボタンを分けることは一般的と思う。

その時、以下のように毎回「Gen」プロシージャを書くのかという問題。なんかダサくないすか？

Sub P0_All()
    Call P1_Import
    Call P2_Update
    Call P3_Export
End Sub
Sub P1_Import()
    Call Gen
    Call Import_1
End Sub
Sub  P2_Update()
    Call Gen
    Call Update_1
End Sub
Sub P3_Export()
    Call Gen
    Call Export_1
End Sub

今回はこの課題を、「Caption名を変数として取り込む関数」を作って、ボタンに割り当てることで解決する。

先に言っておくが、必ず最初に特定のプロシージャを実行したいという目的に対し、この手法がいつも適切というわけでもない。
実を言うと、このボタンの元ネタは上記の事例ではなく、いつだったか担当した既存のシステム仕様に合わせて作ったものだ。画面遷移するときに、別に持っている式や値に対し、押下したボタンで処理が分岐するプログラムだったが、細かいところはちょっと覚えていない。
言いたいのは、手数を増やしておくと、どこかしらで役に立つということだ。

フォームコントロールのボタンを使う

今回使うのはActivXコントロールのコマンドボタンではない方。フォームボタンは細かいコントロールはできないが、ボタン自体をどうこうしたいわけじゃないなら、単純で使い出が良い。

【共通関数】キャプション取得関数の作成

まずは、ボタン名とキャプションを取得する関数を作る。

Function CapFBtn()
    'ボタン名取得
    NmFBtn = Application.Caller
    'キャプション取得
    With ActiveSheet
        CapFBtn = .DrawingObjects(NmFBtn).Characters.Text
    End With
End Function

【共通プロシージャ】汎用フォームボタンの作成

そして、4つのボタンすべてに、以下のGenプロシージャを割り当てる。

'共通処理
Sub Gen()
    Application.ScreenUpdating = False
    'ボタンのキャプション取得
    TgtProc = CapFBtn
    'キャプション名のプロシージャ実行
    Application.Run TgtProc
    Application.ScreenUpdating = True
End Sub

もうお分かりだろう。
あとは、ボタンのキャプション名と同じ名前のプロシージャを記述するだけだ。

'全部
Sub P0_All()
    Call P1_Import
    Call P2_Update
    Call P3_Export
End Sub

'P1の処理を書く
Sub P1_Import()    
End Sub
'P2の処理を書く
Sub P2_Update()    
End Sub
'P3の処理を書く
Sub P3_Export()    
End Sub

画面描画のオンオフなど、はじめと終わりが必要な処理が、煩雑なフラグ管理なしに1回で済むのは何気に便利だ。

VBAの強みは「初心者でもなんとなく分かる」「なんとなく動く」ことだと思っているので、見た目通りに、ユーザの期待通りに、直感的に動く書き方を突き詰めていきたい。

2019-01-17

ワークシートのデータベース的利用①設定シート編

このエントリは、以下のような状況において、力を発揮する。

ツールから成果物への要求が頻繁にある
ツールの保守エンジニアや、ツールを使用する現場スタッフに、引き継ぎの可能性がある
中長期間使われるツールである
Excelの使える現場スタッフで、ちょっとしたツールの仕様変更に対応したい

限定された状況とはいえ、いわゆるバックオフィスのEUA/EUC（エンドユーザーシステム）開発においては、ありがちな条件だろう。この前提条件は整理してまた別エントリに記載する。

テーブルを作る

たとえば、以下のような構成の設定シートを作る。

実際のツールでは、必要に応じて、条件付き書式やデータの入力規則を設定し、入力エラーを防ぐと良いだろう。設定シートの組み方のポイントはまた後日。

【共通関数】検索関数の作成

セル位置（行・列）の特定

まずは、変数名の入った行や、変数の入った列がどこかを特定する共通関数を作ろう。

'X列目を対象に検索し、行を特定する
Function Row_TgtCol(ByVal ValName As Variant, ByVal TgtCol As Long)
    With ActiveSheet
        Row_TgtCol = WorksheetFunction.Match(ValName, .Columns(TgtCol), 0)
    End With
End Function

'X行目を対象に検索し、列を特定する
Function Col_TgtRow(ByVal ValName As Variant, ByVal TgtRow As Long)
    With ActiveSheet
        Col_TgtRow = WorksheetFunction.Match(ValName, .Rows(TgtRow), 0)
    End With
End Function

VBAにおけるワークシート関数は、たいてい実行速度もそこそこだし、人によって書き方が変わる部分が少ないので、引き継ぎや改修の工数を減らせるのが利点と言える。

【共通関数】変数操作関数の作成

固定値の宣言・設定

まずはPublicかConstで、変数名や変数を入れる列と、タイトル行を指定する行の、変数を宣言しよう。
そして、ツールの実行時に、必ず最初に実行するプロシージャも作っておこう。ここにはワークシートの略称などを適宜設定しておくと良いだろう。

'設定シート固定行
Public Row_SetTitle As Long
'設定シート固定列
Public Col_SetVN As Long
Public Col_SetVal As Long

'開始設定（例）
Sub SetHack()
    Set Add_B = ThisWorkbook
    Add_B.Activate
    
    'シート名（シート名に合わせる）
    Set Add_S_GlobSet = Worksheets("GlobalSetting")
    Set Add_S_AddProf = Worksheets("AddinProf")
    '設定シート固定行
    Row_SetTitle = 1
    '設定シート固定列
    Col_SetVN = Col_TgtRow("VariableName",  Row_SetTitle)
    Col_SetVal = Col_TgtRow("Value",  Row_SetTitle)
End Sub

Public宣言してあるRow_SetTitle は、 Const Row_SetTitle As Long = 1 としても良い。

ただ、自分なら、このツールでは定数として扱うべきであっても、原則Publicで表記を統一する。なぜなら、同一の現場に Row_SetTitle と同じ役割を担う行が他の値であったり、自分や他人が作った関数を使うケースがありうると考えるからだ。なるべく書き方は統一し、保守コストを抑えたい。

また、決め打ちでの指定か、検索関数を使うかどうかも、現場で将来ありそうな仕様変更と、日頃のツールの使い方をヒアリングして決めよう。

変数の取得

次は、変数を取得する関数を作成する。

'変数取得_標準
Function GetVal_Dft(ByVal ValName As Variant)
    With ActiveSheet
        GetVal_Dft = WorksheetFunction.Index(.Cells, Row_TgtCol(ValName, Col_SetVN), Col_SetVal)
    End With
End Function

変数の書き換え

自動で変数の値を変更する処理が発生する場合（このシートでは、「連番」の値が該当するだろう）のために、変数を書き換える関数を作っておこう。

'変数記入_標準
Function SetVal_Dft(ByVal ValName As Variant, ByVal Val As Variant)
    With ActiveSheet
        .Cells(Row_TgtCol(ValName, Col_SetVN), Col_SetVal) = Val
    End With
End Function

人が変数値を決定するシーンでは、わざわざ上記の関数を使うことは勧めない。VBAのエラー発生のリスク低減や、VBAによる値チェックでコードを無駄に増やさないためだ。

名前の定義の削除/設定/取得（2019/1/23更新）

シートにDefinedNameという列と、変数Col_SetVNを追加した。

'名前の定義削除_標準
Function DltDN_Dft(ByVal ValName As Variant)
    With ActiveSheet
        Range(.Cells(Row_TgtCol(ValName, Col_SetVN), Col_SetVal).Address).Name.Delete
    End With
End Function

'名前の定義設定_標準
Function SetDN_Dft(ByVal ValName As Variant)
    With ActiveSheet
        Range(.Cells(Row_TgtCol(ValName, Col_SetVN), Col_SetVal).Address).Name = GetDN_Dft(ValName)
    End With
End Function

'名前の定義取得_標準
Function GetDN_Dft(ByVal ValName As Variant)
    With ActiveSheet
        GetDN_Dft = WorksheetFunction.Index(.Cells, Row_TgtCol(ValName, Col_SetVN), Col_SetDN)
    End With
End Function

SetHackについては、いちいちVBAから指定するよりは、「シート上で毎回実行するマクロとして明記して、Application.RunまたはCallする」、または「フォームボタンにSetHackを仕込んで、Captionに実行したいプロシージャ名を記載しておき、SetHack後にプロシージャを呼び出す」方がスマートなケースが多いだろう。この話も別エントリで。

2019-01-16

アドインファイル（.xlamと.xla）のシート制御

アドインファイルのシート表示/非表示

アドインファイルは、シートを裏で持っている。
以下のコードで、シートを表示することができる。

'アドインファイルのシート表示
Thisworkbook.IsAddin = False

この状態では、ブックを上書き保存することはできないが、セルに書き込みを行うことは可能である。そこで、たとえばSheet1のA1セルに「a」と書き込みを行った後、以下のコードで再びシートを隠す。

'アドインファイルのシート非表示
Thisworkbook.IsAddin = True

さて、この状態で、以下のサンプルコードを動かしてみよう。

'サンプルコード
Msgbox Worksheets("sheet1").cells(1,1).value

無事、先ほど入力した値「a」が表示されたことだろう。

アドインファイルの上書きコード

一旦、作業がしやすいように、上書き用のコードを作成する。

Sub Addin_DevSave()
    'アドイン上書きを随時行うためのエラー回避ロジック
    'シートを出したまま保存しようとすると、エラーが出るため
    ThisWorkbook.IsAddin = True
    With ThisWorkbook
        .Save
    End With
    ThisWorkbook.IsAddin = False
End Sub

Excelにこのファイルのアドイン設定を行い、ツールバーにボタンとして配置してしまうのがおすすめだ。

シート制御機能と、共通の呼び出し関数

※あとで記事を分割するかもしれません
これらのプロシージャが使いやすいように、呼び出し用の関数を作成した。

'呼び出し用の関数
Function Addin_Switch(ByVal NameProc As Variant, MemoProc As Variant)
    Addin_Switch = MsgBox("※ 機能をオンにする場合はYES、オフの場合はNOを選択してください" & vbCrLf & vbCrLf & "◆ " & NameProc & vbCrLf & vbCrLf & MemoProc, vbYesNo, "スイッチ：" & NameProc)
End Function

以下のプロシージャを作成し、設定シート制御のボタンとして割り当てている。

'設定シート制御
Sub Addin_Setting()
    NameProc = "Addin_Setting"
    MemoProc = "アドインの設定シートを開きます。"
    
    If Addin_Switch(NameProc, MemoProc) = vbYes Then
        With ThisWorkbook
            .IsAddin = False
            .Activate
        End With
    Else
        ThisWorkbook.IsAddin = True
        Call Addin_Save
    End If

End Sub

さて、自分がこのコードをどのように使っているかというと、シートへの変数の格納だ。複数ツール（Excel）からなる業務システムを組む際には、ひとつのファイルで共通の変数を管理したいシーンが多くある。 Excelのシートは、ノンプログラマの自分にとっては、計算内容の視認性が良く、変数の適用範囲もややこしいことがない。
何より、ドメイン知識が複雑な業務（金融・製造業など）の場合は、バックオフィスのスタッフがエラーの状況を理解できることが重要だし、変数を見える化することで、業務フローを簡略化できることすらある。

シートでどう変数を管理しているかは、別エントリに記載する。

神Excelの雑記

闇Excelでバックオフィスの人です。メモ帳的な。

ファイルの暗号化・圧縮①方法編

VBAによるファイルの簡単な暗号化

7-Zipの準備

7-Zipをインストールしよう

7-Zipによるコマンドラインによる圧縮方法

圧縮ツールの概要

UI概要

【共通プロシージャ】コマンド実行