VBA/Worksheet_Changeがうまくいかない

Question

エクセル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

KenKen_SP · Accepted Answer

こんにちは。

>　　 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

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

cj_mover · Answer

こんにちは

値としての「長さ０の文字列※」が問題でしたか。
（※例えば、数式 ="" をセルごとコピーしてそのまま値貼付けした場合の""値）
　IsEmpty()　とか、 .SpecialCells(xlCellTypeBlanks)　とか、
VBAでの積極活用を考えると意外に面白いので、私は嫌いじゃないです。
これって、VBAでも数式でも、捉え方の問題って気もするし、オプションで非表示にした
ゼロ値と何処が違うの？って気もするけれど、嫌われてますよね。
数式音痴な私ですが、何か、使いこなす「セオリー」の様なものないのですかね。
避けて通る方が犠牲にするものが大きいような気もするのです。
本件では、.ClearContentsで何も犠牲にしていない筈ですから、いいのですけれど。

ところで、ずっと頭から離れなかったのが、
F5: =IF(C5="","",D42)　　というシート上の数式でした。
もし私なら、この数式で困らない為の工夫を延々と考えて、
場合によってはシートの設計を変えちゃうかも知れませんね。
単に私の想像力が乏しいだけで、数式では不足があるのでしょう。
逸れた、過ぎた、話題ばかり、すみませんでした。

KenKen_SP さん^^、こちらこそ、ご面倒をおかけしました。
少しびびって（畏れ）コメント書いてましたが、あれでも本当は嬉しかったのですよ。
あらためて真意を書いてくださって、なお嬉しく、今後の勉強の励みになります。
ありがとうございます。
素敵な回答、きれいなソース、これからも楽しみにさせていただきます。

KenKen_SP · Answer

> 意外に test1の Value = "" の方が早いという結果がでました？！

（・∀・）へー...意外ですよね。

当方で、異なる２つの環境でテストしてみたら、4倍程度は test2 の
方が速かったですよ。理屈上でも VBA 標準コマンドである ClearContents
の方が自力実装の Null の値再セットより速い気がしますけども。

このようなケースもあるんですね。余談が過ぎました。

cj_mover 様へ

反意を以って回答したものではありません（むしろ逆です）から、ご心配
なく。心煩わせてしまい、申し訳ありません。

確かに、イベントプロシージャは稼働率が高いものですし、それが直接的に
ユーザー操作の快適性に関わるケースも多く、そのことを意識したソースを
書くことは非常に大切なのだと私も思います。

cj_mover · Answer

KenKen_SP さん、こんにちは
勉強になります。

私の書き方が悪かったでしょうか。
少なくとも、＃11のキッカケを作ったのは私のようで(^^;)
なんら異論はありません。

.MergeArea.ClearContents
そのものに疑問がある訳ではなくて、
もし、= "" で代用が利くのだとしたら、
という話をしたかっただけなのですが。
計時にAPIを使うことも、
.ClearContents の件も、
複数のセルの値変更に対応させるように書かれたことも、
至極納得です。

思わぬ処で、興味ある記述に触れることが出来て
ありがたく存じます。

以後よろしくお願いします。

KenKen_SP · Answer

話が膨らんでますね^^; 　余談です。

値の消去は 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

cj_mover · Answer

あ、すみません

"既定のプロパティ"
は、用語と誤解されそうなので、
一般語としての
「既存のプロパティ」
に読み替えてください。


念の為、
Target(1)を、#7のお礼欄にあるコードで使うとしたら、
無理があります。
Targetの中で最も左、最も上、にあるセルという意味なので、
例えば、A1:D5に貼付けを行うと、A1セルを参照してしまいます。
（セルの結合を適用した範囲に貼付けというのも限られた話ではありますが）
つまり、前稿は、
結合セルC5:D5のみの値変更に限定した内容になっています。

以上、訂正と補足です。
失礼しました。
それでは、また

cj_mover · Answer

こんにちはオマケ程度の話ですが、、、元のコードのまま、　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で可能なことをイベントで処理するということなら原則、私は消極的な考えです。まぁ気になるという程度の話ですが、、、。書きたいこと、伝えたいこと、他にも色々ありますが、今回は要点（各論）だけのコメントにて、失礼させて頂きます。

KenKen_SP · Answer

#7 です。すみません。

判別式を次のとおり訂正します。

　　If Application.CountA(Target.Value) > 0 Then
　　　　　↓
　　If Application.CountA(Range("C5").MergeArea) > 0 Then

非常に限定されたセル範囲での話なので、前者でも大丈夫だと
思うのですが、判別セルを明確に絞り込んだソースの方がより
確実だと思いましたので。

pkh4989 · Answer

失礼しました。以下のように変えてみてください。 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

pkh4989 · Answer

統合セルには、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

VBA/Worksheet_Changeがうまくいかない

こんにちは。

こんにちは

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

KenKen_SP さん、こんにちは

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

あ、すみません

こんにちは

#7 です。

失礼しました。

統合セルには、Deleteキーの イベントが発生しないようで

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

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

統合セルには、Deleteキーのイベントが発生しないようで