Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/команда Offset замедляет работу - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по VBA » команда Offset замедляет работу (Макросы/Sub)
команда Offset замедляет работу
aivella Дата: Вторник, 07.04.2020, 12:39 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 37
Репутация: 10 ±
Замечаний: 20% ±

Excel 2013
Добрый день!

Есть такой код:

[vba]
Код
Dim z As Range
For Each z In ActiveSheet.Range("E115:E155")
If IsEmpty(z.Offset(0, 0)) Then
z = z.Offset(0, -2)
End If
Next z
[/vba]

И вот незадача, как только заданный массив (ActiveSheet.Range) превышает 40 строк, таблица тормозит, подвисает минуты на две.

В идеале массив должен быть 300 строк (Е30:Е330). Есть ли варианты, как применять команду, не замедляя работу таблицы?

Заранее спасибо!

Елена.


Сообщение отредактировал Pelena - Вторник, 07.04.2020, 16:48
 
Ответить
СообщениеДобрый день!

Есть такой код:

[vba]
Код
Dim z As Range
For Each z In ActiveSheet.Range("E115:E155")
If IsEmpty(z.Offset(0, 0)) Then
z = z.Offset(0, -2)
End If
Next z
[/vba]

И вот незадача, как только заданный массив (ActiveSheet.Range) превышает 40 строк, таблица тормозит, подвисает минуты на две.

В идеале массив должен быть 300 строк (Е30:Е330). Есть ли варианты, как применять команду, не замедляя работу таблицы?

Заранее спасибо!

Елена.

Автор - aivella
Дата добавления - 07.04.2020 в 12:39
Pelena Дата: Вторник, 07.04.2020, 13:05 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 18555
Репутация: 4202 ±
Замечаний: ±

Excel 2016 & Mac Excel
aivella, оформите код тегами с помощью кнопки # в режиме правки поста.
Offset(0, 0) точно можно убрать.
И да, менять параметр цикла z в цикле - плохая идея. Думаю, дело не в Offset


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщениеaivella, оформите код тегами с помощью кнопки # в режиме правки поста.
Offset(0, 0) точно можно убрать.
И да, менять параметр цикла z в цикле - плохая идея. Думаю, дело не в Offset

Автор - Pelena
Дата добавления - 07.04.2020 в 13:05
aivella Дата: Вторник, 07.04.2020, 14:17 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 37
Репутация: 10 ±
Замечаний: 20% ±

Excel 2013
Благодарю!

Сообщение отредактировала.

Если убрать Offset(0,0), то какой будет код?

Спасибо еще раз!
 
Ответить
СообщениеБлагодарю!

Сообщение отредактировала.

Если убрать Offset(0,0), то какой будет код?

Спасибо еще раз!

Автор - aivella
Дата добавления - 07.04.2020 в 14:17
Pelena Дата: Вторник, 07.04.2020, 15:43 | Сообщение № 4
Группа: Админы
Ранг: Местный житель
Сообщений: 18555
Репутация: 4202 ±
Замечаний: ±

Excel 2016 & Mac Excel
Сообщение отредактировала

не совсем)
В режиме редактирования выделите код и нажмите кнопку с изображением # на панели инструментов. Потом сохраните изменения


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение
Сообщение отредактировала

не совсем)
В режиме редактирования выделите код и нажмите кнопку с изображением # на панели инструментов. Потом сохраните изменения

Автор - Pelena
Дата добавления - 07.04.2020 в 15:43
aivella Дата: Вторник, 07.04.2020, 16:28 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 37
Репутация: 10 ±
Замечаний: 20% ±

Excel 2013
Пока попробовала вот-что:

[vba]
Код
Dim zelle As Range
For Each z In ActiveSheet.Range("E150:E250")
If IsEmpty(z.Value) = True Then
z = z.Offset(0, -2)
End If
Next z
[/vba]

Терпимо работает для интервала в 100 клеток. Больше - тормозит. Но это уже тоже облегчение.


Сообщение отредактировал Pelena - Вторник, 07.04.2020, 16:49
 
Ответить
СообщениеПока попробовала вот-что:

[vba]
Код
Dim zelle As Range
For Each z In ActiveSheet.Range("E150:E250")
If IsEmpty(z.Value) = True Then
z = z.Offset(0, -2)
End If
Next z
[/vba]

Терпимо работает для интервала в 100 клеток. Больше - тормозит. Но это уже тоже облегчение.

Автор - aivella
Дата добавления - 07.04.2020 в 16:28
Hugo Дата: Вторник, 07.04.2020, 18:47 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3140
Репутация: 670 ±
Замечаний: 0% ±

