2022/06/23

条件付書式で色変更されたセル値を合計する関数




1.背景

Excelで表を作り、ある範囲内の合計を計算するには、御存知の通りワークシート関数のSUM等を使用します。データを更新すれば、合計値もすぐに自動更新されます。
その表に対し、条件付き書式で特定のセルに背景色などを付けて強調することも良くやる手法です。しかし、その強調されたセルの値だけを拾って合計しようとすると、やっかいです。

ワークシート関数には「セルの背景色」を取得するものは無さそうですし、「Cell関数の"format"」を使えばセル書式の一部を情報として取得できますが「条件付き書式で変更された書式」には反応してくれません。
VBA側で考えてみると、RangeのDisplayFormatオブジェクトを使えば取得は可能ですが、VBAから呼び出す時にしか使用できない(=ユーザー定義関数内では使用できない)という制限がありますし、VBAから呼び出すという事は「シート上の何らかのボタンをユーザーがクリック」する必要があるということになるため、自動更新という訳には行きません。
また条件付き書式の数式を取り出して、改めて各セル値を調べ、条件付き書式に合致しているセルの値を合算していくという手法は使えそうです。しかし、条件が複数になると「優先度の高い条件に合致していたか否か」も判断に加えなくてはならず、なかなか面倒そうです。

そこで今回は「初回入力日時を保存できるワークシート関数」でも紹介をした「ユーザーフォームをモードレスで起動し、そのフォーム側から計算を行う」手法を用い、DisplayFormatオブジェクトを使って「条件付き書式で変更された書式(今回は背景色)のセル範囲の合計を計算するユーザー定義関数」を実現させます。

2.システム概要

今回作成したものは「標準モジュール+ユーザーフォーム」です。ですのでユーザー定義関数として使用するには、そのコードを「関数を使うブックに搭載」しておくか、または「Excelにアドイン」しておくという準備が必要です。
アドインのやり方については「アドインとしてExcelにマクロを登録」を参照下さい。

ワークシートからユーザー定義関数を呼び出せる準備が完了したら、条件付き書式で色分けする様な表を作ります。図2-1は「1ヶ月間の4名の〇〇の毎日の数」を表した表で、図2-2のように条件付き書式で「土日はオレンジ色」「5・10日(ゴトウビ)は緑色」と塗りつぶしをしています。なお、土日の方を優先させているため、例えば10日や30日は5・10日ではありますが、土日の色になります。
ユーザー定義関数の設定
図2-1

図2-1のように、この表の左端部にユーザー定義関数「ColorSum」を書き込みます。ユーザー定義関数「ColorSum」の引数は以下のように指定します。
 ColorSum( 計算対象範囲 , 条件付き書式の番号 , 結果を出力するセル )
①第一引数「計算対象範囲(Range型)」は、合計計算の対象となるセル範囲を指定します。図2-1で言えば、D4セル~AH7セルを計算対象としています(8行目に列合計の計算をしていますので、D8~AH8を対象としてもOKです)。
②第二引数「条件付き書式の番号(Integer型)」は、計算対象範囲に設定してある条件付き書式の番号を指定します。今回は図2-2のように2種の条件付き書式を設定してあり、上側(優先度高)よりNo.1、No.2 ・・・としています。例えばNo.1の書式はオレンジ色なので、A2セルに記載したColorSum関数は「オレンジ色のセル値を合計」する事を意味します。またA5セルのColorSum関数は「緑色のセル値を合計」します。
③第三引数「結果を出力するセル(Range型)」は、合計値を出力するセル範囲です。今回は条件付き書式No.1については「B3セル」に、条件付き書式No.2については「B6セル」に出力することにしています。

計算対象範囲(D4~AH7セル)の中の値を変更すると、その範囲を参照しているユーザー定義関数(ColorSum:A2セル・A5セル)が反応し、計算対象範囲の背景色と条件付き書式の背景色を比較して、条件付き書式で変更された背景色と判断されたセル範囲の合計値をB3セル・B6セルに書込みます。
なお、条件付き書式の背景色が変わる(例えば、C2セルの年月を変更する)だけや、同じセル値を入力するだけでは、ユーザー定義関数は反応しません。計算対象範囲(または、結果出力セル)の「値を変更」することで再計算が実行されます。
(この辺が、通常のSUM関数などとは感覚がズレる感じです。)

