ExcelのVBA配列とは何ですか?1つをプログラムする方法

VBAは、長年にわたってMicrosoftOfficeスイートの一部です。完全なVBアプリケーションの完全な機能とパワーを備えているわけではありませんが、VBAは、Officeユーザーに、 (Office)Office製品を統合し、それらで行う作業を自動化する柔軟性を提供します。

VBAで利用できる最も強力なツールの1つは、データの全範囲を配列と呼ばれる単一の変数にロードする機能です。この方法でデータをロードすることにより、さまざまな方法でその範囲のデータを操作または計算することができます。

では、VBAアレイとは何ですか?この記事では、その質問に答え、独自のVBAスクリプト(your own VBA script)で使用する方法を示します。

VBAアレイとは何ですか?

Excelで(Excel)VBA配列を使用するのは非常に簡単ですが、配列を使用したことがない場合は、配列の概念を理解するのが少し複雑になる可能性があります。

(Think)内部にセクションがあるボックスのような配列を考えてみてください。1次元配列は、1行のセクションを持つボックスです。2次元配列は、2行のセクションを持つボックスです。

この「ボックス」の各セクションには、好きな順序でデータを入れることができます。

VBAスクリプトの最初に、VBA配列を定義してこの「ボックス」を定義する必要があります。したがって、1セットのデータを保持できる配列(1次元配列)を作成するには、次の行を記述します。

Dim arrMyArray(1 To 6) As String

プログラムの後半で、括弧内のセクションを参照することにより、この配列の任意のセクションにデータを配置できます。

arrMyArray(1) = "Ryan Dube"

次の行を使用して、2次元配列を作成できます。

Dim arrMyArray(1 To 6,1 to 2) As Integer

最初の数字は行を表し、2番目の数字は列を表します。したがって、上記の配列は、6行2列の範囲を保持できます。

この配列の任意の要素に、次のようにデータをロードできます。

arrMyArray(1,2) = 3

これにより、セルB1に3がロードされます。

配列は、文字列、ブール値、整数、浮動小数点数など、あらゆるタイプのデータを通常の変数として保持できます。

括弧内の数字も変数にすることができます。プログラマーは通常、Forループ(Loop)を使用して配列のすべてのセクションをカウントし、スプレッドシートのデータセルを配列にロードします。これを行う方法については、この記事の後半で説明します。

ExcelでVBAアレイをプログラムする方法

スプレッドシートから多次元配列に情報をロードしたい簡単なプログラムを見てみましょう。

例として、製品販売スプレッドシートを見てみましょう。ここでは、スプレッドシートから営業担当者の名前、アイテム、および総売上を引き出します。

VBAでは、行または列を参照するときに、左上から1で始まる行と列(rows and columns)をカウントすることに注意してください。したがって、rep列は3、item列は4、total列は7です。

これらの3つの列を11行すべてにロードするには、次のスクリプトを作成する必要があります。

Dim arrMyArray(1 To 11, 1 To 3) As String
Dim i As Integer, j As Integer
For i = 2 To 12
For j = 1 To 3
arrMyArray(i-1, j) = Cells(i, j).Value
Next j
Next i

ヘッダー行をスキップするには、最初のFor lookの行番号が1ではなく2から始まる必要があることに気付くでしょう。これは、セル値をロードするときに、配列行の値から1を引く必要があることを意味します。Cells(i、j).Valueを使用して配列に挿入します。

VBA配列スクリプト(Your VBA Array Script)を挿入する場所

プログラムをExcelに(Excel)VBAスクリプトを配置するには、 VBAエディターを使用する必要があります。これにアクセスするには、 [開発者(Developer)]メニューを選択し、リボンの[コントロール]セクションで(Controls)[コード(View Code)の表示]を選択します。

メニューに開発者(Developer)が表示されない場合は、追加する必要があります。これを行うには、[ファイル(File)オプション](Options)を選択して[ Excelのオプション]ウィンドウを開きます。

選択コマンドをドロップダウンからすべてのコマンド(All Commands)に変更します。左側のメニューから[開発者(Developer)]を選択し、[追加(Add)]ボタンを選択して右側のペインに移動します。チェックボックスを選択して有効にし、[ OK]を選択して終了します。

コードエディタ(Code Editor)ウィンドウが開いたら、データが含まれているシートが左側のペインで選択されていることを確認します。左側のドロップダウンで[ワークシート(Worksheet)]を選択し、右側で[アクティブ化(Activate)]を選択します。これにより、 Worksheet_Activate(Worksheet_Activate)()という新しいサブルーチンが作成されます。

この関数は、スプレッドシートファイルが開かれるたびに実行されます。このサブルーチン内のスクリプトペインにコードを貼り付けます。

このスクリプトは12行で機能し、列3から担当者名、列4からアイテム、列7から総売上高をロードします。

両方のForループが終了すると、2次元配列arrMyArrayには、元のシートから指定したすべてのデータが含まれます。

ExcelVBAで配列を操作する

すべての最終販売価格に5%の消費税を適用してから、すべてのデータを新しいシートに書き込むとします。

これを行うには、最初のループの後に別のForループを追加し、結果を新しいシートに書き込むコマンドを使用します。

For k = 2 To 12
Sheets("Sheet2").Cells(k, 1).Value = arrMyArray(k - 1, 1)
Sheets("Sheet2").Cells(k, 2).Value = arrMyArray(k - 1, 2)
Sheets("Sheet2").Cells(k, 3).Value = arrMyArray(k - 1, 3)
Sheets("Sheet2").Cells(k, 4).Value = arrMyArray(k - 1, 3) * 0.05
Next k

これにより、配列全体がSheet2(Sheet2)に「アンロード」され、合計に税額の5%を掛けた行が追加されます。

結果のシートは次のようになります。

ご覧のとおり、 Excelの(Excel)VBA配列は非常に便利で、他のExcelのトリック(any other Excel trick)と同じように用途が広いです。 

上記の例は、配列の非常に単純な使用法です。はるかに大きな配列を作成し、それらに格納されているデータに対して並べ替え、平均化、またはその他の多くの機能を実行できます。

真のクリエイティブを実現したい場合は、2つの異なるシートからセルの範囲を含む2つの配列を作成し、各配列の要素間で計算を実行することもできます。(containing a range of cells)

アプリケーションはあなたの想像力によってのみ制限されます。



About the author

私は 10 年以上の経験と Android デバイスでの作業経験を持つコンピューター技術者です。また、過去 5 年間オフィスで働いており、Office 365 と MacOS の使い方を学びました。余暇には、屋外で音楽を演奏したり、映画を見たりするのを楽しんでいます。



Related posts