Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : Microsoft 365)
共有ブック(レガシ)を開くと、のユーザー定義関数がエラーになる
投稿日時: 22/07/28 15:43:56
投稿者: harahara

ブックの共有(レガシ)設定にしているブックを開くと、
ユーザー定義関数を設定してるセルが「#NAME?」になります。
 
この状態で新たに、同じユーザー定義関数を設定すると、
「#NAME?」が解除されます。
 
1週間前から上記のような現象が起きていました。
 
ブックの共有(レガシ)を解除し、該当ブックを開きなおせば、
「#NAME?」にならず、定義した関数の計算結果が表示されます。
 
ただ、便宜上ブックの共有(レガシ)設定のまま、
「NAME?」にならないようにしたいです。
 
アドバイスをお願いいたします。
 
ソースコードは以下の通りです。
標準モジュールに記載しています。
 

Function NonYellowSum(R1 As Range)
    Dim r As Range
    NonYellowSum = 0
    
    For Each r In R1
        If r.Interior.Color <> vbYellow Then
            NonYellowSum = NonYellowSum + r.Value
        End If
    Next r
End Function

 
■今の状況の流れ
 該当ブックを開く(共有レガシ)
 ↓
 ユーザー定義関数を入力しているセルが、「#NAME?」になっている。
 ↓
 空いているセルに「=NonYellowSum(任意のセル)」を設定すると、
 「#NAME?」だった部分が、なぜか定義した関数の計算結果になる。
 ↓
 保存し、再度該当ブックを開くと、再び「#NAME」になる。

回答
投稿日時: 22/07/28 15:51:22
投稿者: taitani
投稿者のウェブサイトに移動

ファイルの設定で、自動計算になっているか、以下どちらかのコマンドが入っていませんか?
Application.Calculation = xlCalculationManual or xlCalculationSemiautomatic
 
name になっているとき、VBE のイミディエイト ウィンドウに
Application.Calculation = xlCalculationAutomatic
を入力し、エンターを押下しても、解消されないでしょうか。

回答
投稿日時: 22/07/28 15:51:54
投稿者: taitani
投稿者のウェブサイトに移動

誤:ファイルの設定で、自動計算
正:ファイルの設定で、手動計算
です。

投稿日時: 22/07/28 16:15:31
投稿者: harahara

taitani様
 
ご返信ありがとうございます。
 
ファイルの設定 数式は自動計算になっていました。
 
また、イミディエイトウィンドウに
 
Application.Calculation = xlCalculationAutomatic
 
を入力し、エンターを押下しても、nameエラーは解消されませんでした・・・

回答
投稿日時: 22/07/28 16:49:54
投稿者: taitani
投稿者のウェブサイトに移動

あまりお勧めはできませんが、"application.volatile" を追加してみたら更新されますでしょうか。
https://www.moug.net/tech/exvba/0100006.html

回答
投稿日時: 22/07/28 16:54:22
投稿者: taitani
投稿者のウェブサイトに移動

Function NonYellowSum(R1 As Range)
    Application.Volatile
    Dim r As Range
    NonYellowSum = 0
    
    For Each r In R1
        If r.Interior.Color <> vbYellow Then
            NonYellowSum = NonYellowSum + r.Value
        End If
    Next r
End Function


 
計算式が多いと、再計算がやたら重くなってしまいますが。。。
ちなみに、私の環境では "#Name" の再現できませんでした。
※ 起動したとき、ちらっと"#Name"が表示されますが、すぐに 黄色以外の合計が表示されます。
 

投稿日時: 22/07/29 10:45:36
投稿者: harahara

taitani様
 
”Application.Volatile”をソースコードに追記しましたが、状況は変わりませんでした・・・
 
また、数式タブから「再計算実行」しても、#NAMEのままになります。

回答
投稿日時: 22/07/29 10:50:41
投稿者: taitani
投稿者のウェブサイトに移動

そうですか。。。
例えば、#Name のセルをダブルクリック (編集) してみるといかがでしょうか。
また、Excelの左下のステータスバーのコメントは、どうなっていますか?
通常は、「準備完了」の認識です。
 
変わらないと思いますが、「F9」で再計算を押してみる。
あと、VBE を起動したとき、一時停止のアイコンがグレーアウトしているとか。

回答
投稿日時: 22/07/29 10:53:18
投稿者: taitani
投稿者のウェブサイトに移動

もしかして、その自作関数が登録されているブックが別ブックとかという落ちだったり?

回答
投稿日時: 22/07/29 10:54:39
投稿者: taitani
投稿者のウェブサイトに移動

