[VBA] 1から100の数字を出力する。ただし、12の異なる方法で。
🐶
「エクセルで、A1セルからA100セルに、1から100の数字を1つずつ出力する」
これをVBAで書いてみましょう。ここでは、とっても基本的な書き方から、ちょっと変わった書き方まで、思いつく範囲で書いていきたいと思います。全部で12の方法で書いてみます。ちなみに、一番短いものは、1行で書けます（Subの行とEnd Subの行は除いて）。
なお、普段は、IT系の内容は「xxxx7」という名前でブログを書いているのですが、思った以上に記事が長くなってしまったので、こっちのブログに書いてみます。普段は888文字で書いていますが、たまに8888文字で書くこともあり、今回はその8888文字バージョンとなっています。
目次を見ちゃうと書き方がわかってしまうかもしれないので、ちょっと考えてみたいという人は、ひとまずこの下にある目次を見ずに考えてみましょう。
[目次]
For文
まずは、王道中の王道。For文を使ったものですね。たいていの人が一番初めに思いつくのはこれだと思います。
特に解説はいらないでしょう。一番オーソドックスな書き方で、テキストで問題として出題されていたら、これが模範解答として載るはずです。これが書けないと、VBAが使えるとは言えません。「マクロの記録」から頑張って抜け出しましょう。
Do Loop文
つまらない言い換えですが、繰り返しといえば、For文とこれです。一般的なプログラムではwhile文ですが、VBAの場合は、"Do Loop"になります。
ただの言い換えでは面白くないので、セルの指定方法を少し変えてみました。i番目のセルを指定するときには、Cellsを使うことが多いと思いますが、上のようにRangeを使って書くこともできます。個人的にはあまりこういった書き方はしないのですが、どの列を指定しているかがわかりやすいので、このように書く人もいます。
GoTo文
繰り返しというと、For文かDo Loop文を使うのが一般的ですが、こういう書き方もできなくはないです。
現在のプログラマーでわざわざGoTo文を使う人はいないかもしれません。昔は誰もがスパゲティコードに苦しめられましたが、GoToはそれを生み出す原因の一つですからね。ただ、VBAでは、エラー処理などの時に、GoTo文を使う場面は出てきます。そういう意味では、GoToは覚えておくべき事項ですが、今のような処理の時には普通は使わないでしょうね。現実的ではない書き方です。
GoToのあとにあるのは、行ラベルです。設定した行ラベルに飛ぶことができます。上の例では「MyLabel:」に飛んでいくということですね。ラベルは特に何か処理をするわけではないので、一度目は素通りするだけです。その後、GoToのところまで来ると、ラベル行に戻ってから再度処理が進みます。なので、For文と同じような処理の流れになります。
ちなみに、エラーの処理では、次のように使います。
「On Error GoTo」とは、エラーがあったときに、そのあとに指定する行ラベルにジャンプするということです。エラーが発生すると、通常はエラーメッセージが表示されて処理が止まってしまいます。しかし、上のように書けば、指定した行ラベルに移動して処理が続きます。エラーが出ない場合でもEndの行まで行ってしまうので、必要があれば「Exit Sub」と書いて、ラベルに移る前に強制的に処理を終わらせます。GoTo文が使われるのは、こういう場合に限られ、For文の代わりに用いられることはないでしょう。
再帰関数
再帰関数とは、簡単に言えば、自分で自分を呼び出す関数のことです。VBAのコードを書く場合にはあまり出てきませんが、他のプログラムでは登場することもあります。
2つ目の処理の最後で、自分自身を呼んでいます。上のセルに1を足して次のセルへ移動する、という処理を何度も繰り返しているわけですね。今の処理が終わる前に別の処理を呼ぶことになるため、終わらない処理がどんどんたまっていきます。そのため、呼び出す回数が多くなるとエラーが出てしまったりするのですが、VBAでもこういう書き方ができるということを覚えておくのはいいことだと思います。
再帰関数は、フィボナッチ数列や階乗の計算などの例を使って説明されることが多いですね。気になる人はググってみましょう。VBAのコードで解説しているサイトは少ないかもしれませんが。
文字連結
今度は少し違ったアプローチです。パッと見ると、For文を2重にしただけのように見えますが、実際にはもう少し不思議なことをしています。
「i & j」というのは、iとjを文字として連結している、ということです。つまり、"00"という文字から"99"という文字までを作っているということです。見た目は数字ですが、プログラムではテキストとして扱われています。そのあと、1を足していますが、この足し算の段階では数値として認識され、結果として1から100までの数字が出来上がる、という流れになっています。
数字の見た目をしたテキスト（例えば"00"とか）でも、数値の演算の際には自動的に数値に変換されてしまいます。こういう現象は、「暗黙の型変換」と呼ばれています。文字の連結は「&」を使っても「+」を使ってもいいのですが、こうした暗黙の型変換が行われないようにするために、「&」を使うようにしたほうが安全です。意図しない型変換が起こると、エラーの原因になる可能性がありますからね。
ちなみに、明示的に型変換をしたい場合は、CInt関数などを使います。
2次元配列
次は、値を2次元配列に入れてから、その配列をセルに吐き出すというやり方です。
実はセルに値を書き込むという処理は、すごく時間がかかるんですよね。今まではそれをちまちま1個ずつやっていたので、相当処理が遅いんです（処理数が少なければ、体感できませんが）。セルに値を入れていくより、配列に入れていくほうが圧倒的に早くなります。また、配列のデータをセルに吐き出すのも、一度にやるならそんなに時間はかかりません。
この方法は、範囲と配列の大きさが合っていないとうまくいきませんが、使える時には積極的に使ったほうがいいです。特に、処理する範囲が大きければ、処理にかかる時間が劇的に減ります。
手作業の自動化
ここからは、もっとエクセルの機能を活かした書き方を考えてみます。
まず、「1から100までの数を書く」というのを、エクセル上で手作業で行う場合を考えてみます。一番思いつきやすい方法は、A1セルに1を入力し、A2セル以降は「上のセル+1」という式を入力する、というものでしょう。そういった作業をそのままコードにしてみると、次のようになります。
「PasteSpecial」は貼り付けのことですが、そのあとの「xlPasteFormulas」は数式貼り付け、「xlPasteValues」は値貼り付けのことです。セルに入っている数式を値に変換するために、値貼り付けを行っています。
For文などで繰り返しの作業をしていたのに比べると、作業時間は減ります。また、変数が使われていないことにも注目です。問題文を見ると「変数を使って繰り返せばいい」と考えてしまいがちですが、別に変数がなくても書くことはできます。1個1個処理していくよりも、一気に処理をしてしまったほうが、一般的に処理時間は短くなります。
オートフィル
次はもっとエクセル独自の機能を活かした書き方です。連続した値を簡単に入力する、「オートフィル」という機能を使った書き方です。
今のところ、一番短い書き方ですね。
「オートフィル」は、「他の範囲の値を自動的に埋める」という機能ですが、この機能を使うためには、基準となる値、値を埋める範囲、埋める値の計算方法、を指定する必要があります。それが上の「AutoFill」の箇所で行っていることですね。
上のコードでは、Typeとして「xlLinearTrend」を指定しています。これは線形にどんどん増やしていく（または、減らしていく）という設定で、ここでは「１ずつ足し上げていく」というように解釈されます。
このTypeの設定はいろいろあって、何も指定しなければ「xlFillDefault」を指定したことになります。これは、エクセルが自動的に判断して値を埋めてくれる、という設定です。しかし、上のコードでデフォルトの値を使うと、「1が入ってるから、きっと他も1が入るんだろうな」と解釈されてしまいます。すべての範囲に1が入ってしまうという、意図しない結果になってしまいます。なので、ここでは、「xlLinearTrend」を指定しています。
もし、「xlFillDefault」を指定したい、または、この設定を省略したい場合は、次のように書けばOKです。
1と2を入力してからエクセルに判断させると、「これはどんどん足していけばいいんだな」と理解してくれます。1行長くなりますが、上のような書き方でも同じ結果になります。
セル選択
次は、かなり気持ち悪い書き方です。セルを順番に選択していき、値を入れていくというやり方です。Do Loop文ですが、変数を使わないという不思議な書き方です。セル選択を頻繁に行うので、実行すると画面がチカチカします。
「Selection = Selection.Row」とは、「選択したセルに、選択したセルの行を入力する」ということです。そして、選択するセルをどんどん下に移動していく、というやり方です。「Offset(1, 0)」が、「１つ下のセル」を意味しています。
途中のIf文で、選択したセルのアドレスとA100セルのアドレスを比較しています。最後のセルまで行けば、そこでDo Loop文から抜ける、ということをしています。アドレスを比較していますが、もし「.Address」をつけなければ、それぞれのセルに入っている値をただ比較するだけになってしまいます。
ちなみに、「If Selection Is Range("A100") Then」という書き方はできません。文法的にはあっているのでエラーは出ませんが、意図した結果は得られません。残念ながら、RangeオブジェクトをIsで比較することはできません。Rangeオブジェクトごとに、別のメモリ領域に格納されるためです。下のサイトが参考になります。
RangeオブジェクトはなぜIs演算子で比較できないのか:エクセルマクロ・Excel VBAの使い方-Rangeオブジェクト
Addressで確認するというやり方が、一番簡単で確実です。もちろん、ここでいうアドレスとはメモリのアドレスではなくて、エクセルにおけるセルのアドレスのことです。
For Each文
「繰り返し」で使うものには、For Each文というのもあります。これを使った書き方もやってみます。
「For Each 要素 In コレクション」というような書き方をします。コレクションとは、何かが集まった集合みたいなものです。例えば、Sheetsとはシートが集まったコレクションだし、Shapesとは図形が集まったコレクションです。
上の例は、A1からA100というRangeが集まったコレクションに対し、そこに含まれる要素を1つ1つ取り出して処理を行うということです。Valueは省略することができるので、「r.Value = r.Row」の部分は、「r = r.Row」と書いても同じです。
For Each文のいいところは、個数がわからなくてもいいということです。今は処理する個数が決まっているのでうれしさがよくわかりませんが、例えば100個になることもあるし、10個になることも1000個になることもある、という状況では書き方が簡単になる場合があります。「処理する個数を数えて変数に格納する」というコードをわざわざ書かなくてもよくなるからです。また、「i番目のオブジェクト」と指定するより、「それぞれのオブジェクト」とするほうが、見やすいコードになることも多いです。
使える場面は限られますが、すっきり書けるようになることはよくあります。ただ、コレクションの知識が必要になり、難易度が少し上がってしまうところが欠点です。
Formula
次は、もっと短く書こうと頑張ってみるコードです。該当する範囲に、式を直接入れて計算するという方法です。
1行目で式を設定し、2行目でそれを値に変換しています。実質的には1行目で処理は終わっています。値にしなくてもよければ、1行でおしまいです。
セルの数式を指定する方法は、いくつかパターンがあります。その中でも一番わかりやすいのは、上で使用している「Formula」だと思います。これは、設定する数式内に登場するセルをA1形式で書く方法です。上のように書けば、他のセルに対しても、参照先が自分自身となるように数式が設定されます。
「FormulaR1C1」を使うこともできます。これは、Formulaと同じく数式を設定するものですが、R1C1形式でセルを設定する点がFormulaとは異なります。このR1C1形式では、数式の入ったセルを基準に相対的にセルを指定することもできるし、絶対参照でセルを指定することもできます。自分自身のセルは「RC」と書けばよいので、次のように書き換えてもOKです。
Range("A1:A100").FormulaR1C1 = "=ROW(RC)"FormulaR1C1は慣れるまでは少しわかりづらいのが欠点です。アドレスがわかっていなくても使えるので便利なのですが、この形式に慣れている人はたぶん少ないので、できれば使うのを避けたほうがいいんじゃないかと思います。
ちなみに、数式を設定するには、今までに述べた方法のほかに、次のような方法でもいいんですよね。下の２つのどちらも、同じ結果になります。
Range("A1:A100").Value = "=ROW(A1)"
Range("A1:A100") = "=ROW(A1)"Formula系を使わなくても、式を設定することはできるんですね。
元のコードに話を戻しますが、2行目ではこんなことをしています。
Range("A1:A100").Value = Range("A1:A100").Value実はこれは値貼り付けと同じ効果です。「そのセルで計算した値を、そのセルに代入する」ということなので、数式が消えてセルには値が入るようになります。わざわざコピーして値貼り付けをする、ということはしなくてもいいんですよね。書き方もシンプルだし、クリップボードを使わなくなるので、覚えておくと便利です。
Evaluate
前の例では、「式を入れる、値にする」という2段階の処理を行っていました。もうこれ以上短くなるような気がしません。これ以上短くするには、「いきなり計算結果を値にしてセルに入力する」必要があるからです。
しかし、実はそういうメソッドがあるんですよね。マイナーですが、Evaluateというのがあります。
このように書くと、計算した結果が値となってセルに格納されるようになります。これ1行でおしまいです。
ちなみに、次のように書いてしまうと、意図しない結果になります。
Range("A1:A100").Value = Evaluate("ROW(A1:A1)")これを実行すると、すべてのセルに1が入ってしまいます。式がコピーされて計算されるわけではないようですね。参照先が変わってほしいのですが、そうはなってくれません。
ちなみに、こう書いてみるとまた違った結果になります。
Range("A1:A100").Value = Evaluate("ROW(A1:A10)")この実行結果は、A1セルからA10セルまでは、1から10が入り、A11セル以降はエラー値、となります。Evaluateの処理について、僕はそんなに詳しくないのですが、上の処理結果から大体どういうことが起こるかは想像できると思います。
なお、Evaluateは、角括弧（[]）を使っても同じ結果を得ることができます。また、Evaluateの中に数式を入れると計算結果が返ってきますが、セルのアドレスを入れると対応するRangeオブジェクトが返ってきます。この2つのことから、上のコードは次のように書き換えることができます。
[A1:A100] = [ROW(A1:A100)]、これだけ！ これが、僕が思いつく中で、一番短いコードです。
ただ、この書き方は短さで言うと一番ですが、はっきり言ってわかりにくいです。この書き方はマイナーすぎます。しかも、これを見た人が「[]ってどういう意味かな？」と疑問に思っても、検索しづらいです。なので、もっとメジャーな書き方にしたほうがいいですね。これはネタとして知っておくくらいでいいのではないか、と思います。
処理速度について
さて、今までで12通りの書き方を紹介してきました。長いものから短いものまでありましたが、最後にそれぞれの実行速度を計ってみましょう。
100個では少なすぎるので、1000000個でやってみます。つまり、上のコードで100のところを1000000に、IntegerをLongに変えたコードで計測してみます。文字連結を使ったコードは、2文字ではなく6文字の連結に書き換えました。タイムを計るところは、次のように書きました。呼び出すプロシージャをころころ変えて、実行しました。
Sub execute()
Dim startTime
Range("A:A").ClearContents
startTime = Timer
Call output1to100_1
Cells(1, 5) = Timer - startTime
End Sub計測結果は次のようになりました。
一番早いのは、意外にも、オートフィルでした。100万個処理して0.2秒というのは、かなりの早さです。「一瞬」といってもいいレベルです。この問題に対して、「オートフィルを使う」書き方を真っ先に思いつく人は少ないと思うんですよね。しかし、よくよく考えてみると、1から順番に数字を入れていくという処理に、もっとも最適化された機能だということを考えれば、そんなに不思議な結果ではないのかもしれません。
2次元配列やEvaluateを使った処理も、1秒台となかなかの早さです。2次元配列を使うと処理が速くなるというのは知っていたので、個人的にはそれほど驚きはないです。Evaluateや角括弧を使った書き方は、短いうえに処理も早いんですね。素晴らしいです。
手作業の自動化やFormulaを使った書き方は、上の処理よりも少し遅くなります。「式を設定する」「それを値にする」という2回の処理で、それぞれセルの操作をしていますが、おそらくこれが時間のかかる原因でしょうね。セルへの操作回数は少ないほどいいので、この差が時間の差となって表れたということでしょう。
それ以外の繰り返し処理は、かなり時間がかかっています。早くても、18秒台。「結構処理に時間がかかるなぁ」と感じてしまうレベルです。ちなみに、Do Loopが少し遅くなっていますが、これはセルの選択方法を変えたためです。RangeではなくCellsを使えば、For文と大して変わらない速さになります。つまり、文字の連結が遅いということです。
これからわかることは、繰り返しだからと言って、常にFor文を使えばいいというわけじゃないということですね。処理が多い時には、セルへの処理回数を減らす書き方はないかを考えたほうがいいです。100個くらいの処理なら誤差ですが、これが多くなってくると、遅くなったりエクセルが固まってしまう原因となります。
ちなみに、グラフが突き抜けてしまってますが、「セル選択」を使った書き方は、400秒くらいかかりました。1個ずつセル選択をしてしまうと、半端なく時間がかかってしまいます（マクロを実行すると、「応答なし」になってしまいました）。「マクロの記録」を使ってコードを作った場合、「カーソルの移動」が入ることがよくありますが、そのまま使ってしまうのは避けたほうがいいでしょう。
また、再帰関数はエラーとなってしまいました。これは時間がかかりすぎるというわけではなくて、「スタック領域が不足しています」というエラーが出て、マクロが止まってしまう、ということです。そもそも結果までたどり着けません。上の文中でも書きましたが、処理が終わる前に別の処理を呼ぶので、処理が多くなってくると、こういうエラーが出てしまいます。ただ、これは再帰関数が使えないというわけじゃなくて、得意な分野と不得意な分野があるということです。処理回数が多いと使える場面は減りますが、処理回数が不明な繰り返し処理では使える場面が増えます。
まとめると、
- 法則性のある数字をたくさん入力する場合は、オートフィルを使おう
- たくさんのセルに値を入力するときは、一気にやってしまおう
- 処理が少ない時は、For文でもOK
最後に
最後に宣伝です。僕はVBAのコードを書くことが多く、今の会社でもVBAをよく書いています。また、ネット上で「ココナラ」というサービスを使って、「エクセルマクロ、エクセル関数のこと、ご相談ください！」といった仕事も請け負っています。簡単なマクロ作成やエクセルに関する相談を格安で受ける、という内容です。もし、困ったことがあれば、このサービスからぜひ相談してみてください！
