読者です 読者をやめる 読者になる 読者になる

t-hom’s diary

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

VBA XlRgbColor定数をシート上に色相順、明るさ順で出力する

VBA

「あなたの好きな色は何色ですか?」と聞かれたら

赤、青、緑、黄、黒、白、紫…

まあ、普通はこんな感じで答えると思う。

ここで、狐色、若草色、深紅、枯草色といったちょっとこだわった感じの名前を返してくると、「おっ、情緒的でいいな」と思う。ラベンダー、アイボリー、アクアマリンなんて答えも素敵だ。

色にはそれぞれ素敵な名前がついている。その名前はモノを連想させたり、イメージを膨らませる。

さすがに団十郎茶、勿忘草色、空五倍子色、ラベンダーブラッシュ、コーンフラワーブルーなんて言われると「こいつ、ちょっとひねくれてるな」って感じがするけど。


団十郎て誰やねん。

団十郎茶(だんじゅうろうちゃ)とは、江戸時代の歌舞伎役者「市川團十郎」が代々用い…

団十郎茶(だんじゅうろうちゃ)とは?:日本の色・和色

知らんがな。
(いや、もちろん色を扱う特殊な職業の方なら良いと思う。)


さて、今回はVBAの色の話。
VBAではRGB関数を使って簡単に色を作成できるが、面倒くさくてもあえて名前で呼ぶというのは情緒があって良い。

それで、以前こんな記事を書いた。
thom.hateblo.jp

しかし、
案の定、面倒くさい。

何がって、探すのが。
まずもって色名をそんなに知らないうえ、シートに書き出しても英語名でアルファベット順なので。

f:id:t-hom:20170218054058p:plain
こっから選べって言われてもなぁ。。

普通、人間が色を探すときって、赤系とか青系といった色相(しきそう)や明るい、暗いといった輝度(きど)で探す。
※感覚的には濃い・薄いという用語のほうがシックリくるけど、あれは絵の具の話なので、ディスプレイ上は輝度(きど)

彩度も重要な要素だけど私は最初から彩度を基準に探すってことはしないので、一般的にもそうだと思う。

それで今回は、XlRgbColor定数を色相順、輝度順で出力してみたいと思う。

Win32APIにRGB値から色相、輝度、彩度を求めるColorRGBToHLS関数があるので、そちらを利用する。
※HLSはHue(色相)、Luminance(輝度)、Saturation(彩度)の意味

参考:ColorRGBToHLS function (Windows)

まずシート上に色相、輝度、彩度を入力する箇所を設ける。
f:id:t-hom:20170218060449p:plain

次に標準モジュールに以下を貼り付ける。

Public Declare Sub ColorRGBToHLS Lib "Shlwapi.dll" (ByVal clrRGB As Long, _
     pwHue As Integer, pwLuminance As Integer, pwSaturation As Integer)

次にシートモジュールに以下を張り付ける。

Enum 列
    XlRgbColor定数名 = 1
    定数値
    日本語名
    色相
    輝度
    彩度
End Enum

Sub HLS値取得()
    Dim H, L, S, i
    For i = 2 To 143    '←単発モノなのでハードコード
        Debug.Print Cells(i,.定数値).Value
        Call ColorRGBToHLS(Cells(i,.定数値).Value, H, L, S)
        Cells(i,.色相) = H
        Cells(i,.輝度) = L
        Cells(i,.彩度) = S
    Next
End Sub

そしてHLS値取得を実行すると、色相、輝度、彩度が入力されるので、あとはオートフィルタで並び替えるだけ。

色相順に並べてみた。
f:id:t-hom:20170218061106p:plain

彩度の昇順で並び替えたあと、輝度の昇順で並び替えてみた。
f:id:t-hom:20170218061236p:plain

無彩色でフィルタリングしてみた。
f:id:t-hom:20170218061439p:plain

