t-hom’s diary

主にVBAネタを扱っているブログ…とも言えなくなってきたこの頃。

VBA 中級者を悩ませるプロシージャ分割をマスターする極意

タイトルで大きく出てしまった。極意だなんてまあよく恥ずかしげもなく。
「だって教えるプロの~」よりマシか。。なんちゃって。

ま、是非知ってほしい内容ではあるので、釣ってみた感じ。

さて、それなりにVBAを書けるようになった方が次に悩むこととして、プロシージャの分割方法が分からないというものが多い。Functionの使い方が分からないという相談もよくいただくけれど、これもプロシージャ分割の問題。

上級者のコードは1つのマクロで複数のプロシージャを呼び出していたりするので、「ああ自分のコードと違う」と最初に気付くのがこのプロシージャの分割なのかもしれない。

よくある相談として、「どこをどう分けていいかわからない」というものがある。

これ、まずこの日本語を分けよう。

  • どこを分けていいかわからない
  • どう分けていいかわからない

この2つは別物で、前者は設計の話であるし、後者は具体的なコーディングテクニックの話である。これは明確に区別しておきたい。

実際にプロシージャの分割を学ぶ順としては、まずコーディングテクニックから学んだほうがスッと入ってくると思う。「分割できる」という実感がなければ、どこで分けるべきかという設計の話をしてもいまいちピンとこないんじゃないだろうか。だからまずどこでも分割できるだけのテクニックを身につけると良い。

ということで、今回はこの分割のためのテクニックを中心に解説していく。
設計についても書くつもりだったけど、テクニックを書いたら疲れてしまったのでそれはまた別の機会に。。

具体的なプロシージャ分割テクニック

さて、ここからは実際にプロシージャの分割方法について説明していく。
サンプルに使用するコードはこちら。

Sub じゃんけん()
    Dim you As Integer
    Dim com As Integer
    Do
        you = CInt(InputBox("じゃんけんの手を数字で入力" & vbNewLine & _
            "1:グー、2:チョキ、3:パー"))
        com = WorksheetFunction.RandBetween(1, 3)
        
        Select Case com
        Case 1
            MsgBox "相手はグーを出しました。"
        Case 2
            MsgBox "相手はチョキを出しました。"
        Case 3
            MsgBox "相手はパーを出しました。"
        End Select
        
        If you = com Then
            MsgBox "あいこです。もう一度。"
        ElseIf (you = 1 And com = 2) Or (you = 2 And com = 3) Or (you = 3 And com = 1) Then
            MsgBox "あなたの勝ちです。"
        Else
            MsgBox "あなたの負けです。"
        End If
    Loop While you = com
End Sub

まずは空行に注目する。皆さんもコードを書くとき、ある程度処理のまとまりごとに適宜空行を入れていると思う。つまりここが処理が切り替わる地点だと認識しているわけだ。
そもそもこの程度のマクロをわざわざ分割すべきかどうかという話は一旦おいといて、とりあえず分割してみよう。

最初にやることは、マクロをそっくりそのままバックアップしておくこと。マクロを分割するということは、下手をすると動かなくなってしまうからバックアップは大事。
別のモジュールを挿入してコピー&ペーストしておこう。

次にやることは、メインコードと同じモジュールに新しいプロシージャを作ることだ。

Sub 手の入力()

End Sub

まあこれは当たり前。
次に元のコードから切り出したい部分を、文字通り切り出してくる。

元のコードの該当部分は、作成した「手の入力」プロシージャに変更する。

Sub 手の入力()
    you = CInt(InputBox("じゃんけんの手を数字で入力" & vbNewLine & _
        "1:グー、2:チョキ、3:パー"))
    com = WorksheetFunction.RandBetween(1, 3)
End Sub

Sub じゃんけん()
    Dim you As Integer
    Dim com As Integer
    Do
        Call 手の入力
        
        Select Case com
        Case 1
            MsgBox "相手はグーを出しました。"
'----以下略

この時点ではまだ正しく動作しない。じゃんけんプロシージャの変数you、comと手の入力プロシージャの変数you、comはプロシージャを分割した時点でまったく関係がなくなってしまうからだ。単に名前が同じなだけ。

関係ないものを同じ名前にしておくとややこしいので、別の名前に変更してしまおう。

Sub 手の入力()
    your_hand = CInt(InputBox("じゃんけんの手を数字で入力" & vbNewLine & _
        "1:グー、2:チョキ、3:パー"))
    computers_hand = WorksheetFunction.RandBetween(1, 3)
End Sub

次に外からデータを受け取れるように、手の入力プロシージャのカッコ内にこの変数を入れる。

Sub 手の入力(your_hand, computers_hand)

ここに入力した変数は仮引数(かりひきすう)と呼ばれ、外部から渡されたデータが代入される専用の変数として宣言したことになる。
仮引数(かりひきすう)はプロシージャ内ではふつうの変数(専門的にはローカル変数という)と同じように使えるが、一点注意として、特に指定しなければ仮引数(かりひきすう)は参照という方法でデータを受け取るということ。

※いちいち(かりひきすう)と振り仮名を打ってるのは、私が「いんすう」と読む癖がなかなか抜けなかったので。最初の思い込みはなかなか消えないものだ。

