Excel

【変数宣言Dimを3分で徹底解説】ExcelVBAを知る始めの一歩

しんたろ。
この記事でわかること
  1. 変数の型を指定する意味がわかる
  2. 各変数の型の特徴がわかる
  3. 変数のよくある疑問とその答えがわかる

 「変数ってどういうときに使うのか。」

変数の仕組みと、変数を使う知識を持っていると不測の事態等を予防するためには、変数の型の指定は必須です。

 Excel VBAでは型を指定しない場合、変数の型はバリアント(Variant)型がデフォルトとなります。

しんたろ。

バリアント型 = 全てのデータに対応した変数の型のこと

 ですがバリアント型だけでマクロを組んでしまうと、本来であれば関係のない値や命令までも変数の値として処理してしまい、予期せぬ不具合を引き起こす可能性があります。

 また変数の型を指定することにより、PCは「指定された型によって決められた容量」を変数に割り当てるだけで良くなりますので、処理速度の向上につながります。

しんたろ。

変数の使い方をマスターしてExcelVBAを正しく使おう

 ExcelVBAについて詳しく解説を見たいという方は、[保存版]Excel2013のVBAを徹底解説!にて解説しています。

それでは変数の解説に移ります。

ExcelVBAの「Dim」とは

ExcelVBA 変数の説明

 「Dim」とは「Dimension」(寸法)の略です。

ExcelVBAでは、「どのような値だけが代入されるのを許すか」を変数で宣言するために使用します。

 変数は端的に言えば値を入れた箱です。数値・文字列・小数など変数に入れて、いつでも取り出せるようにしておく状態にします。仕組みを理解しておくことが大事です。

宣言をするための構文例

「as」のあとには【変数の型】や【オブジェクト名】を指定します。

 実際にDimを使った宣言の例を以下にて紹介します。今回は文字列を格納できる変数「String型」を使用します。

ExcelVBA MSB表示
ExcelVBA sheet名変更

また以下のようにすると、一度に複数の変数を宣言することもできます。

先程メッセージボックスで表示した内容を変数2つに宣言してみましょう。

ExcelVBA MSB表示2

初心者によくある間違い

 注意点として、下記のように変数を宣言するところに纏めて入れる人がいます。

このように宣言した場合、最後にきている「c」のみがString型となり、他の「a,b」はデフォルトの「バリアント型」となるため、型を宣言していないのと同じ状態になります。

ExcelVBAでは、「Dim」を使用しなくても変数を使用できますが、プログラミングではどの言語でも変数は必ず型を指定して使用することが一般的です。

しんたろ。

次は、ExcelVBAにおける基本の型を解説します。

ExcelVBAの基本の型

 VBA では値を扱う場合にデータ型があります。例えば何か計算をする際は「数値」ですが、何かを表示する場合は「文字列」となります。同じ『1』という値であっても数値としての『1』の場合もありますし、文字の『1』の場合もあるわけです。

 ここでは Excel VBA におけるデータ型について解説します。

VBAで利用できるデータ型の種類やバイト数を解説

データ型VBAでの表記値の範囲(上限下限)領域サイズ
文字列型String文字列10バイト+文字列の長さ
プール型BooleanTrue or False2バイト
バイト型Byte0から255の正の整数1バイト
整数型Integer-32,768から32,767の整数2バイト
長整数型Long-2,147,483,648から2,147,483,647の整数4バイト
単精度浮動小数点型Single-3.402823E38
から
-1.401298E-45

1.401298E-45
から
3.402823E38
4バイト
倍精度浮動小数点型Double-1.79769313486231E308
から
-4.94065645841247E-324
4.94065645841247E-324
から
1.79769313486232E308
8バイト
通貨型Currency-922,337,203,685,477.5808
から
922,337,203,685,477.5807
8バイト
日付型Date西暦100年1月1日から西暦9999年12月31日 (日付と時刻)8バイト
オブジェクト型Objectオブジェクト参照するデータ型4バイト
バリアント型Stringあらゆる種類の値を保存できる型22バイト+文字列
ユーザー定義型Stringユーザー定義の型要素に必要な数
引用:Microsoft公式ページ
しんたろ。
  1. 文字列
  2. 数値
  3. 日付
  4. プール
  5. オブジェクト

