Как получить/установить уникальный идентификатор для ячейки в Excel через VBA

Question

Как получить/установить уникальный идентификатор для ячейки в Excel через VBA

В соответствии с этим ответом , голос с наибольшим количеством голосов оставляет «прогиб» в конце. Вот мое решение по-настоящему получить куски одинакового размера, насколько это возможно, без сучков. Он в основном пытается выбрать именно дробную точку, где он должен разбить список, но просто округляет его до ближайшего целого числа:

from __future__ import division  # not needed in Python 3
def n_even_chunks(l, n):
    """Yield n as even chunks as possible from l."""
    last = 0
    for i in range(1, n+1):
        cur = int(round(i * (len(l) / n)))
        yield l[last:cur]
        last = cur

Демонстрация:

>>> pprint.pprint(list(n_even_chunks(list(range(100)), 9)))
[[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
 [11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21],
 [22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32],
 [33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43],
 [44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55],
 [56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66],
 [67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77],
 [78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88],
 [89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99]]
>>> pprint.pprint(list(n_even_chunks(list(range(100)), 11)))
[[0, 1, 2, 3, 4, 5, 6, 7, 8],
 [9, 10, 11, 12, 13, 14, 15, 16, 17],
 [18, 19, 20, 21, 22, 23, 24, 25, 26],
 [27, 28, 29, 30, 31, 32, 33, 34, 35],
 [36, 37, 38, 39, 40, 41, 42, 43, 44],
 [45, 46, 47, 48, 49, 50, 51, 52, 53, 54],
 [55, 56, 57, 58, 59, 60, 61, 62, 63],
 [64, 65, 66, 67, 68, 69, 70, 71, 72],
 [73, 74, 75, 76, 77, 78, 79, 80, 81],
 [82, 83, 84, 85, 86, 87, 88, 89, 90],
 [91, 92, 93, 94, 95, 96, 97, 98, 99]]

Сравнение с вершиной проголосовал chunks ответ:

>>> pprint.pprint(list(chunks(list(range(100)), 100//9)))
[[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
 [11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21],
 [22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32],
 [33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43],
 [44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54],
 [55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65],
 [66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76],
 [77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87],
 [88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98],
 [99]]
>>> pprint.pprint(list(chunks(list(range(100)), 100//11)))
[[0, 1, 2, 3, 4, 5, 6, 7, 8],
 [9, 10, 11, 12, 13, 14, 15, 16, 17],
 [18, 19, 20, 21, 22, 23, 24, 25, 26],
 [27, 28, 29, 30, 31, 32, 33, 34, 35],
 [36, 37, 38, 39, 40, 41, 42, 43, 44],
 [45, 46, 47, 48, 49, 50, 51, 52, 53],
 [54, 55, 56, 57, 58, 59, 60, 61, 62],
 [63, 64, 65, 66, 67, 68, 69, 70, 71],
 [72, 73, 74, 75, 76, 77, 78, 79, 80],
 [81, 82, 83, 84, 85, 86, 87, 88, 89],
 [90, 91, 92, 93, 94, 95, 96, 97, 98],
 [99]]

5

excel vba excel-vba

задан Chris Kimpton 19 June 2009 в 14:18

5 ответов

Concur with Ant - ваш код отлично работает здесь, в Excel 2003 SP3.

Я также мог использовать:

Set currCell = Application.Caller
If Application.Caller.ID = "" Then
    gNextUniqueId = gNextUniqueId + 1
    'this line fails no matter what value I set it to.
    currCell.ID = Str(gNextUniqueId)
End If

Ага! Я думаю, что у меня это есть.

Я думаю, вы вызываете это из формулы массива, и она вызывается только ОДИН РАЗ с полным диапазоном. Вы не можете получить идентификатор для диапазона - только для одной ячейки. Это объясняет, почему Application.Caller.ID не работает для вас, потому что Range ("A1: B9"). ID генерирует определяемую приложением или объектно-определяемую ошибку .

Когда вы используете Range (Application.Caller.Address) , чтобы получить «ячейку», просто перенесите эту ошибку на строку currCell.ID .

1

ответ дан 14 December 2019 в 08:58

Я думаю, у нас может быть несколько проблем, но я думаю, что это проблемы тестирования, а не проблемы с самим кодом. Во-первых, если вы вызываете функцию из чего-либо, кроме ячейки, например, из непосредственного окна, другого кода и т. Д. Application.Caller не будет установлен. Это то, что вызывает ошибку "объект не найден". Во-вторых, если вы скопируете / вставите ячейку с функцией, вы также скопируете / вставите идентификатор. Поэтому куда бы вы его не вставили, результат останется прежним. Но если вы просто скопируете текст (вместо ячейки), а затем вставите, то все будет нормально. (Включая исходное использование Application.Caller.)

1

ответ дан 14 December 2019 в 08:58

Хорошо ...

Похоже, что если лист заблокирован, макросы не имеют доступа на запись к низкоуровневой информации, такой как идентификатор.

Однако я не думаю, что можно снять защиту листа в UDF. По дизайну, UDF сильно ограничены; Я думаю, что наличие формулы ячейки, управляющей защитой листа, нарушит парадигму формулы, согласно которой формула ячейки влияет только на ячейку. См. эту страницу на веб-сайте Microsoft для получения более подробной информации.

Я думаю, это ограничивает ваши возможности. Вы должны либо:

отказаться от защиты листа
отказаться от UDF, использовать событие Worksheet_Change для захвата изменений ячеек и записать туда ID
использовать UDF, который записывает ID в значение ячейки, а не сохраняет к ID

Подход UDF чреват проблемами, так как вы пытаетесь использовать что-то, предназначенное для расчета ячейки, чтобы сделать постоянную отметку на листе.

Тем не менее, вот пример UDF, который вы можете использовать для штамповки «постоянное» значение ячейки, которое работает с разблокированными ячейками защищенного листа. Этот работает только для отдельных ячеек (хотя его можно адаптировать для формулы массива).

Public Function CellMark()

    Dim currCell As Range
    Set currCell = Range(Application.Caller.Address)

    Dim myId As String
    ' must be text; using .value will cause the formula to be called again
    ' and create a circular reference
    myId = currCell.Text

    If (Trim(myId) = "" Or Trim(myId) = "0") Then
       myId = "ID-" & Format(CStr(gNextUniqueId), "00000")
       gNextUniqueId = gNextUniqueId + 1
    End If

    CellMark = myId

End Function

Однако это довольно ошибочно. Однако использование copy или fillbox приведет к сохранить предыдущее скопированное значение. Только если явно указать в ячейках новую формулу, это будет работать. Но если вы снова введете формулу в ячейку (просто щелкните по ней, нажмите ENTER), будет рассчитано новое значение - что является стандартным поведением ячейки.

Я думаю, что событие Worksheet_Change - это правильный путь, который имеет гораздо большую широту . Вот простой пример, который обновляет идентификатор любых изменений ячейки. Это может быть адаптировано к вашему конкретному сценарию. Эту функцию необходимо добавить на каждый рабочий лист, для которого требуется настройка идентификатора.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim currCell As Range
    Set currCell = Target.Cells(1, 1)

    Dim currId As String
    currId = currCell.ID

    If Trim(currCell.ID) = "" Then
        Target.Parent.Unprotect
        currCell.ID = CStr(gNextUniqueId)
        Target.Parent.Protect
        gNextUniqueId = gNextUniqueId + 1
    End If

End Sub