質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.48%
VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

マクロ

定義された処理手続きに応じて、どのような一連の処理を行うのかを特定させるルールをマクロと呼びます。

Q&A

解決済

2回答

4801閲覧

マクロを使用して特定の値を含んだ名前の定義を一括削除したいです

shun56

総合スコア7

VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

マクロ

定義された処理手続きに応じて、どのような一連の処理を行うのかを特定させるルールをマクロと呼びます。

0グッド

1クリップ

投稿2018/09/14 14:15

編集2018/09/15 03:21

前提・実現したいこと

初めてマクロを書いているので理解不足な点が多々あることはご了承ください。
大量のexcelファイルを一つのフォルダに格納して、
該当の文字列を含んだ名前の定義(値、参照範囲どちらかに含んでいるもの)を削除するマクロを作成したいです。
その中で、非表示となっている名前の定義も削除対象としたいです。

発生している問題・エラーメッセージ

getName(nm.Name) = 1 _部分でrangeのglobalエラーが表示されてしまっています。

該当のソースコード

vba

1Public Sub DeleteNames() 2 '非表示となっている名前の定義を表示 3 DisplayNames 4 Dim nm As name 5 Dim i As Integer: i = 0 6 On Error Resume Next ' エラーを無視。(削除件数にカウントしてしまう) 7 '名前の定義の「値」、「参照範囲」に特定の文字列を含んでいた場合、該当する名前の定義を削除 8 9 For Each nm In ActiveWorkbook.Names 10 If InStr(nm.Value, "#REF") > 0 OR _ 11 getName(nm.Name) = 1 _ 12 Then 13 nm.Delete 14 i = i + 1 15 End If 16 Next nm 17 18End Sub 19 20'非表示の名前の定義を再表示する 21Public Function DisplayNames() 22 Dim name As Object 23 For Each name In Names 24 If name.Visible = False Then 25 name.Visible = True 26 End If 27 Next 28End Function 29 30'セル範囲の値を順番に取得し、CheckRangeValueを呼び出す 31Public Function getName(na As String) 32 Dim i As Long 33 Dim naStr As String 34 Dim result As Integer 35 result = 0 36 37 For i = 1 To Range(na).Count 38 naStr = Range(na).Item(i).Value 39 40 If CheckRangeValue(naStr) = 1 Then 41 result = 1 42 End If 43 Next i 44 getName = result 45 46End Function 47 48'名前の定義の「値」に特定の文字列を含んでいた場合1を返却 49Public Function CheckRangeValue(r As String) 50 Dim val 51 Dim result As Integer 52 If InStr(val.Value, "#REF") > 0 _ 53 Then 54 result = 1 55 End If 56 CheckRangeValue = result 57End Function

試したこと

処理の中にdebugを張ったりしてみましたが、名前の定義の名前部分が正しく取得できていることは確認できました。

補足情報(FW/ツールのバージョンなど)

特になし

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

退会済みユーザー

退会済みユーザー

2018/09/14 23:45 編集

まず人に見てもらうならインデントしましょう。次にDeleteNames()のIf InStr(nm.Value, "#REF") > 0 _ getName(nm.Name) = 1 _ Thenですが、Orが抜けているのが原因と思われます。さらにrestult = 1はスペルが違いますね。 CheckRangeValue(r As String)のrはFor Eachで使っていることからAs Rangeではないでしょうか。 この他にもエラーの原因が多数ありそうです。デバッグ中はOn Errorは切ったほうが良いと思います。最初のREF判定とgetNameをOrでつなぐのではなく、別のIfで括ったほうが理解しやすいかと思います。時間があればデバッグしてみます。
imihito

2018/09/14 23:25

ソースコードの直前の行に「```vba」、直後の行に「```」を追加して、ソースコードにシンタックスハイライトが適用されるようにしてください
guest

回答2

0

On Error Resume Next ' エラーを無視。(削除件数にカウントしてしまう)

先に書いたように、まずコレは消しましょう。無い状態で動くように書くべきです。

該当の文字列を含んだ名前の定義(値、参照範囲どちらかに含んでいるもの)

参照範囲の中のセルに少しでも#REFがあったら名前定義の削除対象にすると解釈しましたが合ってますか?

それと関数を細かく細分化されていますが、この程度の処理なら分ける必要は無いように思います。

これを踏まえてコードを整理してみましたが如何でしょうか。

VBA

