VBAにExcel関数を挿入しましたが構文エラーとなります。

投稿者 : はなこ     投稿日時 : 2022/05/23(Mon) 18:36:41     OS : 未指定     EXCEL : 未指定

---

ファイル名称をVBAで取得し、ファイル名の中にある氏名を取得したいです。

■実現したいこと
　例：日報サンプル_松本.xlsx→松本
　　氏名は１文字や３文字など可変するので対応したい。
　　氏名の前の「日報サンプル_」「.xlsx」は変化しない

■挿入したいExcel関数
=MID(A1,RIGHT(14-6),FIND(".xlsx",A1)-1-FIND("_",A1,1))

■エラー
赤文字になる
構文エラーとでる

解決方法お分かりになりますでしょうか。
宜しくお願いします。

投稿者 : tok     投稿日時 : 2022/05/23(Mon) 19:09:05

---

とりあえず、255文字以内の数式ならEvaluateしちゃえばなんとかなります。
https://docs.microsoft.com/ja-jp/office/vba/api/excel.application.evaluate

Sub test()
    氏名 = [MID(A1,RIGHT(14-6),FIND(".xlsx",A1)-1-FIND("_",A1,1))]
End Sub

投稿者 : はなこ     投稿日時 : 2022/05/23(Mon) 19:32:06

---

tokさん
ご返信ありがとうございます。
Evaluateの方法は知りませんでした。勉強になります。
ありがとうございます。

追加で質問なのですが対象が複数の場合、

日報サンプル_松本.xlsx
日報サンプル_田中.xlsx
日報サンプル_遠藤.xlsx
日報サンプル_金.xlsx

複数のファイルの場合、for ループで回そうと考えています。
どのように書けばよいのでしょうか。





■[返信 1] tokさん（2022-05-23 19:09:05）の記事
＞ とりあえず、255文字以内の数式ならEvaluateしちゃえばなんとかなります。
＞ https://docs.microsoft.com/ja-jp/office/vba/api/excel.application.evaluate
＞ Sub test()
＞ 氏名 = [MID(A1,RIGHT(14-6),FIND(".xlsx",A1)-1-FIND("_",A1,1))]
＞ End Sub
＞

投稿者 : tok     投稿日時 : 2022/05/23(Mon) 23:35:53

---

ループは基本不要です。

Sub test()
    Dim 氏名s()
    '範囲固定
    氏名s = [MID(A1:A4,RIGHT(14-6),FIND(".xlsx",A1:A4)-1-FIND("_",A1:A4,1))]
    
    '範囲可変
    Dim sad As String
    Dim s As String
    s = "MID(address,RIGHT(14-6),FIND("".xlsx"",address)-1-FIND(""_"",address,1))"
    sad = Range("a1", Cells(Rows.Count, "a").End(xlUp)).Address
    氏名s = Application.Evaluate(Replace(s, "address", sad))
    
    'ループ
    Dim r As Range
    Dim 氏名 As String
    For Each r In Range("a1", Cells(Rows.Count, "a").End(xlUp))
        sad = r.Address
        氏名 = Application.Evaluate(Replace(s, "address", sad))
    Next
End Sub

投稿者 : はなこ     投稿日時 : 2022/05/26(Thu) 16:06:04

---

tok様
ご返信ありがとうごじざいます。

変数氏名の中にファイルの中の氏名があることを確認できました。
ありがとうございます。

質問なのですが、、、
Replaceの"address"はどこのことを指しているのでしょうか。
Replaceを調べてみたのですが、なぜこのような処理になっているのか
理解が追い付いておらず可能であれば教えていただきたいです。
Replace(文字列, 検索文字, 置換文字)


もし可能であればB列にその処理結果を書き込むにはどうすればいいでしょうか。
考えてみたのですがわかりませんでした。

投稿者 : tok     投稿日時 : 2022/05/26(Thu) 18:30:58

---

元の数式
＞=MID(A1,RIGHT(14-6),FIND(".xlsx",A1)-1-FIND("_",A1,1))
（ところで、RIGHT(14-6)って無駄じゃ無い？）
を
"A1"を可変に出来るよう文字列修正し、分り易くしたつもり（"A1" ⇒　"address")
＞ s = "MID(address,RIGHT(14-6),FIND("".xlsx"",address)-1-FIND(""_"",address,1))"

これなら解りますか？
s = "MID(アドレス,RIGHT(14-6),FIND("".xlsx"",アドレス)-1-FIND(""_"",アドレス,1))"
＞ sad = Range("a1", Cells(Rows.Count, "a").End(xlUp)).Address
氏名s = Application.Evaluate(Replace(s, "アドレス", sad))
文字列　：　s
検索文字： "アドレス"
置換文字： sad

＞なぜこのような処理になっているのか
Evaluateの引数は文字列である必要があるため、アドレス部を３回生成して書くより、
1回置換の方が分り易いかな？と思ったため（全て裏目のようでした）

＞もし可能であればB列にその処理結果を書き込むにはどうすればいいでしょうか。
範囲固定バージョン

Sub test()
    Range("b1:b4").Value = [MID(A1:A4,RIGHT(14-6),FIND(".xlsx",A1:A4)-1-FIND("_",A1:A4,1))]
End Sub

範囲可変バージョンはトライ下さい。

投稿者 : はなこ     投稿日時 : 2022/05/27(Fri) 14:06:27

---

tok様
ご丁寧な回答ありがとうございました。
助かりました。