💽

セキュリティが厳しい環境でVBAだけを使ってデータベースらしきものをゼロから実装したときの作業記録

2023/02/11に公開

Excel

はじめに

皆さんはどのように日々のデータを管理していますか？

筆者の所属する組織では、ほとんどExcelで管理されています。どんなデータが、というと、ほぼ全てのデータです。

組織全員が使用するようなシステムにデータベース等(Access, 各種SQL)は使用されていますが、日々の仕事を行う上では個別にツールを作るようなことはせず、データ管理をExcelに頼り切っています。

当然ながら、コンピュータの知識はみんなほとんど持っていません。Word, Excelが使える程度です。

筆者はデータ解析を行っていますが、普段はオフィスワークでデータ解析とは遠い場所にいます。アプリケーションの作成も、簡単なものしか経験がありまえん。

そんなオフィス環境・経験において、筆者が部署内共有データベースを作成することになった話です

環境構築までの検討内容

共有したいデータは、毎日増え続ける顧客データのようなものです。以前は個別に記録を作成していたのですが、登録データがある時期から急に増えることになり、効率的な管理の方法を検討する必要が出てきました。

このデータを管理するデータベースを構築し、複数人で閲覧・編集できるようにするミッションです。

これだけ聞くと、社内のコンピュータにデータベースを作成して、アクセスするためのインターフェースをHTMLなどで作成すればいいように思います。 Node.js を常時稼働するPCに準備して SQLite を導入すれば解決しそうです。

しかしながら筆者のオフィスでは前提として以下の条件があるため、自由な開発ができない状況にあります。

自由に使うことができる社内サーバーはないが、NASはある
セキュリティが厳しく、PCに自由にアプリケーションをインストールできない
外部インターネットとはほぼ隔離されており、GitHub等の資源をインターネットを経て利用できない

絶望的です。ちなみに、データベースを本格的に設置するのは、筆者はこのときが初めてです。

インストールせずに使えるデータベースで言うと、先述した SQLite が有名ですが、調べたところ、SQL発行や他アプリケーションからの接続には、別途ドライバーのインストールが必要でした。

ユーザーが操作に慣れているExcel VBAからSQLiteを操作できないか、とも思いましたが、 ODBCドライバー のインストールが必要であるらしいこと、また、ODBCドライバーなしに使用する SQLiteForExcel という便利なツールもあるが、パット見て操作がよくわからなかったため、ExcelをとおしてSQLiteを操作することは難しそうだと感じました。いろいろ試してみても良かったのですが、時間ばかりが経過しそうな予感です。

ちなみに、RではWebアプリケーションを作成する Shiny というパッケージがあり、これはRだけで実装することができます。筆者も実際に簡単なアプリは作った経験があります。しかしながら、Shinyもサーバーとして動作するアプリケーションであり、常時稼働するサーバーがオフィス内に存在しないため、検討から除外することになりました。

また、Excelに標準でついている共有機能や Power Query ですが、複数人で使用する場合に、データ修正のバッティングなどが起きやすいこともあり、これまた検討から除外しました。

データベースの完成イメージとして、筆者のオフィス環境からするとデータはNAS上に置き、操作は各PCのブラウザやExcel等の使い慣れたもので操作できるようにすることがベストだと思います。追加でいろいろと情報を探りましたが、上記前提条件でも使うことができるアプリケーションは、残念ながら見つかりませんでした(経験がないと実装が難しそうでした)。

なら、いっそのこと自分で作るか、ということで、未経験ながらデータベース(らしきもの)を設計することにしました。

実行環境の準備

前提条件もあるため、インターフェースはExcelかブラウザにしようと、最初に決めました。また、使うプログラミング言語もVBAだけ、必要に応じてHTMLやJavascriptも使用するようにしようと思います。

筆者にもっとスキルがあれば、SQLを核にして実行ファイルだけのポータブルアプリケーションを作成して配布するということもできたのでしょうが、残念ながら筆者は R, VBA, Javascript ぐらいしかまともに使えません。もっと開発用言もしっかり勉強しておけばよかったと後悔したところです。筆者のプログラミングはデータ解析がメインであるためこれまで使う必要がなかったのですが、データ管理をするためにはSQLが必要で、SQLを使いたいなら他の開発用言語も必要になる、ということがよくわかりました。

