Δημιουργία αναφοράς ονόματος με κωδικό VBA

Εάν χρησιμοποιείτε πολλά επώνυμα κελιά και εύρη στο βιβλίο εργασίας σας, μπορεί να εκπλαγείτε όταν ανακαλύψετε ότι το Excel δεν παρέχει τρόπο για να παραθέσετε τις λεπτομέρειες για κάθε όνομα. Χρήσιμες πληροφορίες εμφανίζονται στο παράθυρο διαλόγου Διαχείριση ονομάτων, αλλά δεν υπάρχει τρόπος να εμφανιστούν αυτές οι πληροφορίες με τρόπο που να μπορεί να εκτυπωθεί.

Ο κώδικας VBA που προσφέρεται εδώ δημιουργεί μια χρήσιμη αναφορά που περιγράφει τα ονόματα που ορίζονται σε οποιοδήποτε βιβλίο εργασίας.

Παράδειγμα αναφοράς ονόματος

Εδώ, βλέπετε ένα παράδειγμα αναφοράς ονόματος.

Η αναφορά, η οποία δημιουργείται σε ένα νέο φύλλο εργασίας, περιλαμβάνει τις ακόλουθες πληροφορίες για κάθε όνομα:

• Όνομα: Το όνομα.

• RefersTo: Ο ορισμός για το όνομα. Συνήθως, αυτό θα είναι ένα κελί ή ένα εύρος, αλλά ένα όνομα μπορεί επίσης να ορίσει τύπο.

• Κελιά: Ο αριθμός των κελιών που περιέχονται στην ονομαζόμενη περιοχή. Για τύπους με όνομα, αυτό το πεδίο εμφανίζει #N/A.

• Πεδίο εφαρμογής: Το εύρος του ονόματος — είτε Βιβλίο εργασίας είτε το όνομα του συγκεκριμένου φύλλου εργασίας στο οποίο ισχύει το όνομα.

• Hidden: True αν το όνομα είναι κρυφό. Τα κρυφά ονόματα δημιουργούνται από ορισμένα πρόσθετα (όπως το Solver) και δεν εμφανίζονται στο πλαίσιο διαλόγου Name Manager.

• Σφάλμα: True αν το όνομα περιέχει λανθασμένη αναφορά.

• Σύνδεσμος: Ένας υπερσύνδεσμος στον οποίο, όταν κάνετε κλικ, ενεργοποιεί την περιοχή με το όνομα. Μόνο τα ονόματα που αναφέρονται σε κελιά ή εύρη περιλαμβάνουν σύνδεσμο.

• Σχόλιο: Το σχόλιο για το όνομα, εάν υπάρχει.

Ο κώδικας VBA

Για να χρησιμοποιήσετε αυτόν τον κωδικό, πατήστε Alt+F11 για να ενεργοποιήσετε το Visual Basic Editor. Στη συνέχεια, επιλέξτε Εισαγωγή → Μονάδα για να εισαγάγετε μια νέα μονάδα VBA. Αντιγράψτε τον κώδικα και επικολλήστε τον στη νέα μονάδα.

Sub GenerateNameReport()
Δημιουργεί μια αναφορά για όλα τα ονόματα στο βιβλίο εργασίας
(Δεν περιλαμβάνει ονόματα πινάκων)
  Dim n Ως Όνομα
  Αδύναμη σειρά όσο διαρκεί
  Dim CellCount As Variant
Έξοδος αν δεν υπάρχουν ονόματα
  Αν ActiveWorkbook.Names.Count = 0 Τότε
    MsgBox "Το ενεργό βιβλίο εργασίας δεν έχει καθορισμένα ονόματα."
    Έξοδος υπο
  Τέλος εαν
' Έξοδος εάν το βιβλίο εργασίας είναι προστατευμένο
  If ActiveWorkbook.ProtectStructure Τότε
    MsgBox "Δεν είναι δυνατή η προσθήκη νέου φύλλου επειδή το βιβλίο εργασίας είναι προστατευμένο."
    Έξοδος υπο
  Τέλος εαν