1Option Explicit 2 3Public Sub DeleteNames() 4 Const DelStr = "#REF" '該当の文字列 5 6 Call DisplayNames '非表示となっている名前の定義を表示 7 8 Dim nm As Name 9 Dim i As Long: i = 0 10 'On Error Resume Next ' エラーを無視。(削除件数にカウントしてしまう) 11 '名前の定義の「値」、「参照範囲」に特定の文字列を含んでいた場合、該当する名前の定義を削除 12 13 For Each nm In ActiveWorkbook.Names 14 If InStr(nm.Value, DelStr) > 0 Then 15 'nm.Delete 16 Debug.Print nm.Name & "を削除しました。" 17 i = i + 1 18 Else 19 If getName(nm, DelStr) Then 20 'nm.Delete 21 Debug.Print nm.Name & "を削除しました。" 22 i = i + 1 23 End If 24 End If 25 Next nm 26End Sub 27 28'非表示の名前の定義を再表示する 29Public Function DisplayNames() 30 Dim Name As Object 31 For Each Name In Names 32 If Name.Visible = False Then 33 Name.Visible = True 34 End If 35 Next 36End Function 37 38'名前定義の範囲に特定文字列sが存在するか 39Public Function getName(nm As Name, s As String) As Boolean 40 Dim R As Range 41 For Each R In nm.RefersToRange 42 If InStr(R.Formula, s) > 0 Then 43 getName = True 44 ElseIf InStr(R.Value, s) > 0 Then 45 getName = True 46 End If 47 Next 48End Function

getName()とCheckRangeValue()については、細かく指摘するとキリがなさそうなので、完全に書き直しました。元のコードと比較して何が違うのか、ご自分で確かめて下さい。

投稿2018/09/15 04:46

退会済みユーザー

退会済みユーザー

総合スコア0

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

0

ベストアンサー

rangeのglobalエラー

については、「VBAのOrは短絡評価をしない」という仕様によるものです。

短絡評価というのは

「参照範囲がエラー」 Or 「参照先の値がエラー」

と書いたときに「参照範囲がエラー」だったら、「参照先の値がエラー」かどうかは評価しない(左が決まれば全体の結果は決まるため)、というものです。

VBAは常に両側を評価するため、「参照範囲がエラー」なのに参照先を確認しようとしてRangeを取得できない、ということになります。

対策として単純なのはIfを分けることです。

vba

1'なんらかの参照エラーがあるかどうかのフラグ 2Dim hasMissingRef As Boolean 3hasMissingRef = (InStr(nm.Value, "#REF") > 0) 4'2個目以降の条件はIf文で分岐 5If Not hasMissingRef Then hasMissingRef = (getName(nm.Name) = 1) 6 7If hasMissingRef Then 8 nm.Delete 9 i = i + 1 10End If

Select Caseを使うと短絡評価できるため、私は以下のように書くこともあります(Select Caseの使い方としてはやや邪道ですが)。

vba

1Select Case True 2 Case InStr(nm.Value, "#REF") > 0, _ 3 getName(nm.Name) = 1 4 nm.Delete 5 i = i + 1 6End Select

その他気になった点

  • DisplayNamesの対象が明確ではないため、対象のブックを指定できるようにしましょう
  • DisplayNamesは結果を返さないため、Functionの意味がありません
  • 各種Functionは返り値の型を明示しましょう
  • OK・NGの2択の判定であればBoolean型を使うのが適当です
  • CheckRangeValueはいろいろ問題外

またgetNameCheckRangeValueで判定したいものがが「参照エラーとなっているセル」の場合、natoさんのコードでも漏れる可能性があります。

これらを踏まえて私なりに書き直すと以下のような感じでしょうか。

vba

1Public Sub DeleteNames() 2 Dim targetBook As Excel.Workbook 3 Set targetBook = ActiveWorkbook 4 5 '非表示となっている名前の定義を表示 6 DisplayNames targetBook 7 8 Dim delCnt As Long 'i から名前を変更 9 10 '名前の定義の「値」、「参照範囲」に特定の文字列を含んでいた場合、該当する名前の定義を削除 11 Dim nm As Name 12 For Each nm In targetBook.Names 13 14 '`[Name].RefersToRange`で参照先のセル範囲は取得できる 15 Select Case True 16 Case InStr(nm.Value, "#REF") > 0, _ 17 ContainsErrValue(nm.RefersToRange, xlErrRef) 18 19 nm.Delete 20 delCnt = delCnt + 1 21 22 End Select 23 24 Next nm 25 26 Call VBA.MsgBox(delCnt & "個の名前を削除しました") 27 28End Sub 29 30'非表示の名前の定義を再表示する 31Public Sub DisplayNames(tgtBook As Excel.Workbook) 32 Dim nm As Excel.Name 33 For Each nm In tgtBook.Names 34 If Not nm.Visible Then 35 nm.Visible = True 36 End If 37 Next nm 38End Sub 39 40'セルの値にエラーが含まれているかどうか 41'chkRng:対象のセル範囲 42'errType:エラーの種類(Excel.XlCVErrorはセルのエラーの種類) 43Public Function ContainsErrValue(chkRng As Excel.Range, errType As Excel.XlCVError) As Boolean 44 Dim r As Excel.Range 45 For Each r In chkRng 46 47 If VBA.IsError(r.Value()) Then 48 49 'errTypeのエラーかどうか 50 'エラー値同士の比較じゃないと`型が一致しません。`となる(エラー値の仕様) 51 If r.Value() = CVErr(errType) Then 52 Let ContainsErrValue = True 53 Exit Function 54 End If 55 56 End If 57 58 Next r 59End Function

投稿2018/09/15 05:26

編集2018/09/15 14:36
imihito

総合スコア2166

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.48%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問