t-hom’s diary

主にVBAネタを扱っているブログです。

VBA SubとFunctionとPropertyの使い分け

VBAには3種類のプロシージャがある。SubとFunctionとPropertyだ。
しかしその守備範囲はキッチリ線引きされているわけではなく、使い分けに困る場面がある。

今回はその使い分けのヒントとなる考え方を紹介する。

なお、3種類と書いたがProperty にはLet・Set・Getがあるので実際には5種類になる。
このうちLetとSetはいずれも値を設定するという目的が同じなのでひとまとめに扱うことにする。

私がプロシージャを使い分ける際に考えていることを以下2つの表にまとめた。
f:id:t-hom:20180527212534p:plain

表が2つあるのは、何のためのプロシージャか(What)と、どのようなプロシージャか(How)の2つの観点で考える為。

What表の解説

単純な手続き

単純な手続きとは、いわゆる「マクロ」など、単純に手続きとしてのプロシージャを指す。

純粋な関数

純粋な関数とは、手続きの外で状態の変更を引き起こさず、ユーザーとの対話用ダイアログが表示されず、与えられた引数によってのみ戻り値が決まる普通の関数のこと。
たとえばLeft関数やFormat関数などが純粋関数である。

逆に「純粋でない」とは、セルの値を変更したり、参照渡しされた引数の中身を変更してしまったりといった状態の変更を引き起こすものや、現在時刻の取得など、システムの状態を取得するもの、ユーザーにメッセージを表示させたり選択肢を表示させたりといった副作用を伴う処理を含むことをいう。

状態の取得

状態とはプロシージャの外で設定されたあらゆる値を指す。
例えばVBAのNow関数はシステムから日時を取得するという意味で状態の取得にあたる。
実際、VBAのNow関数はProperty Getプロシージャで作られている。

状態の設定

状態の取得と逆にプロシージャの外に働きかけて値を変更することを指す。

How表の解説

重い処理

DBへのアクセス、Webからのデータ取得、広いセル範囲への個別アクセスなどある程度負荷が想定される処理を指す。

軽い処理

プロシージャ自体が単純で短く、連続で実行されてもそれほど負荷がかからない処理を指す。

破壊的処理

状態の変更を引き起こす処理を指す。
What表に書いた「状態の変更」は、まさに状態の変更を目的としたものであるが、ここでいう破壊的処理は主目的かどうかを問わず何らかの状態の変更が行われてしまう処理を指す。
たとえばWhat表に書いた「単純な手続き」は多くの場合はセルへの書き込み等の破壊的処理を含むことになる。

これは耳慣れない言葉だと思うが、私はRubyの学習でこの言葉を知った。
破壊的という言葉の響きは何か悪いものを連想させるかもしれないが、全くそういった意味は無い。
大抵の状態変更は意図された正当なものであるが、一律「破壊的」と呼ばれる。

非破壊的処理

プロシージャ外部の状態変更を引き起こさない処理を指す。単純に状態の参照だけならそれは非破壊的処理である。
代表的なものにプロパティの参照や関数がある。

対話的処理

ユーザーに対してダイアログやフォームを表示させる処理をいう。
MsgBoxやInputBoxはFunctionで作られているが対話的処理の代表である。
個人的にはFunctionは純粋な関数に使用されてほしいが、Subで値の取得をするというのも微妙だしProperty Getに対話的処理を入れるなどもってのほかだと考えているので、値を返す対話的処理の場合は妥協してFunctionを使う。

プロシージャで複数の戻り値を扱えないことに対する代替手段

FunctionやProperty Getは単一の戻り値しか返せない。
そこで代替手段として引数を参照渡しにしておき、戻り値の代わりとするテクニックがよく用いられる。
これはこれで否定しないけれど、もしその複数の値が互いに関連するものだった場合は以下のいずれかの手段を採った方がデータの扱いが楽だ。

  • クラスモジュールでオブジェクト設計し、そのオブジェクトを返す。
  • コレクションに格納して返す。
  • 配列に格納して返す。

これらの方法では複数データを単一の戻り値として扱えるのでFunctionやProperty Getを使った非破壊的な処理が実現できる。
つまり複数値を扱いたいがために、表で紹介した原則を曲げるという必要がなくなる。

おわりに

