Excel VBA 動的配列の書き込み先セル範囲を事前に調べる

前回記事で、Application.Evaluateメソッドを使ってスピルするワークシート関数を扱う方法について解説しました。その中の注意点として、VBAで結果をセル範囲に書き込む場合、既存データがあっても構わず上書きしてしまうというものでした。そこで今回は、書き込むセル範囲に既存データが存在するかどうかを確認する方法について解説します。

くるみこ

ワークシート関数の場合スピル範囲が空白でない場合や結合セルがあると #SPILL! エラーになります。でも、VBAで処理する場合はスピルしないので強制的に結果を書き込んでしまします。大切な既存の計算式などを失わないようにしたいですよね。

書き込む前にそのセル範囲にデータや結合セルが存在するかどうかを調べる方法を詳しくお教えてください。よろしくお願いしますm(__)m

【この記事でわかることは】
・CountA関数で範囲内のセルが空白かを調べる方法
・MergeCellsプロパティで範囲内に結合セルがあるかを調べる方法

スピルする動的配列数式が使えるのは、サブスクリプション版の「Microsoft 365」アプリの「Excel for Microsoft 365」と永続ライセンス版の最新版 Excel 2021です。

セル範囲の状況を調べる
1. COUNTA 関数で範囲内のセルが空白かを調べる
2. MergeCells プロパティで結合セルがあるかを調べる
セル範囲の状態を調べてから処理するコード
まとめ（おわりに）
記事のサンプルファイルをダウンロードできます

セル範囲の状況を調べる

セル範囲が空白かどうかを調べる方法と結合セルの存在を調べる方法について見ていきましょう。

COUNTA 関数で範囲内のセルが空白かを調べる

今回の目的は、VBAで計算した結果を書き込むセル範囲が、何もない状態かを事前に調べることです。
見た目は「空白」でも「結果が空白状態の計算式」や「見えていない文字列」が存在しているかもしれません。

動作としては「空白」じゃなかったら「書き込みを中止する」などのようにしたいと思います。
「空白じゃない」の判定には COUNTA 関数で範囲内の「空白じゃない」セル数を数えます。

WorksheetFunction.CountA を使う方法

「COUNTA」はワークシート関数なので「WorksheetFunction.CountA」として次のように使います。

If WorksheetFunction.CountA(範囲) <> 0 Then
    MsgBox "書き込み範囲が空白ではありません！中止します！"
    Exit Sub
End If

範囲が「空白」だけなら「０」ですから、「０」以外の場合にメッセージを出して終了する設定です。

「空白」を数えるのなら「ISBLANK」や「COUNTBLANK」を使いますが「空白を返している計算式」に対しても「空白」としてカウントしてしまうので今回の判定には使えません。

MergeCells プロパティで結合セルがあるかを調べる

Rnageオブジェクトの MergeCells プロパティはセルが結合されているかどうかを判定します。
結合されていれば「True」、結合されていなければ「False」を返します。
セル範囲で指定した場合、その範囲内に両方が含まれる場合には「Null」を返します。

判定を「Falseでない場合は」としたいところですが「Null」が返された場合は「False」ではないと判定してくれません。「Null」の場合は IsNull 関数を使わないと判定できないのでコードは次のようにしました。「Null」または「True」の場合はメッセージを出して終了する設定にしました。

If IsNull(範囲.MergeCells) Or 範囲.MergeCells Then
    MsgBox "書き込み範囲に結合セルがあるので中止します！"
    Exit Sub
End If

通常はセルの結合・解除を行うには MergeCells メソッドを使いますが、この MergeCells プロパティ
を使って「True」を指定すれば結合、「False」指定で解除することができます。

セル範囲の状態を調べてから処理するコード

前回記事のコード（EvaluateメソッドでFILTER関数を使った例）に先ほどの処理を加えてみます。
※Evaluateメソッドについては前回記事で解説していますのでリンク先を参照してください。
状態を調べる部分は、再利用できるように別プロシージャに書いて処理を分割するようにします。

【変更前のコード】がこちらです。

