【知らなくても使える】複数セルの値を一つのセルに貼り付けるアドイン（Excel VBA）

Option Explicit

'-----（設定値）------------------------
Private Const SEPARATE_VALUE = ","        '１．区切り文字の初期値
'---------------------------------------

Dim SepValue As String

'===================================
'複数セルをコピーした際の処理
'===================================
Sub Copy_Click(control As IRibbonControl)
    
    Dim tmpValueArr() As Variant
    Dim cntRow, cntCol, i, j As Long
    Dim unionValue As String
    Dim tmpValue As String

    Dim c As Range
    Set c = Selection
    
    '選択されたセルが一つの場合はそのまま代入する
    If Selection.Cells.Count = 1 Then
        unionValue = c.Value
    Else
        tmpValueArr = c.Value
        cntRow = UBound(tmpValueArr)
        cntCol = UBound(tmpValueArr, 2)
        
        For i = 1 To cntRow
            For j = 1 To cntCol
                If j = 1 Then
                    tmpValue = tmpValueArr(i, j)
                Else
                    tmpValue = tmpValue & SepValue & tmpValueArr(i, j)
                End If
            Next
            If i = 1 Then
                unionValue = tmpValue
            Else
                unionValue = unionValue & vbLf & tmpValue
            End If
        Next
    End If
    
    'クリップボードにコピーする
    With CreateObject("Forms.TextBox.1")
        .MultiLine = True
        .text = unionValue
        .selstart = 0
        .sellength = .textlength
        .Copy
    End With
    
End Sub

'===================================
'一つのセルに貼り付けをした際の処理
'===================================
Sub Paste_Click(control As IRibbonControl)

    Dim Form As Object: Set Form = CreateObject("Forms.TextBox.1")
    Form.MultiLine = True
    
    'クリップボードに貼り付ける
    If Form.CanPaste = True Then Form.Paste
    Selection.Value = Form.text

End Sub

'-------------------------
'区切り文字のテキスト内容を取得する際の処理
'-------------------------
Sub editBox_getText(control As IRibbonControl, ByRef returnValue)
    SepValue = SEPARATE_VALUE
    returnValue = SepValue
End Sub

'-------------------------
'区切り文字に変更があった際の処理
'-------------------------
Sub onChange(control As IRibbonControl, text As String)
    SepValue = text
End Sub