2010, теперь уже с PQ
Отключите обновление экрана, и если Вам не нужно на изменение каждой ячейки где-то получать результат обсчёта этой ячейки формулами - то и пересчёт тоже есть смысл на время работы выключить.

Например вот, кардинально выключаем всё:
[vba]
Код
Dim global_var_calc_state As Integer, global_var_calc_bfr_sv_state As Boolean

Sub Kakoetonazvanie()
    Call uskorenie

    Dim zelle As Range
    For Each Z In ActiveSheet.Range("E150:E250")
        If IsEmpty(Z.Value) = True Then
            Z = Z.Offset(0, -2)
        End If
    Next Z

    Call neuskorenie
End Sub

Sub uskorenie()
    With Application
        global_var_calc_state = .Calculation
        global_var_calc_bfr_sv_state = .CalculateBeforeSave
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayStatusBar = False
        .DisplayAlerts = False
    End With
End Sub
Sub neuskorenie()
    With Application
        .Calculation = global_var_calc_state    ' xlCalculationAutomatic
        .CalculateBeforeSave = global_var_calc_bfr_sv_state
        .ScreenUpdating = True
        .EnableEvents = True
        .DisplayStatusBar = True
        .DisplayAlerts = True
    End With
End Sub

[/vba]


excel@nxt.ru
webmoney: R418926282008 Z422237915069


Сообщение отредактировал Hugo - Вторник, 07.04.2020, 18:51
 
Ответить
СообщениеОтключите обновление экрана, и если Вам не нужно на изменение каждой ячейки где-то получать результат обсчёта этой ячейки формулами - то и пересчёт тоже есть смысл на время работы выключить.

Например вот, кардинально выключаем всё:
[vba]
Код
Dim global_var_calc_state As Integer, global_var_calc_bfr_sv_state As Boolean

Sub Kakoetonazvanie()
    Call uskorenie

    Dim zelle As Range
    For Each Z In ActiveSheet.Range("E150:E250")
        If IsEmpty(Z.Value) = True Then
            Z = Z.Offset(0, -2)
        End If
    Next Z

    Call neuskorenie
End Sub

Sub uskorenie()
    With Application
        global_var_calc_state = .Calculation
        global_var_calc_bfr_sv_state = .CalculateBeforeSave
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayStatusBar = False
        .DisplayAlerts = False
    End With
End Sub
Sub neuskorenie()
    With Application
        .Calculation = global_var_calc_state    ' xlCalculationAutomatic
        .CalculateBeforeSave = global_var_calc_bfr_sv_state
        .ScreenUpdating = True
        .EnableEvents = True
        .DisplayStatusBar = True
        .DisplayAlerts = True
    End With
End Sub

[/vba]

Автор - Hugo
Дата добавления - 07.04.2020 в 18:47
aivella Дата: Вторник, 07.04.2020, 18:47 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 37
Репутация: 10 ±
Замечаний: 20% ±

Excel 2013
не совсем)
В режиме редактирования выделите код и нажмите кнопку с изображением # на панели инструментов. Потом сохраните изменения

спасибо за дополнительную редакцию и пояснения!

буду знать!


Сообщение отредактировал aivella - Вторник, 07.04.2020, 18:47
 
Ответить
Сообщениене совсем)
В режиме редактирования выделите код и нажмите кнопку с изображением # на панели инструментов. Потом сохраните изменения

спасибо за дополнительную редакцию и пояснения!

буду знать!

Автор - aivella
Дата добавления - 07.04.2020 в 18:47
aivella Дата: Вторник, 07.04.2020, 18:50 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 37
Репутация: 10 ±
Замечаний: 20% ±

Excel 2013
Спасибо за совет, Hugo!

"Отключите обновление экрана, и если Вам не нужно на изменение каждой ячейки где-то получать результат обсчёта этой ячейки формулами - то и пересчёт тоже есть смысл на время работы выключить."

это обходной путь... надеялась, что есть напрямую через облегчение кода...
 
Ответить
СообщениеСпасибо за совет, Hugo!

"Отключите обновление экрана, и если Вам не нужно на изменение каждой ячейки где-то получать результат обсчёта этой ячейки формулами - то и пересчёт тоже есть смысл на время работы выключить."

это обходной путь... надеялась, что есть напрямую через облегчение кода...

Автор - aivella
Дата добавления - 07.04.2020 в 18:50
Hugo Дата: Вторник, 07.04.2020, 18:52 | Сообщение № 9
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3140
Репутация: 670 ±
Замечаний: 0% ±

