Microsoft Excel:

  Таблицы и VBA. Справочник.
  Вопросы и Ответы. Советы. Примеры.
Меню FAQ | Макросы | Names


Rambler's Top100


Counter CO.KZ

  1. Как присвоить имя ячейке средствами VBA 2004
  2. Как присвоить имя ячейке средствами VBA, так чтобы это имя не отображалось в списке имён ? 2004
  3. Как присвоить имя формуле средствами VBA, чтобы это имя не отображалось в списке имён ? 2004
  4. Как найти все скрытые имена, и сделать их видимыми ? 23.07.2016
  5. Как в активной книге скрыть все имена ? NEW 22.06.2018
  6. Как скрыть/отобразить все имена в любой открытой книге ? NEW 22.06.2018
  7. Как создать примечание к имени ячейке, которое будет отображаться в диспетчере имён ? 23.07.2016
  8. Как определить наличие именованных ячеек, диапазонов, формул и их количество ? 05.04.2005
  9. Как вывести все имена, в т.ч. именованные формулы в отдельный список, в виде имени и адреса ? 18.07.2005
  10. Как определить уровень имени (именованная ячейка, диапазон, формула) ? 09.09.2006
  11. Как определить "тип" имени, т.е. узнать именованная ли это ячейка, диапазон или нет ? 12.04.2010
  12. Как определить наличие ошибки #ССЫЛКА! в имени ? 09.09.2006

  • Ответ : Скачать пример

    Вариант I.
  • Range("A5").Name = "фигня"
    Вариант II.
    Names.Add Name:="фигня", RefersTo:="=$A$5", Visible:=True 'For ActiveSheet
    
    Names.Add Name:="фигня", RefersTo:="=Лист1!$A$5", Visible:=True
    Примечание :
  • - Вы можете также обращаться к ячейке используя другие методы [Как обратится к ячейке]
  • Ответ :

    Вариант I.
  • Names.Add Name:="фигня", RefersTo:="=$A$5", Visible:=False 'For ActiveSheet
    
    Names.Add Name:="фигня", RefersTo:="=Лист1!$A$5", Visible:=False
    Вариант II.
    Range("A5").Name = "фигня" 
    Range("A5").Name.Visible = False
    Range("A5").Name = "фигня" 
    Range("фигня").Name.Visible = False
    Для того чтобы скрыть уже существующее имя, используйте этот код :
    Names("фигня").Visible = False
    Range("фигня").Name.Visible = False

  • Ответ :
  • Names.Add Name:="СуммаНалог", RefersTo:="=SUM(Лист1!$B$5:$B$10)", Visible:=False
    
    Names.Add Name:="СуммаВзятка", RefersToLocal:="=СУММ(Лист1!$C$5:$C$10)", Visible:=False
    
    Примечание :
    a) Рекомендуется использовать абсолютные ссылки на адреса ячеек.
    б) При использовании в функциях "" их необходимо заменить на """"
    Если в функциях имеются аргументы разделённые ; то в VBA их необходимо заменить на , но это касается только функций "созданных" без использования FormulaLocal, см. ниже пример ...
    ="=IF(A5>13,A5,""<>"")" 
    ="=ЕСЛИ(A5>13;A5;""<>"")"

    Для того чтобы скрыть уже существующее имя, используйте этот код :
    Names("СуммаНалог").Visible = False 
    Names("СуммаВзятка").Visible = False

  • Ответ :

    Если в активной рабочей книге используются имена, но Вы не видите их в списке имён стандартного диалогового окна, которое появится, если выполнить следующии действия :

    XL2003 меню Вставка пункт Имя и команда Присвоить
    XL2007 закладка Формулы группа Определённые имена и кнопка Диспетчер имён

    То, чтобы "найти" все скрытые имена и сделать их видимыми, достаточно выполнить нижеопубликованный макрос.
  • Private Sub AllNamesVisible() 
        Dim iName As Name 
        For Each iName In Names 'ActiveWorkbook.Names 
            iName.Visible = True 
        Next 
    End Sub

  • Ответ :

    Если в активной рабочей книге используются имена и Вы хотите их все скрыть, т.е. сделать так, чтобы их не было видно в списке имён стандартного диалогового окна, которое появится, если выполнить следующии действия :

    XL2003 меню Вставка пункт Имя и команда Присвоить
    XL2007 закладка Формулы группа Определённые имена и кнопка Диспетчер имён

    То, просто выполните нижеопубликованный макрос.
  • Private Sub AllNamesHidden() 
        Dim iName As Name 
        For Each iName In Names 'ActiveWorkbook.Names 
            iName.Visible = False 
        Next 
    End Sub

  • Ответ :

    Если Вам необходимо иметь "универсальный" макрос, который может скрывать/делать видимыми имена в любой открытой книге, то используйте нижеопубликованный макрос.
  • Private Sub ChangeVisibleAllNames(Wb As Workbook, prValue As Boolean)
        Dim iName As Name
        For Each iName In Wb.Names
            iName.Visible = prValue
        Next
    End Sub
    Примеры вызова :

    1) Скрыть имена в текущей книге
    ChangeVisibleAllNames ThisWorkbook, False
    2) Отобразить имена в активной книге
    ChangeVisibleAllNames ActiveWorkbook, True
    3) Отобразить имена в открытой книге с именем "Файл_с_именами.xls"
    ChangeVisibleAllNames Workbooks("Файл_с_именами.xls"), True

  • Ответ : Актуально для MS Excel 2007

    Если необходимо, чтобы в диспетчере имён, напротив нужного имени ячейки/диапазона, появилось примечание, которое, предоставляет дополнтельные сведения (текст не более 255 символов) об этом об'екте, то такой комментарий можно создать так :
  • Names.Add(Name:="Иванов", RefersTo:="=$B$2:$B$100").Comment = "Приход от Иванова"
    
    Names.Add Name:="Иванов", RefersTo:="=$B$2:$B$100" 
    Names("Иванов").Comment = "Приход от Иванова"

  • Ответ :
  • iCountAllNames = ActiveWorkbook.Names.Count 
    
    If iCountAllNames = 0 Then 
       MsgBox "Имён нет" 
    Else 
       MsgBox "Имён : " & iCountAllNames & " шт." 
    End If
    Для рабочего листа мы можем узнать количество количество имён только уровня рабочего листа
    iCountLocalNames = ActiveSheet.Names.Count

  • Ответ : Скачать пример

    Вариант I.
  • For Each iName In ThisWorkbook.Names 
        Cells(iName.Index, 1).Value = iName.Name 
        Cells(iName.Index, 2).Value = "'" & iName.RefersToLocal 
    Next
    Вариант II.
    With ThisWorkbook.Names 
         For iCount = 1 To .Count 
             Cells(iCount, 1).Value = .Item(iCount).Name 
             Cells(iCount, 2).Value = "'" & .Item(iCount).RefersToLocal 
         Next 
    End With
    Bonus.
    For Each iName In ThisWorkbook.Names 
        With iName 
             iIndex = .Index + 1 
             Cells(iIndex, 1).Value = .Name 
             Cells(iIndex, 2).Value = .Parent.Name 
             Cells(iIndex, 3).Value = TypeName(.Parent) 
             Cells(iIndex, 4).Value = "'" & .RefersTo 
             Cells(iIndex, 5).Value = "'" & .RefersToLocal 
             Cells(iIndex, 6).Value = .Visible 
        End With 
    Next 
    With Range(Cells(1, 1), Cells(1, 6)) 'Range("A1:F1") 
         .Value = Array("Name", "Parent", "NameLevel", _ 
         "RefersTo", "RefersToLocal", "Visible") 
         .Font.Bold = True 
         .EntireColumn.AutoFit 
    End With

  • Ответ :

    Как известно имена бывают двух уровней : уровня рабочей книги (по умолчанию глобальные) и уровня рабочего листа (локальные) и если необходимо программно определить уровень имени, то Вы можете использовать любой из трёх вариантов.

    Вариант I.
  • For Each iName In ThisWorkbook.Names 
        MsgBox "Имя : " & iName.Name & vbCrLf & _ 
        "Уровень : " & TypeName(iName.Parent) 
    Next
    For Each iName In ThisWorkbook.Names 
        If TypeName(iName.Parent) = "Workbook" Then 
           MsgBox "Имя : " & iName.Name & vbCrLf & _ 
           "имеет уровень рабочей книги (глобальное)" 
        Else 
           MsgBox "Имя : " & iName.Name & vbCrLf & _ 
           "имеет уровень рабочего листа (локальное)" 
        End If 
    Next
    Примечание : Если необходимо поменять местами порядок выполнение инструкций, то используйте :
    If TypeName(iName.Parent) = "Worksheet" Then
    Вариант II.
    For Each iName In ThisWorkbook.Names 
        If iName.Name Like "*!*" Then 
           MsgBox "Имя : " & iName.Name & vbCrLf & _ 
           "имеет уровень рабочего листа (локальное)" 
        Else 
           MsgBox "Имя : " & iName.Name & vbCrLf & _ 
           "имеет уровень рабочей книги (глобальное)" 
        End If 
    Next
    Примечание : Если необходимо поменять местами порядок выполнение инструкций, то используйте :
    If Not iName.Name Like "*!*" Then
    Вариант III.
    For Each iName In ThisWorkbook.Names 
        If InStr(iName.Name, "!") = 0 Then 
           MsgBox "Имя : " & iName.Name & vbCrLf & _ 
           "имеет уровень рабочей книги (глобальное)" 
        Else 
           MsgBox "Имя : " & iName.Name & vbCrLf & _ 
           "имеет уровень рабочего листа (локальное)" 
        End If 
    Next
    Примечание : Если необходимо поменять местами порядок выполнение инструкций, то используйте :
    If InStr(iName.Name, "!") > 0 Then
    Комментарий : Во всех примерах управляющую инструкцию If Then Else можно заменить на функцию IIf :
    For Each iName In ThisWorkbook.Names 
        MsgBox "Имя : " & iName.Name & vbCrLf & _ 
        "имеет уровень" & IIf(TypeName(iName.Parent) = "Workbook", _ 
        "рабочей книги (глобальное)", "рабочего листа (локальное)") 
    Next

  • Ответ :

    Как известно присвоить имя можно не только ячейке или диапазону ячеек, но и формуле, а также константе. И если потребуется узнать ссылается ли имя на ячейку/диапазон, то можно использовать один из предложенных вариантов, учитывая, что :

  • формулы, которые возвращают ссылку на ячейку/диапазон, также считаются Range
  • однако, если формула ссылается на другую рабочую книгу (внешняя ссылка), то в случае, если другая рабочая книга открыта, то это Range, в противном случае Error
  • трёхмерные ссылки как Range не определяются

    Вариант I.
  • For Each iName In ThisWorkbook.Names 
        If TypeName(Evaluate(iName.RefersTo)) = "Range" Then 
           MsgBox iName.Name & " cсылка возвращает об'ект Range" 
        Else 
           MsgBox iName.Name & " оставим пока в покое" 
        End If 
    Next
    For Each iName In ThisWorkbook.Names 
        If TypeOf Evaluate(iName.RefersTo) Is Range Then 
           MsgBox iName.Name & " cсылка возвращает об'ект Range" 
        Else 
           MsgBox iName.Name & " оставим пока в покое" 
        End If 
    Next
    Вариант II.
    For Each iName In ThisWorkbook.Names 
        If Evaluate("IsRef(" & Mid(iName.RefersTo, 2) & ")") = True Then 
           MsgBox iName.Name & " cсылка возвращает об'ект Range" 
        Else 
           MsgBox iName.Name & " оставим пока в покое" 
        End If 
    Next

  • Ответ :

    Как известно имена могут содержать ссылки на ячейки и диапазоны ячеек, но после удаления этих ячеек, вместо ссылки появится значение ошибки #ССЫЛКА! И если возникнет необходимость в определении наличия подобных имён, например, для удаления, то можно использовать любой из двух предложенных вариантов.

    Вариант I.
  • For Each iName In ThisWorkbook.Names 
        If iName.RefersTo Like "*REF!*" Then 
           MsgBox "Найдено ошибочное имя : " & iName.Name 
           'iName.Delete 'удаление имени 
        End If 
    Next
    Если Вы привыкли работать с учётом национальных настроек, и по возможности, используете более привычные варианты, то :
    For Each iName In ThisWorkbook.Names 
        If iName.RefersToLocal Like "*ССЫЛКА!*" Then 
           MsgBox "Найдено ошибочное имя : " & iName.Name 
           'iName.Delete 'удаление имени 
        End If 
    Next
    Вариант II.
    For Each iName In ThisWorkbook.Names 
        If InStr(iName.RefersTo, "#REF!") > 0 Then 
           MsgBox "Найдено ошибочное имя : " & iName.Name 
           'iName.Delete 'удаление имени 
        End If 
    Next

    Воспроизведение любых опубликованных здесь материалов возможно только с письменного разрешения автора : Microsoft Excel 95, 97, 2000, XP

    © 2004-2018 Климов П.Ю. Все права защищены. WebDesign & Error's Klimoff