Mutable_Yunの業務改善ブログ

VBA勉強中の非エンジニア会社員向けのブログです。業務改善についても触れています。

VBA 値の型を変換するときの注意点

今回は値の型を変更するときの注意点について解説します。

この記事は中級です。
レベルについてはExcel VBAの実力(レベル)を定義してみる 初心者~三段をご参照ください。

目次

値の型変換を行うとはどういうことか

値には型が存在します。変数のところで勉強したように、数値や文字列型、日付型、浮動小数点型などがあります。型変換を行うとはどういうことかというと、数値を文字列として扱いたい、日付を文字列として扱いたいと言うような時に、その値を取り扱いたい型に変更することを言います。

値の型変換を行った後の注意点

早速セルに値を書き込むときの注意点から見て行きます。

セルに書き込むとき

今回サンプルとしてあげるのは電話番号をセルに書き込むことです。電話番号一覧のようなイメージで良いです。

普通に電話番号の数値を入れようとしてみるとうまくいかない
Sub 電話番号を書き込もうとするが失敗してしまう()

    Cells(1, 1) = 09012345678
    
End Sub

このプログラムを書こうとすると、VBEの親切機能によって、下のように自動で書き換えられてしまいます。

そもそもプログラムが意図したように書けない例
そもそもプログラムが意図したように書けない

なにやら後ろに#(ハッシュ)が挿入されて、0がなくなってしまっています。この#(ハッシュ)の意味は、「整数なら整数、浮動小数点という型」と言う意味です。

よく分からない数値を入れてしまったので、エクセルさんが気を利かせて下さってしまった状態です。

文字列として入れようとしても、今度はシートの機能によって変換されてしまう

文字列は”で囲むというルールがありました。文字列として取り扱う事によってうまくいきそうです。

Sub 電話番号を書き込もうとするが失敗してしまう2()

    Cells(1, 1) = "09012345678"
    
End Sub

今度はプログラム内で0が取れたり、勝手に#が付くことなく実行できました。しかし、やはりゼロが取れてしまいます。

文字列としてシートに記入してもうまくいかない例
シートの機能によって0が取れてしまう

これは、エクセルシートには、「値を見なせるときは値と見なす」という親切であり、お節介な機能があるために起きます。デフォルトでセルの書式は「G/標準」という書式になっています。これが原因です。

正しい対処法はセルの書式設定を変えてから、文字列として代入すること
Sub 電話番号を書き込む正しい書き方()
    
    Cells(1, 1).NumberFormat = "@" 'セルの書式を文字列に設定
    Cells(1, 1) = "09012345678"
    
End Sub

ポイントはセルの書式設定を先に行う事です。先に値を代入すると先頭の0が取れてから文字列に変換するので、意味がなくなってしまいます。

正しい対処法で記述したプログラムを実行した結果
先にセルの書式を設定してから値を代入した結果

2019/11/25と言った、日付を入れるときも同様です。先にセルの書式を設定してから、文字列として取り扱うようにしましょう。

元から値が入っている場合は、Cstr関数で文字列に置き換える。

VLOOKUPを当てるときの検索先のセルが文字列と数値が混在しているような場合は、一度文字列に変換してから入力するのがオススメです。数値と文字列だとVLOOKUPで検索できない場合がある為です。

予め値が入力されているサンプル
予め値が入力されているサンプル


このような場合はセルの書式設定を文字列に置き換えてから、Cstr関数で文字列に置き換えて、セルに書き込みます。

Sub 元から数値型の値が入力されている場合の正しい書き方()
    
    Cells(1, 1).NumberFormat = "@"
    Cells(1, 1) = CStr(Cells(1, 1))
    
End Sub

実行結果は下記の通りです。

元から入力さえれている場合でも文字列に置き換える事ができた
実行結果

まとめ

まとめると下記の通りです。

  • 値はVBEの親切心とエクセルシートの親切心のダブルで数値で読み取ろうとするので2つの対策が必要
  • セルに値を書き込むときは、先にセルの書式設定を文字列に変更してから、値を文字列に変換して書き込む

今後はVLOOKUPを当てる値のセルなどのセルの書式設定と値の型にも気を配って、よりエラーの少ないコードを目指していきましょう。

質問とうあれば、コメント欄にどうぞ。