セルに貼り付けた自作関数での書式反映について

質問

引数で日付を渡し、漢字の曜日一文字が返る関数をＶＢＡで作成しました。
どうせなら日曜日の場合は文字の色を赤色にしようと思い、日曜日の場合だけ文字色を赤にする一文を追加しました。
が、セルにこの関数を貼り付けると曜日は返ってくるのですが、日曜日の場合の赤色が反映されません。
どなたかご教授いただければ嬉しいのですが、、、
宜しくお願い致します！

OS:WindowsXP
Excel:OfficeXP です。

以下に作成したマクロ関数を貼り付けます。

*********************************************************

Public Function week(d) As String
　On Error GoTo err_week
　Select Case Weekday(d)
　Case vbSunday
　　week = "日"
　　Worksheets(Application.Caller.Worksheet.Name).Cells(Application.Caller.Row, Application.Caller.Column).Font.ColorIndex = 3
　Case vbMonday
　　week = "月"
　Case vbTuesday
　　week = "火"
　Case vbWednesday
　　week = "水"
　Case vbThursday
　　week = "木"
　Case vbFriday
　　week = "金"
　Case vbSaturday
　　week = "土"
　Case Else
　　week = "×"
　End Select
Exit Function

err_week:
　week = "ER"
　Exit Sub 'End
End Function

*********************************************************

回答No.2

venzou · ベストアンサー

マクロと違い、ユーザー定義関数には制限があります。
http://support.microsoft.com/kb/170787/ja

-----引用-----
ワークシートのセル内の数式から呼び出されるユーザー定義関数では、Microsoft Excel の環境を変更できません。つまり、このような関数では次の処理が実行できません。
・スプレッドシートでのセルの挿入、削除、または書式の設定。
・別のセルの値の変更。
・ブックでのシートの移動、名前の変更、削除、または追加。
・計算方法や画面表示など、環境オプションの変更。
・ブックへの名前の追加。
・プロパティの設定およびほとんどのメソッドの実行。
-----引用-----

書式の変更は出来ないのでは？

今回の件は、条件付書式で代用出来ると思います。

補足

どうもありがとうございます。

書式変更は無理って事ですね、、、

FormatConditionsを使えばって事でしょうか？

回答No.5

imogasi

エクセルのFunction（ユーザー関数）は値を返すことができるだけで
書式を変更したり、行・列の削除挿入等全てができません。
今までユーザー関数を作って使った経験が無いから不思議に思うだけで、経験者には自明のことです。
そういうもんだと知ってください。
一般の他の言語の関数と違いがあります。
通常のワークシートの関数も書式や行列・挿入他できないのですが、このことを知らない初心者が、どうしたらよいかという質問をこのコーナーでしばしば見かけます。この関数の制約と軌を一にします。
SUBプロシージュアーは改めて（起動をかけないと）実行しないと処理結果が代わり・出ません。通常のエクセル関数はデータの変化に対してその点自動反映をします（注）が、この点不便ですが、イベントプロシジュアーででも賄わないと他に方法はないでしょう。
(注）その代わりワークシートで全使用関数の計算しなおしをやっている（という犠牲を払っている）ようです。関数使用のセルが増えると
処理速度への影響が無視できません。

補足

＞エクセルのFunction（ユーザー関数）は値を返すことができるだけで
＞書式を変更したり、行・列の削除挿入等全てができません。

この文言だけだと誤解を生むと思いますので、、

セルに貼り付けずに直接マクロ(Function)を実行するなら書式設定も行や列の挿入や削除は出来ますが、、、、、
関数をセルに貼り付ける使い方は普段はしていなかったのですが、貼り付けると普通に出来ていたことが出来なかったのでどうしてかなっ？
と言う疑問になった次第です。
上のほうで教えていただいたＭＳのＨＰも実は見ていたのですが、できる筈だとの思い込み、どうすればできるかと言う視点で探していたものですからよく読んでいなかったと言う次第ですが、あらためて教えていただき、よく読んだら出来ないと書いてありました。

回答No.4

fumufumu_2006

呼び出す時にcell情報も渡してしまうのはどうでしょうか？

Public Function week(d As Date, row As Long, column As Integer) As String
...
Case vbSunday
week = "日"
ActiveSheet.Cells(row, column).Font.ColorIndex = 3
...
End Function
にして、
=week(A1,ROW(),COLUMN())
で呼び出す。

または、
Public Function week(d As Date, row As Long, column As Integer, filename As String) As String
...
Case vbSunday
week = "日"
Sheets(Mid(filename, InStrRev(filename, "]") + 1)).Cells(row, column).Font.ColorIndex = 3
...
End Function
にして、
=week(A1,ROW(),COLUMN(),CELL("filename"))
で呼び出す。

補足

コメントありがとうございます。

実験はしていませんが http://support.microsoft.com/kb/170787/ja
を読む限りは意図した使い方は出来ないかと、、、、

セルに貼り付けるときにrowとcolumnを引数で渡すと言うのも意図した
使い方とはちょっと違います、、、

回答No.3

venzou

＞書式変更は無理って事ですね、、、

リンク先のマイクロソフトの情報を信じれば、
そういう事になると思います。

＞FormatConditionsを使えばって事でしょうか？

VBAからではなく、普通のエクセルのメニューから、
「書式」→「条件付き書式」と言う意味で書きました。

自作関数が入力されているセルに、条件付き書式で、

　　セルの値が　　　次の値に等しい　　　="日"

として、書式を設定します。

補足

FormatConditionsを使って下記の様にしてみましたが撃沈しました、
--------------------------------------------------------
　　　：
　Dim r, c
　　　：
　　　：
　Case vbSunday
　　r = Application.Caller.Row
　　c = Application.Caller.Column
　　week = "日"
　　Cells(r, c).FormatConditions.Delete
　　Cells(r, c).FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
　　　Formula1:="日"
　　Cells(r, c).FormatConditions(1).Font.ColorIndex = 3
--------------------------------------------------------

言われるように普通に予めセルに設定しておくと言う事にしようと思います。
ご教授、ありがとうございました！　<(_ _)>

回答No.1

pbforce

セルの指定方法が違いそうです。

補足

Cells(Application.Caller.Row, Application.Caller.Column)の部分は
間違いなく呼び出し元のセルになっており、曜日もキッチリ返っているんですが、、、、、
rowとcolumnについては内容を確認して間違っていませんでした、、
R16C8に貼り付けたとき
　Application.Caller.Rowは16に、
　Application.Caller.Columnは8になっています