ExcelVBA：数字⇔文字列変換

上記の表から、申請者ごとにソフト名とその申請理由をそれぞれカンマ区切りで抽出したい場合に組んだVBA。

Dim i as integer

'1行目の右端まで処理を繰り返す
For i = 2 To ThisWorkbook.Sheets("list").Cells(1, Columns.Count).End(xlToLeft).Column

    user = Cells(1, i)

'templateシートをコピーして一番右に追加する。
    ThisWorkbook.Sheets("template").Copy After:=Sheets(Sheets.Count)
    Set objWorksheet = ActiveSheet
    ActiveSheet.Name = user
    
'数字を列番号に変換
    Dim colNum As Long
    colNum = i
'--- アドレス文字列 ---'
    Dim tmpStr As String
    tmpStr = ThisWorkbook.Worksheets(user).Cells(1, colNum).Address(True, False)
'--- 列番号を格納する変数 ---'
    Dim colAddress As String
    colAddress = Left(tmpStr, InStr(tmpStr, "$") - 1)

'各列の2行目から6行目までを選択し、soft_listに格納
    soft_list = colAddress & "2:" & colAddress & "6" 
'各列の7行目から11行目までを選択し、reason_listに格納
    reason_list = colAddress & "7:" & colAddress & "11"
    
'TextJoin関数を用いて範囲内の文字列を結合
    soft = WorksheetFunction.TextJoin(", ", True, Worksheets("list").range(soft_list))
    reason = WorksheetFunction.TextJoin(", ", True, Worksheets("list").range(reason_list))

ポイント1 　シートのコピー

ThisWorkbook.Sheets("template").Copy After:=Sheets(Sheets.Count)
Set objWorksheet = ActiveSheet
ActiveSheet.Name = user

Set objWorksheet = ActiveSheet　とすることで、コピーした後のシートが選択された状態とすることで、継続して作業が可能。今回の場合は、シート名をuserに変更した。

ポイント2　数字を列番号に変換（Addressプロパティ）

    Dim colNum As Long
    colNum = i
'--- アドレス文字列 ---'
    Dim tmpStr As String
    tmpStr = ThisWorkbook.Worksheets(user).Cells(1, colNum).Address(True, False)
'--- 列番号を格納する変数 ---'
    Dim colAddress As String
    colAddress = Left(tmpStr, InStr(tmpStr, "$") - 1)

Addressプロパティ構文

Cells([行番号], [列番号]).Address([行の相対参照 ], [列の相対参照])

・相対参照か絶対参照かを指定する引数はTRUEかFALSEで指定する。
　TRUEにすれば絶対参照の形式（$が付いた形式）でアドレスを表す文字列が返される。

・列番号を取得するには、行の相対参照のみをTRUEにして値を取得する。
　その結果、「A＄1」のようなアドレスが返されるので、「$」よりも左側を抽出すれば列番号が取得できる。
【出典】http://www.fingeneersblog.com/1483/
・InStr関数は、引数string1で指定した文字列の中から、引数string2で指定した文字列を検索して、見つかった位置を返す。

ポイント3　TextJoin関数
TextJoinプロパティ構文

TextJoin (delimiter、ignore_empty、text1, [text2],...)

引数	説明
delimiter（必須）	区切る文字
ignore_empty（必須）	TRUE の場合、空のセルは無視される。
text1（必須）	結合するテキスト項目。 文字列またはセルの範囲などの文字列の配列。

 soft = WorksheetFunction.TextJoin(", ", True, Worksheets("list").range(soft_list))

range(soft_list)への入力
動かない：range((cells.(1, i): cells.(6, i))
動く：range(A2:A6)
→このためにAddressプロパティで列情報をアルファベットで取得した。