手の入力プロシージャを呼び出すときに変数youとcomを渡すと、your_handとcomputers_handはそれぞれyouとcomと同じものとして扱われ、たとえばyour_handに値を代入するとyouにも同じ値が代入される。これが参照渡しである。

参照渡しの詳しい仕組みは以下の記事に書いたので興味があればどうぞ。
thom.hateblo.jp

次に、じゃんけんプロシージャから手の入力を呼び出すコードのカッコ内に、youとcomを記入する。

Call 手の入力(you, com)

これは実引数(じつひきすう)と呼ぶ。仮引数(かりひきすう)と実引数(じつひきすう)はどちらも単に引数(ひきすう)と呼ばれることが多いが、説明の都合上分けておいたほうが理解しやすいのであえて用語を紹介した。

さて、これで1箇所分割できた。

他の箇所も分割するとこのようになる。

Sub 手の入力(your_hand, computers_hand)
    your_hand = CInt(InputBox("じゃんけんの手を数字で入力" & vbNewLine & _
        "1:グー、2:チョキ、3:パー"))
    computers_hand = WorksheetFunction.RandBetween(1, 3)
End Sub

Sub 相手の手を表示(computers_hand)
    Select Case computers_hand
    Case 1
        MsgBox "相手はグーを出しました。"
    Case 2
        MsgBox "相手はチョキを出しました。"
    Case 3
        MsgBox "相手はパーを出しました。"
    End Select
End Sub

Sub 勝敗判定(your_hand, computers_hand)
    If your_hand = computers_hand Then
        MsgBox "あいこです。もう一度。"
    ElseIf (your_hand = 1 And computers_hand = 2) _
        Or (your_hand = 2 And computers_hand = 3) _
        Or (your_hand = 3 And computers_hand = 1) Then
        MsgBox "あなたの勝ちです。"
    Else
        MsgBox "あなたの負けです。"
    End If
End Sub

Sub じゃんけん()
    Dim you As Integer
    Dim com As Integer
    Do
        Call 手の入力(you, com)
        Call 相手の手を表示(com)
        Call 勝敗判定(you, com)
    Loop While you = com
End Sub

あ、それと今回はちょっと例が悪いので紹介できないけれど、プロシージャ分割した時点で、元の変数がそのプロシージャ内だけで使う一時的な変数になることもある。つまり仮引数として外部から持ってこなくても、そのプロシージャ内で宣言して、そのプロシージャ内で使い終わるような変数。

これをローカル変数と呼ぶが、その前段として以下の準備が必要になる。
thom.hateblo.jp

関数分割するかどうかにかかわらず、普段から変数は使用する直前で宣言するようにしておくと良い。

プロシージャを関数化するテクニック

VBAにおいて関数というのは、要するにデータを返すプロシージャで、ふつうはFunctionプロシージャで作る。
Functionをどういうときに使うのかという質問もよく受けるけど、その前にプロシージャの分割ができていることが前提となる。
さて、プロシージャの分割までは前項で完了したので、これの一部を関数化していこう。

まずはこちら。

Sub 相手の手を表示(computers_hand)
    Select Case computers_hand
    Case 1
        MsgBox "相手はグーを出しました。"
    Case 2
        MsgBox "相手はチョキを出しました。"
    Case 3
        MsgBox "相手はパーを出しました。"
    End Select
End Sub

まずはSubをFunctionに書き換える。

Function 相手の手を表示(computers_hand)
    Select Case computers_hand
    Case 1
        MsgBox "相手はグーを出しました。"
    Case 2
        MsgBox "相手はチョキを出しました。"
    Case 3
        MsgBox "相手はパーを出しました。"
    End Select
End Function

まだこの時点では値を返すことはできない。

そして、MsgBoxを表示させていたところを、プロシージャ名への代入式に変更する。

Function 相手の手を表示(computers_hand)
    Select Case computers_hand
    Case 1
        相手の手を表示 = "相手はグーを出しました。"
    Case 2
        相手の手を表示 = "相手はチョキを出しました。"
    Case 3
        相手の手を表示 = "相手はパーを出しました。"
    End Select
End Function

「相手の手を表示」プロシージャの変更はとりあえずこれだけでもOK。

プロシージャに代入ってところがイメージできにくいかもしれないのでもう少し簡単なサンプルで例を示す。

まずはSubで参照渡しを使った値の取得からおさらい。

Sub ヨブ()
    Dim ret As Long
    Call ヨバレール(10, ret)
    MsgBox ret
End Sub

Sub ヨバレール(a, return_value)
    return_value = a * 2
End Sub

ヨブを実行すると、ヨバレールに実引数10とretが渡り、仮引数aとreturn_valueで受け取る。
このときヨバレールのreturn_valueはヨブのretと同じものを指しているのでreturn_valueに10*2が代入されるということは、retにも20が入る。

…という面倒な処理を頻繁にしなくて良いように、もうreturn_valueは書かなくても使えるようにしない?って生まれたのがFunction。
Functionを使って書き直すとこうなる。

Sub ヨブ()
    Dim ret As Long
    ret = ヨバレール(10) '呼出しから戻ると見えないreturn_valueがretに入る
    MsgBox ret
End Sub

Function ヨバレール(a) '見えない仮引数return_valueがある。
    ヨバレール = a * 2