緑系だけフィルタリングしたのち、輝度・彩度の順に並び替えた。
f:id:t-hom:20170218061903p:plain

これで色がずいぶん探しやすくなった。

というわけで皆、色名も使ってあげて。

VBA オートシェイプで作った桜のアイコンでユーザーフォームを可愛くデコレーションする

VBA ユーザーフォーム

普段からこのブログを読んでくれてる方は、今回のタイトルを見て「ついにthomもVBAのやり過ぎで頭がおかしくなってしまったか」と思われた方もいるかもしれない。

可愛くだなんて。30超えたオッサンが何言うとんねん。
さてさて、今回作ったのはこれ。

でん!
f:id:t-hom:20170212001539p:plain

きゃーカワイイー!

…まじめな話、最初からコレをねらった訳ではなく、こんなふうになってしまったのは偶然の産物である。

きっかけはイラストの重要性に気付いたこと

マクロの機能的にはアイコンなんて何の意味もないと思う方も居るかもしれないけど、ユーザーがそのマクロの使用感に満足を覚えるかどうかという点でデザインは超重要な要素だ。

繰り返す。

デザインは超重要な要素だ。

さて、具体的にアイコンを付けようなどという発想に行き着いたきっかけはこちらのサイト。
ateitexe.com

アイコンの話が出てくるわけではないのだけど、説明にいろいろと可愛らしいイラストが添えてあり、読んでいて楽しい。
これまで私は説明のための図解をすることはあっても、ユーザーを楽しませるためという観点でイラストを書いたことは無かった。

そうだ!イラストだ!

と思い立ったとき、たまたまユーザーフォームのデザインを考えていたのでその2つが結びついた感じ。
それで、「よし、アイコンを付けよう。」となった。

なんでまた桜なのかというと、アイコンを作るにあたってとりあえず適当な画像が手元にないので、オートシェイプで済ませようと書き始めたところ、昼間ジュンク堂でたまたま立ち読みした本に、オートシェイプで桜の花びらを描く方法が載っていたので。

絵心がなくてもできる Wordで素敵なお絵描き

絵心がなくてもできる Wordで素敵なお絵描き

花びらの特徴的な形は一見難しそうに見えるけど、実はハートを挿入して頂点を上にずらすだけのもの。超簡単!たーのしー!
f:id:t-hom:20170212013144p:plain

それで桜を描いてそれに合うようにラベルでヘッダーに色をつけて、それに合う色は、、とやってるうちに気づいたら完全に女子だった。不覚。。

実際にアイコンを付ける方法

さて、アイコンを付けようと思ったはいいが、ひとつ困ったことがある。

基本的にはUserFormにImageコントロールを置いてLoadPictureする感じなんだけど、その画像は外部から持ってこなきゃいけない。するとExcelファイル単体で動作しなくなるので配布に難がある。

そこで、隠しシートに張り付けた画像を読み込むことを思いついた。ただ直接シートからLoadPictureはできないらしく、どうしても一回保存する必要がある。

Excelには幸い環境変数を取得するEnviron関数があるので、Temporaryフォルダを取得してそこに保存するようにしよう。

ここでまた技術的な課題があって、シェイプって直接保存できないんだ。
まぁ、いろいろ調べてたら、ChartObject(つまりグラフ)はExport命令でビットマップに書き出せるらしい。

何もデータを指定せずにグラフを挿入すると空の枠だけできるので、
f:id:t-hom:20170212013841p:plain

縦横比をそろえてからシェイプを張り付ける。
f:id:t-hom:20170212014142p:plain

グラフは標準で枠のサイズにあわせて中身が伸び縮みするので、最初に比率を合わせておかないとぐちゃっとなる。

それからグラフツールのレイアウトタブからグラフ名を「SakuraIcon」としておこう。
f:id:t-hom:20170212014704p:plain

最後にシート名を「Images」として準備完了。
最終的にシートは非表示にすれば良いけど、検証段階では表示させておく。

