Excel VBA質問箱 IV

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

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

  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
10660 / 13646 ﾂﾘｰ ←次へ　｜　前へ→
▼ 【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 -
引用なし
パスワード
   やりたいこと。
　　　Ａ列　　　　Ｂ列　　　　　Ｃ列
1　 　　　　　　Ｘ社支払予定　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不要だと思うのですが・・・。

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

>　　　Ａ列　　Ｂ列　Ｃ列　　　　　Ｄ列　Ｅ列
>1　 　　　　　支払　Ｘ社支払予定　支払　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さんと同じ事考えてましたが、１回目は取り上げませんでしたけど。
条件付書式を使った場合、セルに文字を入力するだけなので着色の手間が楽。
といったものもありますね。
条件付書式を使った方が楽は楽だけど、数が多過ぎると重くなりそうなきもするけど。
速さを選ぶんだったら、条件付書式でしょうね。

↓１回目のやつ

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

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

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

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 っていう関数は、知りませんでした。
助かりました。
  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
10660 / 13646 ﾂﾘｰ ←次へ　｜　前へ→
ページ：  ┃  記事番号：
2610219
(SS)C-BOARD v3.8 is Free