エクセル2013vbaで、見えない名前の定義の「_xlfn.IFERROR」を削除したい

エクセル2013で、IFERROR関数を使うようにしたら、VBAで特定シートの名前の定義を削除するコードを実行すると、途中でエラーがでるようになってしまいました。
そこで、普段表示されない名前の定義を表示させるコードを実行すると、「_xlfn.IFERROR」という名前定義が値や参照範囲が「#NAME?」の状態でありました。

これを削除すると、正常動作するようなのですが、ファイルを開く度に作成されるようです。
そこで、最初に「_xlfn.IFERROR」を名前の定義から削除してエラーがでないようにしたいのですが、削除がうまくできません。
または、「#REF!」や「#NAME?」などのエラーを名前の定義から削除するようにしたいと思っています。

どうすればよいでしょうか？

うまくいっていない該当部分のコードです。

Sub sample()

'見えない名前定義があれば表示させる
Dim wName As Object
Dim wCnt As Long

For Each wName In Names
If wName.Visible = False Then
wName.Visible = True
wCnt = wCnt + 1
End If
Next


'#NAME?を削除
Dim n As Name
For Each n In ActiveWorkbook.Names
On Error Resume Next
If n.RefersToRange.Parent.Name = "#NAME?" Then
n.Delete
End If
If Err.Number > 0 Then
n.Delete
End If
On Error GoTo 0
Next n
End Sub

質問者からの補足コメント

• 

  手動ならいけますが、実際に使う人があまり詳しくないので簡単にできるようにしてあげたいと思っています。
  Sub sa()
  Dim nm As Object
  Dim mystr As String
  For Each nm In ActiveWorkbook.Names
  If InStr(1, nm.RefersTo, "#NAME?") Then
  nm.Delete
  End If
  Next
  End Sub
  だと、nm.Deleteでエラー1004で入力した名前は正しくありません、という内容。
  試しに名前を「_xlfn.IFERROR」から「xlfn.IFERROR」に手動で変更して上記マクロを動かすと削除してくれますが、_があるとだめ。

  No.1の回答に寄せられた補足コメントです。 補足日時：2015/07/09 23:00

• 補足の字数制限があるようなので、さらに続きです。
  「_xlfn.IFERROR」という名前だとどうして前回補足のコードが動作しないのでしょうか？
  「_xlfn.IFERROR」はエクセルが自動で作成する名前の定義なのでどうしようもないし。
  「_xlfn.IFERROR」の名前を消すことができる方法があるでしょうか？

    補足日時：2015/07/09 23:05

• 拡張子はxlsmです。2013なので最新です。
  IFERROR関数も計算はされています。
  ただし、名前の定義に「_xlfn.IFERROR」というものをエクセルが自動で作るようです。削除しても開くたびに再作成されます。この名前の定義は普通は見えませんが、非表示の名前の定義を表示させるマクロを実行すると存在を確認できます。
  これが、邪魔をして他のマクロの動作を妨げているようです。

  No.2の回答に寄せられた補足コメントです。 補足日時：2015/07/10 12:47

• どうもありがとうございます。
  こんなに大変なコードになってしまうのですね。それでも最終的には手動なんですね。了解しました。
  
  少し改造して、Sub Add_Button()は削除してPrivate Sub ErrNames_DeleteButton()のコード部分を少しだけ改造、それ以下のコードはそのまま。
  改造の部分をぬきだすと、
  
  Sub ErrNames_DeleteButton改造()
  ・・・
  MsgBox msg & vbCrLf & "しかし、名前：" & msg2 & "は安全ではありません。", vbExclamation
  Else
  Call extraction　’←現在のコードを呼び出してつなげるように追加
  ’ MsgBox msg←コメントアウトに変更
  
  
  ・・・・・・字数不足で続きは次回補足で。

  No.3の回答に寄せられた補足コメントです。 補足日時：2015/07/12 11:40