データ型は5種類あると考えておいてください。

そもそも変数は何のためにあるのか

ExcelVBA 変数の説明2

 プログラムは、1度だけ動いて終わりではなく何度も反復して動かされるのが前提に作られます。

 例えば、計算機のプログラムは「何度も」色々な計算を行いますし、お問い合わせフォームのプログラムは色々な人からのお問い合わせを「何度も」受け付けます。

もしプログラムに「1 + 1の計算をしろ」と書いた場合

 そのプログラムは「何度」動かしても「1 + 1」の計算しかしません。何度動かしても「2」と答えるだけのプログラムですね。

しかしここで『「箱a + 箱b」の計算をしろ』と書いておく。

 箱aと箱bに好きな数字を入れると、どんな数字も計算できるプログラムになります。

しんたろ。

『箱aに4、箱bに5』をいれると、『9』という結果がでますね。

この箱のことを『変数』と呼びます。毎回中身が「変わる」ので「変数」と呼ばれます。

プログラミングの注意点は?

プログラムを書く時に心がけていること6選
  1. 1度やれば良い処理を2度以上しない
  2. 同じことを何度も書かない
  3. テストは積極的に書く
  4. コメントの書き方には注意を払う
  5. プログラミングとは、すなわち「命令する事」
  6. 1つの関数・メソッドに機能を詰め込みすぎない
しんたろ。

4のコメントについては、書きすぎても見にくいし書かなすぎても解りづらいので、程よく誰でもわかるようにコメントを残しておきましょう。

プログラミングのアルゴリズム(考え方)

 「アルゴリズム」とは、問題解決のための計算方法や処理方法で、プログラムの基盤と考えると良いです。

 例えば交通機関を利用する際に「乗り換え検索」を利用した時に、所要時間・料金・乗り換え回数ごとに複数の検索結果が出てきます。

何を優先させるかによってアルゴリズムは変りますので、アルゴリズムは目的の動作を行うための一連の命令であり、プログラムにとって重要な役割を果たします。

プログラムにおけるアルゴリズムの必要性3つ
  1. パフォーマンス向上
  2. 保守性を高める
  3. プログラムの処理能力に大きな差を無くすため
①パフォーマンス向上

 アルゴリズムはパフォーマンスを向上させて結果が出るまでの時間を短縮することが出来ます。

コンピューターやサーバーには「サイバースペース」と呼ばれる仮想空間があり、この中にデータを格納しているデータベースが存在します。プログラムで命令することにより、コンピューターはデータベースに必要なデータを取りに行きます。

しんたろ。

そのデータを取りに行く回数が少ないほどパフォーマンスが高いことになります。

②保守性を高める

 プログラムには機能の追加や変更、障害発生への対応がしやすい「高い保守性」が必要であり、優れたアルゴリズムは問題が置きた時にすぐ対応できます。

  • コンピューターにおいて、正常な状態を保つことを「保守」。
  • 「保守性」は保守のしやすさ。

 プログラムにおいて、大切なことは「分かりやすさ」です。難解で複雑なプログラムは、後から修正する際に作った本人でもすぐに対応できない可能性があるため保守性は重要な要素です。

③プログラムの処理能力に大きな差を無くすため

 アルゴリズムは、プログラムの処理能力に大きな差を無くすことができます。

 プログラムは内容によって処理速度が変わりますが、近年の容量の大きいコンピューターは処理能力速度が上がったと感じる人も多いですよね。

処理速度には動作環境も関係しますが、プログラム開発時において重要なのはアルゴリズムの計算時間の指標である「計算量」です。

 計算量を適正化することでデータ量が増えても処理能力を高めることができます。

しんたろ。

