本コラムでは、Excel開発においては専門外と位置づけられるシステム会社への開発依頼について、「Excel専門外のリスクとは!?」というアプローチで、できるだけ、分かりやすい事例でご紹介いたします!
以下は、以前、開発コラムでご紹介いたしましたExcelシステム～納品前ブック点検【重要7項目】です。Excelシステム開発では、他のプログラム開発言語と決定的な違いがあります。それは、プログラム+Excelブック(ファイル)が一対で存在するということです。
この為、開発会社の責任範囲については、お客様から提供された「Excel顧客マスター」、「Excel商品マスター」、「Excel見積書」など、入力・出力を含め、サポート対象となります。
ここで危惧されることは、Excelファイルには、お客様が気づいていない残骸が多数存在するケースが多い、そして、様々な画像や多数の数式が設置されていることから、Excelファイルの健康状態が不健全である事が多いのです。
残骸が原因によるファイルSize肥大が最たる例ですが、数式の参照整合性の矛盾をはじめ、壊れやすい状態になっている(特に図形Object数が多い状態)ケースでは、全ての要素について、その存在の目的を明らかにし、シンプル化を図りながら、プログラミングしてゆく技術が望まれます。
この7項目は、基本的なチェック箇所ですが、弊社でのQAチェックでは、120を超える項目をチェックすることとしており、Excelを構成する要素や、基礎知識はExcelシステムの品質を大きく左右することになるため、十分な注意が必要でしょう。
01:データ接続が無効にされました[データ]>[リンクの編集]
02:[ホーム]>[条件付き書式]
03:[ホーム]>[セルのスタイル]
04:[数式]>[名前の管理]
05:[データ]>[接続]
06:チェック点検_実装シート妥当性
07:チェック点検_Excelブックのプロパティ
本当は怖いExcelシステム開発～専門外のリスクとは～
Excelシステム開発を侮れない理由…。
Excel基礎応用、VBA基礎がないと多くの場合、必然的に、「プログラム記述=コーディング」に頼った設計となります。最悪のケースでは、マクロ記録の乱用です。こうなると、次回改修の料金への影響はもちろん、何よりもメンテナンス性が問題視されます。
弊社では、煩雑なコーディングStep数を減らすための手法として、普通にExcel機能を併用し、VBA文法を理解し、美しいコーディングを心がけています。納品システムは、「正常動作すれば良い」というものではありません。
これは最低条件のことで、弊社が重視するのは、「メンテナンス性」と「実行速度」です。これまで、専門特化の優位性は、専門外リスクの真逆ですので、今回は、「Excel専門外のリスクとは!?」というアプローチで、できるだけ、分かりやすい事例でご紹介いたします!
01. えっ、何故こんな遅いの?
ExcelVBAツール開発では、高速化に必要なコーディング技法・作法が存在します。もちろん設計が大切なポイントを占めることになりますが、「配列処理」ではなく、「セル操作・シート操作」が繰り返される処理では、実測スピードで、100倍以上の差違が生じきます。
02. えっ、プログラム記述で対応?
たとえば、一般的かつ代表的な「売上集計レポート」。単純に、縦計と横計という概念で構成される一覧表のことですが、「関数」を併用せずに、プログラム演算による算出値を出力する設計では、お客様の検証も負荷労力がかかり、Excel本来のメリットも生かされません。
Excelには、便利な関数がたくさん用意されているので、使いこなすことにより、不要なコストを低減することに繋がります。
03. えっ、残額あり。このまま納品?
7つの点検でも紹介していますが、注意が必要な、Excelブック・シート上にある様々な残額。図形オブジェクト、名前の定義、スタイル書式、クエリ残骸、個人情報プロパティ。
これらの多くは、お客様自身でも気づかぬまま運用しているケースが大半ですので、弊社では、開発着手の前に、必ず、ブック分析ツールにより、「リスク要素」を洗い出し、お客様との相談のうえ、開発を進めて参ります。
04. えっ、固定でつくったの?
Excel開発で多く見られる「単純データ転記」という仕様の場合、可変対応という設計が望ましいと考えます。それは、データ転記という点では「何を→どこに」という概念しか存在しないためです。
この為、列番号をお客様が自由に設定できる設計をするだけで、固定設計は可変設計に変わるのです。基幹系システムから出力されるCSVデータを使用するケースでは、フォーマット変更はあらかじめ想定範囲内と考えることで、わずかな列の変更などは、プログラム改修が不要になるのです。
05. えっ、列幅自動調整は、大量データに対し厳禁
Cells.EntireColumn.AutoFit…Excelの列幅の自動調整ですが、無条件でやってはいけません!
大量データの場合、この処理だけで数10秒、スペックの低いPCの場合、「応答なし」といった事象に見舞われる可能性があります。このケースでは、列幅設定マスターを活用し、ピクセル指定で対応すれば、一瞬です。
06. えっ、何故、プログラムでやるの?
Excelアンケート集計など、円グラフ、棒グラフ、折れ線グラフなどを作成して欲しいとのご依頼がありますが、プログラミングで生成してはいけません!
速度の問題ではなく、「メンテナンス性」の問題です。グラフの見た目の色や、フォント、Sizeなど、お客様に自由に設定いただける「ひな型」を活用することで、出力グラフを自由に表現いただくことが可能になります。
07. えっ、セル書式は書式コピペでしょ
O_98.Range(“A7:S7”).Copy
O_A.Range(“A2:S100”).PasteSpecial Paste:=xlPasteFormats
ユーザ指定のセル書式を、明細エリア100行目まで反映するには、これだけ。
“:”で結合すれば、たった1行です。マクロ記録そのままは、VBAプログラミング可読性においてプロの仕事ではありません。
08. えっ、連想配列を使わないの?
弊社のプログラマー採用条件では、必須としています。VBAでのシステム開発においてとても重要なテクニックなので、処理速度が遅いセル操作は、速度だけの問題ではなく、「消費メモリ」という面でも大敵です。セル操作しても良いのは、データ件数が少ない場合のみです。
09. えっ、個人情報プロパティ削除してない?
残っていたら怖いです。右クリック>プロパティ(R)>詳細>作成者
前任の方のお名前が表示された!?意外と知られていないのですが、要注意ですよね。弊社では、もちろん基本チェック点検項目です。
10. えっ、罫線描画まさかのマクロ記録!?
罫線描画(四方囲い枠)はマクロ記録の場合、40行程度のステップ数になります。ビジネス文書において、罫線の目的は、読み手にとっての「見やすさ&分かりやすさ」の配慮ですので、太さや色や種類くらいは、お客様により任意に変更いただける設計が望ましいと考えます。せっかくのExcel標準機能なのですから。
11. えっ、メモリ不足!?応答なし
原因は、物理メモリ不足ですが、設計での回避策は多数あります。ビッグデータであれば配列を垂直分割したり、ThisWorkBookを途中保存したり、ステータスバー描画ならDoEventsやRepaintなど適宜入れるなど…。
そもそも、これはExcelVBAという開発言語の問題ではなく、ビッグデータ取り扱いなどの際の「情報分散処理」という設計が必要なのです。
官公庁入札案件においてビッグデータは珍しくありませんし、弊社でも直近の案件では、「行数700,000件×列数2,000列」という一般的な業務システムではありえへん!?データを取り扱うこととなりましたが、Excelで開けるファイルではなく、CSV形式でしたが、横分解での対応で処理は可能になります。
12. えっ、シート操作はObject名が原則
Worksheets(“51売上一覧表”).Activate
↓
WS_51.Activate
ExcelVBAでは、様々なシートに遷移したり、取り扱い方も多岐にわたります。上段は、よく見るワークシートの遷移ですが、内部的にシート名にObject名(ws_51)を設定することで、少ない文字数でコントロールできます。
利点はもう一つ、物理的なシート名が変わってしまっても(“売上です”)、プログラム処理に影響はありません。これだけで、かなりスッキリしたコーディングになってくるはずです。
ポイントは、長～い文字列であるシート名称で管理するのではなく、原則、番号の序列管理ですよね。
13. えっ、有効データ範囲の印刷範囲は大丈夫?
「印刷してみたら、何10ページも印刷されてしまった…。」これは有効データ範囲の印刷設定のことですが、『印刷を想定していなかった』という言い訳は、弊社では、QAチェック不合格としています。開発技術の前に、ビジネススキル・ビジネスマナーという観点から問題ありとしています。
14. えっ、そのシート関数バージョン依存は大丈夫?
Excelで使用できる関数は、アプリケーションVersion依存のため意識して活用する必要があります。旧バージョンで動作しない関数は使用してはいけませんし、その逆も然りです。
15. えっ、実行中StausBarが無い?
大量データ処理において、全体の何%を処理中なのかが不明だと不安になりますよね。また「実行したものの、時間がかかりそうなので、いったん中断したい。」そんな時は、「中断機能」が必要です。その手法として、弊社では、ステータスバーではなくUserFormを活用します。万が一の実行時エラー発生時の表示残骸を考慮する為です。
16. えっ、Excelエディション64ビット非対応?
昨年迄は32ビットがExcel標準でしたが、Office365普及に伴い、現在では64ビットがExcelインストール時の初期値となりました。API組み込みにより、32/64ビット両対応が図れます。
17. えっ、Excelネットワーク非対応?
これは致命的かもしれません。ローカル環境だけのファイル入出力テストの場合に見受けられます。APIを組み込むことで参照できますが組み込まないと参照できません。
18. えっ、Excelは「排他制御」には非対応?
そんなことありません。ちゃんとできます。更新先のExcelファイルが「読取り専用モード」だった場合、誰かが開いているという事です。ところが、この判定だけでは非常に危険です。何故なら、重いExcelファイルを開くのに [30秒] かかる場合、開き終わるまで誰かが開きにいった事実を検知できない場合があるからです。
このケースではシンプルTextで[宣言ファイル.txt]を作成するなど、誰かが更新を試みにいった、その事実を検知する必要があります。
それでも駄目な場合は、実行時エラー発生の瞬間をもって更新失敗と判断し、リトライを促すなどの設計により、排他制御が可能です。
19. えっ、Excel旧ファイル形式(*.xls)は非対応?
Excel2007発売当時、Excelファイルの拡張子が新形式(*.xlsx)に変わりました。
2003アプリケーションはサポート対象外ですが、旧ファイル形式(*.xls)には害が無いので、読み込む事が出来ない設計では対応データ範囲が制限されてしまいます。
20. えっ、ExcelVBAで自動バックアップ機能?
弊社では、外部DBにAccessデータベース(accdb)を活用する場合、万一のデータ消失に備え、Excelシステム起動時に「自動世代バックアップ」機能を実装するなど、万全を期しています。もちろんプロならば「データ最適化」も忘れてはいけません。
21. えっ、UserForm日付欄に「あいうえお」入力で実行時エラー!?
ユーザーフォームの全フィールド属性についてテストが不十分のための障害で、初心者をはじめ、大ベテランでも考慮のない場合があります。これは、入力値の妥当性チェックが省略されたための不具合ですので、意地悪チェックというテストですぐに発覚する事象です。
22. えっ、マクロボタンが反応しない?
Windowアップデート不具合が原因なら、システムを過去日付に戻し、暫定対応いただけます。原因は様々ですが、Excel専門特化の弊社スタッフが親身に原因究明にあたります。
23. えっ、電話番号のゼロ”0″が欠落!?
Excelセル書式には、計12種類の属性があり、規定値は[標準]です。 これ以外に、代表的なものとして、[数値]、[日付]、[文字列]がありますが、電話番号や郵便番号、場合よっては、0から始まる可能性がある場合は、原則、[標準]ではなく[文字列]が正解です。[文字列]書式を適用することにより、前ゼロが欠落することは回避できます。
弊社のコード設計が、数値から開始しないようにしているのは、このためです。 JANコードなど桁数が多い数値の場合、「指数表示」となりますので、回避するためには、予め文字列書式にした後で、値を反映する必要があります。既に入力済セルに書式適用しても適用されないため、プログラム処理が必要な場合があります。
24. えっ、PGMハードコーディング?
データ抽出条件の指定など、リスト選択肢において、改修が想定される要素については、プログラム内での記述はNGです。
【比較演算子】=、≠、<、>、>=、<= 【ランク】A、B、C 【クラス】さくら、うめ、たんぽぽ
1、要素を増やしたい 2、要素を減らしたい 3、順番を入れ替えたい
などは、設計段階で想定されるため、「選択肢マスター」でのExcelシート管理が基本です。これは、可変対応を心がけるExcelシステム設計という面で、大切な配慮です。
ExcelVBA開発では、Excel機能の併用スキルが重要
Excelブック処理における他言語との違いですが、ExcelVBAのメリットは、Excel機能を併用できることです。
弊社はこれまで数多くのExcel開発をはじめ、中には、前任者の異動や退職といった背景から、改修なども行って参りました。
このケースでは、以下の2パターンがあります。
(A)マクロ独学で学んだ方のプログラム
(B)明らかなシステム会社が開発したプログラム
どちらが改修しやすいかと言うと、紛れもなく(A)ということになります。
独自Functionやクラスモジュールなど高度な技術がふんだんに記述されていることは少ないため、容易に分析が可能です。
が、(B)の場合は、手出しできないケースが大半です。
いずれの場合でも、大切なことは「コーディングStep数が少ないこと」これが重要です。
本コラムでは、弊社が留意している点をご紹介させていただきました。今後も、サンプル配布などで、その具体的な違いを知っていただくため、コンテンツをアップして参りますので、乞うご期待ください!
