Excel VBA ユーザー定義関数をイベントマクロで使用する

質問

Excel VBA　ユーザー定義関数をイベントマクロで使用する

Excel2003を使用しています。

あるセルと同色に塗りつぶされたセルの値を合計したく、下記1のユーザー定義関数を作成しました。
このユーザー定義関数を下記2のイベントプロシージャ内で呼び出して使用したいのですが、可能でしょうか？
可能であれば、どのようにコードを書いたらいいでしょうか？
Call を使用するのかな？と思い、コードを追加してみましたが、引数の型が一致しないといった内容のエラーメッセージが表示されてしまいました。
よろしくお願いします。

-------------------------------------------------------------

1．ユーザー定義関数(同色セルの合計)
Function SumColor(hanni As Range, iro As Range) As Double
　　Dim myrng As Range

　　SumColor = 0
　　　For Each myrng In hanni
　　　　If myrng.Interior.ColorIndex = iro.Interior.ColorIndex Then
　　　　　SumColor = SumColor + myrng.Value
　　　　End If
　　　Next myrng
End Function

2．イベントマクロ(C列3行目以下ダブルクリックで塗りつぶし)
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
　　If Target.Column = 3 And Target.Row >= 34 Then
　　　Range(Cells(Target.Row, 3), Cells(Target.Row + 1, 38)).Interior.ColorIndex = 36
　　End If
End Sub

回答No.3

Wendy02 · ベストアンサー

#2の回答者です。

>色の設定に重要な意味を持たせる(判定の条件にする等)のは好ましくないといった内容を目にしました。
そういうことを言う人がいますが、それは取り扱いが出来ないなら、という条件であって、「好ましくない」ということではないと思います。

>ユーザー定義関数を作成したものの、塗りつぶしの有無を変更しても計算結果を反映できないので、

私は、何度か発表していますが、以外に難しいということで、イベントを利用することには違いありません。

以下に私の発表したものがあります。
「自作マクロをセルの色が変わる度に再計算させるには」
http://okwave.jp/qa/q4680287.html

1.　は、今試すと、修正が必要のようです。(名案とは言えませんが。)
'
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
　Activesheet.Calculate
End Sub

お礼

Wendy02 さま、再度の回答ありがとうございます。

塗りつぶしの変更を判定の条件にしようとすると、イベントを利用して再計算させないといけないのですね。
今回の質問を投稿する前に、似たような内容の質問を検索してみたところ、再計算が必要との回答がありましたので、イベントで再計算させれば条件にできないこともないのかなとは思いましたが…。

Wendy02 さまが以前発表されたもののURLも拝見しました。
参考にさせていただきます。
ありがとうございました。

回答No.4

Wendy02

補足、

ユーザー定義関数のどこかに、以下の一行を加えてください。

Application.Volatile

回答No.2

Wendy02

>このユーザー定義関数を下記2のイベントプロシージャ内で呼び出して使用したいのですが、可能でしょうか？
>可能であれば、どのようにコードを書いたらいいでしょうか？

Doubleクリックで色塗りしているわけですね。
>　Range(Cells(Target.Row, 3), Cells(Target.Row + 1, 38)).Interior.ColorIndex = 36

一体、何をどうするのか、言葉で説明してほしいです。このままなら、ただのSUM関数で済むのでは？
それで、色抜きでもするなら、また別問題です。その場合、ユーザー定義関数を貼り付けてください。

'//
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
　　Cancel = False
　　If Target.Column = 3 And Target.Row >= 34 Then
　　　With Range(Cells(Target.Row, 3), Cells(Target.Row + 1, 38))
　　　 .Interior.ColorIndex = 36
　　　Target.Offset(2).Formula = "=SUM(" & .Address(0, 0) & ")"
　　　'Target.Offset(2).Value = SumColor(.Cells, .Cells(1))　'ユーザー定義関数の場合
　　　End With
　　End If
End Sub

'//標準モジュール（このような書き方をしたほうがよいですね。)
Public Function SumColor(Hanni As Range, Iro As Range) As Double
　　Dim myRng As Range
　　Dim dblSum As Double
　　dblSum = 0
　　　For Each myRng In Hanni
　　　　If myRng.Interior.ColorIndex = Iro.Interior.ColorIndex Then
　　　　　If VarType(myRng) = vbDouble Then
　　　　　　dblSum = dblSum + myRng.Value
　　　　　End If
　　　　End If
　　　Next myRng
　　SumColor = dblSum
End Function

お礼

Wendy02 さま、回答ありがとうございます。

先の回答者様のお礼にも書かせていただきましたが、意図した質問内容とは違う内容となってしまっていました。
お時間をとらせてしまい、申し訳ありません…。
ユーザー定義関数を作成したものの、塗りつぶしの有無を変更しても計算結果を反映できないので、イベントマクロを使用して計算結果が反映されるようにできないかという内容で質問するはずでした。

ただ、
＞ それで、色抜きでもするなら、また別問題です。
という部分で、ちょっとアイデアをいただいたというか、塗りつぶしを元に戻す(塗りつぶしなし状態)コードを書き加えさせていただきましたので、意味不明な質問でも収穫がありました(^^ゞ

参考書やネットで調べたところ、色の設定に重要な意味を持たせる(判定の条件にする等)のは好ましくないといった内容を目にしました。今回は勉強も兼ねて、このようなユーザー定義関数を作ってみたのですが、確かにそのとおりですね。

回答No.1

keithin

何て言うか，普通に関数として使うだけです。

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
　　If Target.Column = 3 And Target.Row >= 34 Then
　　　Range(Cells(Target.Row, 3), Cells(Target.Row + 1, 38)).Interior.ColorIndex = 36

　　　’使用例
　　　msgbox sumcolor(target.resize(2, 36), target)

　　End If
End Sub

お礼

keithin さま、回答ありがとうございます。

あれこれ試してうまくいかず、今回質問させていただいたのですが、意図する内容とは違う質問内容となってしまっていました…。

この質問文では、おっしゃるとおり、
＞ 何て言うか，普通に関数として使うだけです。
ですよね(^^;

お時間をとらせてしまい、申し訳ありません。
ありがとうございました。