なお通常の関数でしたら「合計値は、関数が記入してあるセルに表示」されます。しかし今回の関数は合計値を表示するセル位置は第三引数で指定し、関数が記入してあるセルは「条書No=1」などという文字列(=関数の戻り値)となります(条書は、条件付き書式の略のつもり)。
もちろん「文字列も何も戻さない」ことも可能ですが、セルに何も表示されないと「どこに関数を書いたか分からなくなる」と思い、適当な文字列を返すことにしました。

条件付き書式の設定
図2-2

なお今回システムが計算対象としているのは「条件付き書式の背景色」のセルですので、第一引数として条件付書式が設定されていないセル範囲を設定してしまうと、その部分の値は無視します。(全てが条件付き書式外の場合は、合計値はゼロ表示となります)
また第二引数に、条件付き書式の設定数を超えた値を設定(例えば図2-2のように2つしか条件付き書式が無いのに、「3」を第二引数として設定)した場合も、全て無視されることになり合計値はゼロ表示となります。

3.プログラムの流れ

ワークシートのセルに入力されたユーザー定義関数の実体は、標準モジュールに置いてあります。計算対象範囲内の値に変化が生じると、標準モジュールの「ColorSum 関数プロシージャ」が呼び出されることになります。

プログラムの流れ
図3-1

関数プロシージャは3つの引数を受け取り、その引数を引き継ぐ形でフォーム上のSubプロシージャを呼び出します。
フォーム上のSubプロシージャは、受け取った引数をフォーム上の共有変数とした後、フォームを「モードレス」で起動します。

「モードレス」で呼び出すことで、制御はフォーム上で留まることなく「呼び出された標準モジュールへ戻る」ことになります。戻った標準モジュール(=関数プロシージャ)の中で、ユーザー定義関数としての戻り値(ここでは合計値では無く、設定値の一部を使った文字列)を作成しセルへ返します。返されたセルにはその文字列が表示されます。

一方「モードレス」で呼び出されたフォームは、フォーム起動時に自動的に発生するLayoutイベント(ActivateイベントでもOKです)内で、フォームとして受け取った引数を使って「計算対象範囲」のセルの表示色(DisplayFormatオブジェクト)を条件付き書式の設定色と比較をし、同じ(=条件付き書式が成立している状態と判断)であればセル値を積算していきます。
計算対象範囲の走査が終了したら、積算した結果(=合計値)を「結果出力セル」に書き込み、フォームを閉じます。

4.ワークシート(サンプルファイルではSheet1)

今回のワークシートは、図4-1のような「月間集計表」としました。月の基準日(1日付け)はC2セルに手入力し、そのセルの書式は「yyyy/mm」と年月だけが表示されるようにしています。
ワークシート上の関数類
図4-1

その月の基準日(C2セル)の値をD2セルへコピーし、そこから右側は+1ずつすることで月間の日付(31日分)を表示しています。日付欄(D2~AH2セル)の書式は「d」と日付だけにしています。
3行目は曜日欄ですので、数式を「=TEXT(D2,"aaa")」とし簡略的な曜日を表示しています。
8行目は日毎の合計欄ですので、数式を「=SUM(D4:D7)」としています。
そして、条件付き書式のセル色による合計関数「ColorSum」は、A2セル・A5セルに入力しました。計算対象範囲は「D4~AH7セル」とし、条件付き書式の1番目(土日のオレンジ色)及び2番目(5・10日の緑色)に対応させています。合計値の出力は、それぞれB3セル、B6セルとしました。
セルに記入した数式は、A2セルが「=ColorSum(D4:AH7,1,B3)」、A5セルが「=ColorSum(D4:AH7,2,B6)」となります。

集計表には、図4-2のように条件付き書式を使って「土日をオレンジ色」「5・10日を緑色」にしています。彩色の範囲(適用先)は「D2~AH8セル」と日付・曜日・データ記入欄・合計欄の全てとしました。
土日を彩色する式は、3行目(=曜日行)に対して「=OR(D$3="土",D$3="日")」としました。また5・10日は2行目(=日付行)に対して「=MOD(DAY(D$2),5)=0」と、5で割り切れる日付としました。
なお、土日の彩色を優先させた(条件付き書式のルールが上にある)ので、例え5・10日であっても土日であればオレンジ色になります。
条件付き書式の設定
図4-2