作成するデータベースについては、以下の操作イメージで作っていきます。

登録データは、毎日10件ほど増加
管理者が最初にデータを登録し、それを見た他のユーザーが修正を加える
複数のデータを別に管理して、確認するときは一つの表にまとめたい

SQLでできることをSQLを使わずに実装しなければならないようです。

(DB)??? --- (interface) ブラウザやExcelシート

データベースとして記録用の媒体が必要になります。このとき、以前に観測データ記録用のアプリケーション(登録用フォーム)を ローコード開発ツールのAppSheet を使って作成したことを思い出しました。その時は記録媒体として Google Spreadsheet を使って作成しており、今回のケースも同じように、 Google Spreadsheet の代わりに記録媒体としてExcelブックを使用すればいいのではないかと考えました。また、インターフェース用に別のExcelファイルを準備すればデータの記録と操作を分離することができ、複数人で使用することもできると思いました。

もう少し詳しく説明すると、データベースはExcelブックとし、各テーブルはワークシートとして管理する。また、インターフェース用の別のExcelブックからは Workbook.Open メソッドを使ってデータベースにアクセスすれば良さそうです。

(DB)Excel book, (table)worksheets --- (interface) Excel book

テスト1 --- 失敗

Excel bookをデータベースとして使用する案でVBAを書いてみました。

登録データはExcelシート上にあるため、データの取り込み自体は Range() で配列形式に一括取り込みの後、インターフェース用のExcel book上に出力する処理であるため、コード自体はすぐに作成できました。

が、以下の点で思い通りにならず開発継続を断念しました(失敗)

データベースの登録数が増えると Workbook.Open メソッドが重くなる
Workbook.Open で、他ユーザーが開いている際(readonly)は読み込み待機させるようにしているが、上記理由で時間が増大
データベース用Excelbookを手動で開いてしまうと処理が進まない

失敗の一番の理由は、１つ目の項目です。

プログラム構成は問題ないのですが、Excel bookを開く処理は思った以上に重い処理のようで、操作に不満が残ります。

筆者はアプリケーション作成の経験はほぼありませんが、UXが重要であることは想像できましたので、上記問題点を解決すべく再検討しました。

テスト2 --- 成功

思い切って、データベース(Excel book)をファイルにすることをやめてフォルダにし、テーブルはテキストファイルで別に管理することにしました。

テキストファイルにすると、データを取り込む際は文字列であるため、テスト１のときのようにいきなり2次元配列での操作はできませんでした。その代わり、行ごとにデータを取得してDictionary型のオブジェクトに格納することで、効率的に、かつ、データ数が増えても読み込みの速度が大きく変わることなく処理できるようになりました。データは登録時に文字列から項目別に配列に変換すれば、その後のアクセスもしやすそうです。

以下にデータ取り込み時の構成を示します。

データベース: フォルダ作成
テーブル: テキストファイルで個別に管理
テーブルは使いやすさを考慮してCSVではなく TSV 形式で保存(テキストファイルをコピーしてExcelに貼り付けると、そのままセルに分かれてペーストされるため、確認したいときも楽)
テーブルは Open メソッドにより読み書きする。処理が高速
テキストファイルから取り込んだテーブルはVBA動作中は Dictionary オブジェクトで保持するようにし、これに併せてテーブルには Primary Key も設定(Primary KeyをDictionaryのキーとして使用)
Dictionaryオブジェクトのitemは、1次元配列 Array() でデータを保持

Key	Item
primary key item	Array(item1, item2, item3, ...)

テーブルの primary key, データ型, データ列名についても管理できるよう、Dictionaryオブジェクトの先頭にそれぞれ primary_key, type, name キーで登録

Key	Item
primary_key	number
type	Array(type1, type2, type3, ...)
name	Array(name1, name2, name3, ...)
key1	Array(item1, item2, item3, ...)
key2	Array(item1, item2, item3, ...)
...	...

