エクセルでマクロ関数がファイル開けるとエラーになる

質問

お世話になります。

だいぶ前にここでマクロを使った関数を教わって非常に有用に使わせていただいているのですが、当初より1つの軽微な問題があったのですが実使用上問題ないのでそのまま使用させてもらっています。

不具合はファイルを開けると、関数の結果が添付のようにエラー
表示になてしまっています。

列のどこかに何か入力するとその列の関数は再計算されて正常な結果が表示されます。
通常はこのファイルを開ける時には表に入力をするので、入力したら結果が表示されれば問題はないのですが、単純に統計結果を見たいときには各列に適当に数字を入れないと結果が確認できないことになります。

手間なのは左端の列に123と適当に入れて横に引っ張ると書式等の列の属性まですべてコピーされてしまうのですべての列に横に手入力し、結果が表示されたらその行を削除しています。

この作業を複数の担当者に説明しているのですが最近人も変わったりしたので何とか改善できないでしょうか？

回答No.7

kkkkkm · ベストアンサー

> 必要な時に各列の最下行のセルに一旦何か入力して、削除するとチャンと表示されるので大きな問題ではないのですが。。。。

定義関数に問題がなく、また、ブックを作り直してもどうにもならない場合には、その操作をマクロで起動時にやるという悪手も考えられますが…

A1としてますがそのあたりは適当に

Private Sub Workbook_Open()
Range("A1").Activate
SendKeys "{F2}", True
Range("A1").value = Range("A1").value
SendKeys "{ENTER}", True
End Sub

お礼

何度もご回答ありがとうございます。

当方の実力ではマクロの不具合をマクロで対応するよりは実害のない、使用実績のある現状で使いたいと考えます。

本当にありがとうございました。
当方欲深いので一度は満足しても使ているうちにどんどん改善要求が出てくるのでまたの機会があれば何卒よろしくお願します。

回答No.6

kkkkkm

> ただ再現性に乏しく、正しく計算結果が表示されて開いてくる場合もあります

現象が出たりでなかったりするというのは難解ですね。
共有しているという事は複数の人が同時に開いているときにエラーになるとかでもないのでしょうか。
多分#NAME?はユーザ－定義関数が認識されていないから出るエラーのような気がしますし、#VALUE!は計算する範囲に数値以外があるのかなとも思えます。

コードがどうなのかは、作成者がいらっしゃるようなので確認する事は遠慮しておきます。

最終的にうまく解決しない場合には、IFERRORでうまくいっているのでしたら、そちらで逃げておくというのも手ではありますね。

お礼

何度もご丁寧なご回答に感謝！
複数開くと作業内容が保存（上書き保存）できないのでこの状況は無く、原因ではないです。

データ入力範囲のセル内容を変更すると直ちに計算され正しい値が表示されるので、数値以外の・・・は非該当、同様に定義関数・・・もセル内容を変更すると正しく動くのでこれも原因では無いように思います。

まぁこのままでも当初の目的は十分満たしているので、必要な時に各列の最下行のセルに一旦何か入力して、削除するとチャンと表示されるので大きな問題ではないのですが。。。。

補足

IFERROR（・・・,””）は質問のエラーの状態だと正しく「空白」になるのでダメでした。
ある意味正しく動作しているということですが。

当該列のどこかのセルを変更すると他の列と同様に正しく計算されます。

回答No.5

kkkkkm

多くのPCでその症状が出ているのではないのでしたら、一度該当PCでOfficeの修復をしてみてはいかがでしょう。
何かタイミングでユーザー定義関数が(一時的にマクロが有効になっていない等)認識されていないのかもしれません。

補足

このVBAは多くのBookに採用されており、複数のPCで共有使用されています。

どのPCでも同じ現象が出ます。
ただ再現性に乏しく、正しく計算結果が表示されて開いてくる場合もあります。
一度数値を入力して正しく計算されるといったん閉じてすぐに開くとちゃんと計算されて開いてきます。

IFERROR（・・・,””）だと空白か正しく表示されて開いてきます。