何回も同じ動作をする際は、for文やwhile文で回すと処理の効率が上がります。そういえば、for文で「i」や「j」を使いますよね・・・。

添え字に「i」を使うのはなぜ?

 for文などでよく使用する「i」。整数に当たる英語「 integer」の頭文字から来ています。そこからプログラミングへと輸入されて、そのような慣習が広く受け入れられています。

そのような規約などがあるわけではないので、別に従う必要はありません。

この2重のforループで実はjと書くべきところをiとミスタイプしているのですが

  • 「i」と「j」が似ている字面なので、見た目では区別がつきにくい
  • 「i」と「j」という名前では、どちらが行か列なのか変数名から分かりづらい
  • しかもこの2文字はキーボード上で隣り合っているのでミスタイプしやすい
  • たった1文字なのでミスタイプしてもコンパイルエラーで発見できない

と、意外とデメリット祭りです。

しんたろ。

行と列を表すなら、「rowindex」「columnindex」を使用するなど、関数を使う手もあり。

ExcelVBA「Variant」型の解説

 先程解説したデータの型でもあるように、変数には「型」という仕組みがあります。

変数は何かを入れる箱ですが、例えば紙袋に麦茶を入れると破れるので入れられませんし、ギターケースにコントラバスは入りません。

変数も入れる型が決まっているので、宣言する際に適切な方を指定しなければなりません。

今回は何でも入れられるバリアント型「Variant」の紹介とデメリットを開設します・

よく使う型

  • 整数を入れる変数  → 長整数型(Long)
  • 文字列を入れる変数 → 文字列型(String)

 この2つを覚えておけば、ほとんどのマクロはこれで十分です。

実例を見ていきましょう。

 長整数型(Long)型整数しか格納できない点に留意してください。小数点以下の数値は格納できません。次のマクロで確認してみましょう。

ExcelVBA 変数 msb1

「10÷4」は「2.5」ですが、変数「tmp」には「2」しか入っていません。このような場合は、小数点以下の数値を扱う変数は単精度浮動小数点型(Single)を使用します。

ExcelVBA 変数 msb2

プール型(Boolean)は、「True」または「False」のいずれかを格納する時に使用します。

ExcelVBA 変数 msb3

日付型(Date)は、日付や時刻のデータを格納するときに使います。

ExcelVBA 変数 msb4

セル自身やワークシート自身など、オブジェクトを格納するときはオブジェクト型(Object)を使います。

ExcelVBA 変数 msb5

型は覚えなくていい

 正直な所、型を覚えるのは本当にしんどいですよね。

そんな方に冒頭話したバリアント型「Variant」あります。整数だろうが文字列だろうが小数点以下だろうが配列だろうがオブジェクトだろうが、何でも迷うこと無く格納できちゃいます。

ここで一つ思うはずです。

そんな便利な型があるなら、最初からこの型だけ使えばいいやん!

そうなんです。どの型を指定すればいいかわからないときは、「バリアント型(Variant)」を指定すれば良いです。

 バリアント型の変数を宣言をする時は

と書きますが、VBAでは型の指定を省略すると「バリアント型とみなす」というルールがあります。

 ただデメリットもありますので詳しく解説します。

ExcelVBA「Variant」型のデメリット

 何でも格納できる万能のバリアント型(Variant)は、実は「あまり使うべきではない」とも言われています。本来であれば型指定すべき変数を、バリアント型(Variant)として扱うデメリットがあります。

  1. マクロの実行が遅くなる。
  2. ご動作を招く原因になる。

 バリアント型(Variant)変数は、その他の型に比べて実行速度が遅くなります。しかし、非常に高速なCPUやメモリを搭載した現在のパソコンでは、その差はごくわずかです。

また誤作動の原因にもなりやすいのですが、一例を挙げると次のようなケースです。