データを保存する際は確実に列名にあったデータを順番に登録するため、内部の配列構造をデータ列名をキーとしたDictionary型オブジェクトに変換して登録作業を行う

Key	Item
name1	item1
name2	item2
name3	item3
...	...

VBAでテキストファイルを読み書きする際は Open メソッドで、他ユーザーのアクセス制限を設定することができるため、データ読み書きの利便性も向上しています。

これでデータベースの作成・テーブル作成・データ読み出しを行う基礎部分が出来上がりました。

SQLライクな操作関数の作成

今回作成したクラスモジュールは記録としてGitHubにアップしました。

詳細は上記のREADMEを確認いただければと思いますが、実装するにあたって考えていたことを以下に続けます。

データベースの作成とテーブルからのデータ入出力部分はできましたが、これだけではVBAを直接使わないとデータへのアクセスができません。

目指すところは、VBAでもデータベース操作ができるようにモジュールや関数を作成し、Excelシート上でボタンを押せば動作するような仕組みにすることです。

そのためには、機能の拡張や、似た操作を行う場合でもVBAを1行書けば実装できるような状態にしなければいけないと思いました。

つまりは、SQLのようにVBAを書いてテキストファイルからデータを読み書きするようにすることが必要です。SQLで実装される柔軟な操作を、全てではありませんがある程度使えるように。

そこで、まずは以下のSQL構文に該当する処理関数を作成し、それらの関数を呼び出すラッパー関数を作成することにしました。

また、経過記録が残るよう、保存のたびにバックアップを作成することも忘れないようにします。

操作	SQL構文
データベースの作成	CREATE DATABASE
テーブルの作成	CREATE TABLE
データの登録	INSERT
テーブルの上書き保存	UPDATE
列の追加	INSERT
テーブルからデータを取得	SELECT
データを条件検索	WHERE
テーブルを結合	JOIN
テーブルデータの並び替え	ORDER
テーブルからデータの削除	DELETE
テーブルの削除	DROP

これらの関数(個別の関数名は省略します)を操作する SQL() 関数を作成しました。

操作イメージは以下に示します。

筆者の力量では、SQL文を文字列から読み取って実行する処理を作ることができませんでした。というより、もっと時間がかかりそうだったのと、Excel VBAの強みであるセルからのデータ取得を活かすのであればSQL文をそのまま実装するより、多少変更したほうが使いやすいだろうと考えました。今回作成した SQL() では、引数としてSQL風に処理を入力することで実行するようにしています。

'' ラッパー関数操作例
Call SQL("CREATE", "TABLE", "table-name", データ型, データ名)
Call SQL("UPDATE", "table-name", 上書きデータ)
Set dic = SQL("SELECT", "*", "table-name", "WHERE", "name1<5", "JOIN", "join-table-colname", "join-table-name", "ORDER", "order-table-colname", 0)

更に、 SQL() では引数の数を指定しない仕様にしたこともあり、入力補完で引数がVBE上に表示されない状態になっています。これももう少しだけ分かりやすくするために、 SQL() を更に簡単にした関数も準備しました。

例えば、 SELECT を実行する場合は以下の関数も使用することができます。引数は optional にしているため、テーブル名だけを指定すればひとまずデータの取り出しはできるようになっています。

Set dic = SELECT_Table("table-name")

これらの関数では、VBAのオブジェクトにデータを取り込むだけの操作です。ここもExcelの強みを活かして、テーブルのデータはExcelシート上に出力できるよう、Dictionary型オブジェクトを2次元配列構造に変換する dic2arr() 関数を準備しました。以下の手順でテーブルから取り込んだデータをExcelシート上に出力します。

Dim TSQL as new textSQL
'' テーブルからデータの取り込み
Set dic = TSQL.SELECT_Table("table-name")
'' データを配列形式に変換
data = TSQL.dic2arr(dic)
'' セルに表示
Range(Cells(1, 1), Cell(ubound(data, 1), ubound(data, 2))) = data

また、出力用フォーマットとしてHTMLが選択できるよう、別に HTMLmacro クラスも作成しました。

