Excel VBAメモ:Format関数での0埋め

今日も仕事で残業でした。
今日は早く帰って良いよと言われてたのに残業してました。

VBAで書いてて詰まったところがあって途中で投げるのも気持ち悪くて残業しました。

詰まったといっても難しいプログラミングをしていたわけではありません。
簡単な箇所で詰まってました。


Format関数
値の表示形式を変換する関数

今回詰まった箇所は「1〜3桁の数値を0埋めでExcelのセルに表示させる」…それだけのものでした。
まぁ普通に考えて下記のコードで出来るはずです。

Dim sheet As Worksheet
Dim cnt As Long
cnt = 11

sheet.Cells(1,1).Value = Format(cnt,"000")
'シートの(1,1)のセルに「011」と表示されます

――表示されるはずなのに、今日は「11」のままセルに表示されたのです。
以前に同じ処理をやったときには問題ありませんでした。

…悩みました…こんなので2時間も考えてしまったんですよ…
で、原因ですが…。

――結局これはコードに間違いはありませんでした。
これはExcelさんが得意な「余計なお世話な機能」のせいです。

…Excelさんって日本語入力でセルに数字を入力しても自動で英数表示に変換するじゃないですか…
あれですよ。
今回の「011」は自動で「11」に変換してくれているんです。…お節介な。

となるとまず思いつく解決案は、Excelのセルを右クリックして[セルの書式設定画面]から[表示形式]を「文字型」にするというのが挙げられます

しかしVBAだけで処理したい場合もあるかと思います。
その場合は以下のように記述します。


Dim sheet As Worksheet
Dim cnt As Long
cnt = 11

sheet.Cells(1,1).Value = Format(cnt,"'000")
'シートの(1,1)のセルに「011」と表示されます

Format関数の2次引数の「000」の前に「'」が入れるだけです。
こうするとセルには「'011」と出力されます。
「'」は文字の先頭に入力することで「'」以降の文字を文字型として表示させることができます。

ちなみに「+」や「=」などの四則演算子も「'」を前に入力すればセルに表示できるようになります。




コメント
管理者の承認待ちコメントです。
  • -
  • 2008/04/01 12:46 PM
 ありがとうございます!
 大変参考になりました!
  • kaki
  • 2011/08/19 1:46 PM
参考にさせていただきました^^
ありがとうございます!

私の場合見かけのセルの値ではなく実際の値を処理に使いたかったのでシングルクォーテーションが邪魔になってしまい...先にセルの書式を文字列に変えてから、フォーマット済みの数値を書き込みました。

With Cells(1, 1)
.NumberFormatLocal = "@"
End With
With Cells(1, 1)
.Value = "000123"
End With

2度手間ですね...^^;
  • さむがり
  • 2016/02/18 7:48 PM
コメントする








    
この記事のトラックバックURL
トラックバック