なお「B2~B3・B5~B6セル」は、分かり易くするために手動で背景色を「オレンジ色」「緑色」に変更しています。

5.標準モジュール(Module1)

標準モジュール先頭の宣言部では、01行目「Dim UF As UserForm1」とUserForm1型の変数UFを宣言しています。これは今回のように同時にユーザー定義関数が複数回呼び出される場合の対応です。

ユーザー定義関数(図5-2)内の19行目の先では「UserForm1を起動」しているのですが、複数セルから「ほぼ同時に」ユーザー定義関数が呼び出されると、UserForm1が起動している最中に、別のところから「UserForm1を起動せよ」との指令が出ることになります。また起動中のUserForm1の中からも再帰的にユーザー定義関数が呼び出されるため、更に重複します。

もしUserForm1を1つで対応しようとすると、同じUserForm1の中で受け渡す引数値が入れ替わってしまうようです。実際にやってみると「一方の関数側しか計算をしてくれない」ような現象が発生します。
ですので、ユーザー定義関数が呼び出される度に「新たなUserForm1を起動」できるようにします。
  1. '========== ⇩(1) 変数の宣言 ============
  2. Dim UF As UserForm1
図5-1

ワークシート上のセルに書かれた数式から呼び出されるのが、図5-2の「ColorSum ユーザー定義関数」です。引数として、以下の3つを受け取ります。
 第一引数:計算対象範囲(Rng:Range型)
 第二引数:条件付き書式の番号(FormatCond:Integer型)
 第三引数:結果を出力するセル( WriteCell:Range型)
なお、ユーザー定義関数が戻す値は今回文字列のため、戻り型はString型にしています。
  1. '========== ⇩(2) ユーザー定義関数 ============
  2. Public Function ColorSum(Rng As Range, FormatCond As Integer, WriteCell As Range) As String
  3.  On Error Resume Next
  4.   If UF.Enabled = False Then Set UF = Nothing
  5.  On Error GoTo 0
  6.  Set UF = New UserForm1
  7.  Call UF.UFstart(Rng, FormatCond, WriteCell)
  8.  ColorSum = "条書No=" & FormatCond
  9. End Function
図5-2

13~15行目は、古いUserForm1を削除(=初期化)しています。
14行目「If UF.Enabled = False Then Set UF = Nothing」では、UserFormが操作可能(Enabled)状態以外の時には初期化しているように見えますが、実際には「操作可能状態の時には初期化しない」という意味で、UserFormのEnableプロパティが取得出来ない状態(=既に閉じられているUserFormなど)の時に初期化をするという実行内容になります。

フォームは「生成直後」と「起動中」には初期化してはいけないので、このIf文をどう組み立てるかを図5-9で検討し、今回は「Enableプロパティ」を使用しました。それ以外の「初期状態」「フォームが閉じた後」はエラーとなりますので、13行目「On Error Resume Next」で14行目の前半のIf文をスルーさせ、後半の「 Set UF = Nothing」を実行させます。
(「初期状態」は本来は初期化する必要は無いのですが、If文を二重にするのも面倒だったため、一緒に初期化しています。)

17行目「Set UF = New UserForm1」では、UserForm1のインスタンスを新しく生成しています。つまり「ユーザー定義関数が呼び出される度に、新たなUserForm1を作成」し、その新たに作成されたUserForm1の中で「単一条件での計算+合計値の出力」を行っていきます。

19行目「Call UF.UFstart(Rng, FormatCond, WriteCell)」では、UserForm1内のUFstartプロシージャ(図6-5)を呼び出します。呼び出す際に、ユーザー定義関数ColorSumが受け取った3つの引数をそのままフォーム側に渡します。この段階では、まだUserForm1のShowメソッドを実行していないため、フォームは起動していません。

21行目「ColorSum = "条書No=" & FormatCond」は、ユーザー定義関数としての戻り値を設定し、呼び出したセル側へ戻しています。今回は「合計値」を戻すのではなく、「何番目の条件付き書式の関数か」が分かるような文字列を戻しています。(条書:条件付き書式の略のつもり)

