Excel VBAでワークシート上に迷路を自動生成するマクロ
くだらないExcelマクロを作るのが些細な趣味なのですが、この前軽い気持ちで作った「迷路を自動で生成して自動で正答ルートを色付けする」というマクロをTwitterで投稿したところ軽くバズりびっくりしました。せっかくなので、ここではその方法を紹介します。今回はまず「自動生成」のほうを紹介します。ソースコードは最後にあります。
「迷路を自動で生成して自動で正答ルートを色付けする」とは
まず、このマクロが何であるのかは、以下のツイート内の動画を見てもらえればわかると思います。
激しい試行錯誤の末、セルと罫で作られたエクセル迷路の正解ルートを自動で描画する、最高にどうでもいいマクロが完成しました。
— (有) (@tdyu05) 2019年4月24日
#Excel #VBA #マクロ pic.twitter.com/iuvtYneYrR
これは、Exceのセルを通路に、罫線を壁に見立てて、ランダムなルートの迷路を描画するマクロと、その迷路の正解ルートを自動で探索し描画するマクロの2つを実行したものです。
迷路を自動生成するアルゴリズム
決して大したものではないのですが、このツイートがバズったために「どうやって生成しているのか気になる」という類のコメントや、エンジニアや競技プログラミングをやられていると思わしき方からも「どんなアルゴリズムなのか」というコメントがちらほら寄せられました。
正直なところ、非プログラマーである私はアルゴリズムをまったく知らず、実際に皆様のコメントを見てから初めて、迷路生成/迷路解答アルゴリズムが存在するのだということを知ったくらいです。ですので、結局どの手法を使っているかわかりません。
生成のロジックは、おそらくいろんな方々が想像されるよりもかなりシンプルな仕組みです。以下の通りです。
(1)真っ白な10マス×10マスのセルを用意する
(2)開始地点として、一番左上のセルに通路を描画する
(3)現在地のセルの四方に通路ができているか否かを確認して、通路ができていない方向をランダムに選択しそのセルに通路を描画
(4)上記の(3)の処理を、四方が通路に囲まれて行き止まりになるか、またはゴールのセルの1つ手前に達するまで再帰処理でループさせる
まず、ここまでを動画にすると以下になります。イメージがつかみやすいでしょうか?
以下、続きです。
(5)既存の通路から新たに別の通路を開通させて上記の(3)~(4)の処理を再度行う
(6)上記(3)~(5)を繰り返し、10マス×10マスのセルをすべて埋めたら再帰処理のループを抜けて終了
(7)左上のセルの上部の罫線と右下のセルの下部の罫線を消して入り口出口を開通
以上です。アルゴリズムとしてはたったこれだけです。
ただ、非プログラマーの私としてはこれをどうやってExcelやVBAの中で実装するかが大変でした。(結果、ものすごく膨大な時間がかかってしまいました…)行数もたかだか220行程度です。本職の方がやればすぐ終わると思います。
Excel VBAで実装する方法
では、実際にVBAでどう作ったのかをポイントを紹介します。ソースコードについては、最後にすべて記すのでそちらを参照してください。まず先ほどの
「(3)現在地のセルの四方に通路ができているか否かを確認して、通路ができていない方向からランダムな方向を選択しそのセルを描画」の部分にふれてみます。
描画する前に、まず隣のセルに通路があるかを確認する方法
通路の判定はセル上のプロパティで行っていません。まず10×10のセルを2次元配列とみなし、配列変数にし、変数上で判定を行っています。デフォルトでは各座標の要素に0の値を入れておき、通路ができたらその座標の値を1にフラグオンします。
つまり、現在地の座標を基準に上下左右隣の座標が0ならその道を開通させる、というロジックです。
2次元配列の初期化
上述の2次元配列を初期化するためには次の記述を行います。下記の赤がy座標(=行)であり、青がx座標(=列)です。
arr(0) = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
arr(11) = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
Dim i As Integer
For i = 1 To 10
arr(i) = Array(1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1)
Next i
上記を見ると要素数はy座標12マス×x座標12マスとなっています。迷路のマスは10マス×10マスなので疑問に思うかもしれませんが、その理由は次の画像の通りです。2次元配列をセル上に可視化してみました。
あるセルから四方の配列要素の値が0か1かを判断するというロジックを使うといっても、壁側のセルの場合、壁の外を探索する必要ないので四方を調べる処理が使えません。しかし壁側セルだけに別の処理を書かせるとコードかなり煩雑になるので、壁側の外側にもy,x座標のマスを設けているのです。
隣のセルに通路を描画する
隣のセルに通路を描写するのは、後述のソースコード内に記載したfillRouteというプロシージャで行います。ただし、単純に道+壁を塗る処理だと以下のようになってしまうので、壁を開通させながら進まなければいけません。
進んだ方向からの壁を開通させるには、セルから次に進む方向を決めた後、自分がもともといた方向の数値を引数に取ってfillRouteプロシージャを呼び出します(ソースコード中「'//次に進む方向を決定」のコメント以下を参照)。進む方向と数字の対応は以下の通りです。
1:上方向
2:右方向
3:下方向
4:左方向
次に進んだ方向がわかれば自分がもともといた場所もわかります。
隣のセルに通路を描画する再起処理を止める条件は?
先ほどのステップ(4)で「四方が通路に囲まれて行き止まりになるか、またはゴールのセルの1つ手前に達するまで再帰処理でループさせる」という書きました。後者は例えば次の画像を見てください(ゴールのセルはK11。その手前のK10に来たのでストップしている)
(※今書いていて思い始めましたが、ゴールセル手前まで到達したら再起をストップという処理は迷路生成ロジックそのものとしては不要だったなと思い始める。別に手前まで止めずとともゴールセル上をそのまま描画しても多分迷路は作れますね・・)
行き止まり到達後、新たなセルから再び通路描画を始める
行き止まりに達したあと、次は任意のセルからまた通路生成を開始します。開始するセルを選ぶ処理は、正直妥協しました・・次に開通するセルは、「まだ通路が描画されていないセルを左上から検索していき最初にヒットした場所にする」という単純な条件で選択しています。先程の図でいうとH2のセルです(下図参照)
新たな通路描画の開始セルと既存の通路を開通させる
当然ですが、新たに生成した通路は既存の通路と開通させる必要があります。上記の画面でいえば、H2セルから始まった場合、G2セル側か、H3セル側の壁を開通させます。ソースコードを見てもらえればと思いますが、ここでは開通のために以下の処理をしています。
(1)配列direction()に壁がある方向の番号を格納
(2)関数getDirectionNumで、(1)で見つかった壁のうちどこに穴を開けるか、壁の方向(の番号)をランダムで取得
(3)その後、(2)で取得した開通先を引数に、最初に述べたfillRouteプロシージャで通路描画
これで行き止まりに達したらまた上記(1)~(3)を行います。10マス×10マスの迷路なので、通路を描画したセルが100個に到達したらExit Subしてマクロを終了します。
作ってみての感想
いま振り返りながら文書化してみると、もう少しシンプルに書けたり改良したりできたなと思うところはあります。特に行き止まりに達したあと、本来であれば新たに通路描画を開始するセルは以下の数だけ存在します(=既存の通路と接しているところ)
なので、「ランダムなx、y座標を生成させて、それが水色の部分に該当したらそこから描画を始める」という処理でもよかったかもしれません。
最後に1ステップずつ迷路を描画するGIF動画を置いておきます。今回迷路生成ロジックを説明しましたが、迷路を解くマクロは感覚的にその倍くらい苦労しました。これは次回解説します。
ソースコード
以下、今回の迷路生成マクロのソースコードです。多分標準モジュールにこのままコピペすれば使えるはずです。
Google Apps Scriptで今日の日付の行に一瞬で移動する方法
Googleスプレッドシートは前回閉じた場所を記憶できない!
スケジュール表をExcelなどのスプレッドシートを使って作るという行為は、おそらくビジネスマンなら大半の人が体験しているはずです。編集・制作の仕事をしている私も日常茶飯事です。前はExcelをよく使っていましたが、最近ではGoogle スプレッドシートを使い始めました。
ただ、すぐにこれには問題があることに気がつきました。行を使って縦方向に日付を記入するスケジュール表の場合、スケジュール期間が短ければよいのですが、「年間予定」のようなかなり期間の長いと問題が発生します。
というのも、Excelと違ってGoogleスプレッドシートはワークシート上の最後に閉じた場所を記憶するわけではないので、ファイルを開いたら必ず先頭の行列(A1)が画面の一番上に来た状態で表示されます。
ですから、確認したいスケジュールの場所がそれよりも何十行も先の行にあるとわざわざ開くたびにそこまでスクロールしなければいけません。
本日の日付が書いてある行を見つけてそこをアクティブにしてみる
そこで私が考えたのが「スプレッドシートを開いたときに今日の日付の行に自動で飛んでくれればよいのでは・・」という解決策です。
(まぁそんなことをせずとも、過ぎた日付の行を非表示にするとかいう運用をすれば完全に解決するのですけど・・)
そんなそもそも論はさておき、A列にある日付の羅列のうち、本日の日付を見つけ、そのセルをアクティブにするというスクリプトを作ってみました。
このコードだけではまだ手動実行になります。上記のコードにあるmyFunctionという関数をonOpenにすれば、スプレッドシートを開くのと同時にこれが実行されるはずです(まだ検証してません)。
上記のコードは細かく説明しませんが、
(1)本日の日付をM月dd日形式にして変数に格納
(2)A列の日付すべてを配列に格納
(3)(2)の配列の中から本日の日付に合致するものを検索する
(4)見つかった行数のセルをアクティブにする
やっているのは上記だけのことです。
ちなみに特定行の中から該当する行を取得する関数(上記コード中のfindRow)については、偉大なるタカハシノリアキ氏の以下の記事内のコードを参考にさせていただきました。
今日の日付を配列の中の日付から検索するために、一度日付を”m月dd日”にしていますが、ここはお使いのスケジュールの日付欄の形式に合うように調整が必要です。
Google Apps Scriptを使ってGoogleスプレッドシートにユーザー定義関数を作る方法
エクセルの場合、VBAを使用してユーザー定義関数を作ることができます。同様に、GoogleスプレッドシートもGoogle Apps Scriptを使ってユーザー定義関数が作れます。というわけで、ここではユーザー定義関数をサンプルで作ってみようと思います。
得点表から平均点を計算する関数を作成してみる
まず以下の表を作ってみました。
5人の人がいて、B列に国語の点数、C列に数学の点数が載っています。国語と数学を足した得点の6人の平均点以上であれば“合格”、それに満たなければ“不合格”という文字をD列に表示する関数を作ってみたいと思います。表は現在5人ですが、人数がどんどん増えても成り立つようにします。
Google Apps Scriptを使ったことがある人なら解説不要かと思いますが、念のため順を追って解説します。
ユーザー定義関数の作成方法
まずは、メニューの「ツール」 > 「スクリプトエディタ」をクリックして、スクリプトエディタを開きます。
すると次のような画面が現れます。
デフォルトでは、プロジェクト名は「無題のプロジェクト」で、その中の関数名が「myFunction」とあります。ここは自由に変えてください。
ここにGoogle Apps Scriptを記述していきます。
記述したらスクリプトエディタのメニューの「ファイル」 > 「保存」をクリックしてできあがりです。
これを最初のスプレッドシートで利用するのは簡単です。エクセルのように、作った関数名をセルの中に=をつけて記入し、引数にB列の数字とC列の数字をいれるだけです。
というように、ただスクリプトエディタに関数をつくって、それをスプレッドシートのセルの中に記入しただけです。なのでいちいち解説するほどでもないことなのですが、Google Apps Scriptでユーザー定義関数のことに触れている情報があまりなかったので整理してみました。
最後に今回作った関数のコードを載せておきます。
ソースコード
今回作ったユーザー定義関数は上記のように記述しました(Google Apps Scriptというか、JavaScript自体あまり知識ないので、これが良いプログラムかはわかりませんが・・・)
JavaScriptで重複しない乱数を生成する
プログラミングでよく必要になるのが、重複しないランダムな数の生成です。出典は忘れてしまったのですが、以前どこかのサイトで非常にスマートな記述方法が載っており感動したので、備忘録としてメモします。JavaScriptでの例です。
重複しない乱数生成のJavaScriptのコード
早速ですが、コードは以下です。下記の例では配列numArrに1から5までの数字をランダムに1つずつ格納するコードです。
重複しない乱数を生成するロジック
では、上記のコードでなぜ重複しない乱数ができるのかを見ていきましょう。
- 生成した乱数を格納するnumArrという配列をつくる
- 上記とは別に配列(arr)を作り、ランダムに生成したい値(今回は1,2,3,4,5)を順にいれておく
- 乱数を生成(Math.floor(Math.random()*len))する・・・①とする
- 配列numArrの中に、配列arrの[①で生成した乱数]番目の数をpushで追加する
- 配列arrの中で、[①で生成した乱数]番目のところに、[arrの配列の要素数-1]番目の数字を埋めていく
- 乱数を生成したい数だけforループで繰り返す(上記の例では5回)。その際、arrの配列の要素数の数字を1つずつ減らしていく
最初見たときこのコードが理解できませんでした。なぜ上記のステップ5でarrの[乱数]番目のところに[arr配列の要素数]番目の数字をいれるのでしょうか。順番に見ていきます。
まず、1~6行目のコードが実行されると配列arrには以下の要素が入っていることになります。
- arr[0] =1
- arr[1] =2
- arr[2]= 3
- arr[3]= 4
- arr[4] =5
まず乱数の生成で2の数字が出たとしましょう。arr[2]は3ですから、numArrに3の値をpushして次のようになります。
- numArr[0] =3
- numArr[1] = ""
- numArr[2] = ""
- numArr[3] = ""
- numArr[4] = ""
先ほど言及した、”ステップ5でarrの[乱数]番目のところに[arrの配列の要素数-1]番目の数字を入れる(arr[rndNum] = arr[len-1])を実行すると以下になります。
- arr[0] =1
- arr[1] =2
- arr[2]= 5
- arr[3]= 4
- arr[4] =5
ここが最大のポイントです。この配列を見るとarr[2]も5だしarr[4]も5なので、一瞬それでいいの?と思ってしまいます。
しかし、この処理が終わった後forループがはじめに戻るわけですが、初期値が5の変数lenは1つ値が減るためMath.floor(Math.random()*len)で生成されるrndNumの値は0~3に限られます。
つまり次に配列arrから値を持ってくるときは、もう二度とarr[4]が選ばれることはないのです(arr[0]、arr[1]、arr[2]、arr[3]しか選ばれない)。
試しにこの状態でもう一度乱数を生成してみましょう。次の乱数に1が生成されたとしたら、arr[1]は2が格納されているので、numArrにプッシュすると以下のようになります。
- numArr[0] =3
- numArr[1] =2
- numArr[2] =""
- numArr[3] =""
- numArr[4] =""
arr[1]にはarrの「arrの要素数-1」番目にあった”4”をいれるので以下のようになります。
- arr[0] =1
- arr[1] =4
- arr[2]= 5
- arr[3]= 4
- arr[4] =5
しつこいですが、さらに繰り返してみましょう。forループで、乱数の2が出現したとすると以下のようになります。
- numArr[0] =3
- numArr[1] = 2
- numArr[2] =5
- numArr[3] = ""
- numArr[4] = ""
- arr[0]=1
- arr[1]=4
- arr[2]=3
- arr[3]=4
- arr[4]=5
繰り返しにはありますが、このプログラムでは乱数の生成で以下の青の数字が生成されると、そのループ時点での[arr配列arrの要素数-1]番目の値が右辺に代入されます。
- arr[0]=XXX
- arr[1]=XXX
- arr[2]=XXX
- arr[3]=XXX
- arr[4]=XXX
Math.random()*lenのうち変数lenが1つずつどんどん減っていくため、発生する乱数の範囲が縮まり、得られる数字が限られてしまうのですが、その限られたarr[X]には、[すでに発生しない乱数]番のarrの値をいれておくことで、必要なものが得られるという仕組みなっているのです。
こうすることでたとえ乱数がどんな順番でどんな数がでてきても必ず重複しない乱数が無駄なループなしに得られるのです。試しに、乱数の0が5回連続で出たとしても、1~5までの数が得られます。
私が最初にこの仕組みを知ったときとても感動しました。一体どうやればこんなコードを思いつくのだろうか想像も付きませんが、真のプログラマーやアルゴリズムを考えられる方は本当に尊敬します。
Excel VBAでセルや文字の色のRGB値を調べる
Excel VBAを使っていると、ColorIndexプロパティやRGB関数を使ってセルや文字の色を指定するシーンは多いと思います。でも、逆にセルや文字から色を調べたいときはどうすればよいでしょうか。
ColorIndexプロパティを使えば色番号は簡単にわかりますが、一方でRGB値をサクッと調べられるプロパティはありません。 そこで、RGB値を調べる方法を以下に記します。
塗りつぶしメニューの色を調べてみる
以下のキャプチャのように、A1セルに「フォント」→「塗りつぶし」のメニュー内で左から5番目にある青色を塗ってみました。試しにこの色のRGB値を調べてみたいと思います。
RGB値をメッセージボックスで表示してみる
早速ですが、以下がこの色のRGBそれぞれの値をMsgboxで表示するコードです(私が考えたものでなく拾い物です)。
(※追記:よりシンプルな方法があるとコメントで指摘されたのでその方法を最後に記します。
Sub Test() Dim R As Integer Dim G As Integer Dim B As Integer Dim MyColor As Long MyColor = Range("a1").Interior.Color R = Val("&H" & Left(Hex(MyColor And 255), 2)) '・・・・・・・①' G = Val("&H" & Left(Hex(MyColor And (256 ^ 2 - 256 ^ 1)), 2))'・・②' B = Val("&H" & Left(Hex(MyColor And (256 ^ 3 - 256 ^ 2)), 2))'・・③' MsgBox "R:" & R & vbCrLf & "G:" & G & vbCrLf & "B:" & B End Sub
上記を実行すると、
R:68
G:114
B:196
とメッセージが表示されます。
つまり、このA1のセルの色は、Rが68、Gが114、Bが196ということがわかりました。
結局、RGB値を調べているのは、上記のコードの①~③の部分だけです。
このコードは拾い物なので、私は①~③の中にある数式(?)でなぜRGBのそれぞれの値が導き出せるのかまでは理解していません。
理屈はわからないけど、とりあえずこのコードをコピペすれば使えるので、知らなくても実用には問題はないでしょう。
上記のコードの中の、Hexは10進数を16進数に変換する関数、Valは、引数に文字列をとり、その文字列の中から数値として抜き出せる部分を返すという関数です。いずれも自分はあまり使わない関数で、このコードを見たときに初めて知りました。
この関数ももし機会があれば調べてみたいと思います。
追記:
コメントで指摘がありましたが上記のコードのうち、R、G、Bに値を入れるコードは以下で大丈夫だそうです。
R = MyColor Mod 256 G = Int(MyColor / 256) Mod 256 B = Int(MyColor / 256 / 256)
めちゃくちゃシンプルですね!これでよかったのか・・
Excel VBAで重複しないランダムな数字を生成するマクロ
趣味でエクセルVBAを使ってあるツールを作ってたとき、重複しないランダムな数(乱数)を生成する必要があり、他のWebサイトを見ながら調べました。それと同じものですが、備忘録がわりに以下にコードを載せます。
早速、コードは以下です。
このコードは数字の1から指定した任意の数まで、1回ずつランダムに数字が生成されます。生成したい数の最大値は、変数MaxNumで指定します(たとえば、1から10までの乱数を生成したいなら“MaxNum = 10”とする)
このコードを丸ごとコピペして、標準モジュールに張り付けて実行すればセルA1からA10までランダムに1から10までの数字が1回ずつ入力されます。
5つ乱数がほしい場合は、"For i = 1 To MaxNum"の部分の”1 To MaxNum”を”1 to 5”とすればOKです。
簡単な解説
おそらくほかの方の記事で解説があるかと思いますし、解説するほどでもないのですが、念のため触れておきます。
配列の要素数を再定義するReDim
最初に以下の記述があると思います。
ReDim Number(1 To MaxNum) As Long
ReDim flg(1 To MaxNum) As Boolean
Number()という配列は、生成したランダムな数字を格納していく配列です。ReDimは最初の決めた配列の要素数を再定義するための宣言です。もちろんこの要素数を最初のDimの段階で定義してももちろん動作しますが、プログラムの途中で要素数を変更する必要もあるかもしれませんし、この記述をしたほうが他のプログラムなどへの移植性や柔軟性を考えた際に便利です。
乱数の生成
ランダムな数の生成を行っているのは“num = Int(Rnd * MaxNum) + 1”の部分です。MaxNumを10とすると、1から10までのランダムな数値が変数numに格納されます。
単にランダムな数字がほしいのであればこの1行でOKです。ただ、これだと重複が発生するかもしれません。そこで必要になるのが次の記述です。
重複を避けるための処理
If flg(num) = False Then
flg(num) = True
Number(i) = num
Exit Do
End If
前の項でランダムな数字numが出現したら、配列変数のflg()の[num]番をフラグオンにしておき、「その[num]番はすでに使いましたよ」という目印を立てておくのです。
ランダムな数字はNumber()という配列変数にどんどん格納してきます。ただし、上記の5行のコードをみてわかるように、それは flg()がフラグオフ(=まだ一度もその数字が出現していない)でなければ実行されません。Do Loopをかけているので、無事にNumberに格納されない限り繰り返されます。
以上が簡単な解説です。ランダムな数字の生成は、業務で使用するツールにはまず出てくることはないでしょうけど、私のようにExcelでの簡単なゲームとかくだらないツール作るときには頻出なので備忘録としてここに記しました。