本ページには広告が含まれています。

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

動的配列書き込み先セル範囲を事前チェックする
2022.07.12

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

くるみこ
くるみこ

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

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

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

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

スポンサーリンク

セル範囲の状況を調べる

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

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）の違いと使用方法についてサンプルコードを使って解説します。
www.kurumico.com

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

VBAの実行動画がこちらです

Evaluateメソッドでワークシート関数のFILTER関数を使用

セルG12からK17の範囲がVBAでデータが書き込まれる部分です。

この部分に事前に「スペース」や「計算式」「結合セル」（範囲内設置と範囲内と範囲外をまたぐ結合セル）を設定しておきます。

設定した状態でVBAを実行した動画がこちらです。

はじめは、「いいえ」を選択して書き込みをキャンセルしています。2回目は「はい」を選択して上書きを実行しています。

上書きして書き込まれたデータを見ると、結合セルではデータの一部（先頭だけ）が書き込まれているのがわかります。

この結果を見ると、書き込み先に「既存データ」や「結合セル」があった場合は「いいえ」を選択して中止した方がよさそうですね(^^;

まとめ（おわりに）

以上、スピルする数式の結果データ（動的配列）を書き込む際に、書き込み先のセル範囲を事前にチェックする方法についての解説でいた。

くるみこ
くるみこ

いかがしたか？
今回は、調べた結果を受けて「上書きする」か「中止する」かの2択でしたが、結合セルを解除してから書き込むなどの動作も追加できると思います。

ByRef キーワードで結果を返すようにする設定を使うのは久しぶりでした。Nullの判定も勉強になりました(^^)

★★★ ブログランキング参加中！ クリックしてね(^^)/ ★★★

にほんブログ村 IT技術ブログ VBAへ
にほんブログ村

人気ブログランキングアイコン
Visual Basicランキング

【今後の記事について】

今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考える」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m

スポンサーリンク
エックスサーバーバナー広告
スポンサーリンク

記事のサンプルファイルをダウンロードできます　

記事で使用したサンプルファイルをダウンロードできるようにページを設置していますので、こちら（このリンク先）からご利用ください

【Excel VBA】サンプルファイル_ダウンロードのページ
【Excel VBA】の記事内で使ったサンプルファイルを（Zipファイルで）ダウンロードできるようにしました。記事内では細かい部分の解説が不十分ですが、実際にファイルをダウンロードして実行することで使えるのかどうか見ていただけたら幸いです。
www.kurumico.com