VBA Changeの記述方法

質問

あるセルの計算結果次第で音を鳴らすという事をやりたいです。

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "B1" Then
If Range("B1") > Range("A1") Then

Shell "C:\Program Files\Windows Media Player\wmplayer.exe C:\音楽ファイル.wav", 0
End If
End If

End Sub

この内容で、B1とA1に直接、数値を入れて条件を満たすと音が鳴るのですが、B1とA1がそれぞれ別のセルの値を参照している式にすると音が鳴らないのですが、そのような場合はどうすればいいでしょうか。
現在、B1セルは=B10を参照して、A1は=A10を参照する式になっています。

また、あるセルに

=IF(H34<G34,"",IF(H34=G34,"同","★"))

という式を入れて、このセルが★になった場合に上記と同様に音を鳴らしたいのですが、その場合はどのような記述にすればよいのでしょうか。

回答No.2

山田 太郎（@f_a_007） · ベストアンサー

同じような処理を色んなところで書く場合には・・・。私なら、【標準モジュール】に次のような関数を用意します。

=Comparison(Range1, Range2, "未満,等しい,超える,空値")

Comparison()は、Range1とRange2のどちらかが変更されると呼ばれます。そして、二つのセルの値を比較して呼応する値を戻します。同関数を用いれば

=IF(H34<G34,"",IF(H34=G34,"同","★"))
　↓
=Comparison(H34, G34, "★,同,★", "同")

と書く事ができます。音楽は戻り値が"同"の場合のみ鳴ります。

Option Explicit

Public Function Comparison(ByVal R1 As Range, ByVal R2 As Range, ByVal returnText As String, ByVal isMusic As String) As String
　　Dim returnValue As String

　　'string1はstring2未満 -1
　　'string1とstring2は等しい 0
　　'string1はstring2を超える 1
　　'String1またはstring2はNull値　Null値
　　If Len(R1 & "") * Len(R2 & "") = 0 Then
　　　　returnValue = CutStr(returnText, ",", 4)
　　Else
　　　　Select Case StrComp(R1, R2, vbTextCompare)
　　　　　　Case -1
　　　　　　　　returnValue = CutStr(returnText, ",", 1)
　　　　　　Case 0
　　　　　　　　returnValue = CutStr(returnText, ",", 2)
　　　　　　Case 1
　　　　　　　　returnValue = CutStr(returnText, ",", 3)
　　　　　　Case Else
　　　　　　　　returnValue = ""
　　　　End Select
　　End If
　　If returnValue = isMusic Then
　　　　' 音楽
End If
　　Comparison = returnValue
End Function

Public Function CutStr(ByVal Text As String, _
　　　　　　　　　　　　ByVal Separator As String, _
　　　　　　　　　　　　ByVal N As Integer) As String
　　Dim strDatas() As String

　　strDatas = Split("" & Separator & Text, Separator, , 0)
　　CutStr = strDatas(N * Abs(N <= UBound(strDatas)))
End Function

補足

詳細なプログラムを記述していただきありがとうございました。
私には分からない事だらけですが

=IF(H34<G34,"",IF(H34=G34,"同","★"))

この関数がセルC1にあるとすると、教えていただいたプログラムのどこにそのセルC1を記述すればよいのでしょうか。
あと、R1とR2にはどこか該当するセルを指定する必要がありますか。

また、音楽を鳴らすための

Shell "C:\Program Files\Windows Media Player\wmplayer.exe C:\音楽ファイル.wav", 0

という記述は

If returnValue = isMusic Then

この下に書けばいいのでしょうか。例えば、最初のご回答のように

MsgBox "XXX"

を出す場合の位置も

If returnValue = isMusic Then

の下あたりでしょうか。

回答No.7

山田 太郎（@f_a_007）

【補足】Target.Address(False, False)に関して

Excelを触るのは始めてですので私も知りませんでしたが、イミディエイトウインドウで確認した結果では、相対アドレスを戻すのか？それとも絶対アドレスを戻すのか？ということかと思いますよ。

補足

ご回答ありがとうございます。
イミディエイトウインドウというもので確認できるのですね。勉強になりました。
ありがとうございました。

回答No.6

山田 太郎（@f_a_007）

If returnValue = isMusic Then
　　　　Shell "C:\Program Files\・・・音楽ファイル.wav", 0
　　End If
　　NumComparison = returnValue
End Function

Comparison = returnValue
　　↓
NumComparison = returnValue

回答自体では修正しているので単なる誤記と思われるでしょうが。念の為に！

回答No.5

山田 太郎（@f_a_007）

C1のセルに

=NumComparison(H34, G34, "★,同,★", "同")

音楽を鳴らすコードは

　　If returnValue = isMusic Then
　　　　Shell "C:\Program Files\・・・音楽ファイル.wav", 0
　　End If
　　Comparison = returnValue
End Function

でOKかと思います。

なお、

Public Function StrComparison(ByVal R1 As Range, _
　　　　　　　　　　　　　　ByVal R2 As Range, _
　　　　　　　　　　　　　　ByVal returnText As String,
　　　　　　　　　　　　　　Optional ByVal isMusic As String="") As String

と、音楽を鳴らす指示を書かなくて済むように４番目の引数をオプションにしておくと汎用性が確保されるかとも思います。

回答No.4

山田 太郎（@f_a_007）

【注意】の内容は、余りにも不親切だと思って・・・