今回紹介したように、プロシージャの使い分けに関して私は一応の指針を持っている。
ただ実際にはそれでもよく迷う。たとえば「状態の取得でかつ、重たい処理」をFunctionとProperty Getのどちらで実装しようか等。
連続で何度も呼び出されるならパフォーマンスに影響しかねないのでHowを優先してFunctionで作る。めったに参照されないならそこまでパフォーマンスに影響ないのでWhatを優先してProperty Getで作るなど。

また、表で×にしたものは、やめておいたほうが良いという私の意見であるが、やろうと思えばできてしまうし、どうしても×のものを選択した方が良いというケースもあるかもしれない。

プログラムが正常に動作する以上、絶対にダメということは無い。最終的にはケースバイケース。自分の頭で悩み・決断を繰り返すうちにコーディングスタイルが磨かれるはず。

VBA Publicなプロシージャをマクロの実行メニューから隠す方法

ユーザーに実行させたくないマクロを「マクロの実行」メニューから隠す方法として最も簡便なのはプロシージャをPrivateにしてしまうことだ。
しかしマクロがある程度大きくなると、複数のモジュールにプロシージャを分散させたい場合がある。

他モジュールから呼び出すためには、Publicにする必要があるが、単にPublicにするとマクロの実行メニューにも見えてしまう。

今回はこのジレンマを解消する方法を紹介する。

まず、引数があるプロシージャはたとえPublicでもマクロの実行メニューに表示されない。
引数を使わない場合はどうすれば良いかというと、Optionalで適当なダミー引数を設定してしまえば良い。

標準モジュールを作って以下のコードを挿入する。

Public Sub HiddenProcedure(Optional void = Empty)
    MsgBox "このプロシージャはマクロの実行メニューには表示とされません。"
End Sub

Public Sub Main()
    Call HiddenProcedure
End Sub

HiddenProcedureプロシージャは引数があるので単体では実行できなくなり、Mainからだと普通に呼び出せる。
また、Publicなので他プロシージャからの呼び出しも可能。

以前はdummyという名称の仮引数を作って実際に1とか0とかの実引数を渡していたんだけど、単にOptionalで良いことに気付いたので今回記事にした。
今回のvoidはC言語のキーワードから英単語を拝借した。

Cでは引数をとらない場合にキーワードvoidを指定する。

#include <stdio.h>
int main(void){
    printf("Hello, World!\n");
    return 0;
}

初期値は何でも良いので最初は0とか""とかにしていたけれど、VBAのキーワードEmptyが最も意図をよく表していることを発見して置き換えた。

ちなみにSubの代わりにFunctionを用いれば引数をつけなくてもマクロメニューから隠すことはできる。
しかしFunctionの主目的は値を返すことだから、私の場合、値を返さない純粋な手続きの場合はSubで作る。

追記

Twitterでimihitoさんに情報もらったので追記。
モジュールの先頭に「Option Private Module」を記述するとそのモジュールのマクロは見えなくなるとのこと。
実際やってみると、マクロメニューからは隠れ、他モジュールからの呼び出しは成功した。素晴らしい!

Static変数を利用してステータスバーに文字をスクロールさせる。

今回はExcelのステータスバーに電光掲示板のように文字をスクロールさせるマクロを紹介。
もともと作りたかった案件とは違うが、そちらが失敗して副産物として単体で何か使えそうな気がしたので簡単にメモ。

矢印の方向に文字が流れる。
f:id:t-hom:20180527132653p:plain

コード

※Mainプロシージャを起動すると無限ループになるので止め方をご存知の方のみ実行してください。

#If VBA7 Then
    Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal ms As LongPtr)
#Else
    Private Declare Sub Sleep Lib "kernel32" (ByVal ms As Long)
#End If

Sub Main()
    Do
        Application.StatusBar = RotateString("ただいまマクロを実行しています。しばらくお待ちください。", " ")
        DoEvents
        Sleep 100
    Loop
End Sub

Private Function RotateString(cap As String, pad As String)
    Dim paddedString As String
    paddedString = String(Len(cap), pad) & cap & String(Len(cap), pad)
    Static i As Long
    If Len(cap) * 2 > i Then
        i = i + 1
    Else
        i = 1
    End If
    RotateString = Mid(paddedString, i, Len(cap))
End Function

解説

RotateString関数のStatic変数 i がポイント。
Staticで宣言した変数はプロシージャが終了しても値が保持されるので、このように呼び出すたびに違う値を返す関数を作るのに便利だ。