End Function

プロシージャ名自体が、return_valueのように機能する。
ここで呼出し元に戻す値を「戻り値(もどりち)」と呼ぶ。

戻り値はどんなデータ型を戻すか指定することもできる。
それにはプロシージャ名のカッコの後ろに[As データ型]を付与すれば良い。

Function ヨバレール(a) As Long

ちなみに引数にもデータ型を指定することができる。

これらを「相手の手を表示」プロシージャに反映させるとこうなる。

Function 相手の手を表示(computers_hand As Integer) As String
    Select Case computers_hand
    Case 1
        相手の手を表示 = "相手はグーを出しました。"
    Case 2
        相手の手を表示 = "相手はチョキを出しました。"
    Case 3
        相手の手を表示 = "相手はパーを出しました。"
    End Select
End Function

ちなみに私は戻り値は一旦retという変数に入れて最後でプロシージャに代入することが多い。

Function 相手の手を表示(computers_hand As Integer) As String
    Dim ret As String
    Select Case computers_hand
    Case 1
        ret = "相手はグーを出しました。"
    Case 2
        ret = "相手はチョキを出しました。"
    Case 3
        ret = "相手はパーを出しました。"
    End Select
    相手の手を表示 = ret
End Function

そうすればプロシージャ名を変更したときに、2箇所の書き換えで済むから。

さて、呼出し側にはString型でメッセージが戻るので、直接MsgBoxに引き渡してやるとそのまま画面表示される。
このようなコードになった。

Sub じゃんけん()
    Dim you As Integer
    Dim com As Integer
    Do
        Call 手の入力(you, com)
        MsgBox 相手の手を表示(com)
        Call 勝敗判定(you, com)
    Loop While you = com
End Sub

勝敗判定の関数化は皆さんでやってみてほしい。
いろんなやり方がある。たとえば、

  • メッセージを返す。
  • 結果を1(あいこの場合)、2(勝ちの場合)、3(負けの場合)とLong型で返し、呼出し元のSelect文でメッセージを分ける。
  • 結果を"あいこ"、"勝ち"、"負け"とString型で返し、呼出し元のSelect文でメッセージを分ける。
  • あらかじめ列挙型定数GameResultを宣言し、Win、Even、Loseを含める。呼出し元のSelect文でメッセージを分ける。

などなど。

どれが良いかという議論の前に、いろんなやり方、引き出しを持っておくことが重要だと思う。

どこを別プロシージャに分割するかについての参考書

以下の書籍が非常に参考になった。

ゲームプログラマのためのコーディング技術

ゲームプログラマのためのコーディング技術

書かれているコードはC++なのでVBAしかしない方は購入を躊躇するかもしれないけど、P67~89(初版 第1刷の場合)の関数化のパターンは中級者にとって非常に有益な情報が掲載されている。

一部引用

コードの重複部分をまとめるだけが関数化ではありません。

~ 中略 ~

関数化するポイントにはパターンがあります。ここでは、次の関数化のパターンを初心者でもわかりやすいように紹介します。

・条件式の関数化
・計算式の関数化
・ループの関数化
・ループのブロック内の関数化
・データ変換の関数化
・データ確認の関数化
・配列アクセスの関数化
・コメント部分の関数化

このパターンを身に付けるだけで格段にコードの保守性が高まります。

以降のページでこれらについて詳しく解説されている。

また、多過ぎる引数の問題、小さな関数の必要性、関数化の目的は再利用だけではない、などの非常にためになるトピックを扱っている。

ちなみにこの書籍でいう関数とは、Functionはもちろん、Subプロシージャも含むと思って良い。C++言語の用語ではどちらも関数なのだ。

ゲームプログラマのための」とタイトルについてるけど、具体的にゲームを作る話は出てこず、専らコーディング技術に焦点を当てた書籍なので、「すべてのプログラマのための基本コーディング技術」というタイトルの方が売れたかもしれない。

Amazonレビューでは「今更感の強い内容」といったレビューもあるのだけど、あくまで経験を積んだ現役バリバリのプログラマーにとって今更だというだけで、事務職や運用でVBAやってる方々からしたら目から鱗なお宝が盛り沢山だ。

一方で、クラスに関してはC++を前提にしているのでVBAで参考にできる部分とそうでない部分がある。VBAには継承が存在しないためだ。また、STLラムダ式など、VBAに無い機能を前提に書かれている箇所もあるのですべて参考にできるわけではない。

そのへんを割り切って、コードではなく解説をメインに読むと色々と学べるところがあると思う。

あ、あとhttps://www.relief.jp/itnote/の伊藤 潔人さんがちょうど先ほどTwitterでタイムリーに以下の記事を紹介されていたのでこれも参考に追記。
www.publickey1.jp

VBA WSHとScriptingに存在するFileSystemObjectは同一のバイナリーであることが判明

VBエディタから参照設定で「Windows Script Host Object Model」と、「Microsoft Scripting Runtime」の両方にチェックを入れると、どちらのライブラリにもFileSystemObjectが存在する。
f:id:t-hom:20170204001354p:plain

今回はこの2つが同じものなのか、それとも別物なのかを検証してみた。

発端は、いみひとさんのツイート。

