Excel VBAからSQLite3のデータベースを触ってみたところ、それなりにハマったのでメモ。

はじめに

本サイトの管理人たるpandaは今までExcel VBAを使って本格的なマクロを書いたことがありません(※この記事を最初に書いた時点(2018年8月)の情報です)。

そんな折、勤務先ではかなり偉い人だという噂の嫁様が、

「(嫁様の)上司がこれまで管理していた案件情報の管理を引き継ぐことになったのだが、現状の管理方法があまりシステマティックなやり方ではなく、そのまま引き継ぐと夜家に帰れなくなり、『働き方改革』とかっていうレベルじゃなくなるので、とにかく作業効率を上げたい。(意訳)」

という長い長い前置きがあった上で、

1. すでにシステムの担当者とは話をしたものの、「開発にはAccessやMySQLなどのデータベースアプリケーションが必要そうである。また開発には相当の日数を要する見込みである。」と言われたこと。
2. 入力はできるだけ効率的にやりたいので、ExcelまたはExcel VBAで開発できるマクロまたはアプリケーションを使うことを前提としたい。
3. ExcelはほぼすべてのPCにインストールされているが、バージョンが統一されていないので、最新のバージョンでなければ動かないという作り方はしないで欲しい。
4. Accessは一部のPCにしかインストールされていないので、できれば使いたくない。

という補足説明もありました。

上記の補足説明でも「MySQL」というキーワードが登場していますが、別件で嫁様と嫁様の勤務先のシステムの担当の人が話した際の話を聴いたときにも、「Apache Tomcat」みたいな単語が登場していたので、

「嫁様の勤務先のシステム担当の中の人はオープンソース方面にはあまり抵抗がないんじゃね?」

と判断しました。

そこで、「案件情報のデータ自体はSQLite3のデータベースに保持し、それにExcel VBAからアクセスするシステム(下図)なら、2日くらいで開発できるよ!!(｀･ω･´)」と嫁様に話を持ちかけ、実際週末の2日間で家事をこなしつつ開発しました。

開発自体はできたんですけど、Excel+SQLite3の組み合わせでの開発時におけるハマりポイントについての情報があまりなさそうだったので、この記事ではそれについて書いていきます。

SQLite3についての書籍はあまり多くありませんが、Pythonから利用する方法について扱っているものがあったので、ご参考にしていただければ幸いです。

ExcelForSQLite3を使って環境構築。

必要なもののダウンロード。

ExcelからSQLite3のデータベースへアクセスするためには以下のものが必要です。

1. SQLite3のDLLファイル。
2. 上記DLLファイルにアクセスするための関数(API)が定義されているDLLファイル。
3. 上記APIを呼び出すためのExcel VBAマクロが組み込まれたExcelのファイル。

スポンサーリンク

上記のファイルをまとめたものがSQLiteForExcelというパッケージとして配布されているので、ダウンロードします。

Excel VBAマクロのコピー

APIを呼び出すためのExcel VBAマクロは以下の手順で別のExcelファイルにコピーすることによって、Excel VBAマクロのコピー先であるExcelファイルから(コピー元のExcelファイルと同様に)SQLite3のデータベースにアクセスできるようになります。

1. 上記のパッケージに同梱されているExcelのファイルからExcel VBAファイルをエクスポートする。
2. 手順1でエクスポートしたExcel VBAファイルを別のExcelファイルにインポートする。
3. 同じディレクトリにsqlite3.dllとSQLite3_StdCall.dllを置く。

なお、32bitの場合はsqlite3.dll(32bitでビルドされたものを使用します。)とSQLite3_StdCall.dllが両方必要になりますが、Excelが64bit版の場合は64bitでビルドされたsqlite3.dllのみが必要なようです(手元にあるExcelが32bit版しかないため、試していません)。

守備範囲の決定。

環境が構築できたら、Excel VBAが受け持つ処理の範囲を決めます。Excel VBAで処理しない機能については(当然のことではありますが)、SQLite3が処理を担当することになります。

今回開発するアプリケーションは嫁様が主に利用するので、Webアプリケーションに移植されたりすることはまずないとは思います。

そうはいっても、オープンソース寄りの部分にできるだけ多くの機能を持たせたいので、Excel VBAのマクロには以下の簡単な処理だけを担当させることにしました。

1. ユーザーインターフェース
2. Excelのセル上での表示に必要なフォーマットの変換(日付データの文字列への変換、番号の左ゼロ詰め等)

上記以外の計算処理はSQLite3に対して発行するSQL文側で行わせることにしました。

Excel VBAのハマりどころ。

分数。

