VBAを今後どのように扱ったら良いか?

VBAはエンドユーザーコンピューティングとして優れたプロダクトだと思っている。ほとんどのユーザーが購入して持っているであろうOfficeアプリケーションさえインストールされていれば開発できる。高価なミドルウェアを購入したり、面倒なコーディングをしなくても標準でレポーティングツールや、データベースだって利用できる。このような言語は中々代替えがないのだが、VBAと離別すべき時も刻一刻と近づいている。

今VBAが置かれている現状を整理したい。

ActiveX Data Object(ADO)

VBAから直接データベースに接続するにはADOぐらいしか選択肢がありません。にもかかわらずADOのサポートは停滞したままで、ADO2.8がリリースされて以降は新たなリリースがありません。機能的にはSQLServer2005で止まっていて、それ以降に追加された型には対応していません。たとえばXML型とかGEOMETRY型を扱うことができません。特にSQL Azureの動作対象リストからはADOやDAOが外されており、別の方法で接続する事を求められます。
唯一幸いなことは、ADOのコンポーネントはOSとともに配布されているので、OSのサポートが終了するまではセキュリティアップデートが提供され続けることです。

ADO以外の方法でデータベースに接続しようとすると、VBA以外の言語でコードを書く必要が出てきます。どんな手段が考えられるでしょうか?

XML WEBサービス

Visual Studio .NETでは容易にXML WEBサービスを作成する事ができます。これをVBAから呼び出す方法が考えられます。
しかし残念なことに無償で使用できるExpress版ではWEBサービスを開発できません。最も安価なVisual Studio 2015 Professionalでも6万円台になります。エンドユーザーコンピューティングを考えたときに6万円超の負担は避けたいところです。Community版があるじゃないかと考えるかもしれませんが、ライセンス的に業務利用は難しいので対象外とします。
もうひとつ問題となるのがXMLパーサー部分です。XML WEBサービスを使用するクライアント用にMicrosoft SOAP Toolkitが配布されています。しかしながら、このコンポーネントは標準ではインストールされていないため「Officeさえインストールされていれば動作する」と言う利点を失います。
Windowsに標準でインストールされているMSXMLをパーサーとして使用する方法もありますが、MSXMLはInternet Explorerのモジュールです。そしてMicrosoft EDGEはMSXMLに対応しないことがアナウンスされています。将来性を考えるとこれも避けたいところです。

根本的な問題はVBAがCOMを基幹技術としているところです。Microsoftの基幹技術は既にとっくに.NETに移行しており、旧技術であるCOMは完全においていかれています。もういっそ、VBAは完全にあきらめて、VB.NET等の他言語で作成するというのはどうでしょうか?

Microsoft Office Interop

Visual Studioで作成したアプリケーションからMicrosoft Office Interopを使用してOfficeアプリケーションを操作する方法があります。幸いなことにOffice InteropはExpressエディションからも使用できるので追加投資も不要です。.Net Frameworkのバージョンを適切に選択すれば、実行ファイルをコピーするだけでも動作することが多く、展開も手軽です。
しかしながらOffice Interopはお世辞にも使いやすいlibraryではありません。単純なCOMのラッパーなのでインスタントの解放はプログラマーの責任で記述する必要があります。実際にコードを書くとインスタンスの生成と解放ばかりになって効率が悪いです。ライブラリが不出来なのが要因でコーディングの難易度が上がるのは、エンドユーザーコンピューティングでは避けたいところです。

Visual Studio Tools for Office(VSTO)

.NETでOfficeのプラグインを作成するために提供されたミドルウェアです。作成したプラグインを動作させるにはインストール作業が必要になるため展開はやや面倒になります。残念なことにVSTOもExpress版では使用できません。

Apps for Office

.NETでOfficeのプラグインを作成するために提供されたミドルウェアです。作成したプラグインを動作させるにはインストール作業が必要になるため展開はやや面倒になります。残念なことにApps for Office もExpress版では使用できません。

ClosedXML

ClosedXMLは Microsoft Open XML Formatのファイルを読み書きできるオープンソースの.NET用のミドルウェアです。Office2007以降ファイル拡張子がXMLXやDOCXに変更され、ファイル仕様が完全に公開されるようになりました。もちろんExpress版からも使えます。
この辺りが現実解では無いでしょうか。VBAを扱っていた経験があるなら、VB.NETに慣れるのもそれ程時間はかからないでしょう。Powersellから呼び出すのも面白いかもしれません。PowersellはWindows7以降に追加された、OSの操作に使用する言語です。Powersellからしか設定できない項目などもあり、Windowsを使う上で覚えておくととても便利です。