次にフォームをデザインしていく。

基本形はこちらで紹介したので説明を割愛。
thom.hateblo.jp

今回重要なのはImageコントロール

まずはフォームのアイコン表示位置に配置する。
今回説明用にイメージコントロールの背景色は黒にしたが、どのみち画像が入るので何色でも良い。
f:id:t-hom:20170212015438p:plain

それから今回イメージコントロールに設定するプロパティは以下の2つ。
f:id:t-hom:20170212015923p:plain

アイコンに枠線が入らないようにBorderStyleを0-fmBorderStyleNoneに設定し、画像が切れずにイメージコントロールに収まるようにPictureStyleModeを1-fmPictureStyleModeStretchに設定しておく。

最後にフォームにコードを書く。

Private Sub UserForm_Initialize()
    Dim chartObj As ChartObject
    Set chartObj = Sheets("Images").ChartObjects("SakuraIcon")
    chartObj.ShapeRange.Fill.ForeColor.RGB = Label1.BackColor
    chartObj.Chart.Export Environ("temp") & "\SakuraIcon.bmp"
    Image1.Picture = LoadPicture(Environ("temp") & "\SakuraIcon.bmp")
End Sub

まずImagesシートからSakuraIconグラフオブジェクトをchartObj変数に代入し、背景色をラベル1(今回はヘッダーのラベルがlabel1)と同じに設定している。こうするとヘッダーラベルの色を変更しても自動で画像の背景色が合うので桜の背景が透過的に見える。

次にグラフオブジェクトをテンポラリーフォルダーに保存し、それを読み込んでいるだけ。

bmpファイルを作る際にはグラフのサイズで出力されるため、実際に利用する際はImagesシートに置くグラフはアイコンと同じくらいのサイズまで縮小しておくと良い。ディスク容量の消費が減るというメリットもあるが、サイズが小さいほうが読み込み書き込み共に高速に行える。

Officeに用意されたImageMSOを利用する手も

さて、アイコンを利用したいだけなら自分で作らなくともImageMSOを利用する手もある。

コードを以下のように書き換えると、

Private Sub UserForm_Initialize()
    Image1.Picture = Application.CommandBars.GetImageMso("HappyFace", 80, 80)
End Sub

こんな感じで殺センセスマイリーが表示される。
f:id:t-hom:20170212022027p:plain

参考にしたのは以下のサイト。
www.ka-net.org

紹介されているのはボタンに表示させるコードだけどオブジェクトブラウザで調べたところボタンのPictureとイメージコントロールのPictureはどちらも同じ型だったのでそのまま流用できた。

ただこの方法だとOffice2010とOffice2013では見え方が違ってくる。
それによく見ると四隅に白色の背景が見える。つまりImageコントロールに読み込むと透過処理が出来ないってことかな。
手軽にアイコンを利用できる点はよさげなので、適宜活用していきたい。

プログラミングの入門に必要なのは「おお、すげー!動いた!」という体験。小難しいことは後回し。

VBAについての考え・意見 プログラミング

こちら、最近たまたま昼休みに書店に立ち寄る機会があり、ふと手に取った書籍。

アイディアを実現させる最高のツール プログラミングをはじめよう

アイディアを実現させる最高のツール プログラミングをはじめよう

ターゲット読者はプログラミングに興味はあるけどやったことがない方。具体的なコードの話はほとんど出てこなくて、プログラミングの楽しさ・面白さを語った本。

自分はまぁターゲットからは外れてるんだけれど、プログラミングが「つまらなさそう」な理由という項目が目につき、パラパラ読んでると面白そうなのでそのまま買って帰った。

ちょうど先ほど読み終えたのだが、この本はとても大事なことを思い出させてくれた。
それは、自分がどうやってプログラミングに入門したのかということ。私も昔はコピペプログラマーだった。プログラマーと呼ぶのもおこがましい。。コピパー?

