VBA/Worksheet_Changeがうまくいかない

質問

エクセル2000です。
以下のワークシートチェンジイベントがうまくいきません。
Targetに値が入る場合は問題ないのですが、TargetをクリアしてもRange("F5").MergeAreaがクリアされません。
Targetをクリアした後、TargetをダブルクリックしてからEnterキーを押せばRange("F5").MergeAreaがクリアされるのですが、いちいちそうさせるわけにもいきません。
どうしたらよいのでしょうか？

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$C$5" Then Exit Sub
If Target.Value <> "" Then
Range("F5").Value = Range("D42").Value
Else
Range("F5").MergeArea.ClearContents
End If
End Sub

回答No.7

KenKen_SP · ベストアンサー

こんにちは。

>　　 If Target.Address <> "$C$5" Then Exit Sub

結合セル（例：$C$5:$D$5）の値を消した場合、Target.Address は
$C$5:$D$5 になりますので、Exit Sub しちゃいそうですが...

結合セルであっても Change イベントはちゃんと発生します。

　　　If Target.Address <> "$C$5:$D$5" Then Exit Sub

としてみるとか。

また結合セルは複数のセルなのですから、Target.Value は、配列を
返します。したがって、単純に

　　　If Target.Value <> "" Then

と比較することはできません。

Private Sub Worksheet_Change(ByVal Target As Range)

　　If Intersect(Target, Range("C5")) Is Nothing Then Exit Sub

　　Application.EnableEvents = False
　　If Application.CountA(Target.Value) > 0 Then
　　　　Range("F5").Value = Range("D42").Value
　　Else
　　　　Range("F5").MergeArea.ClearContents
　　End If
　　Application.EnableEvents = True

End Sub

ぱっと見なので、違ってたらスルー願います。

お礼

KenKen_SPさま、いつもありがとうございます。
MsgBox Target.Address　として試したところ、結合セルの場合、値を入力すると $C$5、Deleteだと $C$5:$D$5 と変わることが判明しました！
そのため、If Target.Address <> "$C$5:$D$5" Then Exit Sub　では片方が作動しません。
また、Target.Value では、配列を返すとのことなので以下のようにしたらうまくいきました。
助かりました。ありがとうございます。

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C5")) Is Nothing Then Exit Sub
If Range("C5").Value <> "" Then
Range("F5").Value = Range("D42").Value
Else
Range("F5").MergeArea.ClearContents
End If
End Sub

回答No.14

cj_mover

こんにちは

値としての「長さ０の文字列※」が問題でしたか。
（※例えば、数式 ="" をセルごとコピーしてそのまま値貼付けした場合の""値）
　IsEmpty()　とか、 .SpecialCells(xlCellTypeBlanks)　とか、
VBAでの積極活用を考えると意外に面白いので、私は嫌いじゃないです。
これって、VBAでも数式でも、捉え方の問題って気もするし、オプションで非表示にした
ゼロ値と何処が違うの？って気もするけれど、嫌われてますよね。
数式音痴な私ですが、何か、使いこなす「セオリー」の様なものないのですかね。
避けて通る方が犠牲にするものが大きいような気もするのです。
本件では、.ClearContentsで何も犠牲にしていない筈ですから、いいのですけれど。

ところで、ずっと頭から離れなかったのが、
F5: =IF(C5="","",D42)　　というシート上の数式でした。
もし私なら、この数式で困らない為の工夫を延々と考えて、
場合によってはシートの設計を変えちゃうかも知れませんね。
単に私の想像力が乏しいだけで、数式では不足があるのでしょう。
逸れた、過ぎた、話題ばかり、すみませんでした。

KenKen_SP さん^^、こちらこそ、ご面倒をおかけしました。
少しびびって（畏れ）コメント書いてましたが、あれでも本当は嬉しかったのですよ。
あらためて真意を書いてくださって、なお嬉しく、今後の勉強の励みになります。
ありがとうございます。
素敵な回答、きれいなソース、これからも楽しみにさせていただきます。

お礼

おはようございます。
はい、まさしく文字長０の文字列が問題なんです。
エクセルなので、ワークシート関数で不整合が出ると困るのです。

