Mutable_Yunの業務改善ブログ

業務改善や生産性向上のブログです。自動化の手段として、VBAやRPAの勉強に役立つ解説をしています。

VBA 配列を徹底的に解説する (全5回) ③静的配列の初期化と実務的なサンプル

VBAにおける配列の3回目です。そろそろ静的配列のイメージが掴めてきたので、実務的なサンプルを考えていきます。

実務的なコードを作成するために、今回は静的配列の初期化について解説します。

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

目次

配列の初期状態とは

あらためて、配列の初期状態について確認しておきましょう。配列を宣言した直後の配列の要素はどうなっているのでしょうか。

Sub 配列を作成する()
    
    Dim arr(4) As Variant
    
End Sub

もっとも単純な例で確認します。F8で一行ずつ実行してローカルウィンドウを確認します。
f:id:mutable_yun:20190922110318p:plain

値の所を見ると、Empty 値 となっています。””(長さ0の文字列)でも数字の0でもNothingでもありません。

Emptyとは「空の状態」という状態を表す特殊な値です。

静的配列の初期化を行う

配列を宣言したばかりの状態では各要素はEmpty(空の状態)となっていることが分かりました。矛盾している様に思うかもしれませんが、各要素にEmpty(空の状態)が格納されている、と考えると良いでしょう。

静的配列の各要素を初期化する

それならばEmptyを格納してやれば、初期化できることになります。

Sub 静的配列の要素の初期化1()

    Dim arr(4) As Variant
    
    arr(0) = "Good Morning"
    arr(1) = "Hello"
    arr(2) = "Good Afternoon"
    arr(3) = "Good Evening"
    arr(4) = "Good Night"
   
   arr(2) = Empty
    
End Sub

f:id:mutable_yun:20190922111256p:plain
arr(2)をEmptyに戻すことができた

静的配列をループで初期化する

VBA 配列を徹底的に解説する (全10回) ②静的配列で、静的配列の使いどころとして、同じグループのパラメータを入れておくのに適している、と言う話をしました。

同じグループの複数パラメータが格納されている、と言う事はその配列を初期化したいときは、どれかの要素を一つだけ初期化するというより、全部初期化したい場面の方が多いと思います。

たとえばエクセルシートに仕入れ先一覧表があって、一行ごとに仕入れ先情報が格納されている。一行ごとにその仕入れ先情報を配列に格納し、別シートで何らかの作業を行い、その作業が終わったら配列を初期化して、次の行の仕入れ先のデータに移る、と言った具合です。

それでは、静的配列を初期化するにはどうすれば良いのでしょうか。
結論としては配列そのものをEmptyにする事はできません。

Sub 実験_配列そのものをEmptyにできるのか()
    
    Dim arr(4) As Variant
    
    arr(0) = "Good Morning"
    arr(1) = "Hello"
    arr(2) = "Good Afternoon"
    arr(3) = "Good Evening"
    arr(4) = "Good Night"
    
    arr = Empty
    
End Sub

f:id:mutable_yun:20190922112154p:plain
コンパイルエラーが発生した

コンパイルとは、皆さんが書いたプログラムコードをコンピュータが理解できるように翻訳する作業です。

誰が翻訳しているかと言うと、エクセルさんが翻訳しています。エクセルのワークブックではなく、エクセルと言うソフトに付属している皆さんがコードを書いているVBEが翻訳してくれます。

VBAではプログラムを書いている最中のリアルタイムと実行直前にこのコンパイルが自動で行われます。

コード一文に対してエラーがあれば、コーディング中にその場で指摘してくれます。

今回は一行ごとの文法のミスはないので(一行だけ見ればarrは配列とはわからず、Variant型の変数の値をEmptyに設定しようとしただけなのでこの一行だけ見ればエラーではない)コーディング中のエラーは出ませんでしたが、実行前のコンパイルでエラーに成りました。

つまり、実行を押した瞬間にコード全体を見ると配列として宣言した変数arrにEmptyをいれようとしたことがわかったのでコンパイルエラーとなりました。(カッコをつける事が配列である事の宣言でしたね)

よってまだプログラムは一行も実行されていない状態です。

脱線してしまいました。

今回のエラーはarr = Emptyの部分で発生しました。

