Excel VBA練習 表形式の値を列形式にする

メモ

• 表形式の値を列形式にする
• 使い勝手はユーザー定義関数(配列数式)よりもマクロのほうが良い気がします

表形式

文字列11	文字列12	文字列13
文字列21	文字列22	文字列23
文字列31	文字列32	文字列33

列形式

値	行	列
文字列11	1	1
文字列12	1	2
文字列13	1	3
文字列21	2	1
文字列22	2	2
文字列23	2	3
文字列31	3	1
文字列32	3	2
文字列33	3	3

使い捨てマクロ

ユーザー定義関数

• 配列数式

' Excel 2019, Windows 11
' 標準モジュール

Option Explicit

Public Function MatrixToColArrayFormula(ByVal 表 As Excel.Range) As Variant()
  Dim row As Long, col As Long
  Dim count As Long
  Dim numRows As Long, numCols As Long
  Dim rng() As Variant

  numRows = 表.Rows.count
  numCols = 表.Columns.count

  ReDim rng(1 To 1 + numCols * numRows, 1 To 3)

  rng(1, 1) = "値"
  rng(1, 2) = "行"
  rng(1, 3) = "列"

  count = 1

  For row = 1 To numRows Step 1
    For col = 1 To numCols Step 1
      rng(1 + count, 1) = 表.Cells(row, col).Value
      rng(1 + count, 2) = row
      rng(1 + count, 3) = col
      count = count + 1
    Next col
  Next row

  MatrixToColArrayFormula = rng
End Function

マクロ

' Excel 2019, Windows 11

Option Explicit

Public Sub 表ValueTo列()
  Dim 表 As Excel.Range
  Dim row As Long, col As Long
  Dim count As Long
  Dim numRows As Long, numCols As Long
  Dim rng() As Variant
  Dim wbNew As Excel.Workbook, wsNew As Excel.Worksheet

  Set 表 = Excel.Application.ActiveWindow.RangeSelection

  numRows = 表.Rows.count
  numCols = 表.Columns.count

  ReDim rng(1 To 1 + numCols * numRows, 1 To 3)

  rng(1, 1) = "値"
  rng(1, 2) = "行"
  rng(1, 3) = "列"

  count = 1

  For row = 1 To numRows Step 1
    For col = 1 To numCols Step 1
      rng(1 + count, 1) = 表.Cells(row, col).Value
      rng(1 + count, 2) = row
      rng(1 + count, 3) = col
      count = count + 1
    Next col
  Next row

  Set wbNew = Excel.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet)
  Set wsNew = wbNew.ActiveSheet
  wsNew.Name = "表"

  wsNew.Range(wsNew.Cells(1, 1), wsNew.Cells(UBound(rng, 1), 3)) = rng
End Sub