でも本当の空白も　=IF(C5="",　でTRUEなのに、実際に　""　を代入すると

=C5=""　はもちろん　TRUE
=ISBLANK(C5) では　FALSE（空白でない）
=COUNTBLANK(C5)=1　では　TRUE（空白だ）
=COUNTA(C5)=1　では　TRUE（空白でない）
こんな矛盾がでてしまうので怖くて使えないんです。
変ですねえ。

F5を =IF(C5="","",D42)　と数式にできないのは、D42(これはC5セルの値によりその都度計算でかわります)から転記されるのはあくまで暫定的な値で、このF5の値の変更を可能にするためです。
通常はD42で計算された値そのままでいいのですが、場合により変えることも可能にしておかなくてはならないのです。
数式ではどうやったって変更できませんよね。

なんどもありがとうございました。

回答No.13

KenKen_SP

> 意外に test1の Value = "" の方が早いという結果がでました？！

（・∀・）へー...意外ですよね。

当方で、異なる２つの環境でテストしてみたら、4倍程度は test2 の
方が速かったですよ。理屈上でも VBA 標準コマンドである ClearContents
の方が自力実装の Null の値再セットより速い気がしますけども。

このようなケースもあるんですね。余談が過ぎました。

cj_mover 様へ

反意を以って回答したものではありません（むしろ逆です）から、ご心配
なく。心煩わせてしまい、申し訳ありません。

確かに、イベントプロシージャは稼働率が高いものですし、それが直接的に
ユーザー操作の快適性に関わるケースも多く、そのことを意識したソースを
書くことは非常に大切なのだと私も思います。

お礼

ありがとうございました。

回答No.12

cj_mover

KenKen_SP さん、こんにちは
勉強になります。

私の書き方が悪かったでしょうか。
少なくとも、＃11のキッカケを作ったのは私のようで(^^;)
なんら異論はありません。

.MergeArea.ClearContents
そのものに疑問がある訳ではなくて、
もし、= "" で代用が利くのだとしたら、
という話をしたかっただけなのですが。
計時にAPIを使うことも、
.ClearContents の件も、
複数のセルの値変更に対応させるように書かれたことも、
至極納得です。

思わぬ処で、興味ある記述に触れることが出来て
ありがたく存じます。

以後よろしくお願いします。

お礼

今回はありがとうございました。
Value = ""　にしなかった理由は#11のお礼に書いた通りです。
これからもご指導ください。

回答No.11

KenKen_SP

話が膨らんでますね^^; 　余談です。

値の消去は Null 値を代入し直すより、ClearContents した方が速い
（低負荷）のかもしれません。参考になりますか？

Private Declare Function timeGetTime Lib "winmm.dll" () As Long

' // 値の消去 Null値の代入 or ClearContents 簡易ベンチマークテスト

Private Const TEST_STR As String = "Sample String"

Sub test1()
　　Dim t As Long
　　Dim i As Long
　　Range("A1:B1").Merge
　　Application.ScreenUpdating = False
　　t = timeGetTime()
　　For i = 1 To 10000
　　　　With Range("A1")
　　　　　　.Value = TEST_STR
　　　　　　.Value = ""
　　　　End With
　　Next i
　　t = timeGetTime() - t
　　Application.ScreenUpdating = True
　　MsgBox Format$(t / 1000, "0.000") & "sec"
End Sub

Sub test2()
　　Dim t As Long
　　Dim i As Long
　　Range("A1:B1").Merge
　　Application.ScreenUpdating = False
　　t = timeGetTime()
　　For i = 1 To 10000
　　　　With Range("A1")
　　　　　　.Value = TEST_STR
　　　　　　.MergeArea.ClearContents
　　　　End With
　　Next i
　　t = timeGetTime() - t
　　Application.ScreenUpdating = True
　　MsgBox Format$(t / 1000, "0.000") & "sec"
End Sub

お礼

KenKen_SPさま、ありがとうございます。
ベンチマークテストしてみたところ、意外に　test1の　Value = ""　の方が早いという結果がでました？！

test1 １回目3.318 ２回目3.296 ３回目3.439
test2 １回目3.649 ２回目3.686 ３回目3.674

ただ、Value = ""　を使うと、セル書式が文字列になっている場合、完全な空白ではなくなるようで、ISBLANKがFALSEなのにCOUNTBLANKでは空白に数えられるという不整合がおきます。それでClearContentsするようにしています。

回答No.10

cj_mover

あ、すみません

"既定のプロパティ"
は、用語と誤解されそうなので、
一般語としての
「既存のプロパティ」
に読み替えてください。

念の為、
Target(1)を、#7のお礼欄にあるコードで使うとしたら、
無理があります。
Targetの中で最も左、最も上、にあるセルという意味なので、
例えば、A1:D5に貼付けを行うと、A1セルを参照してしまいます。
（セルの結合を適用した範囲に貼付けというのも限られた話ではありますが）
つまり、前稿は、
結合セルC5:D5のみの値変更に限定した内容になっています。

以上、訂正と補足です。
失礼しました。
それでは、また

お礼

> 例えば、A1:D5に貼付けを行うと、A1セルを参照してしまいます。

If Intersect(Target, Range("C5")) Is Nothing Then Exit Sub にしたのでその通りでした。
If Range("C5").Value <> "" Then　のままにしておきます。
わざわざありがとうございました。

回答No.9

cj_mover

こんにちは

オマケ程度の話ですが、、、

元のコードのまま、
　Target
を
　Target.Cells(1, 1)
　Target.Cells(1)
　Target.Item(1, 1)
　Target.Item(1)
　Target(1, 1)
　Target(1)
のうちの何れかひとつと置き換えるだけでも、
元の狙い通りの結果になる筈です。
（私自身が最近教わった記述を含めて紹介しました）
（配列ではなく、コレクションのインデックスを指しています）

以上を元にして、手を加えるなら、
.Count > 2　と、.Row <> 5　と、.Column <> 3　で
範囲を判別するのがお奨めです。
（#7さんのように複数範囲の変更を捕える場合は別です。）
イベントプロシージャでは、なるべく
"既定のプロパティ"
（大雑把にいえばローカルウィンドウで確認できる類の純粋な意味でのプロパティ）
を優先して用いる方が好ましいかなぁと。
イベントプロシージャに関する回答では「学習者向け」の
内容を書くことも多いのですが、上に挙げたのは、
私自身実践で用いている方法です。

他、
　Range("F5").MergeArea.ClearContents
が必須なのか、
　Range("F5").Value = ""
で代用できるのか、、、
イベントプロシージャでの記述としては気になります。
または、仮に、非VBAで可能なことをイベントで処理するということなら
原則、私は消極的な考えです。
まぁ気になるという程度の話ですが、、、。

書きたいこと、伝えたいこと、他にも色々ありますが、
今回は要点（各論）だけのコメントにて、失礼させて頂きます。

お礼

ありがとうございます。
If Range("C5").Value <> "" Then　でうまくいきましたが、Target(1)の方がスマートですね。

いろいろありがとうございました。勉強になります。

回答No.8

KenKen_SP

#7 です。すみません。

判別式を次のとおり訂正します。

　　If Application.CountA(Target.Value) > 0 Then
　　　　　↓
　　If Application.CountA(Range("C5").MergeArea) > 0 Then

非常に限定されたセル範囲での話なので、前者でも大丈夫だと
思うのですが、判別セルを明確に絞り込んだソースの方がより
確実だと思いましたので。

お礼

ご丁寧にありがとうございます。

回答No.6

pkh4989

失礼しました。
以下のように変えてみてください。

Private Sub Worksheet_Change(ByVal Target As Range)
　　If Len(Target.Address) > 4 And Target.Address <> "$C$5:$D$5" Then Exit Sub
　　If Len(Target.Address) = 4 And Target.Address <> "$C$5" Then Exit Sub
　　If Cells(5, "C") <> "" Or Cells(5, "D") <> "" Then
　　　　Range("F5").Value = Range("D42").Value
　　Else
　　　　Range("F5").MergeArea.ClearContents
　　End If
End Sub

標準モジュール
Sub test()
　　Application.OnKey "{DEL}", "CellClear"
End Sub
Sub CellClear()
　　If ActiveCell.Address = "$C$5" Then
　　　　ActiveSheet.Range("C5").MergeArea.ClearContents
　　End If
End Sub

お礼

何度もありがとうございました。
勉強になります。

回答No.5

pkh4989

統合セルには、Deleteキーの イベントが発生しないようで
コードはそのままにして、以下のマクロを追加してみてください。

Sub test()
　　Application.OnKey "{DEL}", "CellClear"
End Sub
Sub CellClear()
　　If ActiveSheet.Range("C5") Then
　　　　ActiveSheet.Range("C5").MergeArea.ClearContents
　　End If
End Sub

回答No.4

pkh4989

Sub test()
　　Application.OnKey "{DEL}", "CellClear"
End Sub
Sub CellClear()
　　If ActiveSheet.Range("C5") Then
　　　　ActiveSheet.Range("C5").MergeArea.ClearContents
　　End If
End Sub