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)
アプリケーションはあなたの想像力によってのみ制限されます。
What Is a VBA Array in Excel and How to Program One
VBA has been part of the Microsoft Officе suite for mаny yeаrs. While it dоesn’t havе the full functionality and powеr of a full VB application, VBA provides Office υsers with the flexibility tо intеgrate Оffice products and aυtomate the work you do in them.
One of the most powerful tools available in VBA is the ability to load an entire range of data into a single variable called an array. By loading data in this way, you can manipulate or perform calculations on that range of data in a variety of ways.
So what is a VBA array? In this article we’ll answer that question, and show you how to use one in your own VBA script.
What Is a VBA Array?
Using a VBA array in Excel is very simple, but understanding the concept of arrays can be a little bit complex if you’ve never used one.
Think of an array like a box with sections inside it. A one-dimensional array is a box with one line of sections. A two-dimensional array is a box with two lines of sections.
You can put data into each section of this “box” in any order you like.
At the beginning of your VBA script, you need to define this “box” by defining your VBA array. So to create an array that can hold one set of data (a one-dimension array), you would write the following line.
Dim arrMyArray(1 To 6) As String
Later in your program, you can place data into any section of this array by referencing the section inside of parentheses.
arrMyArray(1) = "Ryan Dube"
You can create a two-dimensional array using the following line.
Dim arrMyArray(1 To 6,1 to 2) As Integer
The first number represents the row, and the second is the column. So the above array could hold a range with 6 rows and 2 columns.
You can load any element of this array with data as follows.
arrMyArray(1,2) = 3
This will load a 3 into the cell B1.
An array can hold any type of data as a regular variable, such as strings, boolean, integers, floats and more.
The number inside the parenthesis can also be a variable. Programmers commonly use a For Loop to count through all sections of an array and load data cells in the spreadsheet into the array. You’ll see how to do this later in this article.
How to Program a VBA Array in Excel
Let’s take a look at a simple program where you may want to load information from a spreadsheet into a multidimensional array.
As an example, let’s look at a product sales spreadsheet, where you want to pull the sales rep’s name, item, and total sale out of the spreadsheet.
Note that in VBA when you reference rows or columns, you count rows and columns starting from the top left at 1. So the rep column is 3, the item column is 4, and the total column is 7.
To load these three columns over all 11 rows, you’ll need to write the following script.
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
You’ll notice that in order to skip the header row, the row number in the first For look needs to start at 2 rather than 1. This means you need to subtract 1 for the array row value when you’re loading the cell value into the array using Cells(i, j).Value.
Where to Insert Your VBA Array Script
To place program a VBA script in Excel, you need to use the VBA editor. You can access this by selecting the Developer menu and selecting View Code in the Controls section of the ribbon.
If you don’t see the Developer in the menu, you’ll need to add it. To do this, select File and Options to open the Excel Options window.
Change the choose commands from dropdown to All Commands. Select Developer from the left menu and select the Add button to move it to the pane on the right. Select the checkbox to enable it and select OK to finish.
When the Code Editor window opens, make sure the sheet your data is in is selected in the left pane. Select Worksheet in the left dropdown and Activate in the right. This will create a new subroutine called Worksheet_Activate().
This function will run whenever the spreadsheet file is opened. Paste the code into the script pane inside this subroutine.
This script will work through the 12 rows and will load the rep name from column 3, the item from column 4, and the total sale from column 7.
Once both For loops are finished, the two dimensional array arrMyArray contains all of the data you specified from the original sheet.
Manipulating Arrays in Excel VBA
Let’s say you want to apply a 5% sales tax to all of the final sale prices, and then write all of the data to a new sheet.
You can do this by adding another For loop after the first, with a command to write the results to a new sheet.
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
This will “unload” the entire array into Sheet2, with an additional row containing the total multiplied by 5% for the tax amount.
The resulting sheet will look like this.
As you can see, VBA arrays in Excel are extremely useful and as versatile as any other Excel trick.
The example above is a very simple use for an array. You can create much larger arrays and perform sorting, averaging, or many other functions against the data you store in them.
If you want to get real creative, you could even create two arrays containing a range of cells from two different sheets, and perform calculations between elements of each array.
The applications are limited only by your imagination.
