Geminiと100本ノック 18本目：名前定義の削除

公開日：2025-05-11　最終更新日：2025-05-11

Geminiと100本ノック 18本目：名前定義の削除

AIのGeminiがVBA100本ノックに挑戦！

Googleが開発した生成AIモデルのGeminiにVBA100本ノックをやってもらいました。
AIがVBAをどこまで扱えるかの実証実験の18本目です。

2025年3月からGemsが無料で使えるようになりました。
そこで、独自に作成したGem（名前は「AI神髄VBA」改良を続けている）を使った回答も掲載しています。

VBA100本ノックの出題と解答

VBA100本ノック 18本目：名前定義の削除

・出題・頂いた回答・解説・補足・サイト内関連ページ

Geminiへの出題とGeminiの回答

Geminiの回答 Geminiへの出題

VBA問題
不明な名前定義が沢山あるので、以下の処理をしてください。
・非表示の名前定義は表示にする
・参照範囲に「#REF!」が含まれる場合は削除しイミディエイトに「名前」と「参照範囲」を出力
・最後に非表示件数と削除件数をメッセージボックスに表示
※ブック指定は任意

はい、承知いたしました。
Excel VBAで、アクティブなブックの名前定義を処理するコードを作成します。

以下のVBAコードを標準モジュールに貼り付けて実行してください。

VBA


        
          
            Sub CleanUpNames()


　　Dim nm As Name

　　Dim hiddenCount As Long

　　Dim deletedCount As Long

　　Dim i As Long


　　' カウンターを初期化

　　hiddenCount = 0

　　deletedCount = 0


　　' 名前定義を後から前にループ（削除してもインデックスがずれないように）

　　For i = ActiveWorkbook.Names.Count To 1 Step -1

　　　　Set nm = ActiveWorkbook.Names(i)


　　　　' 非表示の名前定義を表示にする

　　　　If nm.Visible = False Then

　　　　　　nm.Visible = True

　　　　　　hiddenCount = hiddenCount + 1

　　　　End If


　　　　' 参照範囲に #REF! が含まれるかチェック

　　　　If InStr(nm.RefersTo, "#REF!") > 0 Then

　　　　　　' イミディエイトウィンドウに出力

　　　　　　Debug.Print "削除対象の名前: " & nm.Name & ", 参照範囲: " & nm.RefersTo


　　　　　　' 名前定義を削除

　　　　　　nm.Delete

　　　　　　deletedCount = deletedCount + 1

　　　　End If

　　Next i


　　' 結果をメッセージボックスで表示

　　MsgBox "処理が完了しました。" & vbCrLf & _

　　　　　 "非表示から表示にした名前の数: " & hiddenCount & vbCrLf & _

　　　　　 "削除した名前の数: " & deletedCount, vbInformation


End Sub

コードの説明:

hiddenCount と deletedCount というLong型の変数を宣言し、0で初期化します。これらは処理後に非表示から表示にした名前の数と削除した名前の数をカウントするために使用します。
For i = ActiveWorkbook.Names.Count To 1 Step -1 で、アクティブなブックの名前定義コレクションを、最後の要素から最初の要素に向かってループします。後ろから処理することで、名前定義を削除した際にコレクションのインデックスがずれることによる処理漏れを防ぎます。
ループ内で Set nm = ActiveWorkbook.Names(i) として、現在の名前定義を取得します。
If nm.Visible = False Then で、名前定義が非表示であるかを確認します。もし非表示であれば、nm.Visible = True として表示にし、hiddenCount を1増やします。
If InStr(nm.RefersTo, "#REF!") > 0 Then で、名前定義の参照範囲 (nm.RefersTo) に文字列 "#REF!" が含まれているかを確認します。
もし "#REF!" が含まれていれば、Debug.Print を使用してイミディエイトウィンドウにその名前 (nm.Name) と参照範囲 (nm.RefersTo) を出力します。
nm.Delete でその名前定義を削除し、deletedCount を1増やします。
ループが終了したら、MsgBox を使用して、非表示から表示にした名前の数と削除した名前の数を表示します。

