Блог

Главная - Вопросы по программированию - Эффективность изменения рабочего листа Excel VBA

Эффективность изменения рабочего листа Excel VBA

#vba #excel

#vba #excel

Вопрос:

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

Это событие изменения рабочего листа вызывает другой макрос тогда и только тогда, когда ячейка K4 = «Основана на событии» и в каждой из следующих ячеек что-то есть: J12: J15, M12: M14. Я могу вызвать макрос, если ячейки J12: J15, M12: M14 уже заполнены, а K4 изменен на «Основанный на событиях»

 Private Sub Worksheet_Change(ByVal Target As Range)
If Range("J12") <> "" And _
Range("J13") <> "" And _
Range("J14") <> "" And _
Range("J15") <> "" And _
Range("M12") <> "" And _
Range("M13") <> "" And _
Range("M14") <> "" Then
Dim ZRange As Range
Set ZRange = Range("K4")
If ZRange = "Event Based" Then
If Union(Target, ZRange).Address = ZRange.Address Then
Application.EnableEvents = False
Call EventBasedYes
Application.EnableEvents = True

End If
End If
End If

Моя проблема в том, что я написал другое событие изменения рабочего листа для каждой отдельной ячейки. Следующее срабатывает, если K4 =»Основано на событиях» и J13: J15 содержат что-то в себе, а затем данные добавляются в J12

 If Range("K4") = "Event Based" And _
Range("J13") <> "" And _
Range("J14") <> "" And _
Range("J15") <> "" And _
Range("M12") <> "" And _
Range("M13") <> "" And _
Range("M14") <> "" Then
Dim FRange As Range
Set FRange = Range("J12")
If FRange <> "" Then
If Union(Target, FRange).Address = FRange.Address Then
Application.EnableEvents = False
Call EventBasedYes
Application.EnableEvents = True

End If
End If
End If

Я не знаю, как написать ОДНО событие таким образом, чтобы, если все ячейки были заполнены, вызывался макрос, и таким образом, чтобы, если какая-либо ячейка была очищена, макрос с именем EventBasedNo. Извините, я не очень разбираюсь в коде VBA. Я уверен, что есть способ сделать это.

Комментарии:

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

2. I have written a different worksheet change event for each individual cell — как вы это сделали? На листе может быть только один обработчик.

3. Извините, GSerg, возможно, я был неясен. Я просто имел в виду, что включил код для каждого изменения в основное событие изменения рабочего листа.

Ответ №1:

Что касается улучшения скорости:

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

Итак, ваш текущий код гласит:

 If Range("J12") <> "" And _
   Range("J13") <> "" And _
   Range("J14") <> "" And _
   Range("J15") <> "" And _
   Range("M12") <> "" And _
   Range("M13") <> "" And _
   Range("M14") <> "" Then
    Dim ZRange As Range
    Set ZRange = Range("K4")
    If ZRange = "Event Based" Then
        If Union(Target, ZRange).Address = ZRange.Address Then
            Application.EnableEvents = False
            Call EventBasedYes
            Application.EnableEvents = True
        End If
    End If
End If

это означает, что вы просматриваете значения J12 , J13 , J14 J15 , M12 M13 M14 K4 и , а затем,, решаете, произошло ли изменение листа в интересующей вас ячейке.

Переместив тест относительно Target местоположения ранее, вы можете сохранить эти поисковые запросы:

 If Not Application.Intersect(Target, Range("K4")) Is Nothing Then
    If Range("J12") <> "" And _
       Range("J13") <> "" And _
       Range("J14") <> "" And _
       Range("J15") <> "" And _
       Range("M12") <> "" And _
       Range("M13") <> "" And _
       Range("M14") <> "" Then
        Dim ZRange As Range
        Set ZRange = Range("K4")
        If ZRange = "Event Based" Then
            Application.EnableEvents = False
            Call EventBasedYes
            Application.EnableEvents = True
        End If
    End If
End If

Если многие ячейки, которые вы проверяете, скорее всего, являются частью Target одного события Worksheet_Change, то, возможно, вам лучше сначала протестировать все интересующие вас ячейки, т. Е. использовать что-то вроде:

 If Not Application.Intersect(Target, Range("K4,J12,X47")) Is Nothing Then

а затем сохраните значения ячеек, на которые вы часто ссылаетесь, в переменных (или в массиве вариантов, если они находятся в непрерывном блоке?), чтобы вы могли ссылаться на переменные несколько раз, а не обращаться к самой ячейке несколько раз.

PS Union(Target, ZRange).Address = ZRange.Address эквивалентно Target.Address = ZRange.Address . Намеренно ли ваш код запускается только при изменении одной ячейки (т. Е. Вы не хотите, чтобы код выполнялся, если ячейки K2: K6 изменены в одном событии, только если K4 изменен сам по себе)? В предлагаемом мной изменении используется Intersect , которое будет выполняться, если измененный диапазон включает интересующую вас ячейку, поэтому вам следует изменить это обратно, если вы не хотите, чтобы это произошло.

Возможная перезапись

Я думаю, что понимаю, что вы пытаетесь сделать. Возможно, следующий код выполнит все ваши тесты сразу:

 If Not Application.Intersect(Target, Range("K4,J12:J15,M12:M14")) Is Nothing Then
    Application.EnableEvents = False
    If Range("J12").Value <> "" And _
       Range("J13").Value <> "" And _
       Range("J14").Value <> "" And _
       Range("J15").Value <> "" And _
       Range("M12").Value <> "" And _
       Range("M13").Value <> "" And _
       Range("M14").Value <> "" And _
       Range("K4").Value = "Event Based" Then
        Call EventBasedYes
    Else
        Call EventBasedNo
    End If
    Application.EnableEvents = True
End If