Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


10658 / 13644 ツリー ←次へ | 前へ→

【20601】着色セルの自動集計のユーザ定義関数 SNGN 04/12/13(月) 9:53 質問[未読]
【20604】Re:着色セルの自動集計のユーザ定義関数 ichinose 04/12/13(月) 11:07 発言[未読]
【20605】Re:着色セルの自動集計のユーザ定義関数 Jaka 04/12/13(月) 11:23 発言[未読]
【20609】Re:着色セルの自動集計のユーザ定義関数 SNGN 04/12/13(月) 13:22 お礼[未読]
【20607】Re:着色セルの自動集計のユーザ定義関数 SNGN 04/12/13(月) 13:02 お礼[未読]

【20601】着色セルの自動集計のユーザ定義関数
質問  SNGN  - 04/12/13(月) 9:53 -

引用なし
パスワード
   やりたいこと。
   A列    B列     C列
1        X社支払予定 Y社支払予定
2        3200      300 
3        500     1000
4        400      110
5        4100      700
6        700     2200
7  予定合計
8  済み合計

あらかじめ決められた各社への支払予定金額の表があります。予定合計は、Sum関数で合計するので問題はありません。この予定表をそのまま使って、支払った合計金額を、済み合計欄に自動計算したい。支払った金額には、そのセルを、例えば水色(ColorIndex=8)で着色することだけで区別しています。そのとき、済み合計を、次のようなユーザ定義関数で、処理しようとしましたが、うまく自動計算できません。

****************************************************************
'標準モジュール
Function ColoredSum(セル範囲 As Range, myColor As Integer)
Application.Volatile
myWork = 0
For Each myCell In セル範囲
  If myCell.Interior.ColorIndex = myColor Then
    myWork = myWork + myCell.Value
  End If
Next
ColoredSum = myWork
End Function
******************************************************************

B8の式  =ColoredSum(B2:B6,8)
C8の式  =ColoredSum(C2:C6,8)

調べてみますと、Volatileは、着色する等のセルの書式変更には反応してくれないようです(着色し、金額を変更すると、自動計算してくれますが、、、)。
金額はいじらず、着色のみで自動計算してくれる何か、いい方法がないでしょうか?
よろしくお願いします。

【20604】Re:着色セルの自動集計のユーザ定義関数
発言  ichinose  - 04/12/13(月) 11:07 -

引用なし
パスワード
   ▼SNGN さん:
おはようございます。
塗りつぶしをイベントで認識ができないのでそれでも
やろう思うと難しい話になってしまいますよね?
この問題、レイアウトをちょっと変更すれば、VBA不要だと思うのですが・・・。

例えば、各社の支払予定(金額)の列の前に支払済みか否かを表す区分列を挿入します。

>   A列  B列 C列     D列 E列
>1       支払 X社支払予定 支払 Y社支払予定
>2          3200        300 
>3          500        1000
>4          400         110
>5           4100        700
>6          700        2200
>7  予定合計
>8  済み合計
>
のように・・・・。
支払列が「1」だったら支払済みにするという仕様にすると、
「支払予定」列に色を付けるのは「条件付書式」で可能ですよね?
又、支払列に「1」が指定されたら、「済」という文字列に変更するとすれば
「セルの書式設定」で可能です。

このように設定すれば「済み合計」もワークシート関数のSumif関数を用いれば
可能だと思いますが・・・・。

レイアウトの変更と言う事を考えるわけにはいきませんか??

【20605】Re:着色セルの自動集計のユーザ定義関数
発言  Jaka  - 04/12/13(月) 11:23 -

引用なし
パスワード
   こんにちは。

ichinoseさんがいたので書き直し。
私もichinoseさんと同じ事考えてましたが、1回目は取り上げませんでしたけど。
条件付書式を使った場合、セルに文字を入力するだけなので着色の手間が楽。
といったものもありますね。
条件付書式を使った方が楽は楽だけど、数が多過ぎると重くなりそうなきもするけど。
速さを選ぶんだったら、条件付書式でしょうね。

↓1回目のやつ

▼SNGN さん:
>調べてみますと、Volatileは、着色する等のセルの書式変更には反応してくれないようです(着色し、金額を変更すると、自動計算してくれますが、、、)。
>金額はいじらず、着色のみで自動計算してくれる何か、いい方法がないでしょうか?
>よろしくお願いします。

出来ないと思います。マクロ関数GetCellにしても、通常の関数同様セルの着色のみには反応しません。

取りあえず気休めみたいなものですが、マクロで1番速そうな物だとこれぐらいかなぁ。

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
  'どれか好きなものを選んでください。
  Calculate
  'Worksheets(1).Calculate
  'Worksheets(1).Range("B1:B16").Calculate
End Sub

【20607】Re:着色セルの自動集計のユーザ定義関数
お礼  SNGN  - 04/12/13(月) 13:02 -

引用なし
パスワード
   早速の回答、ありがとうございます。
申し訳ありません、レイアウトの変更はしないということでお願い致します。
といいますのは、
  (1)私のファイルではなくて、職場の他の人のファイルです
  (2)既に、決まっている定型のファイルです
レイアウトを変更したり、別のワークシートを使ったりはしたくないのが、実情です。

もし、いい方法があれば、その人(ユーザ)のファイルの標準モジュールシートに、その関数を書き込んであげて、アトは、ユーザがsum()関数の使い方と同じイメージで、coloredSum()で、計算を出来るようにしてあげられたらとよい、、、と、思ったのですが。。。。

【20609】Re:着色セルの自動集計のユーザ定義関数
お礼  SNGN  - 04/12/13(月) 13:22 -

引用なし
パスワード
   ありがとうございます。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Calculate
End Sub

で、なんとか凌げます。Calculate っていう関数は、知りませんでした。
助かりました。

10658 / 13644 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free