Что нужно оптимизировать?

[nemoomen]

Работа у меня бумажная и довольно однообразная. Выпоскаем грузовае документы после погрузки нефтяных танкеров.
Для оптимизации работы с данными по танкеру и удобства печати документов разработал програмку на основе Excel.
Коллеги довольны, а я нет. Не оптимально, каряво....
Не смотря на то, что коллеги уговариваю угомонится решил сделать более интересную версию программы. В принципе в новой программе принципиально по-новому работает только главный лист, на котором консолидируются все данные, касаемые оформляемого танкера.
Для сравнения прилагаю програмку на которой сейчас работаем BF crude oil.xls и набросок новой программы BF neft.xls
По-моему обе программы несут совершенно одинаковую расчетную и функциональную нагрузку, старая даже большую. Только вот новая программка жутко медленно работает.
Не могу понять, что забирает ресурсы? Над чем еще поработать?
Help!!!!

[EducatedFool]

Не могу понять, что забирает ресурсы?

Сейчас поищем.
Тормоза вижу, но точно установить, из-за чего они, пока не удалось.

Прежде всего, в коде отсутствуют некоторые полезные операторы:

Application.ScreenUpdating = False
Application.EnableEvents = False
' код, вызывающий изменение ячеек на листе Mine
Application.EnableEvents = True

Одна из причин - огромные (из сотни строк) обработчики событий листа:
Worksheet_SelectionChange, и, особенно, Worksheet_Change

Конкретного пока ничего не могу предложить.
Чуть попозже напишу, что изменить в коде, чтобы не тормозило.

На каком действии у Вас больше всего проявляются тормоза?

[nemoomen]

Для начала я разработал "движок" с основным листом "Mine" в основе.
Все работало просло моментально, и я, окрыленный успехом "прицепил" к книге листы с бланками (цветные ярлычки). После этого и начались проблемы.
Еще заметил - замедление происходит на тех кодах, которые делают перенос данных с места на место циклом с большим количеством шагов.

[nemoomen]

Одна из причин - огромные (из сотни строк) обработчики событий листа:
Worksheet_SelectionChange, и, особенно, Worksheet_Change

Это и было основной идеей! Сравните интерфейс старой проги, обвешеной неудобными формами - и новую задумку!!!

[EducatedFool]

Ерунда какая-то...
Простейший код (запись в 50 ячеек) выполняется аж 8 секунд!

Sub test()
    t = Timer
    For i = 50 To 100: Cells(i, "A") = i: Next
    Debug.Print Timer - t
End Sub

Впервые с таким сталкиваюсь...
Комп современный, оперативки пустой предостаточно.
Все обработчики событий отключил...
Больше ничего не выполняется - открыт только этот файл

Этот же код в пустом файле работает тоже 8 секунд (замечу, в этот момент оперативки свободной более гигабайта)

Как только закрываю этот чудный файл - код отрабатывает моментально (в сотни тысяч раз быстрее)

Кто может объяснить данный феномен? Только разве что разработчики Microsoft

Проблема явно в файле. Причем Вы тут не виноваты...
Можно попробовать создать пустой файл, и все данные вместе с макросами перекинуть в него.
Времени это займёт много, но может помочь.

Сейчас поищу другие варианты решения проблемы.

[nemoomen]

Подобные тесты я делал... И на разных машинах испытывал...
Я тоже не ожидал, что с моим наивным подходом к VBA and Excel возможно добраться до прдела их возможностей.

По Вашему коду:
Application.ScreenUpdating = False
Application.EnableEvents = False
' код, вызывающий изменение ячеек на листе Mine
Application.EnableEvents = True
можно разъяснения получить? Справка по MsVB не помогла!

Объяснюсь:
на самом деле я крайне не опытем в програмировании на Визуале.
Все, что применено в проге - наковырял из разных источников, иногда совершенно не понимая как это работает!

[EducatedFool]

Почти нашел проблему.
Причина таких тормозов - в одном из листов файла BF neft.xls
(причём не в главном листе Mine)
Удалите все листы, кроме Mine - всё начинает работать моментально.

Единственный способ локализовать проблему - удалять по одному листу из этого файла, а в другом файле (пустом) запускать простейший макрос:

Sub test()
    t = Timer:
    For i = 50 To 60: Me.Cells(i, "A") = i: Next
    Debug.Print Timer - t
End Sub

Как только макрос станет выполняться не за 2 секунды, а моментально - сразу станет ясно, что проблема в последнем удалённом листе.

Листов много, так что выполнение этой проверки ложится на Вас.
Когда скажете, в каком листе была причина - тогда установим точно, из-за чего это всё было.

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

[EducatedFool]

Я тоже не ожидал, что с моим наивным подходом к VBA and Excel возможно добраться до прдела их возможностей.

У Вас вовсе не наивный подход. Проект достаточно серьёзный.
Конечно, многое можно оптимизировать, но сейчас проблема не в этом.

До предела возможностей Excel Вам ещё далеко.
Это всего лишь один из редких глюков Excel.

Application.ScreenUpdating = False
Application.EnableEvents = False
' код, вызывающий изменение ячеек на листе Mine
Application.EnableEvents = True
можно разъяснения получить? Справка по MsVB не помогла!

Команда Application.ScreenUpdating = False отключает обновление экрана на время выполнения макроса.
То есть, Вы можете макросом создавать новые книги или листы, переключаться между листами, вносить изменения в ячейки, но на экране ничего не будет отображаться до окончания работы макроса (или до команды Application.ScreenUpdating = true)
К тому же, порой это значительно увеличивает скорость работы макроса.

Следующий же код
Application.EnableEvents = False
' код, вызывающий изменение ячеек на листе Mine
Application.EnableEvents = True

применяется для временного отключения событий (таких, как Worksheet_SelectionChange и Worksheet_Change) на время изменения значений на листе.

К примеру, в Вашем макросе LoadToMine 19 раз происходит запись на лист Mine, и по умолчанию все эти 19 раз срабатывает немаленький макрос Worksheet_Change, что явно не способствует ускорению работы макроса.

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

Справка по MsVB не помогла!

Надо читать справку по VBA Excel - такой файл есть у Вас на компе.
Там всё очень подробно описано (правда, на английском)

На примере строки application.ScreenUpdating=False
Ставите курсор посреди слова ScreenUpdating, и нажимаете F1
Через мгновение отобразится подробная справка по этому свойству.

[nemoomen]

Тогда попутный вопрос:
Как "прошерстить" всю книгу на предмет поиска и удаления всех "условных форматирований"

Еще вопрос:
подскажите источник, в котором перечислены все операторы VBA Excel - книга или сайт. Желательно на русском.

Тест несомненно сделаю, только уже не сегодня. К сожалению на судно бежать надо.

Огромное промежуточное спасибо!

[EducatedFool]

Как "прошерстить" всю книгу на предмет поиска и удаления всех "условных форматирований"

Это можно сделать при помощи макроса.
Но делать этот макрос дольше, чем удалять листы по-одному.
То, что во всём виновато условное форматирование - лишь предположение.

Удаляйте листы по одному (начиная с последнего), и запускайте предложенный мной код.
Так будет быстрее всего.

подскажите источник, в котором перечислены все операторы VBA Excel - книга или сайт. Желательно на русском

В интернете я видел много подобной информации. Но вспомнить, где именно, сейчас сложно.

Пока могу только посоветовать хороший ресурс по работе с Excel - http://msoffice.nm.ru/faq/macros.htm
По операторам VBA (коих немного) лучше поискать отдельную книгу (или ресурс)
Главное - разобраться в объектной модели Excel