つまり、arrは配列として宣言してそのものの実態があるのにそれをEmptyにはできない、と言う事です。

考えてみれば私たちがここでやりたいことは、arrの要素をすべてEmptyにしたい、と言う事であって、arrそのものをEmptyにしたわけではありません。

そこで、ループを使って、全ての要素をEmptyにします。各要素にEmptyを格納していくイメージでOKです。

LBound関数とUBound関数

今回は静的配列なので一番小さなインデックスと一番大きなインデックスの数値がいくつか分かっています。なので数字で直接ループの範囲を指定しても構いません。

しかし、今後業務の内容が変更になり、配列に格納したいパラメータ(値)の数が変わることも想定して、配列の一番小さいインデックスと一番大きなインデックスを取得する関数を使う方が
好ましいでしょう。

Sub LBoundとUBound関数()
    
    Dim arr(4) As Variant
    
    Debug.Print LBound(arr)
    Debug.Print UBound(arr)
    
End Sub

実行結果

 0 
 4 

LBound関数、UBound関数の使い方は引数にインデックスの最小値や最大値を取得したい配列名を入れるだけです。

本当はもう一つ引数をとることができるのですが、それは多次元配列のところで解説します。今は、単に配列名を引数として渡す、と覚えればOKです。

*ちなみに引数は「ひきすう」と読みます。プロシジャや関数に渡す入力値のことです。

インデックスの最小値と最大値が取得できたので後はFor~Next文で一つずつEmptyを入れて行きます。

先ほどの、いろいろと挨拶を入れた「実験_配列そのものをEmptyにできるのか」のプロシジャのエラーが出た部分を書き換えます。

 Sub ループで配列の各値を初期化する()
    
    Dim arr(4) As Variant
    
    arr(0) = "Good Morning"
    arr(1) = "Hello"
    arr(2) = "Good Afternoon"
    arr(3) = "Good Evening"
    arr(4) = "Good Night"
    
    Dim i As Long
    
    For i = LBound(arr) To UBound(arr)
        arr(i) = Empty
    Next i
    
End Sub

いつものようにF8で一行ずつ実行していきます。

f:id:mutable_yun:20190922113822p:plain
結果

配列arrの各要素に値が順に格納され、その後、順にEmptyに戻っていく様子が分かります。

実務的なサンプルを作成する

ここまでくれば、もう、実務で使うイメージがわいているのではないでしょうか。

f:id:mutable_yun:20190921184841p:plain
売上データサンプル

今回は上のようなデータをサンプルとして考えます。

各支店の売上のデータです。このデータを分析するために、

  1. 一行分のデータを配列に格納する
  2. 何らかの処理を行う
  3. 配列を初期化する
  4. 次の行に移る

これを最終行まで繰り返します。

Sub 実務的サンプル()
    
    '>下準備で必要な変数や定数を用意しておく
    Const 最初の行rw As Long = 2
    Dim 最終行rw As Long
    
    Const 最初の列clm As Long = 1
    Const 最後の列clm As Long = 4
    
    Dim 売上データ行arr(最初の列clm To 最後の列clm) As Variant
    
    Dim i As Long 'ワークシートの最初の行から行方向にカウントアップする
    Dim j As Long '売上データを格納するためのカウントアップ
    '<下準備ここまで
    
    最終行rw = Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = 最初の行rw To 最終行rw '一番大きなループ。行方向にループする
        
        '>配列にデータを格納
        For j = 最初の列clm To 最後の列clm
            売上データ行arr(j) = Cells(i, j)
        Next j
        '<配列にデータを格納
        
        '>メインの作業
        'ここにメインの作業を書く。Callで別に記述したプロシジャを呼び出すのがおすすめ
        '<メインの作業
        
        '>次の行に移る前に配列を初期化する
        For j = 最初の列clm To 最後の列clm
            売上データ行arr(j) = Empty
        Next j
        '<次の行に移る前に配列を初期化する
    Next i
    
End Sub

はい、できました。便利ですね!これからは配列をうまく使ってコードをシンプルにし、ワークシートへの参照回数を減らしていきましょう。

ワークシートへの参照回数を減らすことはマクロの高速化にもつながりますよ。