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. Как создать примечание к имени ячейке, которое будет отображаться в диспетчере имён ? 23.07.2016
  6. Как определить наличие именованных ячеек, диапазонов, формул и их количество ? 05.04.2005
  7. Как вывести все имена, в т.ч. именованные формулы в отдельный список, в виде имени и адреса ? 18.07.2005
  8. Как определить уровень имени (именованная ячейка, диапазон, формула) ? 09.09.2006
  9. Как определить "тип" имени, т.е. узнать именованная ли это ячейка, диапазон или нет ? 12.04.2010
  10. Как определить наличие ошибки #ССЫЛКА! в имени ? 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

  • Ответ : Актуально для 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-2016 Климов П.Ю. Все права защищены. WebDesign & Error's Klimoff