Excel VBA: варианты переменных массива

Вопрос о вариантах. Мне известно, что варианты в Excel vba являются стандартным типом данных, а также неэффективны (с точки зрения чрезмерного использования в больших приложениях). Однако я регулярно использую их для хранения данных в массивах с несколькими типами данных. Текущий проект, над которым я работаю, по существу является задачей, требующей огромного оптимизма очень плохого кода (c.7000 строк) - и это заставило меня задуматься; есть ли способ обойти это?

Объяснить; код часто хранит данные в переменных массива. Поэтому рассмотрим набор данных из 10 столбцов на 10000. Столбцы представляют собой несколько разных типов данных (строка, double, целые числа, даты и т.д.). Предполагая, что я хочу хранить их в массиве, я обычно:

dim myDataSet(10,10000) as variant

Но, мои знания говорят, что это будет действительно неэффективно с кодом, оценивающим каждый элемент, чтобы определить, какой тип данных он (когда на практике я знаю, что Я ожидаю). Кроме того, я теряю контроль, который дает мне определение отдельных типов данных. Итак, (если первые 6 строк, следующие 4 двойки для удобства объяснения точки), я мог бы;

dim myDSstrings(6,10000) as string
dim myDSdoubles(4,10000) as double

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

myDSstrings(1,r) = cells(r,1)
myDSdoubles(2,r) = cells(r,2)
myDSstrings(2,r) = cells(r,3)
myDSstrings(3,r) = cells(r,4)
myDSdoubles(3,r) = cells(r,5)
..etc...

Что гораздо более уродливо, чем:

myDataSet(c,r) = cells(r,c)

Итак, это заставило меня задуматься - я, должно быть, кое-что упустил. Каков оптимальный способ хранения массива разных типов данных? Или, полагая, что нет способа сделать это - что было бы лучше всего использовать для кодирования массива смешанных типов данных?

Ответ 1

Никогда не оптимизируйте свой код без предварительного измерения. Вы можете быть удивлены, где код самый медленный. Я использую служебную программу PerfMon из Professional Excel Development, но вы также можете использовать ее самостоятельно.

Чтение и запись в и из диапазонов Excel - это большой приемник. Несмотря на то, что Варианты могут тратить много памяти, это

Dim vaRange as Variant
vaRange = Sheet1.Range("A1:E10000").Value
'do something to the array
Sheet1.Range("A1:E10000").Value = vaRange

обычно быстрее, чем цикл через строки и ячейки.

Мой предпочтительный метод использования массивов с несколькими типами данных состоит в том, чтобы вообще не использовать массивы. Скорее, я буду использовать собственный модуль класса и создать свойства для этих элементов. Это не обязательно повышает производительность, но делает код намного проще писать и читать.

Ответ 2

Я не уверен, что ваше узкое место исходит из набора Variant вашего массива.

Кстати, чтобы установить значения из массива в диапазон Excel, вы должны использовать (в Excel 8 или выше):

Range("A1:B2") = myArray

В предыдущих версиях вы должны использовать следующий код:

Sub SuperBlastArrayToSheet(TheArray As Variant, TheRange As Range)
  With TheRange.Parent.Parent 'the workbook the range is in
    .Names.Add Name:="wstempdata", RefersToR1C1:=TheArray
    With TheRange
      .FormulaArray = "=wstempdata"
      .Copy
      .PasteSpecial Paste:=xlValues
    End With
    .Names("wstempdata").Delete
  End With
End Sub

из этот источник, который вы должны прочитать для оптимизации VBA.

Тем не менее, вы должны профилировать свое приложение, чтобы узнать, где ваши узкие места. См. этот вопрос из Issun, чтобы помочь вам сравнить ваш код.