最近では自分が入門者だった頃の気持ちも忘れかけており、すっかり上級者ヅラをして「基礎がいかに重要か」なんてことをドヤ顔で吹聴しまわってるんだけど、入門者にとってみたら基礎なんてどうでもよくて、とにかく早く、動くもの・面白いものが作りたいんだよね。

(なんだかよくわからんけど)できた!動いた!すげー!」

やっぱ、ここからだろう。入門は。

変数宣言なんて後回し。変数名も適当でいい。力技?どんとこい!
入門以前に、小難しい説明で挫折してしまったら意味ないもんな。

ひょっとして将来その人は素晴らしいコードを書くかもしれないのに、そんなつまらないことで芽を摘んでしまったら勿体ない。

そんなことは入門してから考えたら良い。
おぼろげながらプログラミングというものが分かってきたら、そのとき改めて考えなおそう。

ただしこれだけは伝えておく必要がある。業務でミスできないコードを書くときは、きちんと変数宣言して、型にも気を配って、意味の分かるちゃんとした変数名をつけよう。

あ、あと今回書いたのは「入門以前~入門者」であって、「初心者」ではない。
上手か下手かは別として簡単なプログラムを自力で作れるようになったら、それはもう入門者ではない。

入門者を卒業したら、改めて基礎から学びなおすべし。

ちなみに、私もプログラミングの魅力を語った記事を書いてるので、これから初めてみようかなって方は是非読んでみて。
thom.hateblo.jp

VBA クラスモジュールを使ってセル内の文字を簡単に色づけ

VBA クラスモジュール活用

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

VBAでセル内のテキストの個別の文字に色をつけるのはわりに面倒くさい。

たとえばこんな風に、着色したいとしよう。
f:id:t-hom:20170207201819p:plain

上のテキストを実現するには、以下のコードを書けば良い。

Sub hoge()
    Sheet1.Range("A1").Value = "Red, Green, Blue"
    Sheet1.Range("A1").Font.Color = vbBlack
    Sheet1.Range("A1").Characters(1, 3).Font.Color = rgbRed
    Sheet1.Range("A1").Characters(6, 5).Font.Color = rgbGreen
    Sheet1.Range("A1").Characters(13, 4).Font.Color = rgbBlue
End Sub

ここで面倒なのが、Charactorsプロパティに指定する文字数。
「何文字目から、何文字を」という指定をしないといけないけど、頭がこんがらがる。

これ、もう少しなんとかならんかな。。

というわけで、クラスモジュールを使って少し楽にカラフルな文字列を作れるようにしてみた。

作り方

クラスモジュールを挿入し、モジュール名を「ColorfulStringObject」と名づける。
コードはこちら。

Private Type ColorText
    TextPart As String
    ColorPart As XlRgbColor
End Type
Private colorTextArray() As ColorText

Private Sub Class_Initialize()
    ReDim colorTextArray(0)
End Sub

Sub AddText(txt As String, Optional col As XlRgbColor = rgbBlack)
    colorTextArray(UBound(colorTextArray)).ColorPart = col
    colorTextArray(UBound(colorTextArray)).TextPart = txt
    ReDim Preserve colorTextArray(UBound(colorTextArray) + 1)
End Sub

Function GetText()
    Dim ret As String
    Dim i As Long
    For i = 0 To UBound(colorTextArray) - 1
        ret = ret & colorTextArray(i).TextPart
    Next
    GetText = ret
End Function

Sub WriteToCell(r As Range)
    r.Value = GetText
    Dim location As Long: location = 1
    For i = 0 To UBound(colorTextArray) - 1
        r.Characters(location, Len(colorTextArray(i).TextPart)) _
            .Font.Color = colorTextArray(i).ColorPart
        location = location + Len(colorTextArray(i).TextPart)
    Next
End Sub

