Excel VBA練習 Evaluateメソッド / 角括弧 [ ]

メモ

• Excel VBA での角括弧[ ]はExcel.Application.Evaluateメソッド
• PowerPoint VBA で角括弧[ ]を使用する場合は Microsoft Excel xx.x Object Library を参照設定

参考資料リンク

• Guidelines and examples of array formulas - Microsoft Support
• Refer to Cells by Using Shortcut Notation | Microsoft Learn
• Application.Evaluate method (Excel) | Microsoft Learn
• External name not defined | Microsoft Learn

使い捨てマクロ

• Variant/Variant(1 to m, 1 to n)

' Excel 2019, Windows 11 (2023年7月頃)

Option Explicit

Public Sub Main()
  Dim xlWorksheet As Excel.Worksheet
  Set xlWorksheet = Excel.Application.ActiveSheet

  ' 配列数式
  xlWorksheet.Range("[Book1]Sheet1!A1:D3").FormulaArray = "={1, 2, 3, 4; 5, 6, 7, 8; 9, 10, 11, 12}"

  Dim var1 As Variant, var2 As Variant
  var1 = Excel.Application.Evaluate("[Book1]Sheet1!A1").FormulaArray
  var2 = [[Book1]Sheet1!A1].FormulaArray
  ' var2  "={1,2,3,4;5,6,7,8;9,10,11,12}"  Variant/String

  Dim var3 As Variant, var4 As Variant
  var3 = Excel.Application.Evaluate("={1, 2, 3, 4; 5, 6, 7, 8; 9, 10, 11, 12}")
  var4 = [={1, 2, 3, 4; 5, 6, 7, 8; 9, 10, 11, 12}]
  ' var4               Variant/Variant(1 to 3, 1 to 4)
  '   var4(1)          Variant(1 to 4)
  '   var4(2)          Variant(1 to 4)
  '   var4(3)          Variant(1 to 4)
  '     var4(3,1)  9   Variant/Double
  '     var4(3,2)  10  Variant/Double
  '     var4(3,3)  11  Variant/Double
  '     var4(3,4)  12  Variant/Double

  Dim var5 As Variant, var6 As Variant
  var5 = Excel.Application.Evaluate("{1, 2, 3, 4; 5, 6, 7, 8; 9, 10, 11, 12}")
  var6 = [{1, 2, 3, 4; 5, 6, 7, 8; 9, 10, 11, 12}]
  ' var6               Variant/Variant(1 to 3, 1 to 4)
  '   var6(1)          Variant(1 to 4)
  '   var6(2)          Variant(1 to 4)
  '   var6(3)          Variant(1 to 4)
  '     var6(3,1)  9   Variant/Double
  '     var6(3,2)  10  Variant/Double
  '     var6(3,3)  11  Variant/Double
  '     var6(3,4)  12  Variant/Double

  Dim var7(1 To 3, 1 To 4) As Double
  var7(1, 1) = 1: var7(1, 2) = 2:  var7(1, 3) = 3:  var7(1, 4) = 4
  var7(2, 1) = 5: var7(2, 2) = 6:  var7(2, 3) = 7:  var7(2, 4) = 8
  var7(3, 1) = 9: var7(3, 2) = 10: var7(3, 3) = 11: var7(3, 4) = 12
  ' var7               Double(1 to 3, 1 to 4)
  '   var7(1)          Double(1 to 4)
  '   var7(2)          Double(1 to 4)
  '   var7(3)          Double(1 to 4)
  '     var7(3,1)  9   Double
  '     var7(3,2)  10  Double
  '     var7(3,3)  11  Double
  '     var7(3,4)  12  Double

  Debug.Assert False

End Sub