https://support.microsoft.com/ja-jp/office/excel-%E3%81%A7%E3%82%AB%E3%82%B9%E3%82%BF%E3%83%A0%E9%96%A2%E6%95%B0%E3%82%92%E4%BD%9C%E6%88%90%E3%81%99%E3%82%8B-2f06c10b-3622-40d6-a1b2-b6748ae8231f
 
/// 引用開始 ///
カスタム関数を使用するには、作成した関数が入っているモジュールを含むブックを開く必要があります。 そのブックが開いていない場合、その関数を使用しようとすると、#NAME? エラーが発生します。 別のブックで関数を参照する場合は、関数名の前に、その関数が属するブックの名前を付ける必要があります。 たとえば、Personal.xlsb というブックに DISCOUNT という関数を作成し、その関数を別のブックから呼び出す場合は、単に =discount() と入力するのではなく、=personal.xlsb!discount() と入力する必要があります。
 
/// 引用終了 ///

回答
投稿日時: 22/07/29 12:02:14
投稿者: Suzu

harahara さんの引用:
ブックの共有(レガシ)設定にしているブックを開くと、
ユーザー定義関数を設定してるセルが「#NAME?」になります。
 
この状態で新たに、同じユーザー定義関数を設定すると、
「#NAME?」が解除されます。
 
1週間前から上記のような現象が起きていました。
 
ブックの共有(レガシ)を解除し、該当ブックを開きなおせば、
「#NAME?」にならず、定義した関数の計算結果が表示されます。

 
1週間前 との事ですので
 
オートメーションエラーを解消する方法
https://www.moug.net/faq/viewtopic.php?t=81635
と 関連がかるかも知れませんね。
 
オートメーションエラー となっているが、ユーザー定義関数 でワークシート上にて使用している為
オートメーションエラー とはならず #NAME となってしまっている のではないでしょうか。
 
 
1.新規ブックにて、VBEから 簡単な関数を作成し VBEから実行できるか。
2.実行できるなら、ワークシート上から その関数を呼び出し 実行できるか
3.ワークシート上から実行できるなら、保存し開きなおし、どうなるか
4.ブック共有するとどうか
 
の様な順番で原因を探ってはどうでしょう。
 
 
別案
Excel 4.0 マクロを使い、セルの色を取得する方法と SUMIFを組み合わせ目的の値を得る方法
 1)セルの背景色を返す関数を マクロ関数 =GET.CELL(63,〜) を 名前定義 により 名前として登録
 2) 作業列に名前定義を行った 名前を指定し、セルの背景色を返す
 3)2)で取得した値を条件とし SUMIF関数と組み合わせ 目的の値を得る

投稿日時: 22/07/29 14:04:23
投稿者: harahara

taitani様
 
ダブルクリック (編集)すると数式を編集できる状態になり、
その状態でエンターを押すと#NAMEがなくなります。
 
また、左下のステータスバーのコメントは、「準備中」になっています。
 
F9の再計算でも変わらず、VBEの一時停止アイコンが青になっていました。
 
ちなみに、自作関数は同じブックに登録していました汗
 
 
そして、このブックはコピペして使いまわしていますが、
ブックによっては、起動時に#NAMEが表示されず、
期待した計算結果が表示されます。
 
先ほど気づきました。ますます謎です・・・

投稿日時: 22/07/29 14:07:42
投稿者: harahara

Suzu様
 
コメントありがとうございます。
 
確かに、オートメーションエラーの可能性もありそうですね。
リンク先もありがとうございます。
 
順番に試してみて、後ほど結果ご報告します。

回答
投稿日時: 22/07/29 15:46:14
投稿者: taitani
投稿者のウェブサイトに移動

うーん、準備中って見たことないですね。。。
切り分けとして、
 
1.新規で Excel を起動し、その Excel だけの起動にする。
2.同じ VBA を登録して、シートに計算式を入れる。
3.マクロが有効な拡張子で名前を付けて保存して閉じる。
4.3.で保存した Excel のみ起動する。
 
おそらく、上記状態だと正常に計算してくれるのではないでしょうか。
 
「準備中」というのは、ネットワーク上の別の Excel から自作計算式を読み込もうとしている状態かも。

投稿日時: 22/07/29 16:53:39
投稿者: harahara

taitani様
 
申し訳ございません。準備中ではなく、「準備完了」の間違いでした。
失礼しました・・・
 
ご提案いただいた方法も試してみます。

投稿日時: 22/07/29 17:26:45
投稿者: harahara

Suzu様
 
取り急ぎ、下記試しました。
 
1.新規ブックにて、VBEから 簡単な関数を作成し VBEから実行できるか。
 →実行できました。
 
2.実行できるなら、ワークシート上から その関数を呼び出し 実行できるか
 →実行できました。
 
