Excel VBA, Программирующий с Массивами: Передать их или Не Передать их?

Похоже, что Excel и массивы - не самые лучшие инструменты для выполняемой работы. Если бы вы могли немного объяснить, с каким типом данных вы работаете и что вы делаете, это действительно поможет получить лучший ответ. Расскажите как можно подробнее о типах манипуляций с данными и о том, что такое входы и выходы.

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

• Существует объект, который естественным образом обрабатывает объекты типа Recordset, с которыми вы работаете, называемый Recordset. В редакторе VBA перейдите в Tools -> References (Инструменты -> Ссылки) и добавьте библиотеку Microsoft ActiveX Data Objects 2.X (самая высокая на вашей машине). Вы можете объявить объект типа ADODB.Recordset, затем сделать Recordset.Fields.Append, чтобы добавить к нему поля, затем .Open it и, наконец, .AddNew, установить значения полей, и .Update. Это естественный объект для передачи в программах в качестве входного или выходного параметра. Он имеет естественные функции обхода и позиционирования (.Eof, .Bof, .AbsolutePosition, .MoveNext, .MoveFirst, .MovePrevious) и поддерживает поиск и фильтрацию (.Filter = "Field = 'abc'", .Find и т.д.).

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

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

• Если вы хотите улучшить производительность вашего кода, ударьте ctrl-брейк в случайное время во время его работы и взломайте код. Затем нажмите Ctrl-L, чтобы просмотреть стек вызовов. Каждый раз запишите, что находится в списке. Если какой-либо пункт отображается большую часть времени, это узкое место, и именно там вы должны потратить свое время, пытаясь оптимизировать его. Однако, я не советую пытаться оптимизировать то, что у вас есть, до тех пор, пока вы не примете некоторые решения более высокого уровня (например, будете ли вы переключаться на набор записей).

Мне действительно нужно больше информации, чтобы помочь вам лучше.

Если вам интересно, я разработаю демонстрационный код, который покажет, насколько полезен объект Recordset. Вставить данные из Recordset в диапазон Excel очень просто с помощью Recordset.GetRows или .GetString (хотя может потребоваться некоторое переложение массива, это тоже не сложно).

UPDATE: Если ваша цель - ускорить процесс, то перед тем, как что-либо делать, я думаю, что лучше всего вооружиться знаниями о том, что занимает больше всего времени. Не могли бы вы, пожалуйста, нажать ctrl-разрыв около 10 раз и каждый раз записывать в стек вызовов, а затем сказать мне, какие наиболее распространенные элементы в стеке вызовов?

С точки зрения обновления скорости форматирования ячеек, вот мой опыт:

1. Слияние - это самая медленная операция, которую вы можете сделать. Постарайтесь избегать ее, если это вообще возможно. Использование "center across selection" является одной из альтернатив. Другая - это просто не слияние, а использование некоторой комбинации правильного размера, границ, цвета фона ячейки, а также отключение линий сетки для всей рабочей книги.

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

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

Кроме того, я бы не стал автоматически использовать классы. Хотя OO - это здорово, и я делаю это сам (черт возьми, я только что построил 8 классов для чего-то на днях, чтобы смоделировать иерархическую структуру, чтобы я мог легко разоблачать ее части, когда они мне понадобятся), на практике это может быть медленнее. Простой набор публичных переменных в классе быстрее, чем использование геттеров и сеттеров. Определенный пользователем тип еще быстрее, чем класс, но можно столкнуться с геттерами, пытающимися передать UDT в классах (они должны быть объявлены в неклассовом публичном модуле, и даже тогда они могут дать проблемы).

Эрик

Вы предоставили немного подробнее, но все еще довольно сложно понять, что именно происходит, не видя некоторый код. В вашем вопросе я могу определить как минимум 4 больших тем, которые вы переплетете по всему: производство, доступ к данным, VBA и кодированию лучших практик. Мне трудно рассказать именно то, что вы спрашиваете, потому что ваш вопрос охватывает огромный. В любом случае, я ценю вашу попытую написать лучший код в VBA.

Мне трудно точно понять, что вы планируете делать с массивами. Вы говорите:

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

Я не уверен, что вы имеете в виду здесь. Вы используете массивы, чтобы представлять строку данных, которые вы извлекали из базы данных? Если это так, вы можете рассмотреть возможность использования модулей классов вместо обычных «макроменных» модулей. Они позволят вам работать с полноценными объектами вместо массивов значений (или ссылок, в зависимости от обстоятельств). Классы принимают больше работы для настройки и потребления, но они заставляют ваш код намного проще работать и очень поможет вам сеть свой код.

Как уже отметил, что пользователь Emtucifor уже указан, могут быть такие объекты, как ADO Recordseet объекты (которые могут потребовать доступа для установки ... не уверен), которые могут помочь значительно. Или вы можете создать свой собственный.

Вот длительный пример того, как использование класса может помочь вам. Хотя этот пример длительный, он покажет вам, как несколько принципов объектно-ориентированного программирования действительно могут помочь вам очистить свой код.

В редакторе VBA перейдите в Modeule> Class Module . В окне свойств (по умолчанию внизу левый экран) измените имя модуля на Worklogitem . Добавьте следующий код в класс:

Option Explicit

Private pTaskID As Long
Private pPersonName As String
Private pHoursWorked As Double

Public Property Get TaskID() As Long
    TaskID = pTaskID
End Property

Public Property Let TaskID(lTaskID As Long)
    pTaskID = lTaskID
End Property

Public Property Get PersonName() As String
    PersonName = pPersonName
