Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : その他)
ユーザー定義関数の作成方法(条件付き書式で設定した任意のセル色のセルのカウント)
投稿日時: 20/10/27 18:19:49
投稿者: Alice87

Microsoft Excel for Office 365を使用しています。
 
件名に記載したことを行いたいと思い、インターネットでいろいろと調べて下記サイトを参考に実行してみたのですが、これだと元々のセルの背景色でないとカウントできないことが分かりました。
 
https://masagoroku.com/%E3%80%90excel%E3%80%91%E6%8C%87%E5%AE%9A%E3%81%97%E3%81%9F%E8%89%B2%E3%81%AE%E3%82%BB%E3%83%AB%E3%82%92%E6%95%B0%E3%81%88%E3%82%8B%E6%96%B9%E6%B3%95
 
同様の質問を挙げているサイトがいくつかありましたが、解決に至らず(コードが長くて理解できなかったりで)手詰まりになっている為、こちらに書き込みをしました。
 
どなたか良いお知恵がありましたら、拝借できると幸いです。
 
宜しくお願い致します。

回答
投稿日時: 20/10/27 18:52:15
投稿者: mattuwan44

そのユーザー定義関数はシート上で使うのでしょうか?
VBAで呼び出して使うのでしょうか?
 
シート上で使うユーザー定義関数でそういうことができるかどうかは知りません
(チャレンジして確かめることは可能)が、
色で判断しなくても、色が付く条件はわかるはずでは?
色の条件で判断する数式を作ってはいかがでしょうか?

回答
投稿日時: 20/10/27 20:14:58
投稿者: んなっと

DisplayFormat.Interior.Colorがユーザー定義関数だとエラーになるので、
工夫が必要だということらしいです。
 
Function CCCount(r1 As Range, r2 As Range) As Long
  Dim Sht As Worksheet
  Dim n As Long, r As Range
  Set Sht = r1.Parent
  For Each r In Intersect(Sht.UsedRange, r1).Cells
    If Sht.Evaluate("CColor(" & r.Address & ")") = r2.Interior.Color Then
      n = n + 1
    End If
  Next
  CCCount = n
End Function
Function CColor(r As Range)
  CColor = r.DisplayFormat.Interior.Color
End Function
 
【使い方】A列で条件付き書式で赤く塗りつぶされたセルの個数を調べるとき。
 
お隣のC1を同じ赤で塗りつぶしておいて ...
 
   A B  C D
 1     4
 2 青   青 3
 3 黄   黄 4
 4      
 5 青     
 6 黄     
 7      
 8      
 9 黄     
10 黄     
11 青     
 
D1
=CCCount(A:A,C1)

回答
投稿日時: 20/10/27 20:21:24
投稿者: んなっと

通常の色のついたセルも数えます。
 
あと、色は何でもいいときは
 
= r2.Interior.Color
 ↓
<> 16777215

回答
投稿日時: 20/10/28 08:11:04
投稿者: simple

DisplayFormatはマクロの中では使えますが、
UFDの中では使えない、とされてきました。
しかし、別出しにしてEvaluateを使うことで見事にブレークスルーを果たされました。
素晴らしいです。

回答
投稿日時: 20/10/28 15:00:31
投稿者: んなっと

私が見つけたものではありません。
 
シート構成の変更に柔軟に対応できるユーザー定義関数の長所はそのままに、
イベントを使わないでユーザー定義関数の制限を破る技です。
ただ...関数が入力されたセル以外を操作することもできてしまうのです。
そこまでやると相当危険な手法になってしまいます。
 
出典を明示するべきですが、海外サイトで安全性に疑問があるので控えます。
確認できる古いものでは、2017/10/25 に Tim Williams が回答しています。
この方が発見したのかもしれません。

回答
投稿日時: 20/10/28 16:59:25
投稿者: simple

丁寧なご紹介ありがとうございました。
  
# 思えば、今までに何度、UDFでは使えませんとダメ出ししてきたことか。
# 南無、合掌。

投稿日時: 20/10/28 18:29:44
投稿者: Alice87

んなっと様
 
迅速な回答ありがとうございます。
また、んなっと様のアドバイスで解決しました‼
 
「【使い方】」以降の記載も大変わかりやすく、コードも長いものではなかったので可読性もよくて助かりました。
多くの方が参考になるに違いありません!