Εισαγάγετε ένα νέο φύλλο για την αναφορά
  ActiveWorkbook.Worksheets.Add
  ActiveSheet.Move After:=Φύλλα(ActiveWorkbook.Sheets.Count)
  ActiveWindow.DisplayGridlines = False
Προσθήκη πρώτης γραμμής τίτλου
  Εύρος ("A1:H1"). Συγχώνευση
  Με εύρος ("A1")
    .Value = "Name Report for: " & ActiveWorkbook.Name
    .Γραμματοσειρά.Μέγεθος = 14
    .Font.Bold = Αληθινό
    .HorizontalAlignment = xlCenter
  Τέλος με
Προσθήκη δεύτερης γραμμής τίτλου
  Εύρος (“A2:H2”). Συγχώνευση
  Με εύρος ("A2")
    .Value = "Δημιουργήθηκε" & Τώρα
    .HorizontalAlignment = xlCenter
  Τέλος με
Προσθέστε τις κεφαλίδες
  Εύρος ("A4:H4") = Πίνακας ("Όνομα", "Αναφορά σε", "Κελιά", _
    "Πεδίο εφαρμογής", "Κρυφό", "Σφάλμα", "Σύνδεσμος", "Σχόλιο")
«Διαβάστε τα ονόματα
  Σειρά = 4
  Σφάλμα Συνέχιση Επόμενο
  Για κάθε n Στο ActiveWorkbook.Names
    Σειρά = Σειρά + 1
    «Στήλη Α: Όνομα
    Αν ν.Όνομα Like “*!*” Τότε
      Cells(Row, 1) = Split(n.Name, “!”)(1) ' Κατάργηση ονόματος φύλλου
    Αλλού
      Κελιά(Σειρά, 1) = n.Όνομα
    Τέλος εαν
    «Στήλη Β: Αναφέρεται σε
    Κελιά(Σειρά, 2) = "'" & n.RefersTo
    «Στήλη Γ: Αριθμός κελιών
    CellCount = CVERr(xlErrNA) ' Επιστρεφόμενη τιμή για ονομασμένο τύπο
    CellCount = n.RefersToRange.CountLarge
    Κελιά (Σειρά, 3) = Αριθμός κελιών
    Στήλη Δ: Πεδίο εφαρμογής
    Αν ν.Όνομα Like “*!*” Τότε
      Κελιά(Σειρά, 4) = Διαίρεση(n.Name, “!”)(0) ' εξαγωγή ονόματος φύλλου
      Κελιά(Σειρά, 4) = Αντικατάσταση(Κελιά(Σειρά, 4), "'", "") 'αφαίρεση αποστρόφων
    Αλλού
      Κελιά (Σειρά, 4) = "Βιβλίο εργασίας"
    Τέλος εαν
    «Στήλη Ε: Κρυφή κατάσταση
    Κελιά(Σειρά, 5) = Όχι n.Ορατό
    «Στήλη ΣΤ: Λανθασμένο όνομα
    Κελιά(Σειρά, 6) = n.Αναφέρεται Σε Μου αρέσει "*[#]REF!*"
    «Στήλη Ζ: Υπερσύνδεσμος
    Αν όχι Εφαρμογή.IsNA(Κελιά(Σειρά, 3)) Τότε
      ActiveSheet.Hyperlinks.Add _
        Άγκυρα:=Κελιά(Σειρά, 7), _
        Διεύθυνση:=““, _
        Υποδιεύθυνση:=n.Όνομα, _
        TextToDisplay:=n.Όνομα
    Τέλος εαν
    «Στήλη Η: Σχόλιο
    Κελιά(Σειρά, 8) = n.Σχόλιο
  Επόμενο n
Μετατρέψτε το σε τραπέζι
  ActiveSheet.ListObjects.Add _
    SourceType:=xlSrcRange, _
    Πηγή:=Εύρος (“A4”).ΤρέχουσαΠεριοχή
Προσαρμόστε τα πλάτη των στηλών
  Στήλες("A:H").EntireColumn.AutoFit
End Sub