Excel REST API

https://channel9.msdn.com/Events/Visual-Studio/Connect-event-2015/315
まだPublic preview段階ですが、OneDriveやOffice365のクラウドストレージに保存したExcelファイルを編集するためのWebAPIです。現在、新しいOffice製品はクラウドストレージとの連携を前提として開発されています。WebAPI経由なら言語は選びませんし、各種のオープンデータとの連携も取りやすくなります。もしかしたらエンドユーザスクリプトの主流になるかもしれまけん。

VSTOやApps for Officeには無償でProfessional相当の機能を使えるCommunity版という選択もあるのですが、商用利用は著しく制限されているため、業務でのエンドユーザーコンピューティングには使いにくいです。私の場合には、ぐるっと回って「C#デスクトップアプリケーション + LINQ for MSSQL + ClosedXML」で組んでいくことにしてみました。

参考:
Roadmap for Apps for Office, VSTO, and VBA(http://blogs.msdn.com/b/vsto/archive/2013/06/18/roadmap-for-apps-for-office-vsto-and-vba.aspx)

Cloud上の仮想マシンの最適化

Cloud上の仮想マシンの最適化の鍵はローカルストレージの活用にあります。

一般的に仮想マシンはディスクIOがボトルネックになりやすいです。動作速度に物理的な制約があるデバイスを複数の仮想マシンで共有する上に、実際に物理的に書き込まれるまでの階層も多いので、ボトルネックになるのは避けられません。

Amazon RDSやSQL AzureもEC2やと同じく仮想マシンを提供している事にはかわりありません。通常提供している仮想マシンに比較して、データベースのようにディスクIOが頻繁に発生する用途に特化して最適化が施されています。

Amazon RDSやSQL Azureを使うことで、通常の仮想マシン上にデータベースを構築した場合よりも高速になるか否かは、場合によるので一概には言えません。

Cloud上の仮想マシンの最適化を試みる場合、仮想マシンに割り当てられる揮発性ストレージの活用が鍵になると思います。揮発性ストレージは仮想ディスクとオンプレミスのストレージと遜色ない速度で動作します。障害時に直近のデータが失われてもかまわないログデータや、通常は読み取り専用にしか使わないデータを揮発性ストレージに配置することで大幅にパフォーマンスが変わってきます。

またローカルストレージを使わないにしても、活用する見込みのないログ出力を抑制するだけでも大きく変わってきます。もしクラウド上の仮想マシンでパフォーマンスを問題としているなら、まずは仮想ディスクへのアクセスを減らせないか検討しましょう。

.NET Framework 3つのDB接続方法の使い分け

.NET FrameworkではADO.NET、LINQ to SQL、Entity Frameworkの三種類の接続方法が提供されています。どの接続方法を選択するのが良いのでしょうか?

ADO.NET

もっとも古くから提供されている接続方法です。
原則としてSQL文を直接記述するので、SQL Serverのすべての機能を利用することができます。一方で静的型チェックなどの恩恵を受けることが難しい(型付きDataSetを使えば可能だが面倒)のでコーディング効率が落ちそうですが、ユーザーコントロールとのデータバインディングを使用すればコーディング効率も決して悪くはありません。

LINQ to SQL

SQL Serverのスキーマを元にクラスを自動生成する簡易的なORマッパーです。
原則として自動生成されるSQLを使うことになるので、一般的なORマッパーと同じく非効率なSQL文が生成される事によるデメリットもあります。その一方で一旦データベースから取得したデータに対して、さらに検索や集計をかける事も容易にできるのでうまく活用すればデータベースの負荷を下げる可能性もあります。静的型のチェックによる恩恵もあるのでADO.NETに比較してコーディングは楽になるでしょう。
.NET 3.5から提供されていますが、その後は殆どアップデートされておらず、プロダクトの継続性に疑問符が付きます。

Entity Framework

LINQ to SQLとほぼ同時期、.NET 3.5 SP1から提供されている接続方法です。
Entity Frameworkはクラス設計を元にSQL Serverのスキーマを生成するORマッパーです。先にクラス設計を行うので既存のデータベースに対して利用するのは難しく、利用できるのは新規に設計するときに限られます。また逆にEntity Frameworkを使わずにSQL Serverにアクセスする必要が生じた場合に苦労する可能性があります。

何を使えばよいのかは時と場合によります。私は静的型のチェックによる恩恵を得られ、既存データベースへ適用できる点でからLINQ to SQLの活用機会が多いです。

Windows 記憶域プールは使わない方が良い

Windowsの記憶域プールは追加投資無しでRAID0~6相当のドライブを作ってデータを保護でき、 シン・プロビジョニング(後からディスクを追加して容量を増やしたりできる)にも対応している。USB3.0を使用すれば多数のドライブを接続できるし、SSDと併用して高速化もできる。設定もGUIベースで簡単にできる。良いことづくめに見えるが、私は他者に記憶域プールの使用は進めない。

管理機能の不足

Windowsの記憶域プールのGUIは管理機能を殆どで提供しない。異常が発生しても「壊れています」以上の詳細な情報はGUIでは把握できない。標準GUIで把握できない事態に陥った場合には、PowerShellというWindowsの独自言語を使ったプログラミングが必須となる。この時点で初心者はお手上げになってしまう。

復旧機能の不足

致命的なことに復旧機能がほとんど提供されていません。ディスクの異常が発生して修復に失敗してしまうと、記憶域プールに作成した仮想ディスクを破棄して作り直す以外の方法がありません。これはPowerShellを使っても同じで、Repair-VirtualDisk(自動修復を実行する)というコマンド以外に普及方法が提供されていません。
一般的なハードウェアRAIDであれば強制的にミラーを解除して再構成をすることも出来るのですが強制的に解除するコマンドは用意されていないのです。これでは上級者でもお手上げになってしまいます。

RAID1対応のNASでも安いものなら2万円を切る価格(除くHDD)で購入できることを考えると、安価なRAID対応NASを使うほうが良いでしょう。

ClosedXMLでSortによる並び替えができない

Microsoft Excelで作成したxlmxファイルをClosedXMLで開いて捜査している場合、Sortメソッドによる並び替えに失敗する場合があります。ソートしようとしている範囲内にClosedXMLが対応していない書式情報があるのが原因です。Microsoft Excelでファイルを作成するときに並び替え範囲内の書式を削除しておくと、正常に並び替える事ができます。

Windows Server 2012でVB6.0のアプリケーションを動かす

Support Statement for Visual Basic 6.0 on Windows Vista, Windows Server 2008, Windows 7, Windows 8 and Windows 8.1, Windows Server 2012, and Windows 10」に互換性に関する情報があります。このページに「Supported Runtime Files to Distribute with Your Application」として動作保証されている再配布ファイルのリスト(全言語共通の物と、日本語など言語固有の物にリストが分かれています。)が公開されています。このファイルを別のフォルダにコピーしていきます。

また上記のリストではOSに付属していることになっていますが、msvbvm60.dllはWindows 2012に含まれていませんので、これもコピーします。

Visual Basic 6.0 Service Pack 6:ランタイム再頒布可能パッケージ (vbrun60sp6.exe)」をダウンロードします。ダウンロードしたVB6.0-KB290887-X86を解凍するとvbrun60sp6.exeが得られます。vbrun60sp6.exeのインストーラはCAB形式の自己解凍ファイルになっています。vbrun60sp6.exeを解凍すると、vbrun60sp6.exeがインストールするいくつかのファイルが得られます。その内の「W95INF32.DLL、W95INF16.DLL、ADVPACK.DLL」はリストに載っていませんがコピーします。

用意したランタイムファイルをWindows Server 2012の適当なフォルダ(ここではC:\VB6Runtimeとします)にコピーします。システムのPATH設定にC:\VB6Runtimeを追加したあと、拡張子がOCXの物についてはregsvr32コマンドを使用してレジストリに登録していきます。C:\Windows\System32にコピーした場合はregsvr32コマンドに失敗します。C:\Windows\System32にはランタイムを置かないで下さい。

「Supported and Shipping in Windows Vista, Windows Server 2008, Windows 7, and Windows 8」に含まれているファイルを誤って配布しないように注意してください。C:\Windows\System32を見てもmsado150.dllやmfc40.dllが が見当たりませんが、規定のインストール先がC:\Windows\SysWOW64やC:\Program Files (x86)\Common Files\System\Ole DBに移っているだけで、きちんと存在しています。無闇に導入してしまうと別の問題を引き起こす恐れがあるので、Supported and Shipping~の対象は絶対にコピーしないでください。

VB6.0のランタイムをインストールするためのフリーウェアがありますが、それらはC:\Windows\System32に複製しても問題なかった時代に設計された物がほとんどのはずです。無闇に利用するとトラブルの原因となり得ます。同じ理由で昔自作したインストーラも危険です。気をつけましょう。