excel - Reattaching Named Ranges in VBA -
i'm trying remove named ranges in excel workbook, store them in collection , after deleting, reattach them workbook.
my code looks this
sub resetnamedranges() dim rname excel.name dim cname excel.name dim rangednames excel.names dim collnames new collection set rangednames = thisworkbook.names each rname in rangednames collnames.add rname rname.delete next 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 isn't working. quit sure i'm missing something.
use dictionary
, capture relevant properties such.
your collection method fails reasons outlined in comments above: .delete
method removes reference name
object put in collection. collection full of broken references, , can't restore names broken/invalid object references.
option explicit
sub foo() dim rname name dim dictnames object set dictnames = createobject("scripting.dictionary") each rname in names 'we're going use dict properties, also: dictnames.add rname.name, nothing set dictnames(rname.name) = createobject("scripting.dictionary") dictnames(rname.name) ' not favorite way this, properties undefined raise error ' can work better way if 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 rname.delete next dim itm 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
Comments
Post a Comment