実は私も以前から気にはなってたのだけど、ライブラリが違う以上別物だと思っていた。

Windows Script Host Object Modelの本体はC:\Windows\system32\wshom.ocxである。
一方で、Microsoft Scripting Runtimeの本体はC:\Windows\system32\scrrun.dllである。

まったく同じ機能を備えた、完全な同等品、しかし参照先のバイナリーが違う以上は別物だろうと結論づけていたのだが、相互に代入できるとなってはやはり気になる。

ちなみにこれらのオブジェクトはComponent Object Model(COM)という技術で作成されており、その実態はインターフェースの塊らしい。FileSystemObject型が単なるインターフェースであれば、異なるバイナリーを代入できてもなんら不思議なことではない。

さて、どうしたものかと思ったところで、以前以下の記事でやったファイルを一時的にリネームするという強引な手段を思い出した。
thom.hateblo.jp

そして今回もscrrun.dllをリネームすれば、Scripting.FileSystemObjectが利用できなくなるはず。

それで、実際に試した結果、Scripting.FileSystemObjectに加えてIWshRuntimeLibrary.FileSystemObjectまでもが利用できなくなった。

ということは、WSHのFileSystemObjectは実質scrrun.dllに依存しているということになる。

つまり、この2つのFileSystemObjectは同一ということになる。

WSHを参照設定しているなら、FileSystemObjectを使うためだけにわざわざScriptingを追加で参照設定する必要は無く、IWshRuntimeLibrary内のFileSystemObjectを利用すれば良いということかな。

まぁただ、IWshRuntimeLibrary.FileSystemObjectはなじみが薄いので、私はあえて重複と知ってもScriptingを参照すると思う。。

両方参照している場合、どちらのライブラリのものかはっきりさせるために変数宣言やNewの時にライブラリ名から指定すべきと考えていたが、VBAがどう解釈しようと同じバイナリを指すのだから、これは別にどっちでもいいってことになる。


ちなみにバイナリーってそもそも何?という型はこちら参照。
thom.hateblo.jp

VBA ありそうで無かった、参照設定とCreateObjectの対応表を作った

表題のとおり。。。なんだけど、作ったといってもまだたったの6点のみ。

  • ファイルシステムオブジェクト
  • ディクショナリーオブジェクト
  • WSHシェルオブジェクト
  • WMIオブジェクト
  • InternetExplorerオブジェクト
  • 正規表現オブジェクト

しかしこれがまた曲者で、毎回調べてるとなかなか骨が折れる。
頭がMicrosoftだったか、Windowsだったか忘れたり、次にScriptだったかScriptingだったかScriptletだったか忘れたり、そもそもインターネットのサンプルがCreateObjectばっかりで参照設定名やオブジェクト名がわからなかったりする。

特にややこしいのがWSHシェル。CreateObjectではWScript.Shellなのでてっきり参照設定した場合のオブジェクト名もShellかなと思いきや、WshShellなのだ。しかもライブラリ名はIWshRuntimeLibraryなんていう長ったらしいもの。

それで、対応リストはこちら。私の(一応の)メインサイトである。
参照設定とCreateObjectの対応リスト - You.Activate

まぁちょっとまだ作りかけ感あるし、もう少し掲載するオブジェクトも増やすつもりだけど、一旦私がよく忘れるやつは出そろったので公開。

ちなみにここに書いているProgIDという用語はCOMの書籍で初めて知ったもの。
たぶんProgramIDの略だろうけど、CreateObjectに渡している文字列は実はProgIDというらしい。

最近COMの仕組みをもう少し詳しく理解したくて、この3冊を買った。すべて豊田孝さんという方が書いた書籍。

Visual BasicプラグラマのためのCOM入門 (DeV Selection)

Visual BasicプラグラマのためのCOM入門 (DeV Selection)

まだちゃんと読んでないけど、COM入門の方は多分VBC++で似たようなことやってるので片方で良かったかな。COMアーキテクチャと~はパラパラっと読んだけど難しくてまだまだ消化しれきる気がしない。

(解決済)VBAからPowerShellのパラメーター付きコマンドが実行できずにハマった話

先日以下の記事を書いたが、ひとつ問題が発覚した。

thom.hateblo.jp

パラメーター付きのコマンドがうまく実行されないのだ。
実行時エラーで、「ファイルが見つかりません」と出てしまう。つまり、コマンドが失敗してテンポラリーファイルが作成されてないということ。

さんざん悩んでようやく原因が判明したので詳細を解説しようと思う。

どうやら、ひとつめのコマンドにパラメーターをつける場合、パラメーターごとシングルクォートで括る必要があるようだ。

Sub Sample1()
    Debug.Print SystemAccessor.GetPSCommandResult( _
        "'dir c:\work\'") 'dirはPowerShellではGet-ChildItemのエイリアス
End Sub

ひとつめのと書いたのは、パイプで複数つなぐ場合にふたつめ以降はシングルクォートが要らないから。

Sub Sample2()
    Debug.Print SystemAccessor.GetPSCommandResult( _
        "'dir c:\work\' | select name") 'dirはPowerShellではGet-ChildItemのエイリアス
End Sub

なんでこんな変な仕様になってるのか。

まず私が作ったモジュールではコマンドは最終的に以下のように展開されてWindows Script Hostに渡る。