使用方法:

Excelを開き、この処理を行いたいブックをアクティブにします。
Alt + F11 キーを押してVBAエディターを開きます。
メニューバーの「挿入」から「標準モジュール」を選択します。
表示されたコードウィンドウに上記のVBAコードを貼り付けます。
VBAエディターのツールバーにある実行ボタン（緑色の再生マーク）をクリックするか、F5キーを押してコードを実行します。

処理が実行され、完了後に結果が表示されます。削除された名前とその参照範囲は、VBAエディターの「表示」メニューから「イミディエイトウィンドウ」を選択すると確認できます。

これはまあ、これで良しとしておきましょう。

AI神髄VBAへの出題とAI神髄VBAの回答

AI神髄VBAとは、GeminiのGems機能を使って作ったVBAに特化したカスタムAIです。
前回の10本目で使用したGemを今回は少し改良しました。

AI神髄VBAの回答 AI神髄VBAへの出題

VBA


        
          
            Sub CleanUpNames()

　　' 概要: ブック内の名前定義を整理します。

　　'　　　 非表示の名前定義を表示にし、参照範囲に「#REF!」が含まれる名前定義を削除します。

　　'　　　 処理後、非表示にした件数と削除した件数をメッセージボックスで表示します。


　　Dim nameObj As Name

　　Dim namesToDelete As Collection

　　Dim unhiddenCount As Long

　　Dim deletedCount As Long


　　' 削除対象の名前を格納するCollectionを作成

　　Set namesToDelete = New Collection

　　unhiddenCount = 0

　　deletedCount = 0


　　' ブック内の名前定義をループ処理

　　' 削除処理を後回しにするため、ここでは表示設定と削除対象の収集のみ行う

　　For Each nameObj In ThisWorkbook.Names ' ActiveWorkbookではなくThisWorkbookを使用することも可能です

　　　　On Error Resume Next ' エラーが発生しても処理を続行


　　　　' 非表示の名前定義を表示にする

　　　　If nameObj.Visible = False Then

　　　　　　nameObj.Visible = True

　　　　　　unhiddenCount = unhiddenCount + 1

　　　　End If


　　　　' 参照範囲に「#REF!」が含まれるかチェック

　　　　' InStr関数で"#REF!"が見つかれば削除対象とする

　　　　If InStr(nameObj.RefersTo, "#REF!") > 0 Then

　　　　　　namesToDelete.Add nameObj

　　　　End If


　　　　On Error GoTo 0 ' エラーハンドリングを戻す

　　Next nameObj


　　' 収集した削除対象の名前定義をループ処理して削除

　　If namesToDelete.Count > 0 Then

　　　　Dim nameToDelete As Name

　　　　For Each nameToDelete In namesToDelete

　　　　　　On Error Resume Next ' エラーが発生しても処理を続行


　　　　　　' 削除する名前と参照範囲をイミディエイトに出力

　　　　　　Debug.Print "名前: " & nameToDelete.Name & ", 参照範囲: " & nameToDelete.RefersTo


　　　　　　' 名前定義を削除

　　　　　　nameToDelete.Delete

　　　　　　deletedCount = deletedCount + 1


　　　　　　On Error GoTo 0 ' エラーハンドリングを戻す

　　　　Next nameToDelete

　　End If


　　' 結果をメッセージボックスで表示

　　MsgBox "名前定義の整理が完了しました。" & vbCrLf & _

　　　　　 "非表示から表示に変更した件数: " & unhiddenCount & "件" & vbCrLf & _

　　　　　 "削除した件数: " & deletedCount & "件", vbInformation


End Sub

VBAコードの解説

このVBAコードは、Excelブックに存在する名前定義を管理し、不要なものをクリーンアップするためのものです。