• さらに続き
  ・・・
  
  If n.Name Like "_xlfn*" Then
  MsgBox "名前「_xlfn.IFERROR」を選択して削除して下さい。", vbExclamation　’←追加
  Application.SendKeys "%D{ENTER}", True
  
  ・・・
  
  ' MsgBox "再度・ボタンを押して試してください。" & vbCrLf _
  ' & "一旦保存し、再度開くと、もしかしたら、復活しているかもしれません。", vbInformation
  ’↑コメントアウト
  Call extraction　’現在のコードを呼び出してつなげるように追加
  
  End Sub
  
  前の回答でvbaではできないとのことだったので、IFERROR関数を使うのやめようかな？とも考えましたが、今回の回答でこのコードを使って解決することにしました。

    補足日時：2015/07/12 11:42

• さらに続き
  ・・・
  
  If n.Name Like "_xlfn*" Then
  MsgBox "名前「_xlfn.IFERROR」を選択して削除して下さい。", vbExclamation　’←追加
  Application.SendKeys "%D{ENTER}", True
  
  ・・・
  
  ' MsgBox "再度・ボタンを押して試してください。" & vbCrLf _
  ' & "一旦保存し、再度開くと、もしかしたら、復活しているかもしれません。", vbInformation
  ’↑コメントアウト
  Call extraction　’現在のコードを呼び出してつなげるように追加
  
  End Sub
  
  前の回答でvbaではできないとのことだったので、IFERROR関数を使うのやめようかな？とも考えましたが、今回の回答でこのコードを使って解決することにしました。

    補足日時：2015/07/12 11:44

No.3 ベストアンサー 回答者： WindFaller 回答日時： 2015/07/11 15:41

>手動ならいけますが、実際に使う人があまり詳しくないので簡単にできるようにしてあげたい

>「_xlfn.IFERROR」から「xlfn.IFERROR」に手動で変更して上記マクロを動かすと削除してくれますが、

これだけでは、再起動して、マクロで、Visible=Trueで、復活してきませんでしたか？

繰り返しますが、
>「_xlfn.IFERROR」の名前を消すことができる方法があるでしょうか？

※「名前の登録（見えないオブジェクト)」問題は、マクロでなければ解決しないことと、手動でなければ解決しないことと、二種類あるということです。
（これは、Excel 97 時代からの言い伝えですが……)

ということは、実際は、ワークシートの仕組みとVBAというものは、別もので、インターフェースがあって始めて、同じものではないということです。しかし、これを「自動で削除」というのは、考えられる方法はあるものの、いずれにしても、通常、私たちが使うようなVBAではなく、かなり特殊なマクロになってしまうことはいたしかたがありません。なるべく、このマクロの目的とする考え方を理解した上で、手動で行うことをお勧めします。

'----
以下のマクロは、見えない名前の登録の削除を確実にさせるものではありません。また、VBEditor 画面は、閉じて行ってください。最初のAdd_Button で、ワークシートのボタンが作られ、そこで、マクロを実行させます。問題のファイルは実行した後、保存し再度開いて、再度、マクロを実行ってください。

実行させた後、一度、ファイルを保存して閉じます。再度、ファイルを開けて試します。不要になったボタンは削除して構いません。

