Зависание Excel при выполнении макросов

[bloodnyj]

Здравствуйте!
Недавно начал изучение VBA. Написал программку, для сортировки данных по определенным условиям. Работала хорошо. После, возникла необходимость усовершенствовать написанное. Вот тут и возникла проблема:
Excel зависает при выполнении макроса. При нажатии Esc, возникает табличка, в которой написано, что работа макроса была прервана и можно нажать кнопку debug. Нажимаю. После этого открывается мой макрос и подсвечивается какая-то строка:

либо r = Len(n7_povtor)    (строка №42)
либо j_7 = 0                     (строка №53)
либо End If                       (строка №39)
либо End If (в другом месте, ниже предыдущего end if).          (строка №69)

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

Код (vb) Выделить Развернуть


ReDim Preserve ves7(1 To 7)
ReDim Preserve poz7(1 To 7)
ReDim Preserve cena7(1 To 7)
ves7(1) = v1
ves7(2) = v2
ves7(3) = v3
ves7(4) = v4
ves7(5) = v5
ves7(6) = v6
ves7(7) = v7
poz7(1) = p1
poz7(2) = p2
poz7(3) = p3
poz7(4) = p4
poz7(5) = p5
poz7(6) = p6
poz7(7) = p7
cena7(1) = c1
cena7(2) = c2
cena7(3) = c3
cena7(4) = c4
cena7(5) = c5
cena7(6) = c6
cena7(7) = c7
n_7 = 0
Do
  sum = 0
  Dim max As Variant
  Dim max_dopust As Variant
  max_dopust = 0
  max = ves7(1)
  For i5 = 1 To UBound(ves7)
     sum = sum + ves7(i5)
  Next i5  
  For i6 = 1 To UBound(ves7)
     If Application.WorksheetFunction.And(ves7(i6) <= 49.9 - sum, max_dopust < ves7(i6)) Then
         max_dopust = ves7(i6)
         k2 = i6
     End If
  Next i6  
  n7_povtor = Application.WorksheetFunction.Index(Range("T1:T500"), poz7(k2))
   r = Len(n7_povtor)
  Do While r > 0
     If Mid(n7_povtor, r, 1) = "_" Then
         Exit Do
         Else
         r = r - 1
     End If
  Loop
  n7_povtor = Mid(n7_povtor, 1, r - 1)  
  'Dim name_massiv() As String
  Dim j_7 As Integer
 j_7 = 0
  For i7 = 1 To xyz
     If name_massiv(i7) = n7_povtor Then
         If Cells(i7, 20).Value <> "" Then
              i4 = 0
              For i3 = 1 To UBound(poz7)
                  If i7 <> poz7(i3) Then
                      i4 = i4 + 1
                  End If
              Next i3
              If i4 = UBound(poz7) Then
                  j_7 = 1
                  p_x = i7
                  Exit For
              End If
         End If
   End If
  Next i7
  x_7 = UBound(ves7)
  If j_7 = 1 Then
     n_7 = n_7 + 1
     ReDim Preserve ves7(1 To x_7 + 1)
     ReDim Preserve poz7(1 To x_7 + 1)
     ReDim Preserve cena7(1 To x_7 + 1)
     ves7(x_7 + 1) = Application.WorksheetFunction.Index(Range("V1:V500"), p_x)
     poz7(x_7 + 1) = p_x
     cena7(x_7 + 1) = Application.WorksheetFunction.Index(Range("U1:U500"), p_x)
  End If
Loop While max_dopust <> 0



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

[vikttur]

Весь макрос можно не показывать прямо в сообщении. Можно приложить пример Excel. Понятнее будет, что откуда берется.
Внимание на слово "пример" - рабочий файл объемом 1 Гб не нужен
Достаточно 20-30 строк данных.

[Дмитрий Щербаков(The_Prist)]

Подобный феномен не обязательно указывает на ошибку кода. Бывает это в Excel. Попробуйте перед выполнением кода записать строку:

Код (vb) Выделить Развернуть

Application.EnableCancelKey = xlDisabled
Только не забудьте в конце кода вернуть:

Код (vb) Выделить Развернуть

Application.EnableCancelKey = xlInterrupt

[bloodnyj]

Подобный феномен не обязательно указывает на ошибку кода. Бывает это в Excel. Попробуйте перед выполнением кода записать строку:

Код (vb) Выделить Развернуть

Application.EnableCancelKey = xlDisabled
Только не забудьте в конце кода вернуть:

Код (vb) Выделить Развернуть

Application.EnableCancelKey = xlInterrupt

добавил
Application.EnableCancelKey = xlDisabled  сразу после sub и
Application.EnableCancelKey = xlInterrupt  сразу перед end sub

зависло на том же этапе, когда макрос добрался до новой части, только в этот раз ошибка выскочила здесь:
If Application.WorksheetFunction.And(ves7(i6) <= 49.9 - sum, max_dopust < ves7(i6)) Then  (строка № 36)
i6 = 6, ubound(ves7) = 7, ves7(i6) = 4,20000146, sum = 41, max_dopust = 4,2 (не знаю, поможет ли)

Хотел еще добавить, после возникновения этой путаницы, переставил office 2010 на office 2013.
Также, возможно ошибаюсь, но ошибки начали возникать после изменения последней строки Loop While max_dopust <> 0  (было изменено условие на это, не помню с какого)

Весь макрос можно не показывать прямо в сообщении. Можно приложить пример Excel. Понятнее будет, что откуда берется.
Внимание на слово "пример" - рабочий файл объемом 1 Гб не нужен
Достаточно 20-30 строк данных.

20-30 строк уже есть. Выделить какие-то дополнительные 20-30 строк из всей программы, кроме уже представленных, сложно (они будут не понятны). Разве что весь рабочий файл + описание, что и зачем делалось

[bloodnyj]

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

[nilem]

попробуйте вместо

Код (vb) Выделить Развернуть

If Application.WorksheetFunction.And(ves7(i6) <= 49.9 - Sum, max_dopust < ves7(i6)) Then
    max_dopust = ves7(i6)
    k2 = i6
End If
записать так

Код (vb) Выделить Развернуть

If ves7(i6) <= 49.9 - Sum And max_dopust < ves7(i6) Then
    max_dopust = ves7(i6)
    k2 = i6
End If
но лучше приложить файл с примером (если там не очень заморочено )

[Дмитрий Щербаков(The_Prist)]

Может Вы нам все же напишите текст ошибки хотя бы?

[bloodnyj]

Может Вы нам все же напишите текст ошибки хотя бы?

Ошибки как таковой нет (я имею ввиду номера ошибки). При выполнении макроса, Excel зависает. И я вынужден нажать Esc, или с помощью креста (справа вверху), завершить работу. Иногда первый вариант не работает (не прерывает код), иногда второй действительно закрывает программу, а не прерывает код (как это происходит в основном). Это событие я называю "ошибкой", потому что после прерывания выскакивает такое же окошко, как и в случае с ошибкой. Только в нем написано "работа кода была прервана юзером".
Только что попробовал еще раз вызвать эту табличку. Не получилось, выскочило окно с ошибкой: "run-time erroe '-2147417848 (80010108)' Automation error. The object invoked has disconnected from its client"

попробуйте вместо

Код (vb) Выделить Развернуть

If Application.WorksheetFunction.And(ves7(i6) <= 49.9 - Sum, max_dopust < ves7(i6)) Then
    max_dopust = ves7(i6)
    k2 = i6
End If
записать так

Код (vb) Выделить Развернуть

If ves7(i6) <= 49.9 - Sum And max_dopust < ves7(i6) Then
    max_dopust = ves7(i6)
    k2 = i6
End If
но лучше приложить файл с примером (если там не очень заморочено )

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

[Дмитрий Щербаков(The_Prist)]

Попробуйте избавиться от всех WorksheetFunction в циклах. Ошибка означает, что COM-объекту не хватает ресурсов для продолжения связывания. А каждое обращение к WorksheetFunction это создание еще одного запроса. Замените Index простым массивом. Создайте 3 массива из каждого диапазона и в циклах к ним обращайтесь.

Код (vb) Выделить Развернуть

dim aInd1
aInd1 = Range("T1:T500").Value
n7_povtor = aInd1(1,poz7(k2))

[bloodnyj]

ребята, всем спасибо, проблема решена. Основной цикл (который начинается на строке №26 и заканчивается на №81) имел условие, которое никогда не выполнялось...
Еще раз всем спасибо! =)