寄り道
今回、同じユーザーフォーム(UserForm1)を複数起動させ、それぞれのフォーム内で合計計算を実行しています。複数個起動させるために01行目で「Dim UF As UserForm1」と宣言し、17行目で「Set UF = New UserForm1」とフォームを生成しています。しかしVBEで確認しても、複数のフォームが起動しているようには見えません。唯一、フォームの形が見えるようにする(例えば図6-3の44行目を「Me.Top = +100」にして、画面内表示させる)と複数のフォームが同時に起動しているのが分かります。
そのフォームは、最終的には「Unload Me(図6-6の91行目)」で消えるかと思いきや、消えずに全てのプロパティが「オートメーションエラー」という内容で残ってしまうのです(フォームの形は消えてくれます)。原因は「Unload Me」を実行した時には、既に呼び出し元であるユーザー定義関数は終了している ためのようです。

一体どういう状況になっているのか知りたくて、今回のUFという変数の代わりにオブジェクト配列などを使って確かめてみました。すると起動したフォームは延々と全て残っており、中身は全てオートメーションエラーであることが分かりました。
(もちろんEndステートメントなどで一旦初期化すればクリアされます。また、実行時エラーのオートメーションエラーとは異なり、いきなりプログラムが止まることは無さそうです。)

これでは、データが更新された回数 × ユーザー定義関数の数の分だけ「ゴミが溜まる」ことになり、悪くすればExcelの停止やWindows不安定などにも繋がります。そこで「一連の計算途中はガマンする」として、「次の計算が始まる直前に、UserForm1オブジェクトをクリア」しようと考えました。
しかしオブジェクトをクリアするには、「Set UserForm11 = Nothing」というコードを実行することになります。試しに複数のフォームが起動している途中でクリアすると「全てのUserForm1が初期化」されてしまいます。

そこで、色々調べている内に「VBEで見えているのは、最も最新のUserForm1の姿」であることが分かってきました。
すると、見えている最新のUserForm1の状態が終了処置済みだとして、その後ろに隠れている複数個のUserForm1を全てクリアしても良いのかが問題になります。実行される順番によっては、例えばその1つ前のフォームは「生きている」可能性も考えられるからです。
この実行順序を確かめるために、まずコード単位でのプログラムの流れを調べたのが図5-3です。

実行コードベースの流れ
図5-3

図3-1でも大雑把に説明しましたが、流れは大きくA・B・Cに分けられます。複数のフォームが起動したとしても、このA・B・Cが更に分かれて実行されることは無さそうです。
Aは、ユーザー定義関数としての「一連の動作」に相当します。フォームの域まで達していますが、繋がっている流れです。
Bは、Showメソッドにより「フォームをモードレスで起動した先」であり、ユーザー定義関数の「一連の動作」からは外れた所です。このBの部分で計算をし、その計算結果をセルに書き込むと、その瞬間にまた「ユーザー定義関数」が呼び出されることになります。セルへの書込みが無い場合(今回では、書き込む値が同じ値の時)は、Cのフォームを閉じるコードを実行します。
このA・B・Cの3つのかたまりが、どの順番で実行されるかを示したのが以下になります。

まず、1つだけのユーザー定義関数の場合が図5-4です。
計算した結果が元の値と変わらない場合は、図5-4の上側のように「ストレートにA→B→C」と進みます。
合計値が変わる場合は、セルへの書込みをした直後(B)に、ユーザー定義関数が呼び出されて新たなフォームを起動(A)します。フォームを起動した後は、元のルートに戻りフォームを終了(C)させた後、新たに発生したフォームで計算(B)を始めます。しかし計算結果は、元のルートで書き換え済みなので「セル値と同じ」となり、書き込みをせずにフォームを終了(C)します。
1ヶ所から呼び出される時のコードの流れ
図5-4

ユーザー定義関数が2ヶ所にある場合は、図5-5以下になります。2ヶ所とも値変更がない場合が図5-5になります。
数式からの呼出しはほぼ同時に発生しますので、1ヶ所目のフォーム起動が完了した時点で、1ヶ所目の計算を始める前に2ヶ所目のフォーム起動が始まります。2ヶ所目のフォームが起動したら、1ヶ所目の計算を開始し終了したら2ヶ所目の計算に入ります。
2ヶ所から呼び出される時のコードの流れ(2か所とも合計値変更無し)
図5-5

