教えて!gooにおける不適切な投稿への対応について

エクセル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
gooドクター

A 回答 (4件)

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



>「_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
'///
この回答への補足あり
    • good
    • 0
この回答へのお礼

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

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

こんにちは。



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

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

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

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

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

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

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


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

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

この同様の質問は、以前、私(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% …
「エクセル2013vbaで、見えない名前の」の回答画像1
この回答への補足あり
    • good
    • 0

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

gooドクター

このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング