Оптимизация работы алгоритма обработки диапазона ячеек Excel / Visual Basic

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Visual Basic [игнор отключен] [закрыт для гостей] / Оптимизация работы алгоритма обработки диапазона ячеек Excel

7 сообщений из 7, страница 1 из 1

Оптимизация работы алгоритма обработки диапазона ячеек Excel

#39326344

chikaginsk

Участник

Сообщения: 117

Рейтинг: 0 / 0

Всем привет!
Для документа xls (97-2003) написал код макроса, который для каждого листа документа обрабатывает строки по следующим условиям:
- если в столбцах A, B, C текущей строки ячейки пустые, то убираем верхнюю границу в ячейках этих столбцов у этой строки;
- если в столбце D текущей строки ячейка заполнена словом "Всего", то делаем шрифт этой ячейки полужирным у этой строки.
Алгоритм следующий:

Код: vbnet

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.

Private Sub Workbook_Open()

Dim lLastRow As Long 'последняя заполненная строка на листе
Dim lCurRow As Long 'текущая строка
Dim i As Integer 'номер листа

For i = 2 To Sheets.Count
    
    Sheets(i).Activate
    
    lLastRow = Cells(Rows.Count, 1).End(xlUp).Row 'последняя строка листа
    lLastRow = lLastRow - 1 'предпоследняя строка листа
    
    If lLastRow > 13 Then
    
    For lCurRow = 13 To lLastRow
        
        'границы ячеек
        If Cells(lCurRow, 1) = "" And Cells(lCurRow, 2) = "" And Cells(lCurRow, 3) = "" And lCurRow > 13 Then
            Range(Cells(lCurRow, 1), Cells(lCurRow, 3)).Borders(xlEdgeTop).LineStyle = xlLineStyleNone
        End If
        
          'выделение шрифта полужирным
        If Cells(lCurRow, 4) = "Всего" Then
            Cells(lCurRow, 4).Font.Bold = True
        End If
        
    Next
    
    End If

Next i

Sheets(2).Activate

End Sub

Сильно не пинайте, только начал осваивать VBA.
Вопрос вот в чём: можно ли как-то оптимизировать (ускорить выполнение) данный код?
В xls документе бывает очень много строк (65000 и больше), да ещё и не на одном листе.
Может как-то можно выделять весь диапазон строк для столбцов A, B, C, D и по условиям вносить изменения в нужных ячейках диапазона, а не перебирать строки диапазона по-порядку?

...

Рейтинг:

0 / 0

13.10.2016, 14:49

| Ответить | Цитировать | Написать

Оптимизация работы алгоритма обработки диапазона ячеек Excel

#39326436

Shocker.Pro

Участник

Откуда: ->|<- :адуктО

Сообщения: 23 134

Рейтинг: 0 / 0

для начала попробуйте application.ScreenUpdating=False перед началом обработки и application.ScreenUpdating=True по окончании.

...

Рейтинг:

0 / 0

13.10.2016, 16:17

| Ответить | Цитировать | Написать

Оптимизация работы алгоритма обработки диапазона ячеек Excel

#39326547

by-pass

Участник

Откуда: Москва, Зеленоград

Сообщения: 367

Рейтинг: 0 / 0

chikaginsk,

Перебор всегда будет долгим. Можно попробовать использовать встроенный механизм Find. Попробуйте записать макрос в своем экселе и посмотреть что он делает.

...

Рейтинг:

0 / 0

13.10.2016, 17:57

| Ответить | Цитировать | Написать

Оптимизация работы алгоритма обработки диапазона ячеек Excel

#39326560

Казанский

Гость

chikaginsk, попробуйте кусок "границы ячеек" в таком виде:

Код: vbnet

1.
2.
3.
4.
5.
6.

        'границы ячеек
        With Cells(lCurRow, 1).Resize(, 3)
          If .Text = "" And lCurRow > 13 Then
            .Borders(xlEdgeTop).LineStyle = xlLineStyleNone
          End If
        End With

...

Рейтинг:

0 / 0

13.10.2016, 18:10

| Ответить | Цитировать | Написать

Оптимизация работы алгоритма обработки диапазона ячеек Excel

#39326579

Казанский

Гость

А еще лучше получить массив значений True/False, вычислив в коде формулу листа типа =A1:A19&B1:B19&C1:C19="" (здесь lLastRow=19). И значения ст. D тоже загрузить в массив.

Код: vbnet

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.

Private Sub Workbook_Open()

Dim lLastRow As Long  'последняя заполненная строка на листе
Dim lCurRow As Long   'текущая строка
Dim i As Integer      'номер листа
Dim v(), d()          'массив значений, возвращаемый формулой, и значения ст. D

For i = 2 To Sheets.Count
  lLastRow = Cells(Rows.Count, 1).End(xlUp).Row - 1 'предпоследняя строка листа
    
  If lLastRow > 13 Then
    v = Evaluate(Replace("A1:A~&B1:B~&C1:C~=""""", "~", lLastRow))
    d = Range("D1:D" & lLastRow).Value
    For lCurRow = 13 To lLastRow
        
        'границы ячеек
        If v(lCurRow, 1) And lCurRow > 13 Then
            Cells(lCurRow, 1).Resize(, 3).Borders(xlEdgeTop).LineStyle = xlLineStyleNone
        End If
        
          'выделение шрифта полужирным
        If d(lCurRow, 1) = "Всего" Then
            Cells(lCurRow, 4).Font.Bold = True
        End If
        
    Next
  End If
Next i

Sheets(2).Activate

End Sub

Вообще, метод Evaluate учитывает текущий стиль ссылок приложения (А1 или R1C1). Поэтому формулу лучше привести к текущему стилю ссылок, "обернув" ее в метод ConvertFormula:

Код: vbnet

    v = Evaluate(Application.ConvertFormula(Replace("A1:A~&B1:B~&C1:C~=""""", "~", lLastRow), xlA1, Application.ReferenceStyle))

...

Рейтинг:

0 / 0

13.10.2016, 18:49

| Ответить | Цитировать | Написать

Оптимизация работы алгоритма обработки диапазона ячеек Excel

#39326708

Antonariy

Участник

Откуда: ☭

Сообщения: 80 221

Рейтинг: 0 / 0

Код: vbnet

1.
2.
3.

For lCurRow = 13 To lLastRow
    Cells(lCurRow, 1)
    Cells(lCurRow, 4)

Вместо этого получить Range из строк, обойти его через For Each row In и обращаться к ячейке как row.Cells(1)

Так можно сэкономить еще пару копеек

...

Рейтинг:

0 / 0

14.10.2016, 02:05

| Ответить | Цитировать | Написать

Оптимизация работы алгоритма обработки диапазона ячеек Excel

#39326772

chikaginsk

Участник

Сообщения: 117

Рейтинг: 0 / 0

Благодарю за наводки. Буду пробовать представленные варианты. Пока с кодом в примерах разберусь.

...

Рейтинг:

0 / 0

14.10.2016, 08:55

| Ответить | Цитировать | Написать

7 сообщений из 7, страница 1 из 1

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=60&fpage=19&tid=2155412]:	0ms
get settings:	9ms
get forum list:	15ms
check forum access:	4ms
check topic access:	4ms
track hit:	39ms
get topic data:	12ms
get forum data:	3ms
get page messages:	50ms
get tp. blocked users:	2ms
others:	10ms

total:	148ms