Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Не работает условное форматирование, вставленное макросом
 
#1
02.08.2018 11:29:18
Добрый день!
Спасите! Весь можг сломал...

Создаю макросом условное форматирование (кнопочка на листе).
Условное форматирование работает не корректно (заливает весь 1-й столбец)
Открываю правило вручную. Ничего не меняю. Нажимаю ОК, ОК. И всё работает так как надо.
Что делать не представляю.

Для проверки записывал процесс создания УФ макрорекодером (модуль2). Сделанное вручную правило работает. Записанный макрос глючит.

Проверял на 2013, 2010, 2003 оффисах. Везде такая же беда.
Я не волшебник, я только учусь.
 
 
 
#2
02.08.2018 11:35:53
"=СУММПРОИЗВ(--НЕ(ЕЧИСЛО($C5:$D5)))+(СУММПРОИЗВ(($E5:$F5="""")+($E5:$F5=""+"")+($E5:$F5=""-""))<>ЧИСЛСТОЛБ($E5:$F5))"
потому что из VBA УФ не понимает, что это формула массива.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#3
02.08.2018 11:40:26
А почему начинает понимать если это правило пересохранить вручную или книгу переоткрыть?!
Я не волшебник, я только учусь.
 
 
 
#4
02.08.2018 12:59:41
Wiss, кроме того, у меня оно и не может работать ибо при таком варианте не происходит "авто локализация" и мой ENG интерфейс не воспринимает вашу формулу, то есть в идеале нужно её в интернациональном формате прописать в одну из ячеек (Formula) и извлечь как FormulaLocal, это сработает в любом интерфейсе.
Код
Sub FC()
    Range("A5:A8").FormatConditions.Delete
    [a1].Formula = "=SUMPRODUCT(--NOT(ISNUMBER($C5:$D5)))+(SUMPRODUCT(($E5:$F5="""")+($E5:$F5=""+"")+($E5:$F5=""-""))<>COLUMNS($E5:$F5))"
     With Range("A5:A8").FormatConditions.Add(xlExpression, Formula1:=[a1].FormulaLocal)
        .Interior.Color = 255
    End With
    [a1].ClearContents
End Sub


В целом вопрос интересный, ведь при ручном вводе воспринимается как массивная формула как в УФ, так и в именах.
Изменено: БМВ - 02.08.2018 13:02:15
По вопросам из тем форума, личку не читаю.
 
 
 
#5
02.08.2018 13:04:42
Цитата
Дмитрий(The_Prist) Щербаков написал:
из VBA УФ не понимает, что это формула массива.
Абсолютно точно! К сожалению, разработчики дали урезанный доступ к объектам условного форматирования. Можно поиграться: записывать различные формулы для условного форматирования, сохранять файл и анализировать в структуре архива тэг conditionalFormatting, относящийся к листу.
Изменено: sokol92 - 02.08.2018 13:15:18
Владимир
 
 
 
#6
02.08.2018 13:15:56
Цитата
Wiss написал:
А почему начинает понимать
скорее всего инициализация проходит, также как и локализация при этом срабатывает корректно.
По вопросам из тем форума, личку не читаю.
 
 
 
#7
02.08.2018 13:18:30
Цитата
sokol92 написал:
А почему начинает понимать если это правило пересохранить вручную или книгу переоткрыть?!
это нужно переадресовать разработчикакм.
обнаружтили проблему - запишите ее себе в блокнот, чтобы не нарываться в будущем
тем более, что решение уже есть в тексте Ваших сообщений. записали УФ, сохранили, закрыли, открыли файл и все работает)
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#8
02.08.2018 13:19:39
Откорректировал ответ #5. Формулы условного форматирования (как и все другие) сохраняются в "международном" формате.
Владимир
 
 
 
#9
02.08.2018 14:03:26
Спасибо всем за советы.
Сделал обычной формулой заменил СУММ на СУММПРОИЗВ. Помогло.