片方の計算結果が元の値と異なる場合は、セルへの値出力が行われた瞬間に新たなフォームが起動されます。1ヶ所目の合計値が異なる場合が図5-6です。
2ヶ所から呼び出される時のコードの流れ(1か所目に合計値変更有り)
図5-6

図5-6と同類で、2ヶ所目の合計値が異なる場合が図5-7です。
2ヶ所から呼び出される時のコードの流れ(2か所目に合計値変更有り)
図5-7

2ヶ所共の合計値が異なる場合は、図5-8になります。
それぞれ、フォームが起動された順番に処理を完了させる動きになるようです。
2ヶ所から呼び出される時のコードの流れ(2か所とも合計値変更有り)
図5-8

図5-4~図5-8の「フォームを起動するブロック(=A)」に、起動した順番の番号を振りました。VBAでは「最新のUserForm1の状態」のみが見えるので「各番号の1つ前のブロックが生きているか」という視点で確認してみると、「1つ前は、必ず生きている(=終了処理がされていない)」事が分かります。
3ヶ所以上の組合せを確認している訳ではありませんが、「フォームを起動した後で、それより前に起動したブロックの計算処理を開始する」ことを考えれば「1つ前のブロックは生きている」と言えると思います。

ということで、図5-2の13~15行目で「VBAとして見えるUserForm1(=1つ前のフォーム)」の状況を調べ、オートメーションエラーであれば「全フォームは終了処理済み」と判断しても良いだろう、と考えました。
では、その「フォームが終了処理済みか否か」を調べる手段ですが、図5-9のように3種類を考えました。「閉じた後」だけを選別できれば良いのですが、エラー番号がExcelバージョン等により異なる可能性も考えられるため、決して削除してはいけない「生成直後」と「起動中」以外で選別できる「Enabledプロパティ」を今回使用することにしました。
初期状態生成直後起動中閉じた後
オートメーションエラー状態
UserForm1 Is NothingTrueFalseFalseFalse
UserForm1.Visibleエラー(91)FalseTrueエラー(-2147418105)
UserForm1.Enabledエラー(91)TrueTrueエラー(-2147418105)
図5-9

削除のコードは、図5-2の14行目のように「If UF.Enabled = False Then Set UF = Nothing」とし、「生成直後」「起動中」は実行されずに、エラーの場合(初期状態、フォームが閉じた後)だけその後の「Set UF = Nothing」が実行されるようにしました。
「初期状態」も初期化してしまう事になりますが、更にIf文を重ねるとゴチャゴチャになりそうなので止めました。

なお、モードレスで起動したフォーム内で処理をする以外に「OnTimeを使って時間差でプロシージャを動かす」という手法も思い付くかもしれませんが、今回のようなユーザー定義関数の「一連の動作」の中では、残念ながら「OnTimeは動作しない」ので使えません。

6.フォーム(UserForm1)

6-1.フォームデザイン

ユーザーフォームのフォーム自体は今回使用しません。図6-1のように初期状態のまま、手を加える必要はありません。
既定のままのフォームデザイン
図6-1

6-2.フォームモジュール

先頭の宣言部には、フォームモジュール内で共有する変数の宣言をします。
変数は、ユーザー定義関数として受け取った3つの引数を、フォームレベルの変数にしています。変数への値代入は、図6-5内で行っています。
  1. '========== ⇩(3) フォームレベル変数の宣言 ============
  2. Dim Rng As Range
  3. Dim FormatCond As Integer
  4. Dim WriteCell As Range
図6-2

6-2-1.起動準備

フォームが起動する際に、最初に呼び出されるのがInitializeイベントです。今回で言えば図5-3で分かるように、図5-2の17行目「Set UF = New UserForm1」から呼び出される形になります。
  1. '========== ⇩(4) フォームの初期設定 ============
  2. Private Sub UserForm_Initialize()
  3.  Me.StartUpPosition = 0
  4.  Me.Left = 0
  5.  Me.Top = -100
  6.  Me.Height = 0
  7.  Me.Width = 0
  8. End Sub
図6-3

