Vārda atskaites izveide ar VBA kodu

Ja darbgrāmatā izmantojat daudz nosauktu šūnu un diapazonu, varat būt pārsteigts, atklājot, ka programmā Excel nav iespējams uzskaitīt katra nosaukuma detalizētu informāciju. Dialoglodziņā Name Manager tiek parādīta noderīga informācija, taču šo informāciju nevar parādīt tā, lai to varētu izdrukāt.

Šeit piedāvātais VBA kods ģenerē noderīgu pārskatu, kurā aprakstīti jebkurā darbgrāmatā definētie nosaukumi.

Nosaukuma pārskata piemērs

Šeit ir redzams vārda pārskata piemērs.

Pārskatā, kas tiek izveidots jaunā darblapā, ir iekļauta šāda informācija par katru nosaukumu:

• Vārds: Vārds.

• RefersTo: vārda definīcija. Parasti tā ir šūna vai diapazons, taču nosaukums var definēt arī formulu.

• Šūnas: šūnu skaits, kas atrodas nosauktajā diapazonā. Formulām ar nosaukumu šajā laukā tiek parādīts #N/A.

• Darbības joma: nosaukuma tvērums — vai nu darbgrāmata, vai konkrētās darblapas nosaukums, kurā nosaukums ir derīgs.

• Slēpts: patiess, ja vārds ir paslēpts. Slēptos nosaukumus veido dažas pievienojumprogrammas (piemēram, Solver), un tie netiek rādīti dialoglodziņā Name Manager.

• Kļūda: patiess, ja nosaukumā ir kļūdaina atsauce.

• Saite: hipersaite, uz kuras noklikšķinot tiek aktivizēts nosauktais diapazons. Saite ietver tikai nosaukumos, kas attiecas uz šūnām vai diapazoniem.

• Komentārs: vārda komentārs, ja tāds ir.

VBA kods

Lai izmantotu šo kodu, nospiediet taustiņu kombināciju Alt+F11, lai aktivizētu Visual Basic redaktoru. Pēc tam izvēlieties Ievietot → Modulis, lai ievietotu jaunu VBA moduli. Nokopējiet kodu un ielīmējiet to jaunajā modulī.

Sub GenerateNameReport()
Izveido pārskatu par visiem darbgrāmatas vārdiem
(Neietver tabulu nosaukumus)
  Dim n As Name
  Dim Row As Long
  Dim CellCount kā variants
Izejiet, ja nav vārdu
  Ja ActiveWorkbook.Names.Count = 0, tad
    MsgBox "Aktīvajai darbgrāmatai nav definētu nosaukumu."
    Iziet Sub
  Beigas Ja
Iziet, ja darbgrāmata ir aizsargāta
  Ja ActiveWorkbook.ProtectStructure Tad
    MsgBox "Jaunu lapu nevar pievienot, jo darbgrāmata ir aizsargāta."
    Iziet Sub
  Beigas Ja
' Ievietojiet jaunu atskaites lapu
  ActiveWorkbook.Worksheets.Add
  ActiveSheet.Move After:=Izklājlapas(ActiveWorkbook.Sheets.Count)
  ActiveWindow.DisplayGridlines = False
Pievienot virsraksta pirmo rindiņu
  Diapazons (“A1:H1”). Apvienot
  Ar diapazonu (“A1”)
    .Value = "Nosaukuma atskaite: " & ActiveWorkbook.Name
    .Fonta lielums = 14
    .Font.Bold = True
    .HorizontalAlignment = xlCenter
  Beigt ar
Pievienot virsraksta otro rindiņu
  Diapazons (“A2:H2”). Apvienot
  Ar diapazonu (“A2”)
    .Value = “Ģenerēts” un tagad
    .HorizontalAlignment = xlCenter
  Beigt ar
' Pievienojiet galvenes
  Diapazons ("A4:H4") = masīvs ("nosaukums", "atsauce uz", "šūnas", _
    “Tvērums”, “Slēpts”, “Kļūda”, “Saite”, “Komentārs”)
Pārlūkojiet nosaukumus
  Rinda = 4
  Kļūdas gadījumā atsākt nākamo
  Katram n sadaļā ActiveWorkbook.Names
    Rinda = rinda + 1
    "A aile: nosaukums
    Ja n.Nosaukums Patīk “*!*” Tad
      Šūnas(Rinda, 1) = Sadalīt(n.Nosaukums, "!")(1) ' Noņemt lapas nosaukumu
    Citādi
      Šūnas(Rinda, 1) = n.Nosaukums
    Beigas Ja
    "B kolonna: Atsaucas uz
    Šūnas(Rinda, 2) = “'“ & n.RefersTo
    “C kolonna: šūnu skaits
    CellCount = CVERr(xlErrNA) ' Nosauktās formulas atgriešanas vērtība
    CellCount = n.RefersToRange.CountLarge
    Šūnas (rinda, 3) = šūnu skaits
    D kolonna: Darbības joma
    Ja n.Nosaukums Patīk “*!*” Tad
      Šūnas(Rinda, 4) = Sadalīt(n.Nosaukums, "!")(0) ' ekstrakta lapas nosaukums
      Šūnas(Rinda, 4) = Aizstāt(Šūnas(Rinda, 4), "'", "") 'noņemt apostrofus
    Citādi
      Šūnas (4. rinda) = “Darbgrāmata”
    Beigas Ja
    "E kolonna: Slēpts statuss
    Šūnas (rinda, 5) = nav n. Redzams
    "F kolonna: kļūdains nosaukums
    Šūnas (rinda, 6) = n. Attiecas uz Patīk “*[#]REF!*”
    "G kolonna: hipersaite
    Ja Not Application.IsNA(Cells(Row, 3)) Tad
      ActiveSheet.Hyperlinks.Add _
        Enkurs:=Šūnas (rinda, 7), _
        Adrese:=““, _
        Apakšadrese:=n.Vārds, _
        TextToDisplay:=n.Name
    Beigas Ja
    "H aile: komentārs
    Šūnas(Rinda, 8) = n.Komentārs
  Nākamais n
Pārvērtiet to par tabulu
  ActiveSheet.ListObjects.Add _
    SourceType:=xlSrcRange, _
    Avots:=Diapazons(“A4”).Pašreizējais reģions
' Pielāgojiet kolonnu platumu
  Kolonnas (“A:H”). Visa kolonna. Automātiska ietilpināšana
Beigu apakš