'//
Sub Add_Button()
'ボタン登録
Dim x1 As Double, y1 As Double, x2 As Double, y2 As Double
　With ActiveCell
　　If .Value <> "" Then
　　　MsgBox "データのない所に置いて実行してください。", vbExclamation
　　　Exit Sub
　　End If
　　x1 = .Left
　　y1 = .Top
　　x2 = .Offset(, 2).Left
　　y2 = .Offset(1).Top
　End With
　With ActiveSheet.Buttons.Add(x1, y1, x2 - x1, y2 - y1)
　　.OnAction = "'" & ThisWorkbook.Name & "'!ErrNames_DeleteButton"
　　.Text = "不良名前削除"
　End With
End Sub
Private Sub ErrNames_DeleteButton()
　Dim cnt As Long
　Dim c As Range
　Dim n As Name
　Dim msg As String
　Dim msg2 As String
　If ErrNameCheck() = 0 Then
　　msg = "現在は、エラーの[名前定義]は見つかりません。"
　End If
　If msg <> "" Then
　　msg2 = PossibleErrNameCheck()
　　Call PossibleErrNameCheck2
　　If msg2 <> "" Then
　　　MsgBox msg & vbCrLf & "しかし、名前：" & msg2 & "は安全ではありません。", vbExclamation
　　Else
　　　MsgBox msg
　　End If
　　Exit Sub
　End If
　On Error Resume Next
　With ActiveSheet
　　For Each c In .UsedRange.SpecialCells(xlCellTypeFormulas, xlErrors)
　　　Debug.Print c.Address; Tab
　　　c.Delete
　　Next c
　End With
　With ActiveWorkbook
　　For Each n In .Names
　　　If IsError(Evaluate(n.Value)) Then
　　　　n.Delete
　　　End If
　　Next
　End With
　For Each n In ActiveWorkbook.Names
　　If n.Name Like "_xlfn*" Then
　　　Application.SendKeys "%D{ENTER}", True
　　　Application.Dialogs(xlDialogNameManager).Show
　　　Exit For
　　End If
　Next n
　For Each n In ActiveWorkbook.Names
　　If n.Visible = False Then
　　　n.Visible = True
　　End If
　Next n
　MsgBox "再度・ボタンを押して試してください。" & vbCrLf _
　& "一旦保存し、再度開くと、もしかしたら、復活しているかもしれません。", vbInformation
　
End Sub
Private Function ErrNameCheck() As Long
Dim n As Name
Dim cnt As Long
For Each n In ActiveWorkbook.Names
　If n.Visible = False Then
　　n.Visible = True
　End If
　If n.Name Like "_xlfn*" Then
　　cnt = cnt + 1
　End If
Next n
ErrNameCheck = cnt
End Function
Private Function PossibleErrNameCheck() As String
Dim n As Name
Dim r As Range
Dim r2 As Range
Dim buf As String
For Each n In ActiveWorkbook.Names
　　　On Error Resume Next
　　　 If n.RefersToRange.Parent Is ActiveSheet Then
　　　 Set r = n.RefersToRange.Precedents
　　　 Set r2 = r.SpecialCells(xlCellTypeFormulas, xlErrors)
　　　 On Error GoTo 0
　　　 End If
　　　 If Not r2 Is Nothing Then
　　　　 buf = buf & ", " & n.Name & ":" & r.Address
　　　 End If
　　　 Set r2 = Nothing
Next n
PossibleErrNameCheck = Mid(buf, 2)
End Function
Private Function PossibleErrNameCheck2() As String
Dim n As Name
Dim f As Variant
Dim Funcs As Variant
Dim buf As String
Const NewFUNCS = "AVERAGEIF,AVERAGEIFS,COUNTIFS,IFERROR,SUMIFS"
Funcs = Split(NewFUNCS, ",")
For Each n In ActiveWorkbook.Names
　For Each f In Funcs
　 If InStr(1, n.RefersToRange(1, 1).Formula, f, 1) > 1 Then
　　　buf = buf & vbCrLf & n & ": " & f
　　　n.RefersToRange(1, 1).Formula = "'" & n.RefersToRange(1, 1).Formula
　 End If
　Next f
Next n
　PossibleErrNameCheck2 = Mid(buf, 2)
End Function
'///

この回答へのお礼

長く難しいコードどうもありがとうございました。

お礼日時：2015/07/12 15:28

No.4 回答者： WindFaller 回答日時： 2015/07/12 15:13

こんにちは。

最終的には、IFERROR関数が、問題を引き起こしているようです。Excel 2007以降、同じ問題がずっと続いているようで、バグの類であると私は考えています。この関数を使った[名前の登録]にエラーが発生すると、見えないオブジェクトの_xlfn.IFERROR が発生し、表面化するということで、関数を使う限りは、#3のマクロで、一応は消えている状態になっているということです。エラー値が生じていなければ、問題はありません。

