Excel VBA — какие-либо преимущества в производительности между диапазоном константных строк или определением имен для диапазонов?

Добрый день,

У меня есть вопрос, который поможет мне лучше понять, как Excel VBA может эффективно управлять определенными диапазонами, которые были объявлены в одном месте, чтобы хорошо выполнять данные. Просто хочу выяснить, какие два варианта (я знаю до сих пор) лучше или нет в качестве предпочтительной передовой практики, прежде чем продолжить работу над этим проектом.

Проблема, которую я решаю, состоит в том, чтобы сделать небольшую таблицу, содержащую количество сбоев в наборе вымышленных поставщиков, поэтому таблица выглядит так (извините, что в необработанном виде)

"Название компании" "Количество отказов"
"Be Cool Machine" 7
"Кварталы охлаждающей жидкости" 5
"Little Water Coolants 3
"Системы подачи воздуха" 7
"Генеральные охлаждающие жидкости" 5
"Охлаждающие жидкости Admire" 4

Мой первый вариант (Const String) — это модуль/формула следующим образом.

Option Explicit
Public Const CountofFailures As String = "J7:J12"
Sub btnRandom()
    ' Declaration of variables
    Dim c As Range

    ' Provide a random number for failures across Suppliers
    For Each c In ActiveSheet.Range(CountofFailures)
        c.Value = Random1to10
    Next c
End Sub

Function Random1to10() As Integer
    'Ensure we have a different value each time we run this macro
    Randomize
    ' Provide a random number from 1 to 10 (Maximum number of Failures)
    Random1to10 = Int(Rnd() * 10 + 1)
End Function

Второй вариант (определяемое имя) — это модуль/формула следующим образом.

Option Explicit
Sub btnRandom()
    ' Declaration of variables
    Dim c As Range
    Dim iLoop As Long

    ' Provide a random number for Suppliers with Defined Range
    For Each c In ActiveWorkbook.Names("CountofFailures").RefersToRange
        c.Value = Random1to10
    Next c
End Sub

Function Random1to10() As Integer
    'Ensure we have a different value each time we run this macro
    Randomize
    ' Provide a random number from 1 to 10 (Maximum number of Failures)
    Random1to10 = Int(Rnd() * 10 + 1)
End Function

Любые предложения - я бы сделал тест таймера макроса позже, если это поможет?

Будет ли третий вариант, если я выберу диапазон, указанный в ячейке, как значение? Я не видел кода, который делает это на практике?


performance excel named-ranges vba defined
person Peter M Taylor    schedule 23.12.2012    source источник

Ответы (3)


arrow_upward
3
arrow_downward

Оба ваших кода проходят через диапазоны, которые будут узким местом. я предлагаю тебе

  1. Используйте имя диапазона для автоматического "нахождения" ваших данных - т.е. если вы вставляете/удаляете строки и столбцы, ваша ссылка остается неизменной. Мой опыт, однако, заключается в том, что многие имена диапазонов в файле могут в конечном итоге запутать то, что делает рабочая книга.
  2. Сделайте одну запись в этот диапазон

код

Sub QuickFill()
Randomize
Range("CountofFailures").Formula = "=Randbetween(1,10)"
Range("CountofFailures").Value = Range("CountofFailures").Value
End Sub
person brettdj    schedule 23.12.2012
comment
Бретт, я был бы прав, если бы сказал, что последняя строка .value = .value такая же, как копирование, вставка специальных равных значений? - person Peter M Taylor; 23.12.2012
comment
Да - он преобразует формулы в значения - person brettdj; 24.12.2012
comment
так и думал, спасибо за совет, Бретт. Я организую подпрограммы, чтобы следовать этому общему шаблону. - person Peter M Taylor; 29.12.2012

arrow_upward
5
arrow_downward

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

Что касается именованных диапазонов, преимущество заключается в том, что они перемещаются при вставке строк и столбцов. Если вы вставите новый столбец в столбец I, ваш первый пример необходимо отредактировать, и ваш второй пример продолжит работать.

person Dick Kusleika    schedule 23.12.2012
comment
хорошо. Я перестану думать о производительности, пока не узнаю, что у меня проблемы с производительностью. - person Peter M Taylor; 23.12.2012

arrow_upward
1
arrow_downward

Я обнаружил, что именованные диапазоны работают медленнее (предположительно, потому, что Excel должен выполнять внутренний поиск по имени, чтобы найти, к чему оно относится), но очень маловероятно, что вы сможете найти существенное различие, за исключением очень крайних случаев (десятки тысячи имен упоминаются десятки тысяч или сотни тысяч раз).
И, как говорит Дик: преимущества намного перевешивают незначительную потерю скорости.

person Charles Williams    schedule 23.12.2012