В процессе работы с таблицами ячейки/строки/столбцы могут удаляться/перемещаться через"вырезать-вставить". Формулы листа сразу покажут ошибки при их возникновении. А вот как отследить (найти) ошибки, возвращаемые формулами УФ на листе? Ведь их результат - всего лишь отсутствие должного форматирования. Короче: как сделать цикл по ячейкам листа, который найдёт ячейки с УФ и проверит их формулы на наличие ошибок?
С уважением, Алексей(ИМХО: 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!!!)
Леша, а какой смысл их проверять? Ну, проверил, ну ошибка. И что дальше? Я так не делал. Удалил УФ, потом присвоил новое всему диапазону. Все равно будет быстрей, чем по одной ячейке проверять.
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
По ходу дела встал вопрос: никто не подскажет как называется диалог условного форматирования? Хочу при обнаружении ошибки в УФ ячейки выделить её и открыть диалог редактирования УФ
С уважением, Алексей(ИМХО: 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!!!)
Что-то я не понял, что получится? В А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().
Чебурашка стал символом олимпийских игр. А чего достиг ты? Тишина - самый громкий звук
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 или фиг с ним?
Alex_ST, наши мнения, как обычно, не совпадают : )
> на самом деле - переводить формулы и проверять их слишком муторно. ага. 2-е строки кода (мой первый пост на этой странице) + отключение событий http://dmcritchie.mvps.org/excel/language.htm
> ИМХО, вполне достаточно проверять их на наличие битых ссылок на ячейки-аргументы. Ведь само УФ делает обычно человек достаточно продвинутый и потому вполне можно с большой долей вероятности принять за исходную предпосылку то, что изначально оно работает правильно :) А скопипастить (сам того не зная) любой. С этой же долей вероятности, можно было предположить, что в УФ нет ошибок, что значительно проще и совсем "не муторно".
> If .Formula1 Like "*[#]ССЫЛКА!*" Or .Formula1 Like "*[#]REF!*" Then моя формула: ="#ССЫЛКА!lаlala"
Еще один "не муторный" вариант: попросить у Димы решение и юзать его.
Чебурашка стал символом олимпийских игр. А чего достиг ты? Тишина - самый громкий звук
Невзирая на мнение 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
А вот и созданная "по мотивам" надстройка для работы с УФ. Надстройка добавляет в меню "Ячейка", вызываемое по ПКМ, подменю "Условное форматирование" с пунктами: "Задать/Изменить", "Удалить" и "Проверить аргументы" "Задать/Изменить" - вызов стандартного меню УФ для выделенной ячейки/диапазона "Удалить" - удаляет все УФ в выделенной ячейке/диапазоне "Проверить аргументы" - проверяет в выделенной ячейке/диапазоне аргументы функций УФ всех условий на наличие в них ошибок типа #ССЫЛКА! и #REF!, которые могут возникнуть, например, при удалении каких-либо ячеек, столбцов, строк. При этом в формулах листа такие ошибки видны сразу и обычно успеваешь "откатиться", а вот формулы УФ просто перестают работать и об этом обычно узнаёшь, когда откатиться уже невозможно.
С уважением, Алексей(ИМХО: Excel-2003 - THE BEST!!!)
Немного подполировал надстройку: 1. Сделал чтобы была независима от локализации (проверить не могу, но должна нормально работать и с другими названиями пунктов меню) 2. Чуть подчистил код в процедурах (только для красоты) 3. Переименовал надстройку чтобы опять же не было проблем локализаций. 4. Нашёл и поправил пару мест, где надстройка вылетала в отладку по ошибке.