互換性チェックをして、バージョンダウンした数式を使うなら、せっかくの上位バージョンを使う意味もなくなってしまいます。

もし、このエラーが、どんどん増えたとしたら、メモリ枠を使いきってしまいます。Excelのメモリというのは、それぞれの機能で割り振りされていますから、全体のメモリがあっても、個々の書式にしても、マクロにしても、それほど大きくはありません。したがって、トラブルの発生の元にはなりますでしょう。

あまり、スッキリとした解決方法ではなかったのですが、私自身、いずれ、再び、この問題に直面する時が来るかもしれません。

この回答へのお礼

やはりバグなんですかね？
一応は、これで解決ということとして今回は終わります。
当初は、この名前を削除すれば解決と安易に考えていましたが意外と難しい問題だったようでお手数をおかけしました。
今回はどうもありがとうございました。

お礼日時：2015/07/12 15:27

No.2 回答者： y504 回答日時： 2015/07/10 09:37

xlfnで検索すると以下のサイトが見つかります。

https://support.office.com/ja-jp/article/%e5%95% …

これによると現在実行中の Excel バージョンではサポートされていない関数が含まれています
とありますので拡張子がxlsで作成されているファイルなのではないでしょうか？

No.1 回答者： WindFaller 回答日時： 2015/07/09 19:19

この同様の質問は、以前、私(WindFaller) が答えています。

http://oshiete.goo.ne.jp/qa/8872886.html

思ってもみない問題ですが、もう誰が言ったのか、私が言ったのか、すでに私の教訓のひとつですが、

※「名前の登録（見えないオブジェクト)」問題は、マクロでなければ解決しないことと、手動でなければ解決しないことと、二種類あるということです。

今回は、その別編の手動でなければ解決しないこと、のようです。（マクロでの処理は考えていません。)

私の手順を試してみて、ダメだったら、また返事ください。なお、私のExcel 2010で行っております。
もし、(1) の場所がすぐに特定できるなら、(2)に飛んで構いません。

(1)　ファイル・タブから、[共有の準備]→[互換性のチェツク]を起動してください。
「機能の大幅の損失
「このブック内の一部の関数は、Excel 2007 より前のバージョンでは使用できません。……」
と出てきますので、「検索」をクリックして、その場所を特定してください。

---------------------------------------------------
(2) 　この[名前の登録]の「アドレス」と[名前の登録]の「名前」を新たに設置するなら、数式を書き換えなくてはなりません。

例：
=IFERROR(SUM(C1:C50),"Error")
　　　　↓
=IF(ISERROR(SUM(C1:C50)),"Error",SUM(C1:C50))
言い換えると、#NAME? のエラーを取り去ることです。

(3) 次に、手動ですが、数式グループ－[名前の管理]－
_xlfn.IFERROR　　#NAME?
を選択して削除－閉じる

一度、以下の検索用のマクロを試してみました。
Sub Find_InvisibleNames()
出てきません。（念のための確認で、ここでの必要性はあまり高くありません)

保存して、再起動し、もう一度確認しました。
私が使った検査用のマクロ
'//
Sub Find_InvisibleNames()
Dim n As Name
For Each n In ActiveWorkbook.Names
　If n.Visible = False Then
　　n.Visible = True
　End If
　If n.Name Like "_xlfn*" Then
　　MsgBox n.Name, vbExclamation
　End If
Next
End Sub
'///

メッセージが出てこなければ、この問題は解決になります。
---------------------------------

この問題を発生するのは、以下のような関数があります。
AVERAGEIF、AVERAGEIFS、CUBEKPIMEMBER、CUBEMEMBER、CUBEMEMBERPROPERTY、CUBERANKEDMEMBER、CUBESET、CUBESETCOUNT、CUBEVALUE、COUNTIFS、IFERROR、SUMIFS
などがあるそうです。

Excelの作業自体が、マクロだけで処理できるなら、[名前の登録]とは、共存させないほうがよいようです。
参考にした所：
https://support.office.com/ja-jp/article/%e5%95% …