EXCELでいつも困るんでメモ

• なぜだか消せないExcelの名前がついちゃうことがあるんで、メモ

TIPS : Excelの「名前」（定義済みの名前）をきれいに削除する方法: 記憶の葉脈 (〜Deep In Memory〜)
Excelでシートをコピーしようとした時、既にある名前がなんとかってメッセージが出たときのためのメモ (Now Headline X 開発日記)

• で過去なんどこの件で検索したのかわかんないので、さすがに今回はメモっておく事に (^-^;;

• 手動で消せない定義済み名前の削除

(引用)
Public Declare Function SetTimer Lib "user32.dll" _
    (ByVal hwnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, _
    ByVal lpTimerFunc As Long) As Long
Public Declare Function KillTimer Lib "user32.dll" _
    (ByVal hwnd As Long, ByVal nIDEvent As Long) As Long
Public Declare Function FindWindow Lib "user32.dll" Alias "FindWindowA" _
    (ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Sub DeleteDefinedNames()

    Dim beforeReferenceStyle As Variant
    beforeReferenceStyle = Application.ReferenceStyle

    Dim timerID As Long
    timerID = SetTimer(0, 0, 100, AddressOf TimerProc)

    If beforeReferenceStyle = xlR1C1 Then
        Application.ReferenceStyle = xlA1
    Else
        Application.ReferenceStyle = xlR1C1
    End If

    Dim n As Name
    For Each n In ActiveWorkbook.Names
        If Not n.Name Like "*!Print_Area" And _
            Not n.Name Like "*!Print_Titles" Then
            n.Delete
        End If
    Next

    Application.ReferenceStyle = beforeReferenceStyle

    KillTimer 0, timerID
 
End Sub

Private Sub TimerProc()

    Dim hwnd As Long
    hwnd = FindWindow("bosa_sdm_XL9", "名前の重複")

    If hwnd > 0 Then
        SendKeys getRandomString(3, 20), 10
        SendKeys "{ENTER}"
    End If

End Sub
 
Private Function getRandomString(min As Long, max As Long) As String
 
    Dim s As String
    Dim i As Long
 
    max = Int(max * Rnd)
 
    For i = 0 To min + max
        Randomize
        s = s & Chr(65 + Int(26 * Rnd))
    Next
 
    getRandomString = s
 
End Function

• 見えない名前の表示

(引用)
Public Sub DeleteNames()
    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
    If wCnt <> 0 Then
        MsgBox wCnt & "個の名前定義が見つかりました。",vbInformation
    Else
       MsgBox "非表示の名前定義はありません。",vbExclamation
    End If
End Sub