excel不是最新版的,没有textjoin函数,写两个用。
函数一:
pastestr():优点:简单,第二个参数可以省略;
缺点:只能是一个连续区域,或小括号内的(多个不连续单一元格)。
1 Function pastestr(rng, Optional sep As String = "") 2 '将rng中的值,用分隔符sep连接在一起 3 Dim r 4 pastestr = "" 5 For Each r In rng 6 If r <> "" Then 7 If pastestr = "" Then 8 pastestr = r 9 Else 10 pastestr = pastestr & sep & r 11 End If 12 End If 13 Next 14 15 End Function
函数二:
textjoin()优点:第三个参数是不定长参数,可以输入多个区域,或干脆是个string;
缺点:所有参数必须输入,不可省略。
1 Function textjoin(sep As String, ignore_empty As Boolean, ParamArray arr()) As String 2 '将arr()中的值,用分隔符sep连接在一起 3 Dim r,rr 4 Dim n As Integer 5 textjoin = "" 6 For r = LBound(arr) To UBound(arr) 7 If TypeName(arr(r)) <> "Range" Then 8 If (ignore_empty = True And arr(r) <> "") Or ignore_empty = False Then 'ignore_empty = True时没提r ="",相当于忽视了 9 '上条件可以写成if not (ignore_empty=true and arr(r)="") then 10 If n = 0 Then 11 textjoin = arr(r) 12 n = n + 1 13 Else 14 textjoin = textjoin & sep & arr(r) 15 n = n + 1 16 End If 17 End If '1ignore 18 Else 19 20 For Each rr In arr(r) 21 If (ignore_empty = True And rr.Value <> "") Or ignore_empty = False Then 22 23 If n = 0 Then 24 textjoin = rr.Value 25 n = n + 1 26 Else 27 textjoin = textjoin & sep & rr.Value 28 n = n + 1 29 End If 30 End If 31 Next ' 32 End If 33 Next 34 End Function
标签:vba,End,函数,自定义,sep,arr,pastestr,ignore,textjoin From: https://www.cnblogs.com/yjyblog/p/16988093.html