Staticを使いこなすコツは、「明示的に初期化しない」こと。

たとえば以下の箇所は、宣言された後、値が代入される前にIf文の判定でiを参照している。

    Static i As Long
    If Len(cap) * 2 > i Then
        i = i + 1
    Else
        i = 1
    End If

Long型で宣言したなら初期値は0なので、初回の呼び出し時にiは0である。
2度目の呼び出し時には、iは前回の呼び出しの終了時点の値をキープしている。

もし以下のように0で初期化する処理を挟んでしまうと何度呼び出しても0が入ってしまい、Staticにした意味がなくなる。

    Static i As Long
    i = 0
    If Len(cap) * 2 > i Then
        i = i + 1
    Else
        i = 1
    End If

明示的に代入する前に参照させるのがStaticを使いこなすコツだ。
これは慣れるまでなんとなく気持ち悪いかもしれないが、そういうものだと割り切るしかない。

関数のおおまかな処理

(1) まず、引き渡された文字列に、それと同じ長さの詰め物を両サイドに敷き詰める。
詰め物 & "サンプル" & 詰め物
      ↓こうなる
"□□□□サンプル□□□□"

(2) iが文字列の長さの2倍未満なら、iを増分させる。
初回は0なので、iが1になる。

(3) iの値の位置から元の文字列と同じ長さを切り出す。
"□□□□"となる。

2回目の実行ではiが増えているので、"□□□サ"、3回目は"□□サン"という風に、切り出す位置を変えているだけ。

以上

プログラムコード中に値(あたい)は登場しない

VBAに限らず全てのプログラミング言語で言えることだが、コード中に値(あたい)が直接登場することは無い。
最近、「値とは何か」ということを掘り下げて考えることがあり、ようやくこの結論に行きついた。

たとえば、数字の「1」。これは一般的に値だと理解されるが、表面的には単なる文字にすぎない。
「ひとつ」という概念を表した文字だ。

「1」という値が現れるのは、その文字を見た人の頭の中である。
値は確かに存在するが、書かれた文字は単なる記号でしかない。人が理解することで、初めて値が現れる。

プログラミングにおいても同じことが言える。
コンピューターは物事の概念を理解しないが、しいていえばコンピューターにとっての値とは、CPUが直接処理可能な「電気信号」である。

ただしブログ上に電気信号を書き表すというわけにもいかないので、便宜上スイッチのON・OFFを1と0で表した二進数で説明する。

たとえば次のコードを考えてみる。

Debug.Print 2 + 3

仮にCPUが32ビットだとすると、
「2」は「00000000000000000000000000000010」に変換され、
「3」は「00000000000000000000000000000011」に変換され、
CPUで加算されて「00000000000000000000000000000101」になり、
「5」という文字に変換されて出力される。

プログラム中に現れる「2」や「3」は値ではなく式である。
式と聞くと数式を思い浮かべる方が多いと思うが、本来の意味は「何かの事物や構造を記号で書き表したもの」を指す。
たとえば「2」は2という概念を表した式だ。

このように特定の値を直接的に指す式のことを、リテラルと呼ぶ。
リテラルの和訳は「即値」といい、これまで私はリテラルを式でもあり、値でもあると考えていた。

しかし、リテラルは単に式であり、値ではない。
つまりコード中に現れるこれまで「値」と呼ばれてきたものは、すべて「式」だ。

この視点でFizzBuzzを見てみよう。

Sub FizzBuzz()
    Dim i As Integer
    For i = 1 To 100
        If i Mod 15 = 0 Then
            Debug.Print "FizzBuzz"
        ElseIf i Mod 3 = 0 Then
            Debug.Print "Fizz"
        ElseIf i Mod 5 = 0 Then
            Debug.Print "Buzz"
        Else
            Debug.Print i
        End If
    Next
End Sub

文法的には、こういうことになる。

Sub 識別子()
    Dim 変数 As 型名
    For 変数 =ToIfThen
            Debug.PrintElseIfThen
            Debug.PrintElseIfThen
            Debug.PrintElse
            Debug.PrintEnd If
    Next
End Sub

「式」という表記が、すべての例外を吸収してくれる。

ザッツ、シンプル!ビューティフォー!

裏付け