準備はこれだけ。
このクラスを作るにあたって工夫した点として、クラス内部にPrivateのユーザー定義型「ColorText」を宣言し、それを配列に入れているところ。通常クラス内にPublicなユーザー定義型は宣言できないが、Privateなら問題ない。

ユーザー定義型はコレクションに追加できないのが残念だが、コレクションを使いたいだけのために外部にオブジェクトを作るのも面倒なので、ワンモジュールで完結するようにユーザー定義型の配列にした。

使い方

先ほどのRed, Green, Blueを表示させるには、標準モジュール等に以下のように書く。

Sub ColorRGB()
    Dim colorfulString As ColorfulStringObject
    Set colorfulString = New ColorfulStringObject
    colorfulString.AddText "Red", rgbRed
    colorfulString.AddText ", "
    colorfulString.AddText "Green", rgbGreen
    colorfulString.AddText ", "
    colorfulString.AddText "Blue", rgbBlue
    colorfulString.WriteToCell Sheet1.Range("a1")
End Sub

AddTextメソッドに文字列と色を渡すと内部でColorText型の配列に保管される。
つまり、文字列全体を書いてから位置指定で着色するのではなく、最初からこの文字を赤で、この文字を緑でという風に追加していくのだ。
最後にWriteToCellメソッドにRangeを渡すと、そのRangeに実際にカラーで書き込まれる仕組み。

注意点として、VBAではRangeのValueプロパティを触ると色がリセットされてしまう。
そのためAddTextでは直接セルに書かず、最後にWriteToCallを呼ぶ仕様とした。

今回引数としてxlRgbColor列挙型を使用してみた。これは過去に以下の記事で紹介したもの。
thom.hateblo.jp

プロシージャの引数として列挙型を指定してやると、呼び出す側で入力ヒントが出るので便利。
f:id:t-hom:20170207203153p:plain

列挙型の実態はLongなのでRGB関数で作成した色や、vbのcolor定数(vbRedなど)も指定できる。

以下、別のサンプル。

■ランダムな色でHello, VBA!!を表示する。
f:id:t-hom:20170207203352p:plain

Sub RandomColorHelloVBA()
    Const MESSAGE = "Hello, VBA!!"
    Dim colorfulString As ColorfulStringObject
    Set colorfulString = New ColorfulStringObject
    Dim i As Long
    For i = 1 To Len(MESSAGE)
        Dim r As Byte, g As Byte, b As Byte
        r = WorksheetFunction.RandBetween(0, 255)
        g = WorksheetFunction.RandBetween(0, 255)
        b = WorksheetFunction.RandBetween(0, 255)
        colorfulString.AddText Mid(MESSAGE, i, 1), RGB(r, g, b)
    Next
    colorfulString.WriteToCell Sheet1.Range("a2")
End Sub

SQLの色分け
f:id:t-hom:20170207203443p:plain

Sub ColorSQL()
    Dim colorfulString As ColorfulStringObject
    Set colorfulString = New ColorfulStringObject
    colorfulString.AddText "select", rgbBlue
    colorfulString.AddText " * "
    colorfulString.AddText "from", rgbBlue
    colorfulString.AddText " people_table "
    colorfulString.AddText "where", rgbBlue
    colorfulString.AddText " age "
    colorfulString.AddText ">=", rgbMaroon
    colorfulString.AddText " 20"
    colorfulString.WriteToCell Sheet1.Range("a3")
End Sub

工夫すればRangeへの出力だけでなくHTML出力なんかも作れるかと思う。

以上

VBAでテンプレートを元にHTMLコードを自動生成する

VBA

今回はVBAを利用してHTMLを生成するテクニックを紹介

題材は先日紹介した、参照設定とCreateObjectの対応リスト - You.Activate
thom.hateblo.jp

このページはご覧いただくとわかるように、項目名は同じで内容だけ異なるものが複数回出てくる。
f:id:t-hom:20170206031011p:plain

