VBA（Visual Basic for Applications）はMicrosoft Offce、特にExcelでマクロを作成するためのプログラミング言語です。世の中にはマクロ職人と呼ばれる人達がいて、日常業務をチョコっと自動化するためにVBAを駆使したマクロを作ります。そういう意味で、VBAは余技プログラマー御用達の言語と言えるかもしれません。しかしながら、駄待ち狐はVBAにあまり興味がなかったというか、「所詮、素人向けの簡易言語でしょ」と思っていました。この考えが180°変ったのは2015年、年表の丸印「パネル書式統一」を作成した時です。それまでの2つの丸印はVBScriptなので「7-2. VBScript」で説明します。
2013～14年、駄待ち狐は社内カンパニーで技術戦略を担当していました。実際のところは技術本部で開発中のものを見込み顧客に売込む仕事です。その一環で、某自動車メーカー向け展示会の事務局にも駆り出されました。2015年に部署を変った後も事務局は続けていて、そこで課題になったのが展示パネルのフォーマット統一です。駄待ち狐が若い頃には文字マス目の入った用紙で手書き原稿を作成し、ブース設営業者に発注するというスタイルでした。これが、PowerPoint（PPT）原稿になると、逆に困った問題が発生します。推敲で文字数を合せるという努力を放棄して、行数を増やしたりフォントを小さくしたりしてしまうのです。
その対策として、原稿作成者は入力ページに文字原稿だけを入力し、マクロを使って予め決められたフォントやレイアウトでパネル原版上にテキストボックスを配置するということを考えました。パネル原版はPPTで作成するので入力ページも同じファイルの別スライドとし、PPTにマクロを仕込みます。このPPTマクロも当然VBAですが、PPTマクロをひねくり回して分ったのは、VBAはファイル上の全ての要素をオブジェクトとして扱うことができるプロトタイプベースのオブジェクト指向言語だということです。パネル原版作成のマクロを公開するとパネルの書式が分ってしまうので、それを補完するマクロを紹介します。
コードの表示にはPrism.jsを使用しています。
Sub パネルのフォーマット設定を読込み()
Dim i As Integer
Dim pwdPath As String
Dim invBox As Shape
Dim xlsApp As Object
'パラメータを保存した非表示のテキストボックスを削除
With ActivePresentation.Slides(1)
For i = .Shapes.Count To 1 Step -1
With .Shapes(i)
If .Top >= 2500 Then
.Delete
End If
End With
Next i
End With
'パラメータが記載されたExcelファイルを開く
pwdPath = ActivePresentation.Path & "\"
Set xlsApp = CreateObject("excel.Application")
xlsApp.Visible = msoTrue
xlsApp.Workbooks.Open pwdPath & "パネルフォーマット設定.xlsx"
'パラメータを非表示のテキストボックスに保存
For i = 1 To 100
If xlsApp.Cells(i + 2, 5).value <> "" Then
With ActivePresentation
Set invBox = .Slides(1).Shapes.AddTextbox( _
Orientation:=msoTextOrientationHorizontal, _
Left:=100, _
Top:=2500, _
Width:=600, _
Height:=50)
With invBox
.Name = "Param_Box_" & i
.TextFrame.TextRange = xlsApp.Cells(i + 2, 5).value
.TextEffect.FontSize = 40
.Visible = msoFalse
End With
End With
End If
Next i
'Excelファイルを閉じる
xlsApp.Workbooks.Close
xlsApp.Quit
End Sub
プロトタイプベースではオブジェクト型の依存関係を示すツリー構造が予め定義されており、VBAではこのツリーに従って実在のオブジェクトを指定していきます。例えば8～16行目では、ActivePresentation（現在のPPTファイル）のSlides(1)（1枚目のスライド）のShapes（スライド上の全ての図形）のCount（個数）から始めて1になるまでiを減じていき、Shapes(i)（i番目の図形）のTop（上端位置）が2500px以上だったらDeleteします。With (親オブジェクト) ～ End Withは"."による結合が無闇に長くなるのを防ぐもので、Withブロック内の"."から始まるオブジェクトは親オブジェクト以下に続くものとなります。
この削除した図形（テキストボックス）は27～40行目で改めて書込みますが、スライドの表示域外であるTop:=2500に、不可視（.Visible = msoFalse）で、複数の図形が重なるように追加します。ここに書かれているのはパネル原版上にテキストボックスを配置する別のマクロが使用するパラメータで、原稿作成者には触って欲しくない情報です。このため特別な操作をしない限りこの情報を見ることはできず、誤って消してしまうこともないようにしてあるのです。さらに言うと、パネル原版上のテキストボックスはスライドマスターに書込みます。せっかく自動配置したものが自由に書換えれては意味がないからです。
一方、パネルフォーマットを変更する際には、パラメータを書換える必要があります。例示したマクロはこの書換えを行うためのものです。書換える際の元データは別のExcelファイルに書かれていますが、22行目でこのExcelファイルを開き、26行目のxlsApp.Cells().valueでExcel内のセルの値を参照しています。このように、自PPTファイル内の不可視図形であれ、他Excelファイル内のセルであれ、オブジェクトとして自由自在に操作できるのがVBAであり、決して「素人向けの簡易言語」ではなかったのです。
年表の「パネル書式統一」の右側「弁当注文取り纏め」については、作成した背景も含めて「VI. グループメンバーの弁当注文取り纏め」で紹介します。その右側の「住宅地図」はExcelのVBAマクロです。駄待ち狐は仕事をリタイアしてから自治会に関っており、そこで作成・管理しているものの一つに住宅地図があります。1軒1軒の名前に加えて自治会員かどうか、班の区分け、消火器・防犯灯の位置などが記載されています。かなり精緻に作られた地図なので、印刷されたものを見た時は「どうやって作ったの？」と思いました。
「Excelで」と聞いて驚いたのですが、「Excel方眼紙」教の方が作られたものでした。駄待ち狐はその信仰は邪教だと思っている派ですが、手間暇を考えると今更他の方法で作り直す気にもなれません。一方で、1軒1軒の名前が入った地図を公開あるいは全戸配布するのは個人情報保護が喧伝されている今時いかがなものかという意見がありました。そこで、関係者限定配布の名前入りと、一般配布用の名前なしを作るというところから駄待ち狐の仕事となったのですが、2種類の地図をそれぞれアップデートしていくというのは無駄な労力です。そこでボタン一つで名前を表示したり非表示にしたりできる以下のマクロを仕込みました。
Option Explicit
Dim totalRange As Range
Private Sub Auto_Open()
Set totalRange = unionRange()
End Sub
Sub 名前を非表示()
'処理中の表示更新を止めて高速化
Application.ScreenUpdating = False
totalRange.NumberFormatLocal = ";;;"
'表示更新を復活して最終結果を表示
Application.ScreenUpdating = True
End Sub
Sub 名前を表示()
Application.ScreenUpdating = False
totalRange.NumberFormatLocal = "@"
Application.ScreenUpdating = True
End Sub
Function unionRange() As Range
Dim nameRange As Range '名前が入るはずのセル(ブランクの場合もあり)
'1班南
Set nameRange = Range("AF340, AO340, AX340, BG340, BM340, BR340, BW340, CG340, CL340, CQ340, CV340, DA340, DF340")
'1班北
Set nameRange = Union(nameRange, Range("AE322, AQ322, BA322, BK322, BU322, CE322, CY322"))
'
'(中略)
'
'23班
Set nameRange = Union(nameRange, Range("P267, P272, P277, P282, P287, P292, P297, P302, P307, P312, P317, P322, P332, P337, P342"))
'25班
Set nameRange = Union(nameRange, Range("AN35, AN40, AN45, AN50, AN55, AN60, AN65, AN70, AN75, AN80, AX35, AX48, AX61, BO35"))
Set unionRange = nameRange
End Function
このマクロは名前が入ったセルのアドレスをunionRange()関数で合体してフォーマットを指定するだけです。デジタルデータで配布する時はPDFにするので、非表示のものが表示になることはありません。ExcelのVBAマクロとしてはいたってシンプルなものですが、自治会役員の方々は「手品のようだ」と驚いておられました。年表の右端にある「QRコード会員証作成」も自治会活動の一環で作成しました。詳細は「II. QRコードで本人確認する自治会会員証」で紹介しますが、こちらは少し骨のあるVBAです。
VBScriptは言語としてはVBAとほぼ同じです。違うのは使い方で、VBAはExcelファイルやPPTファイルに仕込むのに対し、VBScriptはバッチファイルのようにWindows上で独立のアプリとして実行可能です。もう一つの使い方として、JavaScriptと同じようにHTMLファイルに埋込んでwebサーバ上で動作させることも可能です。ただ、IIS（Windowsのwebサーバソフト）上でしか動かず、Apache上では動作しません。年表左端の丸印「個人帳票Excel出力」は後者の使い方ですが、垂直の破線でつながったPerlの「人事評価システム」の一部なので「IV. DBを使って人事評価から特許活用まで」の「4.3 Excelファイルへの出力」で紹介します。
次の丸印「17台PC一斉表示切替」は独立のアプリとして実行するVBScriptです。前述の「開発中のものを顧客に売込む」仕事では、社外の展示会に出展するだけでなく、自社を来訪した顧客に見せるための展示フロアも設けていました。ここは常設なので、パネルではなく大型ディスプレイを並べ、それぞれに接続されたパソコン（PC）でPPTスライドショーを表示します。展示しているのは17テーマなので、PCも17台あります。ここで問題になったのが、日本語と英語の切替えです。スライドは日本語版と英語版を作ってありますが、日本人と外国人が入替りで来る度に17台のPCを操作して表示を切替える必要があります。
これは面倒なので、1台のPCで表示を切替えれば他の16台も追従させたいという訳です。17台はLANで繋がっているので実現可能なのは明らかですが、いかに簡単に実現するかが駄待ち狐の腕の見せ所です。考えついた方法は、1台を親PCに設定して今表示されているのが日英どちらかを示すフラッグファイルを置きます。全てのPCに入っている切替え用バッチファイルの内どれか1つを走らせると、このフラッグファイルも書換えられます。全てのPCはOSのタスクスケジューラで1分ごとにChngLang.batを実行し、フラッグファイルが切替っていれば自分の表示も切替えます。こうすれば、1分以内に全てのPCの表示が切替ります。
スケジューラの設定やバッチファイルのコーディングは本項のスコープ外なので割愛しますが、それなりに苦労して上記のアルゴリズムは動くようになりました。しかし、ここで一つ大きな問題が残りました。スケジューラがChngLang.batを実行するたび、つまり1分おきにディスプレイがブリンクするのです。静止画が表示されている時に一瞬でも画面が瞬くのは非常に気になりますし、お客様向けの展示となればなおさらです。それを救ってくれたのが以下の2行のVBScriptです。スケジューラで直接バッチファイルを起動するのではなく、バックグラウンド処理を指定したChngLang.vbsを介すると、ブリンクは見事になくなりました。
Set ws = CreateObject("Wscript.Shell")
ws.run "cmd /c ChngLang.bat", vbhide
駄待ち狐が最初にPHPを使ったのは、年表左端の丸印「特許活用システム」をPerl+TSVからWAMP（Windows+Apache+MySQL+PHP）に移植した時です。さくらのレンタルサーバに載っているプライベートHPではサーバの設定をする必要はありませんが、仕事の余技の場合は会社のサーバそのものの設定もしなければなりません。移植前にはIISを使っていたので、ApacheもMySQLもphpMyAdminも初体験です。WAMPはLinuxベースのLAMPの派生なのでLinuxあるあるの「依存関係が満たされていません」エラーで何度もパッケージをインストールし直しました。その当時は「あるある」であるとも知らず五里霧中でした。
そんな苦労の末にWAMPをマスターして、PHPながら*.class.phpを駆使した大規模データベース「Patent Portfolio Manager」が完成しますが、これについては「IV. DBを使って人事評価から特許活用まで」で述べます。*.phpファイルはPerlの*.cgiファイルと違ってHTMLの中の必要な場所にだけプログラムを埋込めます。*.cgiではファイル全体がプログラムなので、HTMLを出力するにはprint関数を使うか、別に用意した*.htmlファイルを読込んで書出すという方法しかありません。後者ではファイルの全体構成が見通しにくくなりますし、前者の方法ではprint関数の()内のHTMLにエスケープ処理が必要なため冗長になります。
具体的には、例えば
<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
であれば、print関数を使って以下のように書く必要があります。
print "<META http-equiv=\"Content-Type\" content=\"text/html; charset=UTF-8\">\n";
ファイル全体でこの書換えをするのは大変ですし、HTMLとしても読みにくくなります。 これに対して*.phpファイルは全体としてはHTML文書であり、<?php ... ?>タグ内にプログラムを書きます。以下に示すのはともちゃんHPのindex_j.phpの冒頭部分で、年表2番目の丸印「MySQLカウント表示」に対応します。「4. Perl」で紹介したcount.cgi (new)でMySQLに書込んだデータを読出して表示します。
<!DOCTYPE html>
<HTML>
<HEAD>
<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
<TITLE>
ともちゃんホームページ
</TITLE>
</HEAD>
<BODY BGCOLOR="#3FB6BF" LINK="#0000FF" VLINK="#FF4000" ALINK="#FF4000" TEXT="#000000">
<?php
$today = getdate();
$ctime = $today['hours'];
if($ctime >= 5 && $ctime < 11){
printf("おはようございます。");
}elseif($ctime >= 11 && $ctime < 17){
printf("こんにちは。");
}elseif($ctime >= 17&& $ctime < 23){
printf("こんばんは。");
}else{
printf("夜中までご苦労さま。");
}
printf("ともちゃんホームページへようこそ・・・\n<BR>\n");
$db = new mysqli("ホスト名", "ユーザ名", "パスワード", "データベース名");
$id = 1;
$stmt = $db->prepare("SELECT number FROM count WHERE id=?");
$stmt->bind_param("i", $id);
$stmt->execute();
$stmt->bind_result($number);
$stmt->fetch();
printf("あなたは1997年２月24日以来<B><A HREF=\"count_j.php\">%d人目</A></B>のお客様です。\n<BR>\n", $number);
$stmt->close();
$db->close();
$date1 = "1997/2/24";
$y = $today['year'];
$m = $today['mon'];
$d = $today['mday'];
$date2 = "$y/$m/$d";
$interval = (strtotime($date2)-strtotime($date1))/(60*60*24);
$average = $number/$interval;
printf("今日は<B><FONT COLOR=#0000FF>%d日目</FONT></B>なので１日平均のお客様の数は<B><FONT COLOR=#E000C0>%.1f人</FONT></B>です。\n", $interval, $average);
?>
</BODY>
</HTML>
上記のコードでもPHPのブロック内にprintf()が出てくるので、そこではエスケープ処理が必要です。そこで、以下のコードのようにして、もっとHTMLに近付けることも考えられます。この場合はPHPの変数を埋込む必要がある部分にピンポイントで<?php echo $foo; ?>を挿入しているので、ほとんどHTMLの書式のままです。44行目の2つ目の変数のように<?php printf("%.1f", $average); ?>として表示形式を指定することも可能です。ここまでやるかどうかは好みの問題ですが、「6. JavaScript」で書いたインビジブルコードとはこのことです。
<!DOCTYPE html>
<HTML>
<HEAD>
<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
<TITLE>
ともちゃんホームページ
</TITLE>
<?php
$today = getdate();
$ctime = $today['hours'];
if($ctime >= 5 && $ctime < 11){
$greet = "おはようございます。";
}elseif($ctime >= 11 && $ctime < 17){
$greet = "こんにちは。";
}elseif($ctime >= 17 && $ctime < 23){
$greet = "こんばんは。";
}else{
$greet = "夜中までご苦労さま。";
}
$db = new mysqli("ホスト名", "ユーザ名", "パスワード", "データベース名");
$id = 1;
$stmt = $db->prepare("SELECT number FROM count WHERE id=?");
$stmt->bind_param("i", $id);
$stmt->execute();
$stmt->bind_result($number);
$stmt->fetch();
$stmt->close();
$db->close();
$date1 = "1997/2/24";
$y = $today['year'];
$m = $today['mon'];
$d = $today['mday'];
$date2 = "$y/$m/$d";
$interval = (strtotime($date2)-strtotime($date1))/(60*60*24);
$average = $number/$interval;
?>
</HEAD>
<BODY BGCOLOR="#3FB6BF" LINK="#0000FF" VLINK="#FF4000" ALINK="#FF4000" TEXT="#000000">
<?php echo $greet; ?>ともちゃんホームページへようこそ・・・
<BR>
あなたは1997年２月24日以来<B><A HREF="count_j.php"><?php echo $number; ?>人目</A></B>のお客様です。
<BR>
今日は<B><FONT COLOR=#0000FF><?php echo $interval; ?>日目</FONT></B>なので１日平均のお客様の数は<B><FONT COLOR=#E000C0><?php printf("%.1f", $average); ?>人</FONT></B>です。
</BODY>
</HTML>