【Sample8実行の例】
ExcelVBA 変数 msb6
ExcelVBA 変数 msb7
ExcelVBA 変数 msb8
【Sample9実行の場合】
ExcelVBA 変数 msb9
ExcelVBA 変数 msb10
ExcelVBA 変数 msb11

 InputBox関数は、マクロ実行中にユーザーからデータを受け取る命令です。InputBoxは、入力されたデータを文字列形式で返します。Sample8では、変数Aと変数Bがどちらも長整数型(Long)で宣言しています。文字列型で返された「100」と「200」は、自動的に型変換されて、整数として2つの変数に格納されます。変数Aと変数Bには整数が入っているのですから「A + B」で足し算が行われて「300」が表示されます。

 一方のSample9は、変数Aと変数Bがバリアント型(Variant)で宣言されています。InputBoxは文字列型を返しますので、「100」と「200」はどちらも文字列として格納されます。文字列に対して「+演算子」を使うと、「&演算子」と同じように文字列同士が結合されます。したがって「100」と「200」が結合されて「100200」となりました。

 変数の型指定によって実行結果が異なるのは事実ですが、誰もが、毎回必ず遭遇する現象ではありません。たとえば、次のようなケースでは問題ありません。長整数型(Long)バリアント型(Variant)も、同じ結果になります。

【Sample10の実行結果】
ExcelVBA 変数 msb12
【Sample11の実行結果】
ExcelVBA 変数 msb113
しんたろ。

てことは、バリアント型でもOKということです。

ExcelVBAの知っておきたい文字数の制限や限界値

 コード自体はシンプルで短く書き上げることが理想ですが、どうしても長くなる時に限界値を知っておくと安心ですよね。よくある疑問を順に解説していきます。

ExcelVBA「DimString」の文字数は?

 String型はVBAリファレンスでは、「可変長文字列は、最大で約 20 億 (2^31) 文字を格納できます。」と書かれています。

しんたろ。

最大で約20億の「約」、この際はっきりさせようじゃないか

そもそもどうやって確認するか・・・。世に溢れている情報には2つの教訓があります。

  • 書かれていることを妄信してはいけない。
  • 「約」と書かれている場合は、それなりの事情がある。
あずたろ。

ExcelやVBAに限ったことではないね

結論:Excelが64bitなら『2,147,483,635』、しかし、32bitの場合は数億台でエラー

 「最大で約20億(2^31)文字」これを読んでピンきた方は相当にお詳しい人だと思います。

この20億(2^31)という数値は、Long型の数値の最大数になります。ではなぜ、String型の最大文字数がLong型の最大数と関係があるのでしょうか。

  • 最大で約20億(2^31)文字→『2^31=2147483648』
  • Longの最大数 → 『2^31-1=2147483647』 

 String型変数のアドレスには、実際の文字列が入っているメモリアドレスが入っています。実際の文字列があるアドレスの直前には4バイトの文字列長部分があり、文字列の直後には2バイトの終端があります。

「変数のアドレス」引用:VBAにおける変数のメモリアドレスについて

4バイトの文字列長、これは「Long型の数値」です。

 つまりString型の最大文字数は、Long型の最大数『2^31-1=2147483647』ここから終端の2バイトを引いた、『2147483645』これがString型の最大文字数になります。以上が64bitの場合のString型の最大文字数です。

 では32bitの場合はどうなるか。32bitの制限でメモリは4GB(アプリは2GB)が限界となります。Excelの他にもChromeやらその他のソフトでメモリが使われています。メモリを超えるサイズの文字列は作れません。

つまり残っているメモリ次第という事になります。

しんたろ。

2バイト1文字なので、残りが1GBなら5億文字程度が限界です

 Excel以外に色々なアプリが沢山動いていればもっと少なく、3億文字くらいでもエラーになります。PC環境、実行のたびにも違った限界値になります。32bitの場合は、2億文字くらいが限界と考えておいた方が良いでしょう。

あずたろ。

よくある疑問は他にどんなものがあるの?

しんたろ。

文字数やバイト数、列数などたくさんあるので順に解説します

ExcelVBAの1行の最大文字数は?