これはHTML構文を文字列で出力する関数を作成し、それぞれ組み合わせることで最終的にブラウザで表示できるようにしたものです。

基本的なフォーマットである、 HTML, article, h1, h2, h3, p, ... 等を作成したので、組み合わせることでHTML分を作成することができます。

Dim HTML as new HTMLmacro
With HTML
    body = body & _
        .article( & _
            .h1(.font(data_table_name, "red")) & _
            .table(dic, "long"))
    
End With

'' HTML出力
text = HTML.HTML("sample - HTML output", body)
'' Edgeブラウザで表示
Call HTML.PrintHTMLandOpenByBrowser(text)

table() 関数では、Dictionary型オブジェクトを勝手に表形式のHTMLに変換するよう作成しているので、 SQL("SELECT", ...) で取り込んだテーブルのデータを簡単に表示できるようにしました。

詳しくは sample.xlsx をダウンロードしてVBAの標準モジュールを実行してみてください。

基本的にはExcelシート上に表示することで事足りますが、特定フォーマットにして個別の記録を作成するような場合には役立つと思い、HTML生成マクロを作成しました。

それほど機能は多くありませんが、文字と表のセル色ぐらいは変更できるようにしており、使っているところでは、これだけでも十分な仕様です。スタイルシートも準備すればより見栄えは良くなるでしょう。

オフィス内で実装

これまでのプログラムだけでは、VBAが使えないとデータベースのように使えませんので、誰でも簡単に使えるようにExcelシート上にボタンを配置しました。テーブルの呼び出し、修正、ブラウザ表示等へのアクセスを簡単にすることで、誰でも誰でも簡単に使えるようなインターフェースが出来上がりました。

また、Sheet2での入力操作が簡単になるように以下の機能も付けると、より正解に近づいた印象です。

データ型(文字列、数値、日付, ...)と列名の条件により、選択肢(セルの入力規則)を自動で設定
同じく、入力時の注意事項を列名毎にメッセージ表示するよう設定
セルの入力規則では設定できない、複数項目の選択については、セルをダブルクリックすると起動する入力フォームを作成
上記の条件リストはSheet3に集約

Excelシート	操作
Sheet1	データの表示
Sheet2	表示したデータの修正・保存
Sheet3	セルの書式設定リスト

これまで書いたように処理は全てVBAで行われ、他のプログラムは一切使っていません。オフィスでテストしたときも問題なく動作しました。

テーブルがテキストファイルなので、登録データが増えてくると処理が重くなるのかと思いますが、今現在の数千件程度では問題なく動作しています。

もともと数万桁に登る大規模データの取扱は想定していないので、もっと増える場合は本格的にSQLサーバーを設置するなりしたほうが良いでしょう。それまで耐えれば十分です。

今後、動作の不具合が起きたとしても、データベースはExcelとは別に保存されているため、基本的にはインターフェース側であるExcel VBAを修正すれば以前のデータもそのまま使えます。管理も楽そうです。

このような形で、なんとかデータベースを実装し、オフィス内で公開する準備ができました。

終わりに

昨今、データを管理する上でデータベースを使用することは当たり前となっていますが、その当たり前ができない場合にどうしたら良いか、個人的に非常に悩みました。

筆者はアプリ作成者ではなくデータ解析にメインで取り組んでいますので、今回の件は全く初めての取り組みでした。そのため、本来はもっといい方法があるのだと思います。

軽く調べたところでは、Libreoffice Baseを使うことや、高級言語でSQLiteを使う方法などがありました。何れにしても学習コストがかかるため、すぐに実装まではできなかったと思います。使い慣れているVBAだったので、それっぽいことが割とすぐにできました。

SQLiteがJavascriptでオフラインで使えるようになっており(実験段階？)、そうすると入力作業等はjExcelなんかを使って今回と同じような実装できるんじゃないかな、と素人ながら考えているので、その案も今後試してみたいと思います。

https://github.com/jspreadsheet/ce

今回は思いもよらず、データベースの仕組みをはじめから作ったことになったので、かなりの勉強になりました。

願わくば、次回は既存のオープンソース財産を使って楽に開発できんことを。