数式を値に固定して、ついでに#N/Aも消すマクロ - ExcelVBA -

選択している範囲にある数式(シート関数)を値に固定したあと、
ついでに#N/Aエラー(と#DIV/0エラー)を消去するマクロを紹介します。

何がやりたいかは明らかですね(笑)

シート関数が入ったセル範囲を選択
コピー
同じ場所にそのまま「値貼り付け」
#N/Aを""に置換

を自動でやるマクロです。(AIじゃあるまいし、もちろん1番は手でやる）

お忙しい方は、ソースコードをコピーして、
使い方例のように実行しちゃえばOKです。

コードをしっかり読みたい方は、解説もどうぞ。

使いどころ
ソースコード
コードの解説
おまけ：過程より結果が大事

使いどころ

要するに、VLOOKUPの強化パーツです。

① 数式を入力
VLOOKUP関数の入力
　

② 右下の■をダブルクリックしてオートフィルによりコピー
オートフィルでコピー
　※ 関数がコピーされ、ついでに範囲も選択もされる。
　

③ 例えばクイックアクセスツールバーに登録しておきマクロを実行
値固定のマクロを実行
値が固定され、エラー値が空セルになります。

単発の業務で、値さえあればいいような資料で連打しますね。

もちろん単に値を固定するときにも使えますので、VLOOKUP以外の関数とか、ただの足し算とかにも使えます。

長いからタイトルには書かなかったのですが、
ついでに#DIV/0も消すので、0で割る関数も書いてOKです↓
0除算エラーの消去

要するに、

一回しか使わない（＝関数を残して動的にしなくてもよい）資料を作成するとき、
置換するか、If関数書くかの、どっちかやるのをサボりたい

という願いが生んだマクロです。

ちなみに後ほどソースコードでも説明しますが、
「行全体・列全体・シート全体に対しても実行可能」です。
範囲が広すぎて遅くなったりはしませんのでご安心を。

なお、このマクロの起動方法ですが、
②の超便利なオートフィルが、
残念ながらショートカットを持っていません。

どうしてもコピー時にマウスを持ちますから、
このマクロはショートカットキーに登録するよりは、
個人用マクロブックに登録して、クイックアクセスツールバーに登録したほうがいいでしょう。

個人用マクロブックの作り方と、クイックアクセスツールバーの登録方法は、
~~そういう画面キャプチャを貼りまくる記事を書くのが面倒なので、~~
わかりやすいサイトがたくさんありますので、
そちらへどうぞ

ソースコード

Sub 数式を値に固定してNAとDIV0を消去する()

    ' 実行条件の判定（実行エリアの微調整）
    Dim Range実行エリア As Range
    Set Range実行エリア = 選択範囲をUsedRangeで切り取って縮小する
    If Range実行エリア.Areas.Count >= 2 Then
        MsgBox ("Ctrlキーで複数選択されたエリアには実行できません。")
        Exit Sub
    End If
    
    Call エクセルの自動更新を停止する(False)

    ' メインコード
    Range実行エリア.Value = Range実行エリア.Value
    Range実行エリア.Replace "#N/A", "", lookat:=xlWhole
    Range実行エリア.Replace "#DIV/0!", "", lookat:=xlWhole
    
    ' 手作業での検索ダイアログと連動してしまうReplaceの完全一致を部分一致に戻す
    Call Cells(1, 1).Find("", lookat:=xlPart)

    Call エクセルの自動更新を開始する

End Sub

上のコード内でCallされている3つの関数は、便利マクロ用の共通関数です。
既に別のページからコピーしてお持ちの方は、ここから先はコピーは不要です。

' 全体選択のSelectionを縮小
Function 選択範囲をUsedRangeで切り取って縮小する() As Range
    
    ' 行全体・列全体・シート全体が選択されているされた場合は、
    ' UsedRangeで切り取った範囲を選択しなおし
    If Selection.Address = Selection.EntireRow.Address _
    Or Selection.Address = Selection.EntireColumn.Address Then
        Set 選択範囲をUsedRangeで切り取って縮小する _
            = Intersect(Selection, ActiveSheet.UsedRange)
        選択範囲をUsedRangeで切り取って縮小する.Select
    
    ' それ以外はそのまま
    Else
        Set 選択範囲をUsedRangeで切り取って縮小する = Selection
    End If

End Function

' 自動更新の停止
Sub エクセルの自動更新を停止する(isブック計算をOFFに As Boolean _
    , Optional is画面更新をOFFに As Boolean = True _
    , Optional isイベントをOFFに As Boolean = True)
    
    If isブック計算をOFFに Then Application.Calculation = xlCalculationManual
    If is画面更新をOFFに Then Application.ScreenUpdating = False
    If isイベントをOFFに Then Application.EnableEvents = False
    
End Sub

' 自動更新の開始
Sub エクセルの自動更新を開始する()
    With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
        .EnableEvents = True
        .StatusBar = False
        .DisplayAlerts = True
    End With
End Sub

コードの解説

メインコードの解説

メインコードは超単純ですね。

選択範囲を選択範囲へ「値貼り付け」して、
そのあとエラー値を置換しているだけです。

※ #N/Aと#DIV/0以外のエラーは、
　数式作成者が意図しないミスに気づかず空白になると困るので置換しません。

この

「Rangeオブジェクト.Value = Rangeオブジェクト.Valueで、
エリアからエリアへの「値貼り付け」と同じ処理ができる」

ということを初めて知った方は、
今後のマクロを書くうえでかなり重要な技術になると思います。

~~こんなくだらないマクロの記事なんか読んでないで、~~こちらの記事へどうぞ。
www.limecode.jp

他の部分のコードの解説

後の部分はおまけです。（読まずにコピペでもOK）

If Range実行エリア.Areas.Count >= 2 Then
    MsgBox ("Ctrlキーで複数選択されたエリアには実行できません。")
    Exit Sub
End If

とりあえず、Ctrlキーを押しながらセルを複数エリア選択している場合は、
うまく動きませんので除外します。
　

Call Cells(1, 1).Find("", lookat:=xlPart)

この部分で、まったく意味のないFindメソッドを起動しています。
これは「検索ダイアログの設定の上書き」を行っています。

このマクロを使用するような状況では、
手でやるシート上の検索は、部分一致でやりたい場面が多いです。

しかし、Replaceメソッドは「検索ダイアログ」の設定を共有しているため、
マクロ実行後に「xlWhole:セルの内容が完全に同一であるものを検索する」になってしまいます。

これを部分一致xlPartに記憶させなおしています。

システムの設定値をキレイにいじる方法があるんだろうけど、
ちょっと調べてもすぐわかんなかったからこれでいいや。

共通関数の使い方

あとのCallしている関数は、私のすべての便利マクロでの共通処理です。
※ すでにお持ちの方はコピペは不要です。

詳しい解説が欲しい方はこちらをどうぞ
汎用マクロ・便利ツールの共通関数について

Set Range実行エリア = 選択範囲をUsedRangeで切り取って縮小する

便利マクロ全般、うっかりシート全体へ実行してしまうと遅くなるので、
「すべてのセル・行全体・列全体」を選択して実行した場合は、
シートの使っている範囲「UsedRange」との交差部分で切り取ってから実行」します↓

Intersectによる交差部分の取得
　

今回の例では、↓ここで「D列全体」を選択して実行してもOKです。
オートフィルでコピー
　

Call エクセルの自動更新を停止する(False)
Call エクセルの自動更新を開始する

Excelの自動更新～の2つのマクロは、マクロの高速化でおなじみのトリオ
「描画・自動計算・EventのON/OFF」をまとめたやつですね。

それぞれのONOFFを個別に指定するための引数がありますが、
当然今回は自動計算をOFFにしてはいけませんので、
「Isブック計算をOFFに」にはFalseを渡します。
（描画とEventは大抵は停止するので省略可。省略すればTrue）

今回の使用目的は当然速度もありますが、
特に「WorksheetChangeイベントを起こさない」ためです。

「セルの値を変更時に、自動実行されるマクロ」が書いてあるシートで、
大規模な置換を実行すると、Excelが固まり、たまに屍になりますのでご注意を。

コードの説明はこれで終わりです。

長くなりましたが、こういった便利マクロが長くなるのは、
「かゆい所に手が届くように」という処理のせいですね。

メインコードは短く単純なので、
この辺はあまり気にせずじゃんじゃん使ってください。

おまけ：過程より結果が大事

このマクロは私の作成した汎用便利マクロ(以下アドイン)の中で、
圧倒的な使用率を誇ります。

自分がシステム部署の人間ではなく、
企画立案や状況調査を行う部署の人間で、
表を作るより、表を見て考えることが主な業務ということもあるんでしょうけど。

私こういう自作アドインが大好きで、
「作るのにかかった時間＞＞アドインが削減した時間」
でも気にしないで、ガンガンアドインを作るんですけどね。

時間対効果を度外視して、丹精込めて作ったかわいいエリートアドインたちも、
このアホみたいな単純マクロに勝てません。

悲しいですが、実務においては、
どれだけ努力したかより、得られた結果がすべてなのです。

ちなみに使用率２位は、
同じくアホみたいに単純なソースコードのこいつです↓
罫線マクロのサンプル
www.limecode.jp

そして３位がこいつ

Sub 選択範囲を1枚の用紙にクイック印刷する()
    With ActiveSheet.PageSetup
    
        ' 行列全体・シート全体・単独セルで実行時は、シート全体を印刷範囲とみなす
        Dim 印刷エリア As Range
        If Selection.Address = Selection.EntireRow.Address _
        Or Selection.Address = Selection.EntireColumn.Address Then
            Set 印刷エリア = Range(Cells(1, 1), ActiveSheet.UsedRange)
            .PrintArea = ""
        ElseIf Selection.Cells.Count = 1 Then
            Set 印刷エリア = Range(Cells(1, 1), ActiveSheet.UsedRange)
            .PrintArea = ""
            
        ' それ以外はSelectionを印刷範囲に設定
        Else
            Set 印刷エリア = Selection
            .PrintArea = Selection.Address
        End If
        
        ' すべての余白を0にして水平だけセンタリング
        .LeftMargin = 0: .RightMargin = 0
        .TopMargin = 0: .BottomMargin = 0
        .HeaderMargin = 0: .FooterMargin = 0
        .CenterHorizontally = True
    
        ' 拡大縮小設定を「シートを1ページに印刷」に
        .Zoom = False
        .FitToPagesWide = 1: .FitToPagesTall = 1
        
        ' 印刷エリアの幅が高さより長ければ用紙を横に（基本縦がいいので10行分くらい高さを有利）
        .Orientation = IIf(印刷エリア.Height + 160 _
                                                    > 印刷エリア.Width, xlPortrait, xlLandscape)
    
        ' 印刷
        ActiveSheet.PrintOut

    End With
End Sub