【EXCEL VBA】ユーザー定義関数の設定

質問

ネットより拝借した下記ユーザー定義関数を使用して、複数の選択したセルの値を区切り文字（セミコロン）で連結する仕組みを作成している途中です。
（例えば、A;B;C;D のように）
Function myJoin(範囲 As Range, Optional 区切り文字 As String) As Variant
Dim c As Range, buf As String
If 範囲.Rows.Count = 1 Or 範囲.Columns.Count = 1 Then
For Each c In 範囲
buf = buf & 区切り文字 & c.Value
Next c
If 区切り文字 <> "" Then
myJoin = Mid$(buf, 2)
Else
myJoin = buf
End If
Else
myJoin = CVErr(xlErrRef) 'エラー値
End If
End Function

セル(F6)からセル(Fxx)までデータが入力されています。(xxは変動します。)
これらのデータをユーザー定義関数を使ってセル(F1)に[=myJoin (F6:Fxx,";")]を
入力するためのマクロを組んでみたのですが
「コンパイルエラー修正候補:区切り文字または)」となってしまいます。
（添付画像を参照願います。）
手入力でセル(F1)にユーザー定義関数(例:=myJoin (F6:F8,";"))を設定すると問題なくデータの連結が出来ます。このエラーを解消するにはVBE上でどのように定義したらいいのか、ご教授の程宜しくお願い致します。

回答No.2

SI299792 · ベストアンサー

エラーになっているところは、文法上のミスです。""から間違っています。

プロブラムを見ると効率が悪いです。Macro1のようにすれば、ループは不要です。

見ていて思ったのですが、データを変更すると、プログラムを動かさなければなりません。であれば、マクロで関数を入れるのではなく、直接データを入れればいいのでは？ Macro2のようにすればmyJoinは不要になり、効率がいいです。

関数を直すという方法もあります。空白セルがあれば、それ以降連結しないようにしました。これなら、Macro6は不要となり、F1に=myJoin(F6:F100,";")を入れておけばいい、いちいちマクロを動かす必要はなくなります。
'
Option Explicit
'
Sub Macro1()
'
　　Dim gyo As Long
'
　　gyo = [F6].End(xlDown).Row
'
　　gyo = Cells(Rows.Count, "F").End(xlUp).Row
　　[F1] = "=myjoin(F6:F" & gyo & ","";"")"
End Sub
'
Sub Macro2()
'
　　Dim gyo As Long
　　Dim buf As String
'
　　For gyo = 6 To Cells(Rows.Count, "F").End(xlUp).Row
　　　　buf = buf & ";" & Cells(gyo, "F")
　　Next gyo
　　[F1] = Mid(buf, 2)
End Sub
'
Function myJoin(範囲 As Range, Optional 区切り文字 As String) As Variant
'
　　Dim c As Range
　　Dim buf As String
　　Dim ILen As Integer

　　If 範囲.Rows.Count <> 1 And 範囲.Columns.Count <> 1 Then
　　　　myJoin = CVErr(xlErrRef) 'エラー値
　　　　Exit Function
　　End If
'
　　For Each c In 範囲
'
　　　　If c = "" Then
　　　　　　Exit For
　　　　End If
　　　　buf = buf & 区切り文字 & c
　　Next c
　　ILen = Len(区切り文字)
　　myJoin = Mid$(buf, ILen + 1)
End Function

お礼

ご回答頂き有難うございます。ご指摘頂いた内容がとてもよく分かりました。シンプルで効率の良い方法をご教授頂き本当に有難うございました。
無事解決出来ました。

回答No.1

SI299792

Macro6のほうが間違っています。
"=myjoin(F6:F" & gyo - 1 & ","";"")"
にしてください。