「分数を入力したい」という要件があったので、「Excelは分数とか勝手に計算して小数とかに変換しそうで嫌だなぁ。」と思い、SQLite3上では文字列として格納するようにSQL文を書いてみました。ところが、小数だけならまだしも、日付に変換される例もありました(“1/50″→”1950/01/01″等)。(´・ω・｀)

…というわけで、Excelのsheet上は分母のみを表示することとし、データベースに格納する際には文字列に変換後に先頭に”1/”を追加して格納し、データベースから読み出す際には先頭の”1/”を削除後に整数に変換してExcelのsheetに表示させる仕様としました。

[2021/11/03 補足] どこで見かけたかは忘れてしまいましたが、分数は”a b/c”(a, b, cは整数で、aは0であっても良い)と入力すると、Excelでの表示上は分数として表示されるようです。

例えば…

のように入力し、Enterキーを押してセルの入力を確定させると…

のように$\displaystyle\frac{a}{b}$の形式で表示させることができます(確定状態から編集モードに変えると数値表示(上記の例ですと$0.5$)に切り替わります)。

なお、仮分数を…

のように入力し、Enterキーを押してセルの入力を確定させると…

帯分数に変換されます。

保護されているシート上のセルに対する処理。

SQLite3上ではプライマリキーとして人工キーを使用していますが、それをExcelのシートの最初のカラムに表示させています。ただ、このカラムの編集はSQLite3的には厳禁なので、Excelのシート上ではできるだけ編集をさせないようにする必要があります。

ところが、自分がGoogle先生にお伺いを立ててみた範囲では、以下の手順でしか特定のセルの編集を不可とする方法がありませんでした。

1. シート全体を保護する。
2. 編集を許可したいセルに対してLockedプロパティを適切に設定する。

また、Lockedプロパティを適切に設定したはずなのにマクロを実行すると原因不明のエラーが発生することがあったので、広範囲のセルに対して処理を行う際には、いったんシートの保護を解除してから必要な処理を行い、再度シートの保護を行うこととしました。

実装例は以下のような感じになります。

 
なお、このシートの保護はExcelの「校閲」タブから解除できてしまうため、制限事項としてこの機能は使用しないよう嫁様にお願いすることとしました。

SQLite3のハマりどころ。

以下のSQLite3のハマりどころはSQLite3を常時触っている人にとっては当たり前のことかもしれませんが、普段はMariaDBで暮らしている本Webサイトの管理人たるpandaにとってはコロッと忘れてしまうところなので、この機会にメモしておきます。

日付・時刻の処理。

今回開発するアプリケーションではCURRENT_DATE関数を使っています。しかしながら、SQLite3における日付・時刻表現のタイムゾーンはUTCとなります(SQLite3ではデータ型に日付・時刻型は存在しないので、TEXT型でテーブルに格納しておいて、日付・時刻処理用の関数をSQL文発行の都度実行します)。

上記の理由により、SQL文を発行する際に日付・時刻を扱う際には扱い方により、以下のように日付・時刻処理用の関数を使い分ける必要があります。

1. select文で日付・時刻を取り出す場合: 日付・時刻が格納されているカラムについては以下のように関数を実行し、得られた値を使用する。
   datetime(カラム名,”localtime”)

    

2. where句で日付・時刻を指定する場合: ローカルタイムを指定する場合には、以下の例のように指定し、UTCに変換する。
   where カラム名=datetime(ローカルタイム,”utc”)

    
   「俺 or 私は絶対にUTCを指定するんだ!!」と自信を持って言い切れる人はこの変換は不要ですが、後で当該部分のコードを見たときにわからなくなること請け合いですので、素直にローカルタイムを使う癖をつけた方が無難です。

last_insert_rowid関数。

sqlite3のプロンプトから都度SQL文を書くのが面倒だからと言って、Windowsコマンドプロンプトから以下のように実行すると常に0を返してくれて、開発者を疑心暗鬼に陥れてくれるlast_insert_rowid関数ですが…

 

これは同一のセッション中で、かつ直前のinsert文の実行によりセットされたrowidを返す関数です。

よって、プロンプトでテストなんかしないで、insert文の直後にとっとと組み込んで動作確認するのが正解です(偏見)。

まとめ

Excel VBAで初めて本格的なマクロを書きましたが、問題なく動いてくれて良かったです。🐼

嫁様の勤務先で動かしてみると、いろいろと改善が必要な点が出てくるかとは思いますが、嫁様から報告があり次第、修正が可能なものについては修正していくつもりです。

この記事の本文は以上です。

なお、SQLについてもうちょいお知りになり方はこちらもご参照いただけるとよろしいかと思います。😃

参考文献

1. ExcelからODBCなしでSQLiteを操作する(設定編）
2. ExcelからSQLiteを使う方法 | Gabekore Garage