End Property

Public Property Let PersonName(lPersonName As String)
    pPersonName = lPersonName
End Property

Public Property Get HoursWorked() As Double
    HoursWorked = pHoursWorked
End Property

Public Property Let HoursWorked(lHoursWorked As Double)
    pHoursWorked = lHoursWorked
End Property

Приведенный выше код даст нам сильно напечатанный объект, который специфичен для данных, с которыми мы работаем. Когда вы используете многоразмерные массивы для хранения данных, ваш код напоминает это: ART (1,1) - это идентификатор, ARR (1,2) - это персонал, и ARR (1,3) - это работа. Используя этот синтаксис, трудно знать, что такое. Предположим, вы все еще загружаете свои объекты в массив, но вместо этого используете WorkLogitem , который мы создали выше. Это имя, вы сможете сделать ARM (1) .Personname , чтобы получить имя человека. Это делает ваш код намного проще прочитать.

Давайте продолжим двигаться с этим примером. Вместо того, чтобы сохранить объекты в массиве, мы попробуем использовать коллекцию .

Далее добавьте новый класс модуля и вызовите его Processworklog . Поместите следующий код:

Option Explicit

Private pWorkLogItems As Collection

Public Property Get WorkLogItems() As Collection
    Set WorkLogItems = pWorkLogItems
End Property

Public Property Set WorkLogItems(lWorkLogItem As Collection)
    Set pWorkLogItems = lWorkLogItem
End Property

Function GetHoursWorked(strPersonName As String) As Double
    On Error GoTo Handle_Errors
    Dim wli As WorkLogItem
    Dim doubleTotal As Double
    doubleTotal = 0
    For Each wli In WorkLogItems
        If strPersonName = wli.PersonName Then
            doubleTotal = doubleTotal + wli.HoursWorked
        End If
    Next wli

Exit_Here:
    GetHoursWorked = doubleTotal
        Exit Function

Handle_Errors:
        'You will probably want to catch the error that will '
        'occur if WorkLogItems has not been set '
        Resume Exit_Here


End Function

Вышеуказанный класс будет использоваться для «сделать что-то» с коллектором работы Worklogitem . Первоначально мы просто устанавливаем его, чтобы рассчитывать на общее количество работающих часов. Давайте проверим код, который мы написали. Создайте новый модуль (не класс модуля на этот раз; просто «регулярный» модуль). Вставьте следующий код в модуле:

Option Explicit

Function PopulateArray() As Collection
    Dim clnWlis As Collection
    Dim wli As WorkLogItem
    'Put some data in the collection'
    Set clnWlis = New Collection

    Set wli = New WorkLogItem
    wli.TaskID = 1
    wli.PersonName = "Fred"
    wli.HoursWorked = 4.5
    clnWlis.Add wli

    Set wli = New WorkLogItem
    wli.TaskID = 2
    wli.PersonName = "Sally"
    wli.HoursWorked = 3
    clnWlis.Add wli

    Set wli = New WorkLogItem
    wli.TaskID = 3
    wli.PersonName = "Fred"
    wli.HoursWorked = 2.5
    clnWlis.Add wli

    Set PopulateArray = clnWlis
End Function

Sub TestGetHoursWorked()
    Dim pwl As ProcessWorkLog
    Dim arrWli() As WorkLogItem
    Set pwl = New ProcessWorkLog
    Set pwl.WorkLogItems = PopulateArray()
    Debug.Print pwl.GetHoursWorked("Fred")

End Sub

в вышеуказанном коде populatearray () просто создает коллекцию Worklogitem . В вашем реальном коде вы можете создать класс для анализа листов Excel или объекты данных для заполнения коллекции или массива.

Код TestGethoursWorked () код просто демонстрирует, как использовались классы. Вы замечаете, что Processworklog создается в качестве объекта. После того, как он создается, коллекция Worklogitem становится частью объекта PWL . Вы замечаете это в строке SET PWL.Worklogtems = PopulatearRay () . Далее мы просто называем функцию, которую мы написали, которая действует на коллекцию Worklogtrogitems .

Почему это полезно?

Давайте предположим, что ваши изменения данных, и вы хотите добавить новый метод. Предположим, ваш Worklogitem теперь включает в себя поле для TOONOk , и вы хотите добавить новый метод для расчета этого.

Все, что вам нужно сделать, это добавить свойство в Worklogitem , как так:

Private pHoursOnBreak As Double

Public Property Get HoursOnBreak() As Double
    HoursOnBreak = pHoursOnBreak
End Property

Public Property Let HoursOnBreak(lHoursOnBreak As Double)
    pHoursOnBreak = lHoursOnBreak
End Property

, конечно, вам нужно изменить свой способ заполнения вашей коллекции (используемый мной метод образца Populatearray () , но вы, вероятно, должны иметь отдельный класс только для этого). Тогда вы просто добавляете свой новый метод в свой ChangeWorklog класс:

Function GetHoursOnBreak(strPersonName As String) As Double
     'Code to get hours on break
End Function

Теперь, если мы хотели обновить наши TestGethoursWorked () метод возврата результата Gethoursonbreak ,Все, что нам было бы сделать, чтобы добавить следующую строку:

    Debug.Print pwl.GetHoursOnBreak("Fred")

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

Для дальнейшего чтения я бы очень рекомендую получить копию справочника VBA, 2-е издание . Книга полна отличных примеров и лучших практик и тонн кода образца. Если вы инвестируете много времени на VBA для серьезного проекта, он стоит ваше время посмотреть в эту книгу.