回答No.4

HohoPapa

今回のように、
VBAで自前の関数を作り、
シートにその関数を埋める使い方をする場合、
特別な（おそらく一般的ではない）書き方をしない限り
>ファイルを開けると、関数の結果が添付のようにエラー
>表示になてしまっています。
>列のどこかに何か入力すると
>その列の関数は再計算されて正常な結果が表示されます。
という動作は起きません。

可能な限り、
sfAverageという関数の中身と
課題シートが書き換わることで起動されているであろう、
Private Sub Worksheet_Change(ByVal Target As Range)
あるいは、
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
といったコード部分を公開してみてください。

ひょっとすると私が紹介したコードかもしれませんが
まったく思い出すことができません。

あるいは、シートのデザインも画像としてポストすべく
質問を立て直したほうがいいかもしれません。

補足

Hohopapaさん
お久ぶりです。

はい！当該コードはHohoPapaさんに教えていただいたコードです。
既に数えきれないくらいのBookに展開しており今回別工場の部署から要求がありこれを機に改善（欲張り？）質問になりました。
新質問の図の「グラフ」ボタンクリックで2行目の任意のデータ数で3σ線入りの当該列の値がグラフになります。（これもHohoPapa作）

図では切れていますが、別のボタンで指定した2つの項目（列）が最新（下から指定数）のデータ数でグラフ化され、グラフで異常が無いことを確認して書類を印刷します。

この時に指定した複数のセル内容をファイル名にｐｄｆで保存して、印刷までマクロボタンで実行できるようになっています。（これも恐らくHPさん作と記憶しています）

その他にも仕掛けがあり、それらの総集編が「ひな形」に仕込んであり、その多くはHohoPapaさん作のコードたちです。
人のふんどしで仕事しているような感じですが、実用上非常に役に立っており、だんだん関係部署から認知されてきています。

回答No.3

kkkkkm

> 当方にはこの2つのエラーの差が分りませんが何かヒントになりますでしょうか？

以下に該当しないか調べてください。

エクセル エラー #NAME? の意味と修正方法
https://www.tipsfound.com/excel/01503

エクセル エラー #VALUE! の意味と修正方法
https://www.tipsfound.com/excel/01507

お礼

両方の記事を読んでみましたが当方の症状とは違ているようです。

とにかくどこかのセルに何かを入力、もしくは削除するとスロットが回るように計算結果が表示されますので式の内容には無関係だとおもいます。

例えばA列に１を入力しそのまま横に引っ張ってコピーすると引っ張った列の式は正しく表示されます。

またメニュの再計算実行をクリックしても変化無くエラーのままです。

回答No.2

kkkkkm

No1の言葉の訂正です。

ユーザー適宜関数
↓
ユーザー定義関数

補足

今20個程度のBookのシートの1つにだけIFERRORを仕掛けてみました。

気付いたのですがエラー表示に2つあって
＃NAME？？と＃VALUE！の2種類が表示されます。

セルの内容はなったく同じsf・・関数で、仕込んであるＶＢＡも各Ｂｏｏｋにコピペで貼り付けたものです。

当方にはこの2つのエラーの差が分りませんが何かヒントになりますでしょうか？

回答No.1

kkkkkm

ユーザー適宜関数でのエラーはそのVBAが不明だとはっきりした原因は分かりませんが、エラーを非表示にすれば解決する問題でしたら
=IFERROR(sfAverage(D:D,50),"")
にして試してみてください。

補足

早々のご回答ありがとうございます。

早速試してみました。
ご回答のIFERRORを入れると正常に計算結果が出るのですが、別の列のどこかのセルに適当な数値を入れても改行と同時に再計算されますので、IFERRORの効果が現状ハッキリしない状況です。
ファイルを閉じて直ぐ開くとIFERROR列も数値入力後削除した列のどちらも正しく計算結果が表示されますので確証が無い状態です。

いくつかのBookの1列のみにIFERRORを追記してみましたので数日で結果が出ると思いますのでそれから改めて報告します。