エクセル 関数内でCellsを使うと処理キャンセル

質問

皆様の知恵をお貸しください。
よろしくお願いします。

掲題の通りVBAでCellsを使用すると該当の処理が行われないだけでなく、
それまでの関数処理がキャンセルされ、「#VALUE!」のエラー値が返ってきます。

処理内容は和暦の扱いでCurrentNendoは３桁（Public宣言のInteger型）で、
E列のCellRow行に年号部分の頭１桁を設定したいというものです。
　※CurrentNendoは頭１桁は年号（昭和：3、平成：4）で残りは年です。

スクリプトは下記の通りです。
（前任者の引き継ぎで改変しているので無駄な操作が多いですがご容赦ください。）

Function Nendo(PrevNendo As Integer, CellRow As Integer) As String

If CStr(PrevNendo) <> "" Then
Dim NowNendo As Integer
NowNendo = 400 + Format(Date, "e")

If CarrentG = 3 And PrevNendo = 63 Then
PrevNendo = 0
CurrentNendo = 401
End If

If CarrentG * 100 + PrevNendo = NowNendo Then
Nendo = ""
Else
Nendo = PrevNendo + 1
CurrentNendo = CurrentNendo + 1
End If
End If

'(1)
Nengo(CellRow)
'(4)

End Function

Sub Nengo(CellRow As Integer)

'(2)
Cells(CellRow, "E").Value = Left(CStr(CurrentNendo), 1)
'(3)

End Sub

別にSub宣言してNengo関数をコールする必要はないのですが、
処理確認のためこのような形になっています。

「CurrentNendo」の値が「356」で、
エクセルシートで「=Nendo(56,5)」を設定すると、
　　　(1)通過
　　　(2)通過
　　　(3)処理されず
　　　(4)処理されず
返り値は「#VALUE!」エラーです。
セルE5に対する反応は無しです。

「Nengo(CellRow)」をコメントアウトすると、
　　　(1)通過
　　　(4)通過
返り値は「57」です。
セルE5に対する反応はもちろんありません。

テストでイミディエイトウィンドウ上で「Nengo(5)」を入力すると、
正常に「3」がセルE5に設定されます。

たいした処理をしているわけではないと思うのですが、
何故このような現象が生じるのかわかりません。

ちなみにCellsをRangeにしようが、ActiveSheetやらValueやら付けても
結果は同じでした。

よろしくお願いいたします。

回答No.1

keithin · ベストアンサー

ユーザー定義関数からでは，別途セルを触ることは出来ません。アナタが試みたように，別のプロシジャに切り離してもダメです。

ごくシンプルな解決方策としては，Ｅ列セルにふつーにエクセルのワークシート関数を並べておいて，計算で結果を出させてしまうだけでＯＫのようにも見えます。
たとえばＤ列にあなたのnendo関数を置いているなら
Ｅ６：
=LEFT(D6,1)
などのように。

既にユーザー定義関数を使って計算させていることから，シートには生データだけを記入させておきたいといった要請は弱いと考えられますが，敢えてＥ列には必ず生データを記入しておきたい（させたい）ということでご相談の状況が発生しているのだとすると，前述したようにＥ列に「値を記入する」にはユーザー定義関数とは違う由来のプロセスを用意する必要があります。

既存の仕込みを変えたくないという事なのでやりにくいですが，今のご質問のユーザー定義関数のプロセスを通常のマクロのプロセスに作成し直すのも一つの方法です。

または，シートにchangeイベントを別途仕込んでおき，「既存のユーザー定義関数に引数として与えているセル」の変更を受けてＥ列に所定の値を計算させ記入させる方法がツカエルかもしれません。

最悪シートのcalculateイベントを仕込み，ユーザー定義関数が再計算される都度，Ｅ列を漏れなく書き換えていくことも，やればできます。通常はあまりに非効率なので，こういう使い方はしませんが。

お礼

回答頂き、ありがとうございます。
確認が遅れ、お礼が遅れてしまい申し訳ありません。

まず、ユーザー定義関数からのセル操作が出来ないということがハッキリしたことはとても大きな事実でした。
基本的なことなのかもしれませんが、ＶＢＡ自体ＡＣＣＥＳＳで扱ってきたことが多くＥＸＣＥＬに関しては無知でした。
事実を知らぬまま今の方策に固執してしまう可能性もあり、大きくタイムロスする可能性もありましたのでとてもありがたかったです。

また別の方策も何点か教え頂いたこと、配慮に感謝します。

今週はこれで業務が終了ですので、来週アドバイスに基づき別の方策を練ることにします。
ありがとうございました。