Страницы: 1
RSS
Уйти от условного форматирования, применив VBA
 
Всем добрый день, подскажите есть большой файл( более 6000 строк) в котором много проверок через УФ...и как понимаете файл очень сильно тормозит. Есть ли  у кого-то ссылка или пример макроса где можно задать условия для выделения, что бы уйти от УФ и ускорить работу файла.
Изменено: a.i.mershik - 11.04.2018 14:05:48
Не бойтесь совершенства. Вам его не достичь.
 

Если я правильно понял (но я не уверен), случай/дело относится к альтернативе условному форматированию через vba.

Например:

Код
Option Explicit
'Modul Lista

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range(all_rng)) Is Nothing Then
        Call a_la_UF(Target.Address(0, 0))
    End If
End Sub
Код
Option Explicit
'Modul1

Public Const all_rng As String = "H2:K5"

Sub UF_vse_yacheyki()
    Call a_la_UF(all_rng)
End Sub

Sub a_la_UF(rng As String)
Dim colindF As Variant, colindB As Variant
Dim cell As Range

    For Each cell In ThisWorkbook.Sheets("List1").Range(rng)
        Select Case cell.Value
            Case 0 To 1: colindF = 3: colindB = 35
            Case 2 To 5: colindF = 5: colindB = 19
            Case 6: colindF = 7: colindB = 34
            Case Else: colindF = xlAutomatic: colindB = xlNone
        End Select
        cell.Font.ColorIndex = colindF
        cell.Interior.ColorIndex = colindB
    Next
End Sub

Sub udaleniye_UF()
    With ThisWorkbook.Sheets("List1").Range(all_rng)
        .Font.ColorIndex = xlAutomatic
        .Interior.ColorIndex = xlNone
    End With
End Sub
 
6000 строк и более для УФ не проблема. У меня файл имеется в нём больше 10000 строк и столбцов штук 200. Весь файл раскрашен УФ, причём не на одном листе (4 листа). Формул куча. Я бы не сказал, что он тормозит. Там даже формулы с массивом имеются, и имена, и выпадающие списки с формулами. А макрос это не стабильно.
Мастерство программиста не в том, чтобы писать программы, работающие без ошибок.
А в том, чтобы писать программы, работающие при любом количестве ошибок.
 
Цитата
Alemox написал:
выпадающие списки с формулами
ну это в момент ввода активируется, так что пока не трогаешь, не вычисляется.

А вот от формул , как на листе так и в УФ зависеть может.

a.i.mershik, Примерчик бы маленький,
Изменено: БМВ - 11.04.2018 21:27:07
По вопросам из тем форума, личку не читаю.
 
Пример файла. ну формулы есть ..обычные суммеслимн, индекс+поискпоз,...обычное суммирование...а во всех УФ формулы внутри- они примитивные  типо ячейка равна "" а другая не равна, или ячейка меньше этой .. и так далее..
Изменено: a.i.mershik - 12.04.2018 09:08:07
Не бойтесь совершенства. Вам его не достичь.
 
a.i.mershik,
Странно, вроде и правда ничего сверх тяжелого нет. Готов взглянуть на оригинал файла, для сравнения тормозов на другом ПК. Я правильно понимаю, что тормоза пропадают если удалить с листа все УФ?
По вопросам из тем форума, личку не читаю.
 
БМВ, к сожалению оригинал файла сбросить не могу так как типо под грифом секретно.
Наверное удалю не столь важные УФ,  очищу максимально форматы ячеек,  и отключу перерасчет формул автоматический.
Спасибо, что отозвались.
Изменено: a.i.mershik - 12.04.2018 10:16:28
Не бойтесь совершенства. Вам его не достичь.
 
a.i.mershik,  А точно со структурой файла все ок, артефактов никаких нет?  
По вопросам из тем форума, личку не читаю.
 
БМВ, та вроде объектов скрытых никаких нет, удалил так сказать все ненужные данные за пределами таблицы (при на жатии CTRL+END отображается нужная ячейка).
наверное думаю все из-за формул  в большей степени...там формул около 60 тысяч....
Изменено: a.i.mershik - 12.04.2018 10:35:09
Не бойтесь совершенства. Вам его не достичь.
 
a.i.mershik, то ест пример уже модифицирован, ибо в нем формул почти нет, или я ошибаюсь? 6000 и те 7 столбцов  - это конечно много, но , как
Цитата
Alemox написал:
У меня файл имеется в нём больше 10000 строк и столбцов штук 200.
были таблички и поболее. Хотя если смотреть на AH:AM и на 6000, то получаем много поиска. Я б уже начинал склонять вас к использованию query power или MS, но это совсем вопрос не по теме в заголовке.
Изменено: БМВ - 12.04.2018 10:43:57
По вопросам из тем форума, личку не читаю.
 
a.i.mershik, приветствую!

1. Пользовательские стили ячеек имеются (35 шт.) - может влиять на больших объёмах
2. Проверки на СЖПРОБЕЛЫ. Я бы макрос повесил на WorkSheet Change. Просто при вводе любой инфы обрабатывать через Application.WorksheetFunction.Trim. Или формулой бы всю строку проверял.
3. Проверка ввода по спискам. Никакого УФ не надо. Создавать уникальные списки - и в проверку данных их.
4. Выделить цветом, если что-то заполнено, а что-то нет. Я вообще стараюсь уйти от раскрасок. Вставляю первым столбцом столбец проверки, в котором проверяю пустоты по строке там, где их не должно быть. Ячейки, которые могут быть заполнены только попарно (или одна или другая) проверяю отдельной формулой (=ЕСЛИ(условие выполнено;"ГОТОВО";"")), одной на все такие пары, а саму ячейку с этой формулой - в проверку пустот контрольного первого столбца.
Изменено: Jack Famous - 12.04.2018 11:00:31
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
БМВ, да формулы все удалены, формул действительно связанных с поискм данных достаточно много. да вот установил power qwery  правда никогда не пользовался буду постепенно разбираться) Спасибо большое. Нет случайно ссылок где примеры по работе надстройки PQ есть!?

Jack Famous, добрый день, спасибо большое думаю Вы правы.  Правда добавлять столбцы не удобно так как файл обновляется ежедневно минимум 1 раз. А подскажите как Вы определили
Цитата
Jack Famous написал:
1. Пользовательские стили ячеек имеются (35 шт.)
Не бойтесь совершенства. Вам его не достичь.
 
a.i.mershik,
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Цитата
a.i.mershik написал:
Нет случайно ссылок где примеры по работе надстройки PQ есть!?
это у Квэрщиков лучше спросить. Тут есть у кого.
По вопросам из тем форума, личку не читаю.
 
a.i.mershik,
Цитата
БМВ написал:
Тут есть у кого
так-то и у НВП уже немало уроков есть - смотря, что нужно  ;)
А так да - вот тут 2 мастера обсуждали что-то  8) кроме Андрея и Максима помню ещё PooHkrd - остальных сходу не вспомню. Ищите по сайту темы о Power Query и всех найдёте  ;)
Изменено: Jack Famous - 12.04.2018 13:34:40
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Страницы: 1
Наверх