Цитата
sokol92 написал:
сохранять файл и анализировать в структуре архива тэг conditionalFormatting, относящийся к листу.
не должно быть разницы, так как при повторном открытии файла УФ работает нормально. Сделал для  этих целей пример с 2-мя одинаковыми (одно работающее, другое - нет). Пытался их сравнить, но глазами разницу не увидел, а как перебрать все свойства у объекта не знаю, да и фиг с ним. Скорее всего разницы нет(

Цитата
sokol92 написал:
Формулы условного форматирования (как и все другие) сохраняются в "международном" формате.
Невероятно, но формулы условного форматирования сохраняются в ЛОКАЛЬНОМ формате. Формулу в международном у меня не понимает и в свойствах объекта хранит в локальном виде.

Цитата
БМВ написал:
скорее всего инициализация проходит, также как и локализация при этом срабатывает корректно.
Инициализация не срабатывает. Просто Вы когда формулу переводили - привели её к "правильному" виду. Вместо SUMM использовали SUMPRODUCT.


В продолжение борьбы с этой нерешаемой проблемой не подскажете, как мне программно сравнить 2 условных форматирования по всем свойствам (объекты FC и FC1 при нажатии кнопочки "сравнить" в новом примере.)?
Я не волшебник, я только учусь.
 
 
 
#10
02.08.2018 14:19:44
Цитата
Wiss написал:
Невероятно, но формулы условного форматирования сохраняются в ЛОКАЛЬНОМ формате.
Это утверждение не верно. Фрагмент файла xl\worksheets\sheet1.xml из #9:
Скрытый текст
Владимир
 
 
 
#11
02.08.2018 14:31:19
Цитата
Wiss написал:
Формулу в международном у меня не понимает и в свойствах объекта хранит в локальном виде.
не понимает и хранит - это из разных плоскостей.
в #4 я вроде пример привел как правильно. Уже в самом файле оно будет, как демонстрирует Владимир, в формате который прочтет Excel любой локализации и переведет в локализованный вид названия функций.  
Цитата
Wiss написал:
Инициализация не срабатывает.
срабатывает, сейчас специально проверил. открывайте жмите на кнопку (сломается УФ) , сохраняйте, открывайте - УФ снова в порядке.
Изменено: БМВ - 02.08.2018 14:43:19
По вопросам из тем форума, личку не читаю.
 
 
 
#12
02.08.2018 14:50:20
Здравствуйте, Михаил! Да, интересно получается с примером из #1.
  • после занесения формулы УФ макросом - не работает
  • подтверждаем эту формулу вручную. В этот момент Excel вроде бы понимает, что это - формула массива (?)
  • сохраняем и закрываем книгу. В формате xml тэг для FormulaArray не предусмотрен
  • открываем - естественно, не работает
Владимир
 
 
 
#13
02.08.2018 15:08:26
Цитата
sokol92 написал:
Это утверждение не верно.
Виноват. Вы правы. Был введён в заблуждение Excel. Хранит-то он так, как Вы показали, а показывает и разрешает вносить только в локальной версии.
Сделал странный пример:
1. По нажатии кнопки формула (глобальная) прописывается в ячейку.
2. В одно УФ в качестве формулы ,беру из этой ячейки значение FormulaLocal, в другое - просто Formula.
3. Там, где вставлялась формула - ничего не работает и в сохраненном файле пишет странное (похоже, воспринимает родную нотацию как UDF)
Код
<conditionalFormatting sqref="A5:A8"><cfRule type="expression" dxfId="1" priority="123" stopIfTrue="1"><formula>SUM(--NOT(ISNUMBER($C5:$D5)))+(SUM(($E5:$F5="")+($E5:$F5="+")+($E5:$F5="-"))<>COLUMNS($E5:$F5))</formula></cfRule></conditionalFormatting>
<conditionalFormatting sqref="B5:B8"><cfRule type="expression" dxfId="0" priority="124" stopIfTrue="1"><formula>_xludf.SUM(--_xludf.NOT(_xludf.ISNUMBER($C5:$D5)))+(_xludf.SUM(($E5:$F5="")+($E5:$F5="+")+($E5:$F5="-"))<>_xludf.COLUMNS($E5:$F5))</formula></cfRule>
 
Изменено: Wiss - 02.08.2018 15:08:49
Я не волшебник, я только учусь.
 
 
 
#14
02.08.2018 15:15:39
Цитата
БМВ написал:
сохраняйте, открывайте - УФ снова в порядке
Ну Вы же понимаете, что открываете/закрываете "это не наш метод". Нельзя же пользователя заставлять файл туда-сюда открывать/закрывать. Работающее корректное решение Вы уже нашли - избавиться от формулы массива, но всё-равно в идеале хотелось бы впихнуть туда формулу массива, хотя бы узнать как это делать. Пока превалирует мнение, что это сделать вообще нельзя(
Изменено: Wiss - 02.08.2018 15:16:46
Я не волшебник, я только учусь.
 
 
 
#15
02.08.2018 16:50:30
Цитата
Wiss написал:
хотелось бы впихнуть туда формулу массива, хотя бы узнать как это делать
Если формат файла это не предусматривает (вероятность близка к 100%), то никак.
Пример в #13 - иллюстрация тезиса из #5. Если для формулы ячейки мы имеет 5 свойств (Formula, FormulaLocal, FormulaR1C1, FormulaR1C1Local, FormulaArray) с различными ракурсами, то для УФ - только одно.
Владимир
 
 
 
#16
02.08.2018 17:02:27
Цитата
Wiss написал:
это сделать вообще нельзя
Ну почему нельзя? Можно. Записав формулу предварительно в имя, а уже имя использовать в УФ
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#17
02.08.2018 17:15:14
Цитата
Дмитрий(The_Prist) Щербаков написал:
Записав формулу предварительно в имя, а уже имя использовать в УФ
поддержу, хотя там и возникнет сложность с относительной адресацией и лучше записывать в R1C1 стиле чтоб не путаться.
По вопросам из тем форума, личку не читаю.
 
 
 
#18
02.08.2018 17:21:58
Отличное решение!
Владимир
 
 
 
#19
02.08.2018 17:35:45
Цитата
sokol92 написал:
Отличное решение
от слова различное это сделали разработчики, когда решили что в тут можно вводить что угодно и это будет формула массива, там как не вводи формула массива не получится, ну и на листе четко разделили когда то , когда другое.
По вопросам из тем форума, личку не читаю.
 
 
 
#20
02.08.2018 17:42:28
Цитата
Дмитрий(The_Prist) Щербаков написал:
Ну почему нельзя? Можно. Записав формулу предварительно в имя, а уже имя использовать в УФ
Ну теоретически это тот же самый вариант с записью формулы в ячейку и получением из неё Formula и FormulaLocal, но более красивый и продвинутый. Возьму на вооружение.

Но самое главное - он не работает, ну или у меня руки кривые, что более вероятно.

Если я в имя запихиваю нерусскую формулу, то условное форматирование работает только если я использую RefersToLocal, если я использую Value или  RefersTo, то УФ не работает. (Под работает/не работает я подразумеваю вообще принимает формулу, а не использует её как формулу массива)
Изменено: Wiss - 02.08.2018 17:46:30
Я не волшебник, я только учусь.
 
 
 
#21
02.08.2018 17:59:40
Цитата
БМВ написал:
от слова различное это сделали разработчики
Тем Excel и интересен!
Коллега Wiss, прикладываю пример из #1, измененный по методике Дмитрия. Что не так? Проверил на разных версиях, в том числе Excel 2016 en, Windows-1252.
Владимир
 
 
 
#22
02.08.2018 18:00:05
Wiss, Формулу в имя, а имя в УФ

Код
Sub FC()
    Range("A5:A8").FormatConditions.Delete
     With Range("A5:A8").FormatConditions.Add(xlExpression, Formula1:="=QQQ")
        .Interior.Color = 255
    End With
        With ActiveWorkbook.Names("QQQ")
'        .RefersToR1C1 = _
'        "=SUM(--NOT(ISNUMBER(RC3:RC4)))+(SUM((RC5:RC6="""")+(RC5:RC6=""+"")+(RC5:RC6=""-""))<>COLUMNS(RC5:RC6))"
        
        .RefersToR1C1Local = _
            "=СУММ(--НЕ(ЕЧИСЛО(RC3:RC4)))+(СУММ((RC5:RC6=="""")+($E5:$F5=""+"")+(RC5:RC6=""-""))<>ЧИСЛСТОЛБ(RC5:RC6))"
    
    End With
End Sub
По вопросам из тем форума, личку не читаю.
 
 
 
#23
02.08.2018 18:50:03
БМВ, спасибо. Теперь понял как правильно имя пихать, чтобы эксель уже сам думал а какую версию адреса брать.

Цитата
sokol92 написал:
Что не так?
1. Файл, который Вы выложили не использует имя и вообще не работает. Видимо, забыли сохранить.
2. Исправил Ваш пример по методике Дмитрия с пояснениями БМВ - заработало.
3. Стал переделывать пример с созданием диапазона макросом - ничего не выходит. В конечном итоге оказалось, что для имени диапазон нужно брать с первой строки листа.
Код
Sub FC() 'Закоменченные строки для формул в международном формате
Dim sFormula As String
    sFormula = "=СУММ(--НЕ(ЕЧИСЛО($C1:$D1)))+(СУММ(($E1:$F1="""")+($E1:$F1=""+"")+($E1:$F1=""-""))<>ЧИСЛСТОЛБ($E1:$F1))"
    'sFormula = "=SUM(--NOT(ISNUMBER($C1:$D1)))+(SUM(($E1:$F1="""")+($E1:$F1=""+"")+($E1:$F1=""-""))<>COLUMNS($E1:$F1))"   
    Names.Add "formX", , 1, , , , , sFormula
    'Names.Add "formX", sFormula, 1
    Range("A5:A8").FormatConditions.Delete
    With Range("A5:A8").FormatConditions.Add(xlExpression, Formula1:="=formX")
        .Interior.Color = 255
    End With
End Sub

Огромное всем спасибо! Ура. Свершилось чудо. Оно заработало.
Изменено: Wiss - 02.08.2018 18:56:13
Я не волшебник, я только учусь.
 
 
 
#24
02.08.2018 18:56:37
Цитата
Wiss написал:
Файл, который Вы выложили не использует имя и вообще не работает
Перепроверьте. Пример из #21, условное форматирование для ячейки A5.
А, понял. Я проверил на примере ячейки A5 базовый механизм, макросы вообще не трогал.
Изменено: sokol92 - 02.08.2018 19:04:45
Владимир
 
 
 
#25
02.08.2018 20:36:05
Цитата
Wiss написал:
В конечном итоге оказалось, что для имени диапазон нужно брать с первой строки листа.
как раз относится к
Цитата
БМВ написал:
там и возникнет сложность с относительной адресацией и лучше записывать в R1C1 стиле чтоб не путаться
По вопросам из тем форума, личку не читаю.
 
 
 
Страницы: 1
Наверх