Блог

Главная - Вопросы по программированию - Создать изменчивую функцию Excel, которая запускается при изменении формата

Создать изменчивую функцию Excel, которая запускается при изменении формата

#excel #function

#excel #функция

Вопрос:

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

 Function SUMNOTSTRIKE(rng As Range)
    Dim cell As Range
    For Each cell In rng
        If Not (cell.Font.StrikeThrough) Then
            SUMNOTSTRIKE = SUMNOTSTRIKE   cell.Value
        End If
    Next cell
End Function

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

Я кое-что читал Application.Volatile , но это работает только при изменении значения. Мне нужно, чтобы функция запускалась при изменении формата.

Комментарии:

1. Я не думаю, что это возможно. Вам нужно будет найти прокси (например, написать обработчик событий, который нацелен на событие изменения выбора.) Или — возможно, запускать вашу функцию автоматически каждые несколько секунд.

2. Понял, спасибо!!

Ответ №1:

Расширяя мой комментарий. Если вы добавите

 Application.Volatile

в определение вашей функции (по соглашению, в качестве первой строки), а затем в модуле кода рабочего листа:

 Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.Calculate
End Sub

Затем при каждом изменении выбора все изменяемые функции будут пересчитаны. Пересчета не будет, если вы просто измените зачеркивание выбранной ячейки и оставите эту ячейку выбранной, но в тот момент, когда вы перейдете к другой ячейке, ваша функция может быть обновлена.

В качестве предупреждения, запуск Application.Calculate все время может замедлить работу Excel. В зависимости от вашей ситуации это может быть неадекватным решением.

Комментарии:

1. Он не работает. Эта частная функция должна применяться где-то конкретно или выполняться автоматически?

2. Функция SUMNOTSTRIKE должна находиться в стандартном модуле кода (который вы создали Insert > Module в редакторе VBA), а Worksheet_SelectionChange вспомогательный компонент должен находиться в модуле кода рабочего листа, который уже автоматически находится в редакторе, для рабочего листа, содержащего формулу. Я протестировал ее, и она должна работать, как описано.

3. СПАСИБО, МНОГО ПОРАБОТАЛ!!

Ответ №2:

Это вариант отличного предложения Джона. Допустим, у нас есть данные от A1 до A100.

Мы можем использовать событие двойного щелчка для обоих:

  • зачеркивание текста ячейки
  • принудительное повторное вычисление

Введите следующий макрос события в область кода рабочего листа:

 Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Target, Range("A1:A100")) Is Nothing Then Exit Sub
    Cancel = True
    Target.Font.Strikethrough = True
    Application.CalculateFull
End Sub

Двойной щелчок по интересующей ячейке приведет к ее удалению и принудительному повторному вычислению.

Поскольку это код рабочего листа, его очень легко установить и использовать автоматически:

  1. щелкните правой кнопкой мыши название вкладки в нижней части окна Excel
  2. выберите Просмотр кода — откроется окно VBE
  3. вставьте содержимое и закройте окно VBE

Если у вас есть какие-либо проблемы, сначала попробуйте это на пробном листе.

Если вы сохраните книгу, макрос будет сохранен вместе с ней. Если вы используете версию Excel более поздней, чем 2003, вы должны сохранить файл как .xlsm, а не .xlsx

Чтобы удалить макрос:

  1. откройте окна VBE, как указано выше
  2. очистите код
  3. закройте окно VBE

Чтобы узнать больше о макросах в целом, см.:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

и

http://msdn.microsoft.com/en-us/library/ee814735 (v=office.14).aspx

Чтобы узнать больше о макросах событий (коде рабочего листа), см.:

http://www.mvps.org/dmcritchie/excel/event.htm

Для этого должны быть включены макросы!

Комментарии:

1. Мне нравится этот подход. Это убивает двух зайцев одним выстрелом, поскольку добавление зачеркиваний через обычный пользовательский интерфейс несколько раздражает.