Sub CleanUpNames(): プロシージャの開始を宣言します。Attribute VB_Name = "NameManager" は、VBAエディター上でのモジュール名を指定します（任意）。
変数の宣言:
- nameObj As Name: 名前定義オブジェクトを一時的に格納するための変数です。
- namesToDelete As Collection: 参照範囲に#REF!を含む名前定義を一時的に収集するためのコレクションです。コレクションを使用することで、ループ中に要素を削除する際の問題を回避します。
- unhiddenCount As Long: 非表示から表示に変更した名前定義の数をカウントするための変数です。
- deletedCount As Long: 削除した名前定義の数をカウントするための変数です。
Collectionの初期化とカウンターのリセット: Set namesToDelete = New Collection で新しいCollectionオブジェクトを作成し、unhiddenCount と deletedCount を0に初期化します。
最初のループ (For Each nameObj In ThisWorkbook.Names):
- ThisWorkbook.Names は、コードが記述されているブックのすべての名前定義のコレクションを取得します。ActiveWorkbook.Names を使用すると、現在アクティブなブックの名前定義を対象にできます。
- On Error Resume Next: エラーが発生した場合でも処理を中断せずに続行するためのエラーハンドリングです。名前定義の中にはアクセスできないものがある可能性があるため、念のため設定しています。
- 非表示の名前を表示: If nameObj.Visible = False Then で、名前定義が非表示かどうかを判定します。非表示であれば nameObj.Visible = True で表示設定に変更し、unhiddenCount をインクリメントします。
- 削除対象の判定と収集: If InStr(nameObj.RefersTo, "#REF!") > 0 Then で、名前定義の参照範囲 (nameObj.RefersTo) に文字列#REF!が含まれているかを判定します。InStr 関数は、指定した文字列が別の文字列の中に存在するかどうかを調べ、見つかった位置を返します。0より大きい値が返されれば含まれていると判断し、その nameObj を namesToDelete コレクションに追加します。
- On Error GoTo 0: エラーハンドリングを通常の状態に戻します。
削除処理のためのループ (If namesToDelete.Count > 0 Then ... For Each nameToDelete In namesToDelete):
- If namesToDelete.Count > 0 Then で、削除対象の名前定義が1つ以上存在する場合のみ、以下の削除処理を実行します。
- For Each nameToDelete In namesToDelete: 収集した namesToDelete コレクションの各要素（名前定義オブジェクト）に対してループ処理を行います。
- イミディエイトへの出力: Debug.Print "名前: " & nameToDelete.Name & ", 参照範囲: " & nameToDelete.RefersTo で、削除する名前の名前 (nameToDelete.Name) と参照範囲 (nameToDelete.RefersTo) をVBAエディターのイミディエイトウィンドウに出力します。
- 名前定義の削除: nameToDelete.Delete で、該当の名前定義をブックから削除します。
- deletedCount = deletedCount + 1: 削除件数をインクリメントします。
結果の表示 (MsgBox):
- MsgBox 関数を使用して、非表示から表示に変更した件数と削除した件数をユーザーにメッセージとして表示します。vbCrLf は改行コードです。vbInformation は情報アイコンを表示します。

このコードは、Collectionを介して削除対象の名前を管理することで、ループ中に要素を削除する際に発生しうる問題を回避しており、安全かつ効率的に名前定義の整理を行うことができます。

なぜループを回に分けたのか、若干疑問ではあります。
ですが、
「On Error Resume Next: エラーが発生した場合でも処理を中断せずに続行するためのエラーハンドリングです。名前定義の中にはアクセスできないものがある可能性があるため、念のため設定しています。」
エラー処理をしっりと記述している点は高く評価できます。

同じテーマ「生成AI活用研究」の記事

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.変数宣言のDimとデータ型｜VBA入門
3.繰り返し処理（For Next)｜VBA入門
4.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
5.RangeとCellsの使い方｜VBA入門
6.FILTER関数（範囲をフィルター処理）｜エクセル入門
7.メッセージボックス(MsgBox関数)｜VBA入門
8.セルのクリア（Clear,ClearContents）｜VBA入門
9.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
10.マクロとは？VBAとは？VBAでできること｜VBA入門

ホーム
その他
生成AI活用研究
Geminiと100本ノック 18本目：名前定義の削除

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。

生成AI活用研究
Geminiと100本ノック 18本目：名前定義の削除