Страницы: 1
RSS
Как найти ячейки с ошибками в формулах УФ на листе?
 
В процессе работы с таблицами ячейки/строки/столбцы могут удаляться/перемещаться через"вырезать-вставить".  
Формулы листа сразу покажут ошибки при их возникновении.  
А вот как отследить (найти) ошибки, возвращаемые формулами УФ на листе?  
Ведь их результат - всего лишь отсутствие должного форматирования.  
Короче: как сделать цикл по ячейкам листа, который найдёт ячейки с УФ и проверит их формулы на наличие ошибок?
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
Ну, в смысле как сам цикл организовать более-менее ясно:  
Sub Check_CondForm()  
  Dim rCell As Range, rUFCells As Range  
  Set rUFCells = ActiveSheet.Cells.SpecialCells(xlCellTypeAllFormatConditions)  
  If rUFCells Is Nothing Then Exit Sub  
  For Each rCell In rUFCells  
     'а здесь нужно как-то проверить формулы УФ ячейки на ошибку  
  Next rCell  
End Sub  
а вот как формулы проверять, да ещё несколько для каждой ячейки???
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
Прикольно: оказывается, этот вопрос я уже задавал в апреле 2010 г.:  
http://www.planetaexcel.ru/forum.php?thread_id=15242  
но тогда не добился решения и забросил тему.  
А сейчас опять "навеяло"
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
Леша, а какой смысл их проверять? Ну, проверил, ну ошибка. И что дальше? Я так не делал. Удалил УФ, потом присвоил новое всему диапазону. Все равно будет быстрей, чем по одной ячейке проверять.
Я сам - дурнее всякого примера! ...
 
Sub Check_CondForm()  
Dim Ndx As Long  
Dim FC As FormatCondition  
Dim rCell As Range, rUFCells As Range  
Set rUFCells = ActiveSheet.Cells.SpecialCells(xlCellTypeAllFormatConditions)  
If rUFCells Is Nothing Then Exit Sub  
For Each rCell In rUFCells  
For Ndx = 1 To rCell.FormatConditions.Count  
       Set FC = rCell.FormatConditions(Ndx)  
If FC.Type = xlExpression Then  
           If IsError(Application.Evaluate(FC.Formula1)) Then  
MsgBox "HHHHH"  
              End If  
               End If  
          Next Ndx  
Next rCell  
End Sub
 
Неизвестный,  
спасибо большое.  
Только я не понял (просто никогда не программировал УФ), почему Вы проверяете только Formula1 ?
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
Проверил.  
Работает.  
Ещё раз спасибо за подсказку.  
 
По ходу дела встал вопрос: никто не подскажет как называется диалог условного форматирования?  
Хочу при обнаружении ошибки в УФ ячейки выделить её и открыть диалог редактирования УФ
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
 http://msdn.microsoft.com/en-us/library/aa272484.aspx
 
