如何修改Excel VBA textjoiner函数使其同时支持横纵向范围文本拼接

修改逻辑 #

你的原始函数仅支持nx1的纵向一维范围，只需要新增行列维度判断逻辑，即可同时兼容1xn的横向一维范围，修改后还额外兼容单个单元格的输入场景。

完整修改后代码 #

Function textjoiner(x As Range, Optional delimiter As String = ",") As String
    Dim darray() As Variant
    Dim darray2() As Variant
    Dim counter As Long, i As Long
    
    ' 兼容单个单元格输入场景
    If x.Cells.Count = 1 Then
        textjoiner = x.Value
        Exit Function
    End If
    
    darray = x.Value
    ' 判断范围是横向还是纵向
    If UBound(darray, 1) = 1 Then
        ' 1行多列的横向范围，遍历列维度取值
        counter = UBound(darray, 2)
        ReDim darray2(1 To counter)
        For i = 1 To counter
            darray2(i) = darray(1, i)
        Next i
    Else
        ' 多行1列的纵向范围，沿用原有逻辑
        counter = UBound(darray, 1)
        ReDim darray2(1 To counter)
        For i = 1 To counter
            darray2(i) = darray(i, 1)
        Next i
    End If
    
    textjoiner = Join(darray2, delimiter)
End Function

核心修改点 #

• 新增单个单元格判断分支，避免输入单个单元格时数组转换报错
• 新增范围维度判断逻辑：当Range转成的二维数组第一维上限为1时，判定为横向范围，遍历第二维（列维度）取值
• 保留原有纵向范围的处理逻辑，不影响原有使用习惯

内容的提问来源于stack exchange，提问作者Henrietta Martingale