Определяемые пользователем функции в Excel и проблемах скорости

Управление и минимизация перерасчетов с помощью

wks.EnableCalculation = False

или

Application.Calculation = xlCalculationManual

Кроме того, минимизируйте обмены между VBA и рабочими книгами. Быстрее читать и записывать блоки ячеек сразу в массив

MyArray = range("B2:B20000") 

, а не ячейку за ячейкой (для каждой ...).

Пара общих советов

1. Возьмите свою функцию и определите, где на самом деле есть узкие места. См. Этот вопрос для использования таймера в excel. Я уверен, что существуют профилировщики VBA ... но вам, вероятно, не нужно заходить так далеко. (NB: сначала сделайте это с одной ячейкой данных ...)

2. Подумайте о своем дизайне ... 400x120 ячеек данных - это не много. И чтобы это длилось часами, это должно быть болезненно. ( Раньше я взламывал его, подождав минуту, пока 1000 секунд VLOOKUPS () вернут ) в любом случае, возможно, вместо того, чтобы иметь стек UDF, почему бы не создать простой подпрограмма, которая для .. каждого в диапазоне и делает то, что вам нужно. 48000 ячеек могут занять секунды, а может и минуты. Затем вы можете связать подпрограмму с кнопкой или пунктом меню для пользователя.

Из интереса я быстро просмотрел вариант 2 и создал MyUDF (), используя подпрограмму DoMyUDF () , чтобы вызвать его для активного выбора, у меня работало в 10 раз быстрее, чем при использовании UDF в каждая ячейка.

Option Explicit



Function MyUDF(myVar As Variant) As Variant
  MyUDF = myVar * 10
End Function

Sub DoMyUDF()
  Dim r As Range
  Dim c As Variant

  Dim t As Single
  t = Timer


  If TypeName(Selection) <> "Range" Then
    Exit Sub
  End If


  Set r = Selection.Cells

  Application.DisplayStatusBar = True

  For Each c In r
    c.Value = MyUDF(c.Value)

    Application.StatusBar = "DoMyUDF(): " & Format(Timer - t, "#0.0000ms")
  Next

  Debug.Print "DoMyUDF(): " & Format(Timer - t, "#0.0000ms")

End Sub

Если вы замените MyUDF () своим UDF, это может сэкономить вам всего 4,5 минуты ... но, возможно, вы можете использовать и другие способы экономии. Особенно, если вы повторяя одни и те же вычисления снова и снова.

Есть ошибка замедления в способе обработки Excel UDF. Каждый раз, когда вычисляется UDF, Excel обновляет строку заголовка VBE (вы можете видеть, как она мерцает). Для большого количества UDF это очень медленно. Обойти очень просто в режиме ручного расчета: просто запустите расчет из VBA, используя что-то вроде Application.Calculate (вы можете перехватить F9 и т. Д. С помощью OnKey).

см. http://www.decisionmodels.com/calcsecretsj .htm для получения дополнительных сведений.

Рассматривали ли вы замену UDF (которая вызывается один раз для каждой выходной ячейки) макросом, который может работать с диапазоном ячеек в цикле?

Установка / удаление UDF очень медленно, и все, что каждый вызов UDF делает совместно с другими UDF (например, чтение из перекрывающихся входов), становится дополнительным усилием.

Я смог повысить производительность в 10-50 раз, делая это - Менее месяца назад возникла ситуация с электронной таблицей с 4000-30000 вызовов UDF, и они были заменены одним макросом, который работает с несколькими именованными диапазонами.

Убедитесь, что вы начнете Recalc из VBA, а не из электронной таблицы, см. http://msdn.microsoft.com/en-us/library/aa730921.aspx#office2007excelperf_overview и раздел о более быстрой функциях, определенных пользователем VBA. То есть Application.calculate. намного быстрее (в моем тестовом случае 100 раз), чем нажатие F9 в таблице.