2010, теперь уже с PQ
Чем же обходной - это самый прямой :)
Код уже добавил выше.


excel@nxt.ru
webmoney: R418926282008 Z422237915069
 
Ответить
СообщениеЧем же обходной - это самый прямой :)
Код уже добавил выше.

Автор - Hugo
Дата добавления - 07.04.2020 в 18:52
Gustav Дата: Среда, 08.04.2020, 14:36 | Сообщение № 10
Группа: Друзья
Ранг: Старожил
Сообщений: 2138
Репутация: 840 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Можно попробовать сначала отобрать пустые ячейки при помощи метода SpecialCells, а затем уже без условия пройтись в цикле только по ним:
[vba]
Код
Sub test_zelle_1()
    Dim z As Range, rngEmp As Range
    
    On Error Resume Next
    Set rngEmp = ActiveSheet.Range("E150:E250").SpecialCells(xlCellTypeBlanks)
    If Err Then
        Err.Clear
        Exit Sub
    End If
    On Error GoTo 0
    
    For Each z In rngEmp.Cells
        z = z.Offset(0, -2)
    Next z
End Sub
[/vba]


Мой tip box - яд 41001663842605
 
Ответить
СообщениеМожно попробовать сначала отобрать пустые ячейки при помощи метода SpecialCells, а затем уже без условия пройтись в цикле только по ним:
[vba]
Код
Sub test_zelle_1()
    Dim z As Range, rngEmp As Range
    
    On Error Resume Next
    Set rngEmp = ActiveSheet.Range("E150:E250").SpecialCells(xlCellTypeBlanks)
    If Err Then
        Err.Clear
        Exit Sub
    End If
    On Error GoTo 0
    
    For Each z In rngEmp.Cells
        z = z.Offset(0, -2)
    Next z
End Sub
[/vba]

Автор - Gustav
Дата добавления - 08.04.2020 в 14:36
aivella Дата: Среда, 08.04.2020, 16:33 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 37
Репутация: 10 ±
Замечаний: 20% ±

Excel 2013
Огромное спасибо всем помощникам!!!

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

Попробовала решение Hugo. Обалденная удача!!! Работает!!! Ура!!!

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

Hugo, Вы меня спасли!!!

javascript://
 
Ответить
СообщениеОгромное спасибо всем помощникам!!!

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

Попробовала решение Hugo. Обалденная удача!!! Работает!!! Ура!!!

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

Hugo, Вы меня спасли!!!

javascript://

Автор - aivella
Дата добавления - 08.04.2020 в 16:33
Gustav Дата: Среда, 08.04.2020, 16:52 | Сообщение № 12
Группа: Друзья
Ранг: Старожил
Сообщений: 2138
Репутация: 840 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
значительного ускорения не добавил

Так у нас же всё общее, симбиотическое. Вы добавьте Call uskorenie / Call neuskorenie от Hugo (надеюсь, он не против) и в мой код - наверное, будет повеселее.


Мой tip box - яд 41001663842605
 
Ответить
Сообщение
значительного ускорения не добавил

Так у нас же всё общее, симбиотическое. Вы добавьте Call uskorenie / Call neuskorenie от Hugo (надеюсь, он не против) и в мой код - наверное, будет повеселее.

Автор - Gustav
Дата добавления - 08.04.2020 в 16:52
Hugo Дата: Среда, 08.04.2020, 20:10 | Сообщение № 13
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3140
Репутация: 670 ±
Замечаний: 0% ±

2010, теперь уже с PQ
Я не против. Тем более что это и не моё :) Где позаимствовал - каюсь не записал...
Но принцип общеизвестный - если многократно изменяются ячейки листа, то для ускорения нужно отключать все лишние реакции.


excel@nxt.ru
webmoney: R418926282008 Z422237915069
 
Ответить
СообщениеЯ не против. Тем более что это и не моё :) Где позаимствовал - каюсь не записал...
Но принцип общеизвестный - если многократно изменяются ячейки листа, то для ускорения нужно отключать все лишние реакции.

Автор - Hugo
Дата добавления - 08.04.2020 в 20:10
aivella Дата: Четверг, 09.04.2020, 11:39 | Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 37
Репутация: 10 ±
Замечаний: 20% ±

Excel 2013
Спасибо, ребята!

Вы - великолепны!!!
 
Ответить
СообщениеСпасибо, ребята!

Вы - великолепны!!!

Автор - aivella
Дата добавления - 09.04.2020 в 11:39
Мир MS Excel » Вопросы и решения » Вопросы по VBA » команда Offset замедляет работу (Макросы/Sub)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2022 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!