|
我写出来了
Function 文本连接(分隔符组, 忽略空值, ParamArray 引用或数组() As Variant) As Variant
'第一参数为分隔符,后续会可选多区域多类型,空值将被忽略
'可传递内存数组参数
Dim tmptext As Variant, i As Variant, cellv As Variant
Dim cell As Range
tmptext = ""
sz = IsArray(分隔符组)
If sz Then sb = UBound(分隔符组)
If Not sz Then
For i = 0 To UBound(引用或数组) '遍历参数数组
If Not IsMissing(引用或数组(i)) Then '判断参数是否有效
Select Case Right(TypeName(引用或数组(i)), 2) '对不同的参数类型采取不同的处理方式,数组类型可能有多种类型,所以判断最后一个字符
Case "ge" 'Range单元格区域类型,直接遍历
For Each cell In 引用或数组(i)
If 忽略空值 = 0 Then GoTo 1
If cell <> "" Then
1
If k = 0 Then
tmptext = cell.Value
k = k + 1
Else
tmptext = tmptext & 分隔符组 & cell.Value
k = k + 1
End If
If 忽略空值 = 0 Then GoTo 2
End If
2
Next cell
Case "()" '数组类型,
For Each cellv In 引用或数组(i)
If 忽略空值 = 0 Then GoTo 3
If cellv <> "" Then
3
If k = 0 Then
tmptext = cellv
k = k + 1
Else
tmptext = tmptext & 分隔符组 & cellv
k = k + 1
End If
If 忽略空值 = 0 Then GoTo 4
End If
4
Next cellv
Case Else '其他类型,即单个值参数
If 忽略空值 = 0 Then GoTo 5
If 引用或数组(i) <> "" Then
5
If k = 0 Then
tmptext = 引用或数组(i)
k = k + 1
Else
tmptext = tmptext & 分隔符组 & 引用或数组(i)
k = k + 1
End If
If 忽略空值 = 0 Then GoTo 6
End If
6
End Select
End If
Next i
Else
For i = 0 To UBound(引用或数组) '遍历参数数组
If Not IsMissing(引用或数组(i)) Then '判断参数是否有效
Select Case Right(TypeName(引用或数组(i)), 2) '对不同的参数类型采取不同的处理方式,数组类型可能有多种类型,所以判断最后一个字符
Case "ge" 'Range单元格区域类型,直接遍历
For Each cell In 引用或数组(i)
If 忽略空值 = 0 Then GoTo 7
If cell <> "" Then
7
If k = 0 Then
tmptext = cell.Value
k = k + 1
Else
kk = k Mod sb
If kk = 0 Then kk = sb
tmptext = tmptext & 分隔符组(kk) & cell.Value
k = k + 1
End If
If 忽略空值 = 0 Then GoTo 8
End If
8
Next cell
Case "()" '数组类型,
For Each cellv In 引用或数组(i)
If 忽略空值 = 0 Then GoTo 9
If cellv <> "" Then
9
If k = 0 Then
tmptext = cellv
k = k + 1
Else
kk = k Mod sb
If kk = 0 Then kk = sb
tmptext = tmptext & 分隔符组(kk) & cellv
k = k + 1
End If
If 忽略空值 = 0 Then GoTo 10
End If
10
Next cellv
Case Else '其他类型,即单个值参数
If 忽略空值 = 0 Then GoTo 11
If 引用或数组(i) <> "" Then
11
If k = 0 Then
tmptext = 引用或数组(i)
k = k + 1
Else
kk = k Mod sb
If kk = 0 Then kk = sb
tmptext = tmptext & 分隔符组(kk) & 引用或数组(i)
k = k + 1
End If
If 忽略空值 = 0 Then GoTo 12
End If
12
End Select
End If
Next i
End If
文本连接 = tmptext
End Function
和Excel高版本的textejoin 完全一样的应用 |
评分
-
2
查看全部评分
-
|