Initializeでは、(表示して欲しくないが)表示されてしまうフォームの表示位置を定めています。今回はできるだけ「フォームを見せたくない」ので、サイズを小さく(サイズ=ゼロ)します。しかしゼロにしたとしても「タイトル部は表示されてしまう」ので、表示位置は画面の外に追い出すようにします。

42行目「Me.StartUpPosition = 0」は、フォームの表示位置を指定するものです。位置指定は43~44行目で行いますので、それよりも前にStartUpPosition設定を行う必要があります。
設定値は図6-4の中から選びますが、今回は画面から外しますので「ゼロ」を指定します。
設定(定数では無い)説明
Manual0初期設定値を指定せず
CenterOwner1UserFormが属する項目の中央
CenterScreen2画面全体の中央
WindowsDefault3画面の左上隅
図6-4

43行目「Me.Left = 0」で左右方向の位置をゼロ、44行目「Me.Top = -100」で上下方向をマイナスにすることで、画面外に追い出しています。
45行目「Me.Height = 0」ではフォームの高さをゼロにし、46行目「Me.Width = 0」では幅をゼロにします。
上でも説明した通り、高さ・幅をゼロにしてもタイトル部は表示されてしまいますので、タイトル部も含めて画面外に追い出すためにTop値をマイナスにしています。

6-2-2.引数の引継ぎとフォーム起動

図5-2の19行目から呼び出されるのが図6-5です。引数として「計算対象範囲(UFRng)」「条件付き書式の番号( UFFormatCond)」「結果出力セル(UFWriteCell)」を受け取ります。これらは、ユーザー定義関数ColorSumが受け取った引数そのものですが、フォームレベルの変数名をColorSumの引数名と同じにするために、頭にUFを付けた引数名にしています。
  1. '========== ⇩(5) 標準モジュールからの引数受け取り ============
  2. Public Sub UFstart(UFRng As Range, UFFormatCond As Integer, UFWriteCell As Range)
  3.  Set Rng = UFRng
  4.  FormatCond = UFFormatCond
  5.  Set WriteCell = UFWriteCell
  6.  Me.Show vbModeless
  7. End Sub
図6-5

52行目「Set Rng = UFRng」では、引数として受け取った計算対象範囲をフォームレベル変数Rngに代入します。
53行目「FormatCond = UFFormatCond」では、条件付き書式の番号を変数FormatCondに代入します。
54行目「Set WriteCell = UFWriteCell」では、結果出力セルを変数 WriteCellに代入します。

56行目「Me.Show vbModeless」では、自分(=UserForm1)を「モードレス」で起動します。モードレスで起動することで、制御は次の行に移り、UFstartプロシージャを抜け、呼び出し元のユーザー定義関数ColorSumに戻ります。
一方、起動したフォームは、Initializeイベントは完了していますので、その次の「Layoutイベント」→「Activateイベント」へと自動的に進んで行きます。

6-2-3.計算とフォーム終了

フォームが起動されたことで発生する「Layoutイベント」が図6-6です。この中で計算と出力の処理を行います。
なお、同じ内容をActivateイベント内で行っても同じ機能となります。
(Layoutを使えばフォームが非表示になるかな? と期待したのですが、残念ながら表示されてしまいました)
  1. '========== ⇩(6) 計算と合計値出力 ============
  2. Private Sub UserForm_Layout()
  3.  Dim r As Range       '←計算対象範囲の1つ1つのセル
  4.  Dim SumN As Double     '←合計値
  5.  Dim ColorNum As Double   '←条件付き書式で設定されている背景色
  6.  For Each r In Rng
  7.   On Error Resume Next
  8.    ColorNum = r.FormatConditions(FormatCond).Interior.Color
  9.    If Err.Number = 0 Then
  10.     If r.DisplayFormat.Interior.Color = ColorNum Then
  11.      SumN = SumN + Val(r.Value)
  12.     End If
  13.    End If
  14.   On Error GoTo 0
  15.  Next r
  16.  If Not WriteCell.Value = SumN Then WriteCell.Value = SumN
  17.  Unload Me
  18. End Sub
図6-6