数字と文字列とを比較するそれぞれの関数は以下のようです。まあ、Excel用の関数なんて生まれて始めてのど素人のやっつけ仕事。十分にテストされてご利用、ご改変ください。

Public Function StrComparison(ByVal R1 As Range, ByVal R2 As Range, ByVal returnText As String, ByVal isMusic As String) As String
　　Dim returnValue As String

　　If Len(R1 & "") * Len(R2 & "") = 0 Then
　　　　returnValue = CutStr(returnText, ",", 4)
　　Else
　　　　Select Case StrComp(R1, R2, vbTextCompare)
　　　　　　Case -1
　　　　　　　　returnValue = CutStr(returnText, ",", 1)
　　　　　　Case 0
　　　　　　　　returnValue = CutStr(returnText, ",", 2)
　　　　　　Case 1
　　　　　　　　returnValue = CutStr(returnText, ",", 3)
　　　　　　Case Else
　　　　　　　　returnValue = ""
　　　　End Select
　　End If
　　If returnValue = isMusic Then
　　　　' 音楽
　　End If
　　StrComparison = returnValue
End Function

Public Function NumComparison(ByVal R1 As Range, ByVal R2 As Range, ByVal returnText As String, ByVal isMusic As String) As String
　　Dim returnValue As String

　　If Len(R1 & "") * Len(R2 & "") = 0 Then
　　　　returnValue = CutStr(returnText, ",", 4)
　　Else
　　　　Select Case NumComp(R1, R2)
　　　　　　Case -1
　　　　　　　　returnValue = CutStr(returnText, ",", 1)
　　　　　　Case 0
　　　　　　　　returnValue = CutStr(returnText, ",", 2)
　　　　　　Case 1
　　　　　　　　returnValue = CutStr(returnText, ",", 3)
　　　　　　Case Else
　　　　　　　　returnValue = ""
　　　　End Select
　　End If
　　If returnValue = isMusic Then
　　　　' 音楽
　　End If
　　NumComparison = returnValue
End Function

Public Function NumComp(ByVal V1 As Double, ByVal V2 As Double) As Integer
　　Dim R As Integer

　　If V1 < V2 Then
　　　　R = -1
　　ElseIf V1 = V2 Then
　　　　R = 0
　　ElseIf V1 > V2 Then
　　　　R = 1
　　End If
　　NumComp = R
End Function

Public Function CutStr(ByVal Text As String, _
　　　　　　　　　　　　ByVal Separator As String, _
　　　　　　　　　　　　ByVal N As Integer) As String
　　Dim strDatas() As String

　　strDatas = Split("" & Separator & Text, Separator, , 0)
　　CutStr = strDatas(N * Abs(N <= UBound(strDatas)))
End Function

回答No.3

山田 太郎（@f_a_007）

【注意】比較が数字の場合は128の長さで右詰めするなどの工夫が必要かもしれません。その工夫に腐心するようでしたら、数字比較関数、文字列比較関数の二つを用意されるのも手です。

補足

ありがとうございました。文字列比較関数で調べたらEXACT関数というのを見つけました。これを使って以下のような記述をしたらうまく出来ました。

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "H9" Then
If Range("Q9") = "True" Then

Shell "C:\Program Files\Windows Media Player\wmplayer.exe C:\音楽ファイル.wav", 0
End If
End If

End Sub

ただ、上記の記述もサイトなどで調べて見よう見まねでコピーしたものなので、以下の意味が理解できていません。

If Target.Address(False, False) = "H9" Then

これは何を指定しているのでしょうか。Target.Addressで検索してもあまり理解できる説明が見つかりませんでした。

現在のExcelの記述はH9＜G9より大きい場合にI9に★マークが表示されます。そして、Q9セルにEXACT関数を入れて、別のセルに予め入力してある★と比較するようにしています。G9は固定された数字であり、H9は変化する数字です。

If Target.Address(False, False) = "H9" Then

この内容のH9を最初はQ9に変えたのですが、これだと★マークが表示されても音は鳴りませんでした。

回答No.1

山田 太郎（@f_a_007）

Excelは触ったこともない門外漢ですが・・・

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
　　Static A1 As Long
　　Static B1 As Long
　　Dim newA1 As Long
　　Dim newB1 As Long

　　newA1 = Val(Range("A1") & "")
　　newB1 = Val(Range("B1") & "")
　　If newB1 <> B1 Then
　　　　If newB1 > newA1 Then
　　　　　　MsgBox "XXX"
　　　　　　A1 = newA1
　　　　　　B1 = newB1
　　　　End If
　　End If
End Sub

門外漢ですのでVal()での型変換が必要かどうかは不明。ですが、要は、「B1の値が変更された場合には・・・」というIF文を書けばよいのではと推察します。

補足

ご回答ありがとうございます。
教えていただいた方法をやってみたら出来ました。ありがとうございました。記述の中身はVBA初心者の私にはまだ理解できておりませんが、とりあえずこれでやりたい事は実現できるので、中身は丁寧に学んでいきます。

あと、質問内容の後半部分にある以下の件はいかがでしょうか。
＞＞
また、あるセルに

=IF(H34<G34,"",IF(H34=G34,"同","★"))

という式を入れて、このセルが★になった場合に上記と同様に音を鳴らしたいのですが、その場合はどのような記述にすればよいのでしょうか。
＞＞