powershell -ExecutionPolicy RemoteSigned -Command Invoke-Expression "'dir c:\work\' | select name | Out-File -filePath C:\Users\thom\AppData\Local\Temp\rad47E4A.tmp -encoding Default"

ここで以下の部分に注目
Invoke-Expression "'dir c:\work\' | select name

もしシングルクォートがなかったらこうなる。
Invoke-Expression "dir c:\work\ | select name
このとき、Invoke-Expressionは、dirc:\work\を両方自分に渡されたパラメーターとして処理してしまい、エラーになるのだ。

しかしパイプを挟んで次のコマンドはシングルクォートで囲むとうまくいかない。統一感がなくてややこしいけど、単一コマンドならシングルクォートで囲む、パイプ処理なら最初のコマンドだけシングルクォートで囲むということをしないといけない。

そのうちVBA側で加工するように改良したいけどちょっとエスケープ関連の処理が面倒だ。ということでこの挙動は既知の問題、暫定回避策は最初のコマンドをシングルクォートで括る。として一旦対応を保留することにした。

さて、ではもう少し実践的なサンプルを。
たとえば「x200」というホストから稼働中のサービスを取得するにはこのように書く。

Sub Sample3()
    Debug.Print SystemAccessor.GetPSCommandResult( _
        "'Get-Service -ComputerName x200'|Where-Object{$_.Status -eq 'Running'}")
End Sub

短縮版のPowerShellコマンドでもOK。

Sub Sample4()
    Debug.Print SystemAccessor.GetPSCommandResult( _
        "'gsv -c x200'|?{$_.status -like 'r*'}")
End Sub

もう少し複雑な処理サンプル。

Sub Sample5()
    Debug.Print SystemAccessor.GetPSCommandResult( _
        "'Get-Service -ComputerName x200'" & _
            "|Where-Object{$_.Status -eq 'Running'}" & _
            "|Select-Object -f 10")
End Sub


当初はなぜエラーになるかわからずだいぶハマった。
teratailで質問しようかと思い、試したことや挙動を情報整理していたのだが、そこでコマンドプロンプトに直接展開後のコマンドを張り付けることを思い立ち、以下を張り付けたところ、
powershell -ExecutionPolicy RemoteSigned -Command Invoke-Expression "dir c:\work\ | select name"

Invoke-Expression : 引数 'c:\work\' を受け入れる位置指定パラメーターが見つかりません。というエラーが発生した。
f:id:t-hom:20170202033409p:plain

なぜにInvoke-Expressionが引数'c:\work\'を取ろうとしているのか。。ということで原因に気付いた次第。

基本的にVBAからWSH経由で実行させるPowerShellコマンドは、コマンドプロンプトで直接実行させることもできるので、VBAでうまくいかないときはコマンドプロンプトでやってみると良いと思う。

以上。

VBAから手軽にDOSコマンドやPowerShellを実行して結果を取得するモジュールを作成

システムの運用をやっていると、VBAからDOSコマンドとかPowerShellを実行したくなるケースがある。
WScript.ShellのExecメソッドなら標準出力が取得できるのだが、一瞬DOS窓が開いてしまうのがちょっと格好悪い。

非表示でやりたいと思ったら、Runコマンドでコマンド結果をリダイレクトでファイルに書き込み、それを読み込むという面倒なことをしないといけない。

ああ面倒くさい。。

ということで、楽にそういうことができるようにモジュールで包んで抽象化した。
さて、普段なら作り方から説明するんだけれど、今回はコードが長いのでまず使う方のコードを紹介しよう。

使い方

たとえばipconfig /allの結果を取得したいとする。

記述するコードは、

なんと、これだけ!(いぇい!)

Sub Sample1()
    Debug.Print SystemAccessor.GetCommandResult("ipconfig /all")
End Sub

パイプも、ばっちりOK。

Sub Sample2()
    Debug.Print SystemAccessor.GetCommandResult("ipconfig /all | find ""IPv4""")
End Sub

ダブルクォーテーションのエスケープがちょっとめんどい。

Sub Sample3()
    Debug.Print SystemAccessor.GetCommandResult("dir ""c:\program files""")
End Sub

PowerShellコマンドはGetPSCommandResultと書く。

Sub Sample4()
    Debug.Print SystemAccessor.GetPSCommandResult("service")
End Sub

オブジェクトパイプもばっちり

Sub Sample5()
    Debug.Print SystemAccessor.GetPSCommandResult("service|?{$_.status -eq 'Running'}")
End Sub

※ただしPowerShellの場合はコマンド全体をダブルクォートで囲ってInvoke-Expressionに渡しているので、指定するコマンド内にダブルクォートが使えない。代わりにシングルクォートを使う。

その他、結果を1行ごとに分けて配列として返すメソッドとテキストストリームとして返すメソッドを用意している。
また、FileSystemObjectとWScript.ShellはそれぞれSharedFSO、SharedWshShellというプロパティで外部公開しているので、以下のように変数も作らずにいきなり使用することができる。(AriawaseのIOモジュールからアイデアをいただきました。)

f:id:t-hom:20170131011949p:plain

ただし、以下を参照設定しておかないと入力候補は出ない。

2017/2/2 追記

