Excelマクロの配列を扱うときによく使うUbound関数・Lbound関数というものがあるのですが、この関数の役割、使い方を自分なりに整理しましたので記事にしておきます。
目次
ExcelマクロのUbound関数・Lbound関数はどんな役割なのか
そもそもExcelマクロのUbound関数・Lbound関数はどんな役割をするものなのか?という点ですが、
- Ubound関数 → 配列の最大のインデックスを取得します
- Lbound関数 → 配列の最小のインデックスを取得します。
これだけだと「なんのこっちゃ？」になるので具体的な動きを確認するためにデータを用意しました↓
縦に14セル・横に3セル分の表形式のデータになります。税理士業務ではこういったタイプの表データを使用する機会がよくあると思います。
この状態で下記のマクロを実行してみます。
Sub test()
Dim my_array As Variant
my_array = Sheets(1).Range("a1").CurrentRegion
Debug.Print LBound(my_array)
Debug.Print UBound(my_array)
Debug.Print LBound(my_array, 2)
Debug.Print UBound(my_array, 2)
End Sub
LBound(my_array)とすることで縦方向のデータの最小インデックスを取得することができる。
さきほどのマクロですが簡単に説明すると、「my_array」という変数を宣言しておいて、その変数にＡ１セルの周辺セル（今回の場合はＡ１セル～Ｃ14セル）を格納して配列にしています。
この状態で配列「my_array」に対してLbound関数とUbound関数を実行すると実行結果として下記の結果が表示されます↓
実行したマクロのこの部分に関して自分なりの解釈を説明します↓
この部分、配列「my_array」に対してLbound関数とUbound関数を使っています。
これがどういうことかというと、下記の画像の黄色に塗りつぶしたセルの部分の最小と最大を取得していることになります↓
黄色に塗られたセルが縦方向に全部で14個あり、そのうちのLbound関数を使うことで最小のインデックスである「１」が取得されます。
UBound(my_array)とすることで縦方向のデータの最大インデックスを取得
一方、Ubound関数を使うことで黄色に塗られたセルの最大のインデックスである「１４」が結果として取得されたということだと思います。
ざっくりいうと「縦方向」のデータの最小と最大を取得するイメージで自分は理解しています。
LBound(my_array,2)とすることで横方向のデータの最小インデックスを取得することができる。
そして次にこの部分のマクロに関してですが↓
Lbound関数とUbound関数を使うときに「(my_array,2)として使用しています。
これがどういうことかというと、下記の画像の青色に塗りつぶしたセルの部分の最小と最大を取得していることになります↓
青色に塗られたセルが横方向に全部で３個あり、そのうちのLbound関数を使うことで最小のインデックスである「１」が取得されます。
UBound(my_array,2)とすることで横方向のデータの最大インデックスを取得することができる。
一方、Ubound関数を使うことで青色に塗られたセルの最大のインデックスである「３」が結果として取得されたということだと思います。
ざっくりいうと「横方向」のデータの最小と最大を取得するイメージで自分は理解しています。
Ubound関数の具体的な使いどころ「値のみ貼り付け」をするときにUbound関数で取得した結果をResizeの引数に使う
じゃあこの配列のLbound関数とUbound関数を具体的にどんな時に使うの？？という点ですが、これに関しては正直自分もよくわかっていません。
ですが、１つこんな時に使えるんじゃないか？というものを考えましたのでそれを説明します。
以前、Excelマクロで「値のみ貼り付け」をする方法を自分なりに整理した記事を書いたのですが
今回のUbound関数で取得した結果をResizeの引数として使うと「値のみ貼り付け」の時に使えると思います。
例えば今回のデータはシート１のA１セルを中心にデータがありますが、このデータをを別シート（シート２）に値のみ貼り付けをしたいとします↓
そんなときにUbound関数を使ってマクロを作ると具体的にはこんな感じになります↓
Sub test()
'■シート１にあるA1セルの周辺セル全部を配列にいれて
'■シート２のC１セルに値のみ貼り付けをするマクロ
'■変数宣言（配列）
Dim my_array As Variant
my_array = Sheets(1).Range("a1").CurrentRegion
'■処理実行部分 Resizeの引数にUbound関数で取得した値を使用
Sheets(2).Range("c1").Resize(UBound(my_array), UBound(my_array, 2)).Value = my_array
Sheets(2).Activate
End Sub
マクロを実行した際の動作は下記の動画をご覧ください。
元のデータが増減したとしても問題なく別シートに貼り付けができている点も参考にしてもらえると良いかと思います↓
まとめ
Excelマクロの配列を使用する際によく使う関数としてLbound関数とUbound関数があるのですが、それぞれの関数がどんな動きをするのか、どういう役割をするのかについて、自分なりの解釈と具体的な使用方法を記事にしました。
前回の記事に引き続き配列に関する記事を書いていますが、やっぱりなんかモヤモヤしますね。
掴みどころがないというか、しっくりこないというか。。。
【本日の近況報告】
アドビのillustratorが契約して1年たつので、もう1年更新しようと思いamazonで購入。
アドビの管理画面で契約の自動更新を一旦オフにして、amazonで購入したコードを入力しようと思って作業をしていたんですが、なんとアドビから2か月無料でイラレが利用できる特典がもらえました。
なんか得した気分です。
【本日の１曲】
the hotknives/in my dreams
ネオスカバンドのHOTKNIVES。オルガンにギターのカッティング（裏打ち？）、曲全体に漂う哀愁。渋カッコイイ。
このアルバムの最後にこちらの曲の別アレンジとして「intimatley dangerous」という曲が収録されていますがこちらもおススメ。
