Nimiraportin luominen VBA-koodilla

Jos käytät työkirjassasi paljon nimettyjä soluja ja alueita, saatat yllättyä huomatessasi, että Excel ei tarjoa mahdollisuutta luetteloida jokaisen nimen tietoja. Hyödyllisiä tietoja näkyy Name Manager -valintaikkunassa, mutta näitä tietoja ei voi näyttää tulostetulla tavalla.

Tässä tarjottu VBA-koodi luo hyödyllisen raportin, joka kuvaa missä tahansa työkirjassa määritetyt nimet.

Esimerkkinimiraportti

Tässä näet esimerkin nimiraportista.

Raportti, joka luodaan uudelle laskentataulukolle, sisältää seuraavat tiedot jokaisesta nimestä:

• Nimi: Nimi.

• RefersTo: Nimen määritelmä. Yleensä tämä on solu tai alue, mutta nimi voi myös määrittää kaavan.

• Solut: Nimetyn alueen sisältämien solujen määrä. Nimetyille kaavoille tässä kentässä näkyy #N/A.

• Laajuus: Nimen laajuus — joko työkirja tai sen laskentataulukon nimi, jolla nimi on voimassa.

• Piilotettu: Totta, jos nimi on piilotettu. Jotkin apuohjelmat (kuten Solver) luovat piilotetut nimet, eivätkä ne näy Name Manager -valintaikkunassa.

• Virhe: Tosi, jos nimi sisältää virheellisen viittauksen.

• Linkki: Hyperlinkki, jota napsautettuna aktivoi nimetty alue. Vain soluihin tai alueisiin viittaavat nimet sisältävät linkin.

• Kommentti: Kommentti nimelle, jos sellainen on.

VBA-koodi

Voit käyttää tätä koodia painamalla Alt+F11 aktivoidaksesi Visual Basic Editorin. Valitse sitten Lisää → Moduuli lisätäksesi uuden VBA-moduulin. Kopioi koodi ja liitä se uuteen moduuliin.

Sub GenerateNameReport()
Luo raportin kaikista työkirjan nimistä
' (Ei sisällä taulukoiden nimiä)
  Dim n As Name
  Himmeä rivi yhtä pitkä
  Dim CellCount vaihtoehtona
' Poistu, jos ei nimiä
  Jos ActiveWorkbook.Names.Count = 0 Sitten
    MsgBox "Aktiivisella työkirjalla ei ole määritettyjä nimiä."
    Poistu Subista
  Loppu Jos
' Poistu, jos työkirja on suojattu
  Jos ActiveWorkbook.ProtectStructure Sitten
    MsgBox "Uutta taulukkoa ei voi lisätä, koska työkirja on suojattu."
    Poistu Subista
  Loppu Jos
' Lisää uusi taulukko raportille
  ActiveWorkbook.Worksheets.Add
  ActiveSheet.Move After:=Sheets(ActiveWorkbook.Sheets.Count)
  ActiveWindow.DisplayGridlines = Epätosi
Lisää otsikon ensimmäinen rivi
  Alue("A1:H1"). Yhdistä
  Kanta-alueella ("A1")
    .Value = "Nimiraportti kohteelle: " & ActiveWorkbook.Name
    .Font.Size = 14
    .Font.Bold = Totta
    .HorizontalAlignment = xlCenter
  Lopeta kanssa
' Lisää otsikon toinen rivi
  Alue("A2:H2"). Yhdistä
  Kantaman kanssa ("A2")
    .Value = "Luotu" & Nyt
    .HorizontalAlignment = xlCenter
  Lopeta kanssa
' Lisää otsikot
  Alue("A4:H4") = Array("Nimi", "RefersTo", "Cells", _
    "Soveltamisala", "Piilotettu", "Virhe", "Linkki", "Kommentti")
' Selaa nimet läpi
  Rivi = 4
  Virheestä Jatka seuraavaksi
  Jokaiselle n:lle ActiveWorkbook.Namesissa
    Rivi = rivi + 1
    "Sarake A: Nimi
    Jos n.Name Like “*!*” Sitten
      Solut(Rivi, 1) = Split(n.Name, "!")(1) ' Poista arkin nimi
    Muu
      Solut(Rivi, 1) = n.Nimi
    Loppu Jos
    'Sarake B: RefersTo
    Solut(Rivi, 2) = "'" & n.RefersTo
    "Sarake C: Solujen lukumäärä
    CellCount = CVERr(xlErrNA) ' Nimetyn kaavan palautusarvo
    CellCount = n.RefersToRange.CountLarge
    Solut (Rivi, 3) = Solumäärä
    ' Sarake D: Soveltamisala
    Jos n.Name Like “*!*” Sitten
      Solut(Rivi, 4) = Split(n.Name, "!")(0) ' otteen arkin nimi
      Solut(Rivi, 4) = Korvaa(Solut(Rivi, 4), "'", "") 'poista heittomerkit
    Muu
      Solut (Rivi, 4) = "Työkirja"
    Loppu Jos
    'Sarake E: Piilotettu tila
    Solut (Rivi, 5) = Ei n. Näkyy
    'Sarake F: Virheellinen nimi
    Solut (Rivi, 6) = n. Viittaa tykkäämiseen "*[#]REF!*"
    "Sarake G: Hyperlinkki
    Jos ei Sovellus.IsNA(Cells(Row, 3)) Sitten
      ActiveSheet.Hyperlinks.Add _
        Ankkuri:=solut(rivi, 7), _
        Osoite:=““, _
        Alaosoite:=n.Nimi, _
        TextToDisplay:=n.Name
    Loppu Jos
    'Sarake H: Kommentti
    Solut(Rivi, 8) = n.Kommentti
  Seuraava n
' Muunna se taulukoksi
  ActiveSheet.ListObjects.Add _
    Lähdetyyppi:=xlSrcRange, _
    Lähde:=Alue(“A4”).CurrentRegion
' Säädä sarakkeiden leveyksiä
  Sarakkeet ("A:H"). Koko sarake. Automaattinen sovitus
Lopeta ala