76~87行目では、計算対象範囲の1つ1つのセルの表示色(DisplayFormatオブジェクト)を調べ、「そのセルに設定されている条件付き書式」の設定番号の「設定書式(今回は、背景色)」と合致している場合に、セル値を変数SumNに積算をしていきます。
ですので複雑に条件付き書式を設定してしまうと「あるセルには黄色設定は2番でも、あるセルにとっての2番は青色」のような設定になり、思った結果が得られない事も考えられます。

76行目「For Each r In Rng」は、計算対象範囲Rngから1つずつセルを取り出します。1個のセルは変数rとしています。
78行目「ColorNum = r.FormatConditions(FormatCond).Interior.Color」は、そのセル(r)に設定してある条件付き書式の番号(FormatCond)の背景色を取得し、変数ColorNumに代入します。
但し、そのセルに「条件付き書式が設定されていない」場合も考えられます。また、条件付き書式の番号(FormatCond)が条件付き書式の数よりも多い場合もあります。その時には78行目でエラーが出ますので、77行目「On Error Resume Next」でエラーをスルーさせます。

今回は「条件付き書式で変更された背景色」でのセル値のみを拾い上げていきますので、80行目「If Err.Number = 0 Then」で、エラーが出ていない(=条件付き書式が設定されていて、正しく背景色が取得できる)場合だけ81~83行目の値積算を実行していきます。
81行目「If r.DisplayFormat.Interior.Color = ColorNum Then」では、セルの表示色が条件付き書式の背景色と同じ時に82行目「SumN = SumN + Val(r.Value)」でセル値を積算していきます。セル値はVal関数で数値にしていますので、もし文字列が入っている場合は「ゼロ」を積算することになります。

計算対象範囲の全てのセルに対する積算が終了したら、89行目「If Not WriteCell.Value = SumN Then WriteCell.Value = SumN」で、積算値を結果出力セルへ貼り付けます。但し「If Not WriteCell.Value = SumN Then」で、「結果出力セルの値が積算値と異なっている場合」に限っています。
通常ユーザー定義関数は、参照しているセル値を「手入力で同じ値に書き換え」ても、再計算しない(ユーザー定義関数は呼び出されない)のですが、VBA側から同じ値に書き換えると呼び出されてしまいます。そのため「If Not WriteCell.Value = SumN Then」という制限をつけないと、呼び出しが繰り返し発生してしまいます。

91行目「Unload Me」は、フォームを閉じています。この操作で確かにフォームの形は消えるのですが、「よりみち」でも説明したように、フォームの全プロパティがオートメーションエラーという内容に変わり「オブジェクト変数UF」として残ってしまいますので、次回の呼出し開始時に図5-2の13~15行目でオブジェクト変数の初期化を行っています。

7.アドインとしてExcelにマクロを登録

このマクロ付ファイル(サンプルファイル)をExcelのアドインに登録することで、今回の「フォームカレンダー」を他のブックから呼び出して使うことが出来ます。アドイン方法については「年賀状リスト等の宛名検索と追記 アドイン登録」を参照下さい。
なお、ボタンを押して何かを実行するシステムではないので、「開発」タブ→「アドイン」グループ→「Excelアドイン」ボタンで開くダイアログから、登録された今回のアドインを「有効(レ点を付ける)」にするだけでユーザー定義関数として使用できます。

8.最後に

今回、ユーザー定義関数とは一連の流れが切れた「モードレスで起動したユーザーフォームから計算」という手法を用いました。他にも、計算対象範囲の値を変更したことを「Worksheet_Change等のイベント」で受け取り、計算を開始する という方法も考えられます。但し、自由に配置できるユーザー定義関数がどこにあるのか(ユーザーがどこに合計値を表示したいのか)を調べる方法が思いつかず、フォームをモードレスで呼び出し・・・という手法にしました。
もう少し考えれば、上手い方法を思いつきそうな気もします。


条件付書式で色変更されたセル値を合計する関数(it-083.xlsm)
C2セルの年月日を変更しても、合計値は変わりません。計算対象範囲内の数値を変更することで再計算が実行されます。

セキュリティ向上を目的として「インターネット経由でダウンロードしたOfficeファイル(Excel等)のマクロは、既定でブロック」されるようにOfficeアプリケーションの既定動作が変更になりました。(2022年4月より切替開始)
解除の方法については「ダウンロードファイルのブロック解除方法」を参照下さい。