【VBA】Excelの関数に入っているファイル名を削除するVBAマクロ

はじめに

Excelの関数に入っているファイル名を削除する必要があるため、調査とテストマクロを実装した結果、パフォーマンスの良いマクロを実装できたので、本記事にします。

調査した経緯

マクロファイルと納品ファイルを別ファイルにしています。
集計結果をマクロファイルから納品ファイルにコピーする際、関数を使っているため、関数内にマクロファイルのファイル名がファイル名参照に残っていました。マクロファイル、納品ファイル、双方参照するデータシートが同じ名前で存在するため、マクロファイルを関数から取り除く必要があるため。

実装したソースコード

実装したソースコードは下記になります

'関数に入っているファイル名を取り除く処理

Sub remove_test2()
    'ワークブック変数の定義
    Dim wb1 As Workbook
    'ワークシート変数の定義
    Dim ws1, ws2 As Worksheet
    '開始行、最終行の変数定義
    Dim first_row_num, last_row_num As Long
    'カウント変数の定義
    Dim i As Long
    '修正前の関数の入った変数定義
    Dim value As String
    '修正後の関数の入った変数定義
    Dim result As String
    'マクロファイル名の変数定義
    Dim modefy_file As String
    'データファイルの変数定義
    Dim file_name As String
    'セルのデータ班の変数定義
    Dim cell As Range
    
    file_name = "C:\chargeability\コピー先関数データ.xlsx"
    modefy_file = "関数ファイル名取り除きテスト.xlsm"
    Set wb1 = Workbooks.Open(file_name)
    Set ws1 = wb1.Worksheets("Sheet1")
    Set ws2 = ThisWorkbook.Worksheets("社員データ")
    
    first_row_num = 3
    last_row_num = ws1.Cells(Rows.Count, 2).End(xlUp).Row
    
    '開始行から最終行まで関数の中にファイル名が入っていれば取り除く処理
    For Each cell In ws1.UsedRange
        If cell.HasFormula Then
            cell.Formula = Replace(cell.Formula, "[" & modefy_file & "]", "")
        End If
    Next cell
    'ファイル名の保存
    wb1.Save
    
    'ワークブック、ワークシート変数を除去
    Set ws1 = Nothing
    Set ws2 = Nothing
    Set wb1 = Nothing
  
    
End Sub

ポイント

'ワークシート内の全てのセルをループする。
For Each cell In ws1.UsedRange
        'セルが数式を持っていれば、数式からマクロファイル名を取り除く
        If cell.HasFormula Then
            cell.Formula = Replace(cell.Formula, "[" & modefy_file & "]", "")
        End If
    Next cell

セルの中に数式(関数)が含まれていれば、数式(関数)にあるマクロファイル名を取り除きます。

最後に

少しでもお役に立てると幸いです。