このモジュールからPowerShellコマンドを使う際に一つ問題が発覚。
ひとつめのコマンドにパラメーターをつける場合、パラメーターごとシングルクォートで括る必要があるようだ。

Sub Sample()
    Debug.Print SystemAccessor.GetPSCommandResult( _
        "'Get-Service -ComputerName x200'" & _
            "|Where-Object{$_.Status -eq 'Running'}" & _
            "|Select-Object -f 10")
End Sub

詳細はこちら
thom.hateblo.jp

作り方

まず、標準モジュールを作成し、モジュール名を「SystemAccessor」とする。
次に以下のコードをコピーして貼り付けるだけ。

参照設定しなくても動作するけど、参照設定する場合は#Const REF = False#Const REF = Trueに書き換える。

#Const REF = False
'If REF then require references below _
- Microsoft Scripting Runtime _
- Windows Script Host Object Model

'If Not REF Then Requir Nothing

#If REF Then
Private fso_ As Scripting.FileSystemObject
Private shell_ As IWshRuntimeLibrary.WshShell
#Else
Private fso_ As Object
Private shell_ As Object
#End If

#If REF Then
Property Get SharedFSO() As Scripting.FileSystemObject
#Else
Property Get SharedFSO() As Object
#End If
    If fso_ Is Nothing Then Set fso_ = CreateObject("Scripting.FileSystemObject")
    Set SharedFSO = fso_
End Property

#If REF Then
Property Get SharedWshShell() As IWshRuntimeLibrary.WshShell
#Else
Property Get SharedWshShell() As Object
#End If
    If shell_ Is Nothing Then Set shell_ = CreateObject("WScript.Shell")
    Set SharedWshShell = shell_
End Property

Function GetTempFilePath(Optional create_file As Boolean = False) As String
    Dim ret As String
    ret = Environ$("temp") & "\" & SharedFSO.GetTempName
    If create_file Then
        Call SharedFSO.CreateTextFile(ret)
    End If
    GetTempFilePath = ret
End Function

#If REF Then
Function GetCommandResultAsTextStream(command_string, Optional temp_path) As Scripting.TextStream
#Else
Function GetCommandResultAsTextStream(command_string, Optional temp_path) As Object
#End If
    Dim tempPath As String
    If IsMissing(temp_path) Then
        tempPath = GetTempFilePath
    Else
        tempPath = temp_path
    End If
#If Not REF Then
    Const WshHide = 0
    Const ForReading = 1
#End If
    Call SharedWshShell.Run("cmd.exe /c " & command_string & " > " & tempPath, WshHide, True)
    Set GetCommandResultAsTextStream = SharedFSO.OpenTextFile(tempPath, ForReading)
End Function

Function GetCommandResult(command_string) As String
    Dim ret As String
#If REF Then
    Dim ts As Scripting.TextStream
#Else
    Dim ts As Object
#End If
    Dim tempPath As String: tempPath = GetTempFilePath
    Set ts = GetCommandResultAsTextStream(command_string, tempPath)
    If ts.AtEndOfStream Then
        ret = ""
    Else
        ret = ts.ReadAll
    End If
    ts.Close
    Call SharedFSO.DeleteFile(tempPath, True)
    GetCommandResult = ret
End Function

Function GetCommandResultAsArray(command_string) As String()
    Dim ret() As String
    ret = Split(GetCommandResult(command_string), vbNewLine)
    GetCommandResultAsArray = ret
End Function

#If REF Then
Function GetPSCommandResultAsTextStream(command_string, Optional temp_path) As Scripting.TextStream
#Else
Function GetPSCommandResultAsTextStream(command_string, Optional temp_path) As Object
#End If
    Dim tempPath As String
    If IsMissing(temp_path) Then
        tempPath = GetTempFilePath
    Else
        tempPath = temp_path
    End If
#If Not REF Then
    Const WshHide = 0
    Const ForReading = 1
#End If
    Call SharedWshShell.Run("powershell -ExecutionPolicy RemoteSigned -Command Invoke-Expression """ & command_string & " | Out-File -filePath " & tempPath & " -encoding Default""", WshHide, True)
    Set GetPSCommandResultAsTextStream = SharedFSO.OpenTextFile(tempPath, ForReading)
End Function

Function GetPSCommandResult(command_string) As String
    Dim ret As String
#If REF Then
    Dim ts As Scripting.TextStream
#Else
    Dim ts As Object
#End If
    Dim tempPath As String: tempPath = GetTempFilePath
    Set ts = GetPSCommandResultAsTextStream(command_string, tempPath)
    If ts.AtEndOfStream Then
        ret = ""
    Else
        ret = ts.ReadAll
    End If
    ts.Close
    Call SharedFSO.DeleteFile(tempPath, True)
    GetPSCommandResult = ret
End Function

Function GetPSCommandResultAsArray(command_string) As String()
    Dim ret() As String
    ret = Split(GetPSCommandResult(command_string), vbNewLine)
    GetPSCommandResultAsArray = ret
End Function

科学のモデルとはフィクションにおける「設定」のようなもの

今回はこちらの記事を受けて、私が学生の頃、先生にどう説明してほしかったのかという話を。
chemiphys.hateblo.jp

