CODEWITHSUNDEEP
vbaexcel
Smartis has left SO again
Smartis has left SO again

Reputation: 5150

Reattaching Named Ranges in VBA

I'm trying to remove all Named Ranges in my Excel Workbook, store them in an Collection and after deleting, reattach them to my Workbook.

My Code looks like this

Sub ResetNamedRanges()
    Dim rName As Excel.Name
    Dim cName As Excel.Name
    Dim rangedNames As Excel.names
    Dim collNames As New Collection

    Set rangedNames = ThisWorkbook.names

    For Each rName In rangedNames
        collNames.Add rName 
        rName.Delete
    Next

    For Each cName In collNames
        names.Add cName.Name, cName.RefersTo, cName.Visible, cName.MacroType, cName.ShortcutKey, cName.Category, cName.NameLocal, cName.RefersToLocal, cName.CategoryLocal, cName.RefersToR1C1, cName.RefersToR1C1Local
    Next
End Sub

But it isn't working. Quit sure i'm missing something.

Upvotes: 1

Views: 134

Answers (1)

David Zemens
David Zemens

Reputation: 53653

Use a Dictionary and capture the relevant properties as such.

Your Collection method fails for reasons outlined in comments above: The .Delete method removes any reference to the Name object that you put in the collection. Your collection will be full of broken references, and you can't restore names from broken/invalid object references.

Option Explicit

Sub foo()
Dim rName As Name
Dim dictNames As Object

Set dictNames = CreateObject("Scripting.Dictionary")

For Each rName In Names
        'We're going to use a dict for the properties, also:
        dictNames.Add rName.Name, Nothing
        Set dictNames(rName.Name) = CreateObject("Scripting.Dictionary")
        With dictNames(rName.Name)
            ' Not my favorite way to do this, but some properties undefined will raise an error
            ' you can work a better way to do this if you prefer
            On Error Resume Next
            .Add "RefersTo", rName.RefersTo
            .Add "Visible", rName.Visible
            .Add "MacroType", rName.MacroType
            .Add "ShortcutKey", rName.ShortcutKey
            .Add "Category", rName.Category
            .Add "NameLocal", rName.NameLocal
            .Add "RefersToLocal", rName.RefersToLocal
            .Add "CategoryLocal", rName.CategoryLocal
            .Add "RefersToR1C1", rName.RefersToR1C1
            .Add "RefersToR1C1Local", rName.RefersToR1C1Local
            On Error GoTo 0
        End With
        rName.Delete
    Next

Dim itm
For Each itm In dictNames
    Set rName = Names.Add(itm, dictNames(itm)("RefersTo"))
    On Error Resume Next
    'rName.RefersTo = itm("RefersTo")
    rName.Visible = itm("Visible")
    rName.MacroType = itm("MacroType")
    rName.ShortcutKey = itm("ShortCutKey")
    rName.Category = itm("Category")
    rName.NameLocal = itm("NameLocal")
    rName.RefersToLocal = itm("RefersToLocal")
    rName.CategoryLocal = itm("CategoryLocal")
    rName.RefersToR1C1 = itm("RefersToR1C1")
    rName.RefersToR1C1Local = itm("RefersToR1C1Local")
    On Error GoTo 0
Next
End Sub

Upvotes: 1

Related Questions