ひとつのオブジェクトの紹介は以下のHTMLで構成されている。

<h3>ファイルシステムオブジェクト</h3>
<dl class="ProgIDList">
<dt>説明</dt><dd>ファイル・フォルダの生成・移動・削除やテキストファイルの生成、読み込みなどに使用</dd>
<dt class="fl">ProgID</dt><dd>Scripting.FileSystemObject</dd>
<dt class="fl">参照設定名</dt><dd>Microsoft Scripting Runtime</dd>
<dt class="fl">ライブラリ名</dt><dd>Scripting</dd>
<dt class="fl">オブジェクト名</dt><dd>FileSystemObject</dd>
<dt>アーリーバインディング書式</dt>
<dd class="code"><pre class="brush:vb toolbar:false">
    Dim fso As Scripting.FileSystemObject
    Set fso = New Scripting.FileSystemObject
</pre></dd>

<dt>レイトバインディング書式</dt>
<dd class="code"><pre class="brush:vb toolbar:false">
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
</pre></dd>

これをバカ正直にコピー&ペーストで編集しても良いのだけれど、いかんせん効率が悪い。
ということで、これもテンプレートを作ってVBAでやってしまおう。

※本来はDBにデータを格納してPHPで動的にページを生成するのがセオリーだが、たかだか1ページのために仕組みを作るのも面倒だしこれくらいなら得意のVBAで良いかなと。

まず、シートはこんな感じ。
f:id:t-hom:20170206032220p:plain

薄い黄色で塗ってあるのがテンプレートで可変項目は$$$としている。
その右がデータ部で、1列がひとつのオブジェクト紹介。右へ右へとどんどんデータが続くイメージ。

ふつうはデータ方向は下なんだけど、HTMLのテンプレートは縦に書きたかったのでやむなく。
※もっとデータが多ければ、可変項目を「$$1$$」などとナンバリングしたテンプレートを別シートに作り、データは1行1レコードとする方法もある。

コードは以下の通り。シートモジュールに直接貼り付けて実行する。

Option Explicit
Sub HTMLOutput()
    Const MAX_ROW = 19
    Const START_COLUMN = 3
    Const END_COLUMN = 5
    Dim template: template = Range("B1:B" & MAX_ROW).Value
    
    Dim j, i
    For j = START_COLUMN To END_COLUMN
        Dim data: data = Range(Cells(1, j), Cells(MAX_ROW, j)).Value
        For i = 1 To MAX_ROW
            Debug.Print Replace(template(i, 1), "$$$", data(i, 1))
        Next
    Next
End Sub

実行するとイミディエイトウインドウにHTMLコードが生成されるので、あとは切り取ってエディタに貼り付ければ完成。
f:id:t-hom:20170206033502p:plain

最大行、開始列、終了列はハードコーディングしてるので使用時は定数を変更する必要あり。自動取得も簡単だけれど、今回は使い捨てマクロなのでそこは適当に。。

やってることは単純で、テンプレート部とデータ部をそれぞれ別の二次元配列に入れ、あとは1行ずつ$$$をリプレースしながらイミディエイトウインドウに出力している。

ちなみにイミディエイトウインドウは最大200行までしか出力できないので、一気に大量のデータをさばきたいときは変数に入れて最後にクリップボードに送るか、テキストファイルとして出力するなどの工夫が必要。まあ本格的にデータ扱うなら、PHP等で仕組み化した方が良いと思うが。

上記マクロで使っているセル範囲を配列に転記するテクニックはこちらに詳しく書いた。
thom.hateblo.jp

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に無い機能を前提に書かれている箇所もあるのですべて参考にできるわけではない。

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

あ、あとインストラクターのネタ帳の伊藤 潔人さんがちょうど先ほどTwitterでタイムリーに以下の記事を紹介されていたのでこれも参考に追記。
www.publickey1.jp

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

VBA VBAの研究・考察

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

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