Юрий, спасибо за ссылку.  
Попробую докрутить.
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
Вот что получилось:  
Sub Check_CondForm()  ' проверить условное форматирование ячеек на наличие ошибок  
'http://www.planetaexcel.ru/forum.php?thread_id=47802  
  Dim rCell As Range, rFCCells As Range  
  Dim FC As FormatCondition, Ndx&  
  Set rFCCells = ActiveSheet.Cells.SpecialCells(xlCellTypeAllFormatConditions)  
  If rFCCells Is Nothing Then Exit Sub  
  For Each rCell In rFCCells  
     For Ndx = 1 To rCell.FormatConditions.Count  
        Set FC = rCell.FormatConditions(Ndx)  
        If FC.Type = xlExpression Then  
           If IsError(Application.Evaluate(FC.Formula1)) Then  
              rCell.Activate  
              Select Case MsgBox("Обнаружена ошибка условного форматирования." & vbCrLf & _  
                                 """ДА"" - Править, ""НЕТ"" - Искать дальше, ""ОТМЕНА"" - Выйти", vbYesNoCancel, "Ошибка УФ!")  
                 Case vbCancel: Exit Sub  
                 Case vbYes: Application.Dialogs(xlDialogConditionalFormatting).Show  
                 Case vbNo:  
              End Select  
           End If  
        End If  
     Next Ndx  
  Next rCell  
End Sub  
Вроде работает. На тестовом простом примере находит ошибки уверенно.  
Надо будет попробовать на большой таблице.
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
Блин!  
А я порадовался...  
В самом деле, с русскими именами формул не работает :(  
А как быть то?
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
Ух ты как сложно-то всё получается...  
Хорошо хоть формулы УФ только на текущий лист ссылаться могут.  
Правда, они могут ссылаться на имена, приписанные к диапазонам на других листах... В общем, проблемка-то не так проста как казалось.  
 
К стати, а при помощи файла FUNCS.XLS ( http://www.planetaexcel.ru/forum.php?thread_id=8917 ) никак нельзя перевести формулы на инглиш?
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
А может быть можно не РЕЗУЛЬТАТЫ вычисления формул УФ проверять на ошибки, а  проверять, не содержит ли текст формулы волшебное слово #ОШИБКА, которое заменяет в формуле аргумент, когда удаляют ячейку, содержащую его?
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
а если так попробовать  
 
Sub io()  
   With Cells(1)  
       .FormulaLocal = .FormatConditions(1).Formula1  
       MsgBox .Formula ' evaluate()  
   End With  
End Sub
Чебурашка стал символом олимпийских игр. А чего достиг ты?
Тишина - самый громкий звук


https://github.com/nervgh
 
Что-то я не понял, что получится?  
В А1 вводим формулу из .FormatConditions(1).Formula1 ,а потом пытаемся её вычислить что ли? (хотя, наверное, это надо делать на новом листе, т.к. А1 на рабочем проверяемом листе вполне может быть занята)    
Завтра попробую, но, похоже, опять будут "вилы" с ложными ошибками evaluate на русскоязычных функциях.  
Всё-таки я бы попробовал отлавливать слово #ОШИБКА в аргументах функции, т.к. именно так калечатся формулы при удалении ячеек-аргументов.  
 
------------  
30405
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
А всё-таки, как в формуле отловить наличие #ССЫЛКА! ?  
Пытаюсь, например, в А1 сделать тупое УФ по формуле =ДЛСТР($J$1)  
Запускаю макрос:  
Sub tttt()  
Debug.Print ActiveSheet.[A1].FormatConditions(1).Formula1
Debug.Print ActiveSheet.[A1].FormatConditions(1).Formula1 Like "*#ССЫЛКА!*" 'xlErrRef
End Sub  
Получаю в окне Immediate:  
=ДЛСТР($J$1)  
False  
Удаляю столбец J  
Запускаю макрос. Получаю:  
=ДЛСТР(#ССЫЛКА!)  
False
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
Сам разобрался: забыл "заэкранировать" спец.символ # квадратными скобками []
Вот так отлично отлавливается удаляемый аргумент:  
Sub tttt()  
Debug.Print ActiveSheet.[A1].FormatConditions(1).Formula1
Debug.Print ActiveSheet.[A1].FormatConditions(1).Formula1 Like "*[#]ССЫЛКА!*"
End Sub
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
Вот так, вроде, всё работает - отлавливает в формулах УФ ошибки, возникшие из-за удаления ячеек-аргументов:  
Sub Check_CondForm()  ' проверить условное форматирование ячеек на наличие ошибок  
'http://www.planetaexcel.ru/forum.php?thread_id=47802  
  Dim rCell As Range, rFCCells As Range, nFC&  
  Set rFCCells = ActiveSheet.Cells.SpecialCells(xlCellTypeAllFormatConditions)  
  If rFCCells Is Nothing Then Exit Sub  
  For Each rCell In rFCCells  
     For nFC = 1 To rCell.FormatConditions.Count  
        If rCell.FormatConditions(nFC).Type = xlExpression Then  
           If rCell.FormatConditions(nFC).Formula1 Like "*[#]ССЫЛКА!*" Then
              rCell.Activate  
              Select Case MsgBox("Обнаружена ошибка условного форматирования ячейки " & rCell.Address(0, 0) & vbCrLf & _  
                                 "Условие " & nFC & " : " & rCell.FormatConditions(nFC).Formula1 & vbCrLf & vbCrLf & _  
                                  "Выберите действие:" & vbCrLf & _  
                                  """ДА"" - Исправить, ""НЕТ"" - Искать дальше, ""ОТМЕНА"" - Выйти", vbYesNoCancel, "Ошибка УФ!")  
                 Case vbYes: Application.Dialogs(xlDialogConditionalFormatting).Show  
                 Case vbCancel: Exit Sub  
              End Select  
           End If  
        End If  
     Next nFC  
  Next rCell  
End Sub  
А для английской локали можно проверку соответствующим образом подправить (я просто не знаю, что там отображается вместо #ССЫЛКА! )
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
Посмотрел, поискал, а, оказывается, можно было бы и так догадаться: в английской локали вместо #ССЫЛКА! выводится #REF!    
Ну, соответственно, и проверка будет:  
If rCell.FormatConditions(nFC).Formula1 Like "*[#]REF!*" Then
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
> Что-то я не понял, что получится?  
по идее, должно перевести с rus на eng  
 
> nerv предлагает то же самое, что я в посте 26.11.2012, 14:03  
ну, практически. Разумеется, A1 для примера. Можно и в туже ячейку записать, предварительно созранив ее формулу/значение. Можно как-то так Cells.SpecialCells(xlCellTypeLastCell).Next извратиться. Точно не помню, можно ли произвести пересчет одной ячейки. Если да, Cell.Calculate, если нет, достаем формулу на инглише и Evaluate().
Чебурашка стал символом олимпийских игр. А чего достиг ты?
Тишина - самый громкий звук


https://github.com/nervgh
 
nerv,  
на самом деле - переводить формулы и проверять их слишком муторно.  
ИМХО, вполне достаточно проверять их на наличие битых ссылок на ячейки-аргументы.  
Ведь само УФ делает обычно человек достаточно продвинутый и потому вполне можно с большой долей вероятности принять за исходную предпосылку то, что изначально оно работает правильно :)  
Вот я "полирнул" процедурку с проверкой формул УФ на #ССЫЛКА! и #REF!:  
Sub Check_CondForm()  ' проверить формулы условного форматирования ячеек на наличие ошибок типа #ССЫЛКА!  
  Dim rCell As Range, rFCCells As Range, nFC&  
  Set rFCCells = ActiveSheet.Cells.SpecialCells(xlCellTypeAllFormatConditions)  
  If rFCCells Is Nothing Then Exit Sub  
  For Each rCell In rFCCells  
     For nFC = 1 To rCell.FormatConditions.Count  
        With rCell.FormatConditions(nFC)  
           If .Type = xlExpression Then  
              If .Formula1 Like "*[#]ССЫЛКА!*" Or .Formula1 Like "*[#]REF!*" Then
                 rCell.Activate  
                 Select Case MsgBox("Обнаружена ошибка аргумента формулы условного форматирования ячейки " & rCell.Address(0, 0) & vbCrLf & _  
                                    "Условие " & nFC & " : " & .Formula1 & vbCrLf & vbCrLf & _  
                                    "Выберите действие:" & vbCrLf & _  
                                    """ДА"" - Исправить, ""НЕТ"" - Искать дальше, ""ОТМЕНА"" - Выйти", _  
                                    vbYesNoCancel + vbInformation, "Ошибка формулы УФ!")  
                    Case vbYes: Application.Dialogs(xlDialogConditionalFormatting).Show ' xlDialogConditionalFormatting не имеет аргументов, поэтому приходится активизировать ячейку  
                    Case vbCancel: Exit Sub  
                 End Select  
              End If  
           End If  
        End With  
     Next nFC  
  Next rCell  
End Sub  
 
только что-то не соображу, нужно ли проверять ещё и Formula2 или фиг с ним?
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
Alex_ST, наши мнения, как обычно, не совпадают : )  
 
> на самом деле - переводить формулы и проверять их слишком муторно.  
ага. 2-е строки кода (мой первый пост на этой странице) + отключение событий  
http://dmcritchie.mvps.org/excel/language.htm  
 
> ИМХО, вполне достаточно проверять их на наличие битых ссылок на ячейки-аргументы. Ведь само УФ делает обычно человек достаточно продвинутый и потому вполне можно с большой долей вероятности принять за исходную предпосылку то, что изначально оно работает правильно :)  
А скопипастить (сам того не зная) любой. С этой же долей вероятности, можно было предположить, что в УФ нет ошибок, что значительно проще и совсем "не муторно".  
 
> If .Formula1 Like "*[#]ССЫЛКА!*" Or .Formula1 Like "*[#]REF!*" Then
моя формула:  
="#ССЫЛКА!lаlala"  
 
Еще один "не муторный" вариант: попросить у Димы решение и юзать его.
Чебурашка стал символом олимпийских игр. А чего достиг ты?
Тишина - самый громкий звук


https://github.com/nervgh
 
Невзирая на мнение nerv, который как обычно со мной не согласен :), я всё-таки допилил свой вариант, проверяющий УФ на отсутствие ошибок только в аргументах до финального состояния. Теперь процедура проверяет оба способа задания УФ (Значение ячейки и Формула).  
Sub Check_CondForm()  ' проверить формулы условного форматирования ячеек на наличие ошибок типа #ССЫЛКА! и #REF!  
'http://www.planetaexcel.ru/forum.php?thread_id=47802 ,  http://www.excelworld.ru/forum/2-2901-1#31544  
   Dim rCell As Range, rFCCells As Range, nFC&, sFormula$  
   Set rFCCells = ActiveSheet.Cells.SpecialCells(xlCellTypeAllFormatConditions)  
   If rFCCells Is Nothing Then Exit Sub  
   For Each rCell In rFCCells  
       For nFC = 1 To rCell.FormatConditions.Count  
       With rCell.FormatConditions(nFC)  
           sFormula = .Formula1  
           If .Type = xlExpression Then   ' xlExpression==2  
               sFormula = "Формула " & .Formula1  
           ElseIf .Type = xlCellValue Then    ' xlCellValue==1  
               ' xlBetween == 1 xlNotBetween == 2 xlEqual == 3 xlNotEqual == 4 xlGreater == 5 xlLess == 6 xlGreaterEqual == 7 xlLessEqual == 8  
               sFormula = "Значение " & Choose(.Operator, "между", "вне", "равно", "не равно", "больше", "меньше", "больше или равно", "меньше или равно") & " " & .Formula1  
               If .Operator < 3 Then sFormula = sFormula & " и " & .Formula2  
           End If  
           If sFormula Like "*[#]ССЫЛКА!*" Or sFormula Like "*[#]REF!*" Then
               rCell.Activate  
               Select Case MsgBox("Ошибка аргументов формулы условного форматирования ячейки " & rCell.Address(0, 0) & vbCrLf & _  
                   "Условие " & nFC & " : " & sFormula & vbCrLf & vbCrLf & _  
                   "Выберите действие:" & vbCrLf & _  
                   """ДА"" - Исправить, ""НЕТ"" - Искать дальше, ""ОТМЕНА"" - Выйти", _  
                   vbYesNoCancel + vbInformation, "Ошибка формулы УФ!")  
                   Case vbYes: Application.Dialogs(xlDialogConditionalFormatting).Show      ' xlDialogConditionalFormatting не имеет аргументов, поэтому приходится активизировать ячейку  
                   Case vbCancel: Exit Sub  
               End Select  
           End If  
       End With  
       Next nFC  
   Next rCell  
End Sub
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
А вот и созданная "по мотивам" надстройка для работы с УФ.  
Надстройка добавляет в меню "Ячейка", вызываемое по ПКМ, подменю "Условное форматирование" с пунктами: "Задать/Изменить", "Удалить" и "Проверить аргументы"  
"Задать/Изменить" - вызов стандартного меню УФ для выделенной ячейки/диапазона  
"Удалить" - удаляет все УФ в выделенной ячейке/диапазоне  
"Проверить аргументы" - проверяет в выделенной ячейке/диапазоне аргументы функций УФ всех условий на наличие в них ошибок типа #ССЫЛКА! и #REF!, которые могут возникнуть, например, при удалении каких-либо ячеек, столбцов, строк. При этом в формулах листа такие ошибки видны сразу и обычно успеваешь "откатиться", а вот формулы УФ просто перестают работать и об этом обычно узнаёшь, когда откатиться уже невозможно.
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
не приложилась...
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
Блин, форум по Excel, оказывается, не считает допустимыми надстройки:  
 
Ошибка! Тип загружаемого вами файла не является допустимым. Разрешено загружать файлы следующих форматов: .jpg .gif .png .zip .rar .xls .doc .txt .xlsx .xlsm .docx
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
Немного подполировал надстройку:  
1. Сделал чтобы была независима от локализации (проверить не могу, но должна нормально работать и с другими названиями пунктов меню)  
2. Чуть подчистил код в процедурах (только для красоты)  
3. Переименовал надстройку чтобы опять же не было проблем локализаций.  
4. Нашёл и поправил пару мест, где надстройка вылетала в отладку по ошибке.
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
Страницы: 1
Наверх