こういうのって個人差が大きいので、クラス全員がまんべんなく理解できるような解は無いんだろうけど。

実は私、ここ半年くらいの間に原子構造について本気で色々と調べたことがある。
きっかけは以下の記事で、コンピューターの動作を説明しているうちに、どうせなら電気が流れる仕組みにまで遡ってみようと思ったわけだ。
thom.hateblo.jp

まずANDやORなどの論理回路の中身を調べ始めると半導体の話になる。トランジスタである。
じゃあトランジスタってなんぞやと調べていくと、こういう動画に行き着いた。
www.youtube.com

英語だけれど図解が秀逸なので動画だけ見てもなんとなく言ってることがわかると思う。
英語圏の方々は本当に恵まれている。

こういうことを考えているうちに、原子の自由電子とかどうなっているか気になる。そこでよく説明に使われるのが冒頭の記事で紹介されているようなボーアモデルという図。

学生の頃私はこのモデルを事実と混同しており、いくら考えてもわからなかった。
だってもし図の通りなら原子というのはスカスカである。そんなものでこの世界が構成されていたら、コップに入れた水だってスリ抜けてこぼれるだろう。かといって原子核をぎちぎちに敷き詰めたらもはや電子が回るスキマなんてない。何か原子と原子の間を保つような、たとえば磁力のようなものがあるのだろうか。それとも膜でもあるのだろうか。

私の場合、イメージできないってことはなかった。
だってイメージもなにも、それはすでに黒板に書いてあるし、教科書にも図は載っている。

だからたぶん分からないといっている生徒は、

  • 聞いてない
  • イメージしすぎる

のどちらかじゃないかと思っている。(素人考えだけれど)

さて、モデルというのは厳密さを犠牲にして本質だけを抜き出したものである。「そのように考えると色々説明がつく」というものに過ぎず、事実とは異なることが多い。いわゆるマンガなどのフィクションでいう「設定」というやつだ。

人間は空を飛べないし、体が伸びたり火を出したりといったことはできないけれど、そんな事実は置いといて、マンガの世界では一旦「できる」という設定にしてしまう。そうしないとストーリーが先に進まない。

乱暴にいうと科学におけるモデルも似たようなものかなと。これはあくまで「設定」なので現実世界の物理法則は一旦忘れて、そういうものだと受け入れるしかない。

電子雲というモデルもあるようだ。動画で見たらよくわかった。
www.youtube.com
超高速で動き回ってるので残像が雲のように見えるってことかな。要はドラゴンボールの「残像拳」をイメージすると良いかと。
ま、これも所詮モデルにすぎない。

じゃあモデルじゃなくて実際のところはどうなってるの?と色々調べてみたんだけど、無理だということが分かった。
今の技術力では、原子1つがやっと映像化されたところだ。
www.youtube.com

科学の世界はモデルでしか説明できないことが多いのだ。
私が学生の頃にモデルと事実の違いについてきちんと説明を受けたいたらもう少し興味が持てたかもしれない。

VBA マクロ、プロシージャ、メソッドなどの用語について正確に理解する

初心者向けの解説によくマクロとVBAの違いが取り上げられる。
一応違いについて説明した後、同じようなものなのであまり気にしなくて良いと締めくくるケースも多いけど、私はやはり違いを意識したほうが良いと考える。

初心者は同じようなものと言ってくれたほうが嬉しいだろう。単純に「VBA=マクロ」と覚えれば良いのだから。

しかしいざネットで調べものをしようと思った際に、そこに書かれているVBAあるいはマクロという言葉がその文章において何を指しているのか理解できなければ、筆者の意図を正確に理解することはできず、誤解のもとになるかもしれない。

言葉の正確な理解はコミュニケーションの基本である。

今回はマクロとVBAの違いの話がメインではないが、一応違いについても書いておく。

マクロとVBAの違い

マクロはもともと「マクロインストラクション」の略で、和訳すると「大きな命令」。
つまりExcelの命令群をたくさん集めてひとつの大きな命令として集約したものである。
対してVBAはそのマクロを書くための言語のこと。日本語とか英語とかと同じ、VBA語。

マクロとソースコードの違い

VBAなどのプログラミング言語で書かれたものは、ソースコードあるいは省略してソース、またはコードともいう。
じゃあソースコード=マクロなの?っていうとそれも違う。

マクロは実際に実行できる機能としての命令群を指すのに対し、ソースコードは単に「プログラミング言語で書かれたもの」を指している。
ソースコードはマクロの原材料になるものである。実行できようができまいがそんなことは関係なく、書きかけでも断片でもソースコードと呼ぶ。

マクロとプロシージャの違い

さて、ここからが重要。
VBAを学習していくとプロシージャという呼び方が登場する。新しい横文字が登場するたびにウンザリする方もいるだろう。ひょっとして隠れた挫折地点になっているということもあるかもしれない。

ここに適当に書いたソースコードがある。
マクロはいくつある?

Sub Sample()
    MsgBox "Hello!"
End Sub

Sub Sample2()
    Call Sample
    MsgBox "1 + 2 = " & Add(1, 2)
    MsgBox "GoodBye!"
End Sub

Function Add(a, b) As String
    Add = a + b
End Function

3つと答えた方は不正解。
マクロは実行単位で見た言い方なので、単体で実行できないAddはマクロではない。

