2010年01月

以下、Excel2003までの話です。2007では起こらない(はず)。

先輩の使っているファイルで、見た目以上にやけに重たいのがありました。よくある話ですが。
特徴的なのは、カーソルを移動させたりズームしたりするとやけに画面がカクカク。
描画系ってことは、なんか図形オブジェクトがゴミになってるんだな・・・
と、時間があったので調べてみました。

図形オブジェクトって、シート上で見えないままゴミになって残ったりするので要注意です。

イメージ 1

例えば上のような四角形が作ってあったとして、列ごと削除したといたします:

イメージ 2

一見、列と一緒に四角形も消えたかに見えます。ほんとに消えたのか?
「オブジェクトの選択」モードにして調べてみます:

イメージ 3

すると・・・左端になんか残っとるやんけ!行を削除すると、下右図のように上端に残っとります:

イメージ 4

で、行・列両方削除すると左上端に残ります(下左図)。
どういう状態なのかとVBEで調べてみると、下右図のような結果に:

イメージ 5

左から&上からの位置がゼロ、幅も高さもゼロ。これじゃー見た目では気づかないっすよね。
おそろしいのは、こうして端っこに見えない状態で残ったまま範囲をコピーした場合。
例えば行を削除して上端にゴミが残ったまま、列ごとコピーすると・・・

イメージ 6

見えないゴミも一緒にコピーされます。
気づかないままこれを繰り返すと、ゴミが際限なく増え続けるというw

これは図形のプロパティで「セルに合わせて移動やサイズ変更をする」が選択されている場合:

イメージ 7

他の選択肢ではこのようなことは起こりません。
でも「セルに合わせて移動やサイズ変更をする」がデフォルトなんですよね。それが問題の原因。

で、冒頭の先輩のファイルにもこの状況が起こっていたことが分かりました。
左上隅に、表示されていない図形オブジェクトが7000個以上たまってました・・・^^;
ちなみに図形オブジェクトの数はVBEで:
?ActiveSheet.Shapes.Count
みたいにやれば簡単に。
まとめて全部削除したところ、1MB以上あったファイルが200KB以下になりました。
画面がカクカクするのも直りました。ほんと、おそろしいっす。

2007ではこんな心配はないようです。
列ごとや行ごと削除すると、図形オブジェクトも一緒に削除されます。
それと、「オブジェクトの選択と表示」っていうのがあって:

イメージ 8

シート上の図形を一覧で把握できます。この機能、もっと早く導入してほしかった・・・

ところで、シート上の図形オブジェクトをコードからまとめて削除しようとする場合、
ActiveSheet.Shapes.SelectAll
Selection.Delete
って書くことになるようです。

なんか、Shapes.Deleteとか書けないの?と一瞬思ってしまうのですが、そんなわけもなく。
調べてたら、とても勉強になるページを見つけたのでご参考まで:

CAのテンプレをいじっててちょっと気づいたので。

前に半角カタカナがフォント化けを解除するということを書いたのですが、7では状況が違う模様。
先頭に明朝化けを起こすブロック文字を置いてXPと7で比較しました:

イメージ 1

!?
どういうことなの・・・

文字コード表でMS PゴシックとSimSunの同じ領域を見たところ:

イメージ 2

もちろんSimSunに半角カタカナなんてありません。

通常=MS Pゴシック・明朝化け=SimSun・丸文字化け=Gulimという認識でいたのですが・・・
少なくとも7では違うんですね。これは悩ましい。

XPの方では半角カタカナの威力は強力な感じです。
明朝化けを引き起こした状態で、半角カタカナの効果を検証したところ:

イメージ 3

化けるどころか他のコメの明朝化けをちゃんと解除しています。
んー・・・もっと研究しよ。

ささいなことですが・・・

セルの列幅に納まりきらなかった文字列は、隣のセルにはみ出して表示されます:

イメージ 1

これを避けるために、セルの書式設定で「折り返して全体を表示する」にしたりとか:

イメージ 2

「縮小して全体を表示する」にしたりとかするわけですが:

イメージ 3

個人的にはあまり好きではありません。
行の高さが変わったり、文字の大きさが変わったりしてしまうのがイヤで。

文字列が途中で切れてもいいから、はみ出した部分は隠したい。
そんな書式設定はないわけですが、世の中同じことを考える方はいるようで:


Accessのテーブルみたいにこうはならんのかと:

イメージ 4

上のリンク先に答えが書いてありますが、とりあえず隣のセルになんか入れておけば隠れる:

イメージ 5

フォントの色を白にしてしまえば、見た目は空のセルに見えます。
決まったダミーの値を入れておいて、その値の時だけ白になるように条件付き書式で設定すれば、データの入力に使えないこともありません。

あるいはセル結合をしても隠れる:

イメージ 6

シート全体を編集するときに難があるので、状況によりけりですが。
それにしても、なんで結合するとこうなるんだろ。仕様なんでしょう。

隣のセルになにか入れておくパターンで、その列全体を隠してもいいのかも。
と言っても、列を「非表示」にすると冒頭の図と同じ状態になってしまいます。
なので可能な限り列幅を狭くしてみるとか:

イメージ 7

拡大すると隠れてないんですけど:

イメージ 8

これはこれでありかも。
ちなみに列幅の最小値というのは、環境とか標準のフォントを何にしてるかとかによって変わります。
行高や列幅について参考になる記事:

そう言えば、ずっと前に「偶数丸め」の話を書いたのですが→この日記
また丸める話です。元の数値(整数)を5単位で丸めるっていうケースがありました。

下図のように、5の倍数ごとに前後二つずつの値も同じ5の倍数に変換します。
ワークシート上で右側のような式でやってみました。

イメージ 1

元の値から5で割ったときの余りを引けば、当然元の値以下で最初の5の倍数になります。
それを図のように足したり引いたりして“二捨三入・七捨八入”になるようにしています。

ああ、簡単だな・・・と思ったのですが、同じことをVBAのコードの中でやろうとしたらつまずきました。
なんか最初のところの結果が違います:

イメージ 2

問題は余りを求める部分でした。
MOD関数をVBAではmod演算子にしたのですが、被除数(割られる数)がマイナスだと結果が異なりました:

イメージ 3

MOD関数とかmod演算子なんてふだん使わないんで、今まで知らなかったんですが・・・
実はよく知られている話のようでした。


※いちおう公式にも解説がありますが、機械翻訳なので難あり→サポートオンライン

しかし数学なんだから、どっちかに決めればいいんじゃないの?と素人的には思ってしまうんですけど。
なんかそういう話でもないみたいです。頭悪いんでわからないんですが。
とにかく勉強にはなりました。

この件を調べてたら、やさしい解説を見つけたのでご参考まで:

こういうCAを作りました:

イメージ 1

big10行、見た目通り2コメントで作っています。
いつもやってるのと変わりませんが、U+2630という特殊記号を使ってみたかったという:

イメージ 2

ちなみにこの8つ、易学の「八卦」の記号だそうです。知りませんでしたw

ところで上のサンプルでは、先頭にスペース(全角スペースのU+3000です)を置いています。
少なくともXPの場合、全角文字とくっつけないと下図のようにお豆腐になってしまいます。
7では下図右の通り単体でも大丈夫な模様ですが:

イメージ 3

さらに問題なのは、フォント化けした場合:

イメージ 4

7では無問題ですけど、XPでは明朝化けで透明化・丸文字化けで『「』化します。
ゴシックのままなら大丈夫。
上の一番上で、先頭に置いてるのはフォント化け解除文字「・(なかぐろ)」です
幅を合わせるため二つ置いてます。

イメージ 5

後ろに全角文字を置いたところ。こうすればXPでも表示はされるみたいです。
ただしゴシックより微妙に幅が短いようです。

イメージ 6

この記号、CAで使うなら幅的にゴシックじゃないとダメな感じで。
この実験をもとに冒頭のCAを作りました。
つまりpurple部分はゴシックを強制しています(yellow部分は丸文字化け)。

なお、big10行ではコメ本体の左右に表示されない部分があります。
ここにスペースなどを置くわけですが。
いつもは普通の全角スペース(U+3000)を使っています。
ゴシックの時は幅が短いですが、明朝化け・丸文字化けさせれば幅が伸びて安定するからです。

が、purple部分はゴシックだったのでスペースはU+2001を使いました。
前に2001は化けたり縮んだりするからダメだみたいなことを書いたのですが:

イメージ 7

こうして整理してみると、ゴシックを強制(フォント化けを回避)しておけばXPでも7でも大丈夫なんですね。
と言うわけで、また一つ発見でした。

↑このページのトップヘ