以下はVBAの言語仕様書
VBA Language Specification(英語)

この76~77ページにFor文の定義がある。

For文の定義

for-statement = simple-for-statement / explicit-for-statement
simple-for-statement = for-clause EOS statement-block "Next"
explicit-for-statement = for-clause EOS statement-block ("Next" / (nested-for-statement ",")) bound-variable-expression
nested-for-statement = explicit-for-statement / explicit-for-each-statement
for-clause = "For" bound-variable-expression "=" start-value "To" end-value [stepclause]
start-value = expression
end-value = expression
step-clause = Step" step-increment
step-increment = expression

For文の定義の訳

for-statement = simple-for-statement / explicit-for-statement
for文 とは シンプルなfor文 または 明示的なfor文 である。

simple-for-statement = for-clause EOS statement-block "Next"
シンプルなfor文 は for節 ステートメントの終わり ステートメントブロック Next で構成される。

explicit-for-statement = for-clause EOS statement-block ("Next" / (nested-for-statement ",")) bound-variable-expression
明示的なfor文 は for節 ステートメントの終わり ステートメントブロック (Next または (ネストされたfor文とカンマ)) 束縛変数式 で構成される。

nested-for-statement = explicit-for-statement / explicit-for-each-statement
ネストされたfor文 とは 明示的なfor文 または 明示的なfor-each文である。

for-clause = "For" bound-variable-expression "=" start-value "To" end-value [stepclause]
for節 は For 束縛変数式 = 開始値 To 終了値 [step節] で構成される。

start-value = expression
始値 は 式である。

end-value = expression
終了値 は 式である。

step-clause = Step" step-increment
step節は Step step増分 で構成される。

step-increment = expression
step増分 は 式である。

参考

VBAの言語仕様書の定義はABNFというメタ言語で記述されている。基本情報技術者試験で出題されるBNFを拡張したものだ。
ABNFの定義はRFC4234 http://www.rfc-editor.org/rfc/rfc4234.txt を参照(英語)

以上。

学習において、疑問を抱えたまま次のページをめくる勇気

何かを学んでいると、まだ説明されていない箇所に対して色々と疑問が湧いてくることがある。たしかに、分からないというモヤモヤした気分のまま次に進むのはなんとなく気持ち悪いものだ。

一般的に学習において疑問を持ち、自主的に調べるのは良いこととされるけれど、最近思うのはひょっとすると一概にそうとも言えないんじゃないかということ。

私がまずいと思うのは「分からない。嫌だ。」という疑問に対する不寛容さである。

例えば自分で調べてみたものの疑問が疑問を呼んでさらに混乱し、結果的に一歩も動けなくなるという事態に陥る危険性もある。特に独学の場合は適切なアドバイスが受けられないため、疑問に対する不寛容さは挫折につながりやすいのではないか。

以前、物事は全てがつながった時に初めて完全に理解できるという趣旨の記事を書いた。
thom.hateblo.jp

物事を理解するにはそれ相応のステップが必要であり、地道に学んでいく努力も必要である。疑問があって先に進めない、躓いてしまうというのは、ある意味ではそうしたコツコツとした地道な努力を嫌い「すぐに結果が欲しい」という甘えなのかもしれない。

とりあえず、疑問は疑問のまま受け入れて、今は分からないけれど次に進んでみようという勇気も必要なんじゃないかと思う。

ただこの記事を鵜呑みにして何も自分で調べないという姿勢もそれはそれで学習においてマイナスだと思うので、そのあたりはバランスが大事。

高品質なコードを短時間で編み出すには

プログラムのコーディングで一番時間を消費するのは「思考」と「試行錯誤」である。

コーディングスピードが落ちることを嫌って極端に短い変数名をつけたり、一つのプロシージャで一気に書ききったりするとコードが把握しにくくなり、「思考」に時間をとられ、うまくいかない時の「試行錯誤」に時間をとられ、結果的に相当に時間をロスしてしまう。

私が普段のコーディングで大層な変数名を付けたり、プロシージャを分割したり、クラスモジュールを使用したりするのは、コードを把握しやすくして頭の負担を減らし、思考と試行錯誤に費やす時間を大幅にカットするためだ。

わざわざクラスモジュールなどという大層なものを持ち出してきやがってという批判は時折目につくのだけれど、これは「美しいコード第一主義」などではなく、単にそれを使ったほうが早く作れて、メンテナブルで、ついでに美しいからだ。