'FILTER関数をEvaluateメソッドを簡略で記述
Sub Test04()
    Dim r   '戻り値の動的配列は２次元配列
    
    'セルで使っている数式は =FILTER(A2:E101,(D2:D101<J1)+(D2:D101>=J2),"該当なし")
    r = [FILTER(A2:E101,(D2:D101<J1)+(D2:D101>=J2),"該当なし")]
    
    '戻り値は２次元配列なのでUBoundでサイズ（範囲）指定してセルに書き込む
    [G12].Resize(UBound(r, 1), UBound(r, 2)) = r

End Sub

【変更後のコード】は、追加コードを８行目から11行目に加えています。

'FILTER関数をEvaluateメソッドを簡略で記述
Sub Re_Test04()
    Dim r   '戻り値の動的配列は２次元配列
    
    'セルで使っている数式は =FILTER(A2:E101,(D2:D101<J1)+(D2:D101>=J2),"該当なし")
    r = [FILTER(A2:E101,(D2:D101<J1)+(D2:D101>=J2),"該当なし")]
    
    '書き込み前に対象範囲をチェックする
    Dim msg As String
    Call AddrCheckRange(r, msg)
    If msg <> "" Then Exit Sub

    '戻り値は２次元配列なのでUBoundでサイズ（範囲）指定してセルに書き込む
    [G12].Resize(UBound(r, 1), UBound(r, 2)) = r

End Sub

・9行目、調査後の戻り値を受け取るための変数を宣言しています。
・10行目、調査用の別プロシージャの呼び出しでFILTER戻り値「r」と結果を受け取る「msg」を渡しています。
・11行目、調査結果の戻り値「msg」を評価して処理を分岐しています。「msg」が空の場合は以降の処理（書き込み）をせずに終了させます。

【セル範囲の状態をチェックするコード】

'書き込み先アドレスの状況を事前チェックする
Sub AddrCheckRange(ByVal r As Variant, ByRef msg As String)
    Dim addr As String
    Dim rng As Range
    Dim res As Integer
    
    '書き込み先の範囲アドレスを取得
    addr = [G12].Resize(UBound(r, 1), UBound(r, 2)).Address
    '書き込み範囲のアドレスをRangeオブジェクトにセット
    Set rng = Range(addr)

    '空白以外のセルが無いかをチェック
    If WorksheetFunction.CountA(rng) <> 0 Then
        msg = "書き込み範囲に空白以外のセルがあります！" _
            & vbCrLf & "　(既存のデータは上書きされます)" _
            & vbCrLf & vbCrLf
    End If
        
    'セル範囲内に結合セルがあるかを調べる（TrueまたはNullなら存在有）
    If IsNull(rng.MergeCells) Or rng.MergeCells Then
        msg = msg & "書き込み範囲内に結合セルがあります！" _
            & vbCrLf & "　(正しく書き込めない可能性があります)" _
            & vbCrLf & vbCrLf
    End If

    If msg <> "" Then
        res = MsgBox(msg & "支障がありますがこのまま書き込みますか？" _
                & vbCrLf & "(「はい」=このまま実行、「いいえ」=中止)", _
                vbYesNo + vbExclamation)
        If res = vbYes Then msg = ""
    End If
    
End Sub

・2行目、(ByVal r As Variant, ByRef msg As String)で引数を二つ受け取っています。
二つ目の「msg」はByRef（参照渡し）として、「msg」が結果を受け取って返す設定です。

Excel VBA 参照渡し「ByRef」と値渡し「ByVal」

Excel VBA プロシージャを引数を付けて呼び出す場合、呼び出されるプロシージャの引数の定義（引数宣言）の仕方で動作が異なります。参照渡し（ByRef）と値渡し（ByVal）の違いと使用方法についてサンプルコードを使って解説します。

・8～10行目で、書き込み先の範囲アドレスを取得しRangeオブジェクトに入れています。
・13～17行目、書き込み先セル範囲に空白以外のセルが無いかをチェックしています。
・20～24行目、セル範囲内に結合セルがあるかを調べています。
・26～31行目、調査の結果「空白以外や結合セル」が存在する場合、書き込みに支障が生じる可能性がある旨メッセージボックスに表示して処理の選択を促すようにしています。
・メッセージボックスで「はい」（無視して書き込む）を選択した場合、「msg」に「””」を代入して戻り値を呼び出し元に返します。