VBA 行列 – 作成および詳細

このチュートリアルでは、VBAで配列を使用して行列を作成する方法を説明します。

行列は、行と列に相当する数値を持つ2次元の配列で表すことができます。VBAで行列を作成するには、Array型変数に値を入力し、VBAループを使用します。

VBAで行列を作成する

Sub CreateSimpleMatrix()
   Dim matrix() As Integer
   Dim x, i, j, k As Integer
'配列の大きさを変更する
   ReDim matrix(1 To 3, 1 To 3) As Integer
   x = 1
   For i = 1 To 3
      For j = 1 To 3
         matrix(i, j) = x
         x = (x + 1)
      Next j
   Next i
'結果を一度にシートに戻す
   Range("A1:C3") = matrix
End Sub

上記のプロシージャを実行すると、Range(“A1:C3”)に単純な行列がワークシートに返されます。

一行のベクトルを行列に変換する

次のような数値の列を考えてみましょう。数値の列を行列に変換したいと思うことがあるかもしれません。

以下の関数を使用すると、数値のリストから行列を作成することができます。

Function Create_Matrix(Vector_Range As Range, No_Of_Cols_in_output As Integer, No_of_Rows_in_output As Integer) As Variant
   ReDim Temp_Array(1 To No_Of_Cols_in_output, 1 To No_of_Rows_in_output)
   Dim No_Of_Elements_In_Vector As Integer
   Dim Col_Count As Integer, Row_Count As Integer

   No_Of_Elements_In_Vector = Vector_Range.Rows.Count

'NULL条件を排除する
   If Vector_Range Is Nothing Then Exit Function
   If No_Of_Cols_in_output = 0 Then Exit Function
   If No_of_Rows_in_output = 0 Then Exit Function
   If No_Of_Elements_In_Vector = 0 Then Exit Function

   For Col_Count = 1 To No_Of_Cols_in_output
      For Row_Count = 1 To No_of_Rows_in_output
         Temp_Array(Col_Count, Row_Count) = Vector_Range.Cells(((No_of_Rows_in_output) * (Col_Count - 1) + Row_Count), 1)
      Next Row_Count
   Next Col_Count
   Create_Matrix = Temp_Array

End Function

上記の関数を呼び出すと、Excelシートに行列を作成することができます。

Sub ConvertToMatrix()
   Range("C1:H2") = Create_Matrix(Range("A1:A10"), 2, 6)
End Sub

行列を1行のベクトルに変換する

また、行列を1行に変換することもできます。次のような行列を考えてみましょう。

これを以下の関数で1次元の配列に変換します。

Fcuntion Create_Vector(Matrix_Range As Range) As Variant
   Dim No_of_Cols As Integer, No_Of_Rows As Integer
   Dim i As Integer
   Dim j As Integer
'行列から行と列を得る
   No_of_Cols = Matrix_Range.Columns.Count
   No_Of_Rows = Matrix_Range.Rows.Count
   ReDim Temp_Array(No_of_Cols * No_Of_Rows)
'NULLを排除する
   If Matrix_Range Is Nothing Then Exit Function
   If No_of_Cols = 0 Then Exit Function 
   If No_of_Rows = 0 Then Exit Function
'配列の最初の要素までループする
   For j = 1 To No_Of_Rows
'次に2番目の要素までループする
      For i = 0 To No_of_Cols - 1
'1次元の一時配列に代入する
         Temp_Array((i * No_Of_Rows) + j) = Matrix_Range.Cells(j, i + 1)
      Next  i
   Next j
   Create_Vector = Temp_Array
End Function

この関数を呼び出すには、以下のプロシージャを使用します。

Sub GenerateVector()
   Dim Vector() As Variant
   Dim k As Integer
   Dim No_of_Elements
'配列の取得
   Vector = Create_Vector(Sheets("Sheet1").Range("A1:D5"))
'配列をループしてシートに入力する
   For k = 0 To UBound(Vector) - 1
      Sheets("Sheet1").Range("G1").Offset(k, 0).Value = Vector(k + 1)
   Next k
End SUb

このプロシージャは、次のような結果を返します。

WorksheetFunction.MMULTを使った行列配列の作成

次のようなスプレッドシートを考えてみましょう。

以下のプロシージャを使用して、金利とその人が借りたい金額に基づいて、上のグリッドの金利額を計算することができます。

Sub UseMMULT()
   Dim rngIntRate As Range
   Dim rngAmtLoan As Range 
   Dim Result() As Variant
'Rangeオブジェクトを設定する
   Set rngIntRate = Range("B4:B9")
   Set rngAmtLoan = Range("C3:H3")
'MMULT 計算式を使用してResultを埋める
   Result = WorksheetFunction.MMult(rngIntRate, rngAmtLoan)
'シートに入力する
   Range("C4:H9") = Result
End Sub

上記のシートでは、上記のプロシージャが数式ではなく値をセルに入力していることにお気づきでしょう。上の図のC4を見てください。 このように ワークシート関数 を使用すると、数式ではなく、常に静的な値がワークシートに返されます。 つまり、金利や融資額が変わっても、マトリックスに入力された値は変化しません。

WorksheetFunction.MMULT を使用する代わりに、VBA を使用してFormulaArray メソッドを使用して MMULT 関数をセルに適用することができます。

Sub InsertMMULT()
   Range("C4:H9").FormulaArray = "=MMULT(B4:B9,C3:H3)"
End Sub

これで、スプレッドシートが入力されると、セルで数式が使用されることに注意してください。