知識のかけら

業務アプリケーションをExcel + VBA で構築する際の限界(その１)

・書式4000 問題

まぁ、上記問題はVBAは特に関係ないのだが。

書式4000問題とは、Excel 1ブックに対する書式パターンの数が4000に制限されること。
書式パターンとは、セル書式の組み合わせのことで、フォント・罫線・背景色など。
ブックがこのパターン数を保持している様子で、4000を超えたことに気付かず作業を行って、
次回ファイルを開くと書式がぶっ飛んで壊れているとても怖い現象。

作成時だけの問題と思いきや、VBAでセルの保護を操作するなど、
アプリケーションとして動的に書式を変更する場合、その最大値を押さえておく必要がある。
VBA実行時に、4000書式を超えると実行時エラーとなる。

また、この書式数を明確にカウントする方法が(自分のなかで)確立していないので、
管理できない状況にある。

現状、別に4000書式を保持したブックを用意しておき、VBAで対象ブックに１つづつカウントしながらコピー。
例えば1600コピーできたから、現在の書式は2400だねっという状態。

試行錯誤を行った結果、上記に落ち着いたのだが一応考えた方法を記しておく。
1) VBAでFindFormat を使用して書式を検索

  この方法だと、まず検索する書式パターンを指定する必要がある。この書式パターンが曲者で、
  影響する書式の最大が必要だと思った。基本的に「セルの書式設定」ダイアログ上で指定可能
  な書式が影響すると考えられたが、リンクの挿入※でも書式数が変動することが分っていた。
  これで全てだという明確な仕様が確認できなかったため、この方法はあきらめた。

  ※リンクはどうも下線とフォントパターンで書式数をカウントされているようだ。

2)ブックをXMLで保存してStylesタグの子要素Styleの数をカウント

  一応現在採用している方法と近い数値が取れたのだが、やはり明確な仕様を確認できなかった為
  不採用となる。

業務でXMLの対応付けを使用して、クライアントアプリケーションを実装しているが、

致命的な欠陥が発覚。

それは、パフォーマンス。

3000も4000もマッピングを行うと、セルの移動の度に、紐付けられているXMLを

走査するらしく、パフォーマンス劣化となる。

特に、セルの結合を行ったセルに対してマッピングを行うと上記減少が顕著に現れる。

ビジネスアプリケーションとして考えたときに致命的と思えるものは以下。

・ファイル起動時間
・セルの移動
・XMLのインポート

ちなみにXMLのエクスポート時には、気になるほどパフォーマンス劣化は起こらない。

Excel2003 から XMLとセルをドラッグ&ドロップで紐付けておいて、
１操作でセルの値をxmlへ出力できるようになった。

この機能もVBAから操作でき、類似のメソッドを２つ提供している。

①ファイルにはくメソッド
xmlmap.Export  ファイル名

②VBA中のStringにはくメソッド
xmlmap.ExportXML  String

しかし、このふたつでどうも出力後のxmlの結果が異なるようだ。

具体的には、出力後のxmlファイルの文字コード。
①で出力するとUTF-8 で、xmlファイルの中身の定義も下記のようになっている。

<?xml version="1.0" encoding="UTF-8"　standalone="yes"?>

②からの出力。コードは以下のようになる。

Dim xmlmap as XmlMap
Dim xml as String
set xmlmap = Thisworkbook.xmlMaps("SCHEMA_NAME")
xmlmap.exportXML  xml

このxml の中身をのぞいてみると

<?xml version="1.0" standalone="yes"?>

となっている。
結果、こいつをhttpで通信するよう組んでいのだが、サーバ側で文字化けを起こしてしまった。
問い合わせても、そうゆう仕様ですっていわれるんだろうな・・・

業務でExcelの標準バーやメニューバーの制御を実装した。

実装途中、標準バー・メニューバーが表示されなくなってしまった。

ツール→ユーザー設定のツールバータブを覗いてみると

なんと標準バーが表示されているはずのところが空白。

ネットで検索してみるとボタンの情報は下記に格納されるとのこと。

C:\Documents and Settings\ユーザ\Application Data\Microsoft\Excel\Excel10.xlb

Excel 2002 ： Excel10.xlb
Excel 2003 ： Excel11.xlb

初期化したいときは上記ファイルを削除すればよい。

昨日NHKを見ていると

この人この世界で畑村 洋太郎さんの「だから失敗は起こる」が再放送されていた。

んー初めてこの方を知ったが、仰っていることはかなり興味深い。

なになに、調べてみるとかなり偉い方のようだ。

図書館で本を予約してみた。

タイミングよく（？）うちの会社で、ファイルサーバがクラッシュ。

レイド5で組んでいたはずなのに。

ついでに、バックアップも正常に回復できず、

結局ある大型プロジェクトの成果物がごっそり一週間分ロールバック。

これこそ、わが社の失敗。次に繋げよう。ということで、

原因、対応、影響範囲、損失を残そうと決心したのだった。

かなり遅れたがJava5.0の新機能確認

・総称
・拡張された for ループ
・オートボクシング / アンボクシング機能
・型保証された列挙
・可変引数
・static のインポート
・注釈 (メタデータ)

詳細は後日・・・

前回BeforeCloseの取り扱いに困っています。の続き。

一応回避策は

BeforeClose内にThisworkbook.closeを宣言してやること。

当初、無限ループすると思いきや、なにか内部で回避しているらしい。

かつ、前回の例のBに行く前に自分を閉じてくれるようだ。

ただ、いまだになんでそうなるのかが分かっていない。

BeforeCloseでのリソース開放なんてかなり需要あると思うんだが、

みんなどうやってるんだろ。

なにげに、情報をあさってDataSource設定を行っていると以下のエラー

javax.servlet.ServletException: Cannot create JDBC driver of class '' for connect URL 'null'
at org.apache.jasper.runtime.PageContextImpl.doHandlePageException

かなりはまったが、基本に戻って以下を参照。
http://tomcat.apache.org/tomcat-5.5-doc/jndi-datasource-examples-howto.html

どうも、設定方法が変わっているらしく、属性に設定するようになっていた。

業務でVBAを使用中。

アプリケーションレベルで使用したグローバル変数を開放するのは

Excelが閉じられるタイミングということで

その処理をBeforeCloseイベントに記述していた。

ただ、このイベントは複数回呼ばれる可能性がある。

具体的には、対象プロジェクトを含むExcel（A）と通常のExcel（B）が同時に起動

されている場合。

１．（B）が変更されてSaveされていない状態で、Closeボタンが押下される。

２．一回目のBeforeCloseイベントが呼ばれて、グローバル変数を開放。

３．（B）の変更を保存しますか？のダイアログ表示→「キャンセル」選択

４．（A）の画面に戻る

５．アプリケーションの操作再開

４．の時点で、すでにグローバル変数は開放されているので

実行時エラー。。。。

バイナリファイル

多くのコンピュータは0~255の範囲の数字でデータをあらわす。
コンピュータ内部で処理する文には上記の範囲が全て使えるが
ネットワークに送り出すとなると0~31は通信の段取りを表す制御符号
なのでこの範囲の番号が含まれていると異常動作を招きかねない。
こうしたファイルをバイナリファイルと呼ぶ。

画像や音声などのバイナリファイルをネットに送り出す際はMIMEという
手法で、制御符号の範囲を巧妙に避けている。

～引用 図解雑学 文字コード 加藤弘一 著～