3.ワークシート上から実行できるなら、保存し開きなおし、どうなるか
 →実行できました。
 
4.ブック共有するとどうか
 →「プロジェクトがロックされています。プロジェクトを表示できません。」
  の警告のあと、オートメーションエラーになりました。となります。
 
  シート上の自作関数を入力したセルも、#NAMEとなります。
 
ブックの共有が原因のようですが、共有で使用したかったので・・・
ご提案いただいた、別案も試してみます。

回答
投稿日時: 22/08/01 09:24:42
投稿者: Suzu

harahara さんの引用:
4.ブック共有するとどうか
 →「プロジェクトがロックされています。プロジェクトを表示できません。」
  の警告のあと、オートメーションエラーになりました。となります。
 
  シート上の自作関数を入力したセルも、#NAMEとなります。
 
ブックの共有が原因のようですが、共有で使用したかったので・・・

 
そのブックを使用する他のマシンのユーザーも同様でしょうか?
 
 
あとは、トラストセンター の設定を変えてみてくらいでしょうか。
・信頼できる場所
・信頼済みドキュメント
・ActiveX
・マクロの設定
・保護ビュー
 ただし、上記は、その操作を行ったPC(アカウント)に対しての操作ですから
 
そのファイルを LAN上で共有しているのであれば、
 
1週前 突然 との事ですので、WindowsUpDateの可能性が高いと思います。
ロールバックで改善されるかもしれませんが、
安易にロールバックを行うのは セキュリティー上はばかれます。
 
そもそも、
・「色」を条件に処理を行い、   → 別カラムとして条件カラムを持たせる
・それをユーザー関数として
・共有している          → 各ユーザーのアドインや、Personal.xlsbに組み込む
それらを改める方がベターと思います。

投稿日時: 22/08/01 10:48:42
投稿者: harahara

taitani さんの引用:
うーん、準備中って見たことないですね。。。
切り分けとして、
 
1.新規で Excel を起動し、その Excel だけの起動にする。
2.同じ VBA を登録して、シートに計算式を入れる。
3.マクロが有効な拡張子で名前を付けて保存して閉じる。
4.3.で保存した Excel のみ起動する。
 
おそらく、上記状態だと正常に計算してくれるのではないでしょうか。
 

上記方法で計算できましたが、共有(レガシ)設定すると、#NAMEとなりました。
共有設定が原因のようです・・・

投稿日時: 22/08/01 10:51:28
投稿者: harahara

そのブックを使用する他のマシンのユーザーも同様でしょうか?
 
あとは、トラストセンター の設定を変えてみてくらいでしょうか。
・信頼できる場所
・信頼済みドキュメント
・ActiveX
・マクロの設定
・保護ビュー
 ただし、上記は、その操作を行ったPC(アカウント)に対しての操作ですから
 
そのファイルを LAN上で共有しているのであれば、
 
1週前 突然 との事ですので、WindowsUpDateの可能性が高いと思います。
ロールバックで改善されるかもしれませんが、
安易にロールバックを行うのは セキュリティー上はばかれます。
 
そもそも、
・「色」を条件に処理を行い、   → 別カラムとして条件カラムを持たせる
・それをユーザー関数として
・共有している          → 各ユーザーのアドインや、Personal.xlsbに組み込む
それらを改める方がベターと思います。[/quote]
 
設定変更し、テストしました。
 
そのブックを使用する他のマシンのユーザーも同様でしょうか?
 →はい。他のマシンも同様でした。
  
あとは、トラストセンター の設定を変えてみてくらいでしょうか。
・信頼できる場所
 →該当フォルダ設定しましたが、状況変わらず
 
・信頼済みドキュメント
 →許可するに設定済みでしたが、状況変わらず
 
・ActiveX
 →有効にする で設定済みでしたが、状況変わらず 
 
・マクロの設定
 →VBAマクロを有効にする でも状況変わらず
 
・保護ビュー
 →全て無効にしても、状況変わらず
 
ロールバックはセキュリティ上、避けたいところでした。
 
また、ご指摘のように運用法を変更するか、
ご提案頂いたマクロ4.0を使った方法だと実現できるかもしれません。
 
もう少し自分でもテスト、調査してみます。

投稿日時: 22/08/06 11:34:36
投稿者: harahara

ご連絡遅れ、申し訳ございません。
 
taitani様、Suzu様からご提案頂いた方法と異なりますが・・・・
 
Workbook_Openイベントで、
使わないセル.Formula = "=NonYellowSum()"
 
としたところ、ブックを開いてからすぐ使える状態になりました。
複数のアドバイス、ご提案頂きありがとうございました。