Sampleはマクロだ。開始から終了まで一直線で実にシンプル。
f:id:t-hom:20170129085354p:plain

Sample2はもう少し複雑で、実行の流れを図示するとこうなる。
f:id:t-hom:20170129085516p:plain

SampleはHello!と表示するマクロである。
Sample2はまずHello!と表示し、次に1+2の計算結果を表示し、最後にGoodBye!と表示するマクロである。

このように、一連の実行単位で見た呼び方が、「マクロ」なのである。

一方プロシージャという呼び方はもっと単純で、単にSubからEnd Sub、FunctionからEnd Functionというかたまりを指す。
f:id:t-hom:20170129083945p:plain

プロシージャとは手続きという意味の英語である。
マクロとして単体で実行できるものもあれば、単体では実行できないものもある。

プロシージャとメソッドの違い

プロシージャが単に個々の手続きを指すのに対し、メソッドはプロシージャの所属元を意識した言い方である。

それぞれ、Module1のSampleメソッド、Module1のSample2メソッド、Module1のAddメソッドであると言える。
f:id:t-hom:20170129090430p:plain

メソッドとは「手法・方法」という意味の英語であるが、ここではModule1が持つ「技(ワザ)」と考えたほうがしっくりくる。

ちなみにSubプロシージャやFunctionプロシージャはメソッドであるが、Propertyプロシージャはふつうメソッドとは呼ばない。

※標準モジュールでメソッドという言い方は聞きなれないかもしれないが、私は標準モジュールもオブジェクトであると考えているのでメソッドと呼ぶ場合もある。とりわけModule1.Sampleなどとドット付きで呼び出す場合はメソッドという概念を持ち出したほうが解説が楽だ。
thom.hateblo.jp

フィールドとモジュールレベル変数、Public変数

VBAのモジュールはまず先頭に宣言セクションがあり、そのあとにプロシージャを記述する領域となる。
f:id:t-hom:20170129092641p:plain
※宣言セクションに何も書かずにプロシージャから始めることもできる。

この宣言セクションでDimまたはPrivateで宣言した変数はモジュールレベル変数と呼ばれ、モジュール内のどこからでも参照、代入ができる。

モジュールレベル変数はMicrosoftが用いている用語で、一般的なオブジェクト指向の用語ではプライベートフィールドと呼ぶ。

宣言セクションでPublicを用いて宣言した変数はPublic変数と呼ばれる。こちらは一般的なオブジェクト指向の用語ではパブリックフィールドと呼ぶ。

宣言セクションで宣言される変数はPublic、Privateいずれにしてもフィールドと呼ばれる。

フィールドもまた、所属を意識した呼び方である。
Module1のプライベートフィールド、Module1のパブリックフィールドという言い方をする。
f:id:t-hom:20170129094021p:plain

プロパティについて

プロパティというのはこれまたややこしい奴なのだが、以下の記事で詳しく書いたので割愛する。
thom.hateblo.jp

メンバーについて

メソッド、フィールド、プロパティなど、そのオブジェクトに所属するものを総称して「メンバー」と呼ぶ。
f:id:t-hom:20170129093430p:plain

Javaなどでは「メンバ」とのばし棒が入らない呼び方が一般的だが、VBAではオブジェクトブラウザー上で「メンバー」の表記を見かけたのでこちらの表記とする。

関数について

VBAにおいて関数とは値を返すプロシージャのうち、汎用的に使用できるものを指す。

なんともふわっとした定義なのだが、MicrosoftがNowを関数と呼ぶ以上、このように定義するしかなかった。
thom.hateblo.jp

VBAの組み込み関数、ワークシート関数も関数である。
ここでいう汎用的にというのは特定のオブジェクトと結びつきが無いという意味である。

通常はFunctionプロシージャを用いて作られるが、Propertyプロシージャでも作ることができる。また、Functionで作られているものでもオブジェクトモジュールに書かれて固有のオブジェクト操作に用いるものはメソッドと呼び関数とは区別する。

C言語出身の方はVBAのSubプロシージャを関数と呼ぶケースがあるが、これは間違い。
Cの関数はmainでさえ必ず値を返すので手続き(プロシージャ)=関数であるが、VBAではSubは値を返さないので関数ではない。

おわりに

プログラミング初心者はネットで調べものをしても書かれている言葉がちんぷんかんぷんでちっとも理解できないということも多いと思う。今回は特に紛らわしい用語についてその違いを説明したかった。

ネットの記事では恰好つけるためにわざと小難しい言葉が選ばれているわけではない。
同じプロシージャでも、マクロと呼ぶか、コードと呼ぶか、プロシージャと呼ぶか、メソッドと呼ぶか、メンバーと呼ぶかでそれぞれ視点が違う。
この微妙な違いを感じ取れるようになったら、マニュアルやネットの記事を見てもより筆者の言いたいことが理解できるようになる。

やはり言葉の正確な理解はコミュニケーションの基本である。
用語は大事なのでしっかりおさえておこう。

当ブログは、amazon.co.jpを宣伝しリンクすることによってサイトが紹介料を獲得できる手段を提供することを目的に設定されたアフィリエイト宣伝プログラムである、 Amazonアソシエイト・プログラムの参加者です。