高品質なコードを短時間で編み出すには、急がば回れである。

VBAの知識の依存関係の整理を試みる

※今回の記事は基本的に自分用の備忘録です。

VBAの学習における知識の依存関係の整理を試みた。
試みたというのは、お世辞にも成功したとは言えない為である。

書籍でプログラミングを学ぶ際は、ふつう1ページずつ読み進めていく。
これに対して、実際の知識体系は必ずしもシーケンシャルに整理できるわけではない。

たとえばオブジェクトを正しく理解しようと思ったら、中身がどうなっているのか知る必要がある。つまりオブジェクトの知識はクラスの知識に依存すると考える。
f:id:t-hom:20180504194038p:plain

クラスはモジュールなので、前提としてモジュールの知識が必要になる。また、プロパティ・メソッドの理解には前提として変数とプロシージャの理解が必要になる。
f:id:t-hom:20180504194349p:plain

そんな風に整理していった結果。。こうなった。
f:id:t-hom:20180504193300p:plain

知識の依存関係をある程度表すことができたかなとは思うけれど、見て分かりやすい図ではないし、実際のところはこれより更に複雑だと思う。相互依存もあるはず。

VBAに限らず、知識の依存関係というのは有向グラフになっていて、その学習順序は巡回セールスマン問題を抱えているのではないかという着想を得た。

きちんとグラフを定義して、その最適経路を求めれば、最も効率の良い説明順序が求められるだろうか。

理解度の整理(追記)

ただ実際のところクラスを知らなくてもオブジェクトは使えてしまうんだよなぁ…と考えてたところ、「そうか、これは理解度の問題だ」と気づいたので追記。
理解度を図るバロメーターについてもざっくりと整理してみた。

たとえば以下のように知識Bが知識Aに依存していたとする。
f:id:t-hom:20180504201657p:plain

知識Bを単体で学習したとき、知識Bの理解度が1、知識Aを学んだ場合、知識Bの理解度は2になると考える。
逆に、知識Bを学んだことで、より知識Aの理解が進むということも考えられる。何等かの関係がある2つの知識がその理解において完全に一方通行であるとは考えにくい。※ただし矢印を両向きにすると分かりづらくなるのでグラフ上は、より強い依存関係のある方を採用する。

つまり知識Aを単体で学んだ場合に比べ、知識Bを合わせて学んだ場合、知識Aの理解度も2になると考える。

サンプルとして知識A~Dの依存グラフとその最大理解度を描いてみた。
f:id:t-hom:20180504203107p:plain

その知識自体を学習したときに1、関連する項目を学習したときに項目ごとに1レベル上がるという整理。
厳密に言えばその知識自体の学習と、依存元の学習と、依存先の学習で、それぞれその知識に入ってくる経験値って違うんだろうな。
でもシンプルに整理したほうが活用はし易いのでそれは置いておこう。

なんでこんなことを考えているのか(追記2)

なんでこんなことを考えているかというと、発端としては私がブログ記事を書く際に、「この記事を読むには知識Aと知識Bが必要です。」という風に、依存関係をライブラリの参照設定のように書けたら、わざわざ毎回同じような説明を書かなくても済むなぁと思ったから。

↓この先、妄想の世界なので注意。

要は知識のモジュール化である。
知識のモジュールはフォーマットだけ決めておいて、誰でも代替モジュールを作れるようにする。決められたヘッダの付いたパワポ1枚とかでも良いし、ブログのようなメディアの記事1本を1モジュールとしても良い。

学習する人は、ジェネリック医薬品のように、自由に代替モジュールを選んで学習することができる。変数はAさんのモジュールで学習し、関数はBさんのモジュールで学習する。オブジェクトは複雑なのでAさんのモジュールをメインにしつつもBさん、Cさんのモジュールで知識を補完するなど。

既刊のVBA書籍を買ってきてバラバラに切り刻んでモジュール化する手もあるか。。でも著作権がある以上オープンな場に出せないから自由に参照させるってわけにもいかないし、書籍の場合は前後で依存関係を考えて編纂されてるので切り出した単品をモジュールとするのは無理があるか。。

ということで今のところアイデアを温めているだけ。何も作る予定はない。

以上、そういう学習方法があるとおもしろそうだなぁという妄想でした。

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