Visual Basic コードの物理行の最大文字数は 1,023 文字
行の文字数が多すぎる。 スペースとアンダースコア ( _) で表される行連結文字を使用すると、物理行を連結して長い論理行を作成することができます。 1 つの論理行として連結できる物理行の数は最大で 25 行 (連結文字 24 個) です。 したがって、論理行には、合計で 10,230 文字まで格納できます。 それ以外の場合は、行を個々のステートメントに分割するか、一部の式を中間変数に割り当てる必要があります
引用:Microsoft公式ページ

「MsgBox」の文字数制限は?

MsgBox関数の構文 名前付き引数 prompt に指定できる最大文字数は、1 バイト文字で約 1,024 文字

ExcelVBA「string型」の最大文字数は?

コマンドライン引数として使用される環境文字列の最大文字数は 32768 文字

ExcelVBA「cells」の文字数制限は?

255文字
ただし、「元の値」に指定する文字列が255文字に収まっていればOK

ExcelVBA「formula1」の文字数は?

・Office2016:255文字の制限なし
・Office365(19/4上時点):255文字の制限あり
Excelの仕様と制限についての詳細:Microsoft公式ページ

ExcelVBA「utf-8」は何バイト?

UTF-8は半角カナは3バイトで全角は3から5バイトで半角英数は1バイト
「バイト数」という言葉は文字コードによって内容が異なります。 Shift-JISなら全角文字は2バイトで半角は1バイト、UTF-8は半角カナは3バイトで全角は3から5バイトで半角英数は1バイト、そしてDBCSは半角全角登録ず全て1文字を2バイトで表現します。

Lenの戻り値は?

引数に文字列型を指定すると文字数を、それ以外の型を指定するとその型のバイト数を返す。
 引数「文字列」が空文字 “” なら 0 を返します。 引数「型」がバイト型なら 1を、整数型なら 2 を、Long 型なら 4 を返します。

ExcelVBAの列数最大は?

2007年以降なら1048576、2003年なら65536

まとめ

この記事でわかること
  1. 変数の型を指定する意味がわかる
  2. 各変数の型の特徴がわかる
  3. 変数のよくある疑問とその答えがわかる

 変数はVBA初心者にとって最初にぶつかる壁です。焦らずに確実に覚えていきましょうね。

ExcelVBAについての内容やオススメの勉強方法など詳しく解説していますので、ぜひこちらから確認ください。

全会社員を残業地獄から救いたい
本ブログの有効活用方法
1
Concept
初心者に分かりやすい解説

しんたろ。ブログでは「少しでも残業を減らしたい」「時間効率を上げて新しいことにチャレンジしたい」「仕事で評価を上げて年収を上げたい」そんなあなたに向けて私が学んできたノウハウを最短ルートで教えるExcelブログとなっています。

覚えておくべき関数や操作方法を徹底解説!

2
Concept
Excelを最短で
習得する方法を解説

「独学で習得できる気がしない」そんな方に効率よく習得できる記事を書きました。

本や動画を紹介していますが、やはり一番効率の良い習得方法は、講師にわからないことを質問できる『オンラインスクール』です。

3
Concept
Excelが大活躍!
会社員でも不就労所得を作れる。

「会社の収入だけでは不安」そう思い出会った副業がブログでした。書いた記事たちが、あなたが寝ている間もずっと働いてくれます。『不就労』の完成です。

記事を書く際の様々な分析にExcelがとても役立ちます。私は2023年末までにブログで脱サラする予定です!

あなたもブログで脱サラしましょう。

お問い合わせ

Contact

問い合わせより直接相談していたくと
無料で相談にのりますよ♪

お気軽にご相談ください♪

今日はこのへんで。では


ABOUT ME
しんたろ。
しんたろ。
脱サラ目指す会社員
デスクワークに追われ、仕事が極端に遅かった私がExcelを学んだら会社から評価され残業も大幅削減を実現!早期FIREするための副業もしています。 あなたの人生も好転させたい!ぜひ本ブログをご活用ください。
記事URLをコピーしました