Excel VBA Java用定数クラス作成マクロ編

＜エクセルのレイアウト＞

３行目
Ｂ列　区分名
Ｃ列　値
Ｄ列　名称
Ｅ列　型（String等）
Ｆ列　配列　○：配列値行番号使用、◎：配列値直接入力使用　
Ｇ列　配列値行番号
Ｈ列　配列値直接入力


４行目以降は値

＜マクロの内容＞


Option Explicit
Private Const 入力開始行 = 4
Private Const 区分名列 = 2
Private Const 値列 = 3
Private Const 名称列 = 4
Private Const 型列 = 5
Private Const 配列列 = 6
Private Const 配列値行番号列 = 7
Private Const 配列値直接入力列 = 8
Private Const 囲む列 = 9
Private Sub クラス作成_Click()
    Dim maxRow As Integer           ' 最終行番号
    Dim outRow As Integer           ' 出力行
    Dim i, j, k, cnt As Integer
    Dim wStr As String              ' ワーク文字
    Dim TS As TextStream            ' TextStream
    Dim FSO As New FileSystemObject ' FileSystemObject
    Dim 配列値行番号() As String
    Dim 配列値直接入力() As String
    Dim 行番号, 直接入力 As Variant
    '最終行取得
    maxRow = ActiveSheet.Range("B65536").End(xlUp).Row
    '入力開始位置確認
    If maxRow < 入力開始行 Then
        MsgBox "値が入力されていません。"
        Exit Sub
    End If
    Set TS = FSO.CreateTextFile( _
        fileName:=ThisWorkbook.Path & "\" & Range("B1").Value & ".java", _
        Overwrite:=True)
   
    TS.WriteLine "/******************************************************************"
    TS.WriteLine " * モジュール名"
    TS.WriteLine " *　" & Range("B1").Value & ".java"
    TS.WriteLine " *"
    TS.WriteLine " * 変更履歴"
    TS.WriteLine " *    日付          変更者　　　　変更概要"
    TS.WriteLine " *　　YYYY/MM/DD    氏　　名      新規作成"
    TS.WriteLine " ******************************************************************"
    TS.WriteLine " */"
    TS.WriteLine
    TS.WriteLine "package jp.co.xxx.common.constant;"
    TS.WriteLine
    TS.WriteLine "/**"
    TS.WriteLine " * 共通定数"
    TS.WriteLine " * <p>"
    TS.WriteLine " * 使用する定数を定義する"
    TS.WriteLine " * </p>"
    TS.WriteLine " */"
    TS.WriteLine "public class " & Range("B1").Value & " {"

    ' 開始行を設定
    outRow = 入力開始行
    ' 最終行まで繰り返す
    Do Until outRow > maxRow
        TS.WriteLine vbTab & "/**"
        'コメントの出力　開始
        TS.WriteLine vbTab & " * " & Cells(outRow, 区分名列).Value & "の" & Replace(Cells(outRow, 名称列).Value, "J_", "", , , vbTextCompare) & "の定数です。"
        TS.WriteLine vbTab & " */"
        'コメントの出力　終了
        If Cells(outRow, 配列列).Value <> "" Then
            wStr = "public static final " & Cells(outRow, 型列).Value _
                    & " C_" & Cells(outRow, 区分名列) & "_" _
                    & Cells(outRow, 名称列).Value & " = {"
            TS.Write vbTab & wStr
            cnt = LenB(StrConv(wStr, vbFromUnicode)) / 4
            If (LenB(wStr) Mod 4) > 0 Then
                cnt = cnt + 1
            End If
            wStr = ""
            For k = 1 To cnt
                wStr = wStr & vbTab
            Next k
            If Cells(outRow, 配列列).Value = "○" Then
                配列値行番号 = Split(Cells(outRow, 配列値行番号列).Value, ",", , vbTextCompare)
                i = UBound(配列値行番号)
                j = 0
                For Each 行番号 In 配列値行番号
                    If j > 0 Then
                        TS.Write wStr
                    End If
                    TS.Write "C_"
                    TS.Write Cells(Int(行番号), 区分名列).Value
                    TS.Write "_"
                    TS.Write Cells(Int(行番号), 名称列).Value
                    If j < i Then
                        TS.WriteLine ", "
                    End If
                    j = j + 1
                Next
            ElseIf Cells(outRow, 配列列).Value = "◎" Then
                配列値直接入力 = Split(Cells(outRow, 配列値直接入力列).Value, ",", , vbTextCompare)
                i = UBound(配列値直接入力)
                j = 0
                For Each 直接入力 In 配列値直接入力
                    If j > 0 Then
                        TS.Write wStr
                    End If
                    If Cells(outRow, 囲む列).Value <> "" Then
                        TS.Write """"
                    End If
                    TS.Write 直接入力
                    If Cells(outRow, 囲む列).Value <> "" Then
                        TS.Write """"
                    End If
                    If j < i Then
                        TS.WriteLine ", "
                    End If
                    j = j + 1
                Next
            End If
            TS.WriteLine "};"
            TS.WriteLine
        Else
            TS.Write vbTab & "public static final "
            TS.Write Cells(outRow, 型列).Value
            TS.Write " C_"
            TS.Write Cells(outRow, 区分名列)
            TS.Write "_"
            TS.Write Cells(outRow, 名称列).Value
            TS.Write " = "
            If Cells(outRow, 型列).Value = "String" Then
                TS.Write """"
            End If
            wStr = Cells(outRow, 値列).Value
            wStr = Replace(wStr, "半角空白", " ", , , vbTextCompare)
            wStr = Replace(wStr, """", "", , , vbTextCompare)
            TS.Write wStr
            If Cells(outRow, 型列).Value = "String" Then
                TS.Write """"
            End If
            TS.WriteLine ";"
            TS.WriteLine
        End If
        ' 行を加算
        outRow = outRow + 1
    Loop
    TS.WriteLine
    TS.WriteLine vbTab & "/**"
    TS.WriteLine vbTab & " * コンストラクタ"
    TS.WriteLine vbTab & " */"
    TS.WriteLine vbTab & "private " & Range("B1").Value & "() {"
    TS.WriteLine vbTab
    TS.WriteLine vbTab & "}"
    TS.WriteLine "}"
    ' 指定ファイルをCLOSE
    TS.Close
    Set TS = Nothing
    Set FSO = Nothing
End Sub