【Excel VBA】IFERROR関数を付け加えるマクロを作ってみた

目的

ExcelのシートをCSV形式に変換して保存する際、数式エラーの部分は#DIV/0!などのエラーメッセージがそのまま文字列として出力されてしまう。

これを回避するには数式エラー部分を削除してから保存すればよいのだが、せっかく作った数式を消すのはちょっとというケースもある。
IFERROR関数を使えば、数式にエラーがあった場合に任意の文字列で表示できるが、いちいち一つずつ追加するのは大変。

なので、IFERROR関数をVBAで追加するコードを作成してみた。

完成コード

メインプロシージャ

Sub Main_Code()
    Dim inTxt As String
    inTxt = InputBox("数式エラー時に表示させる文字列を入力してください。", "文字列入力")
    Call Add_Formula_IFERROR(Selection, inTxt)
End Sub

処理したいセル範囲を選択して、Main_Codeを実行するとInputBoxが出てくるので、表示させたい文字列を入力してOKボタンを押す。（何も表示させたくなければそのままOKもしくはキャンセルボタン）
すると数式にIFERROR関数が付与される仕組みになっています。
ロジックは以下のAdd_Formula_IFERRORプロシージャになります。

Add_Formula_IFERROR プロシージャ

'**
'* 数式にIFERROR関数を追加する関数
'* 引数1：inRng   {Range型} 処理するセル範囲を指定
'* 引数2：[inTxt] {String型} 代替表示する文字列を指定
'**
Sub Add_Formula_IFERROR(ByVal inRng As Range, _
               Optional ByVal inTxt As String = "")
    
    '処理するセル範囲の最適化
    Set inRng = S.Modify_CellArea(inRng)
    
    '数式にIFERROR関数を追記
    Dim BufRng As Range, Output As String
    For Each BufRng In inRng.SpecialCells(xlCellTypeFormulas)
        Do
            '既にIFERRORがある場合は処理しない
            If BufRng.Formula Like "*=IFERROR(*" Then
                Exit Do
            End If
            
            'IFERROR関数の追記処理
            Output = Mid(BufRng.Formula, 2) '先頭の=以外の残す
            
            'inTxtが数値の場合はダブルクオーテーションをつけない
            If IsNumeric(inTxt) Then
                Output = "=IFERROR(" & Output & "," & inTxt & ")"
            Else
                Output = "=IFERROR(" & Output & "," & Chr(34) & inTxt & Chr(34) & ")"
            End If
            
            'セルに貼付
            BufRng.Formula = Output
            
            Exit Do
        Loop
    Next BufRng
  
End Sub

もう少しすっきり書けると思うのですが、IFERROR関数を二重掛けしないように工夫してあったり、数値で入れられるものにはダブルクオーテーションをつけないという工夫をしているため複雑化しました。

処理するセル範囲を最適化するコードは、すでに解説してますので以下の記事を参照ください。

記事公開後に付け足したIFERROR関数を削除するマクロも作ってほしいという依頼を受けて作りました。
必要な方は以下記事を参考にしてください。