Работа книги на фоне

[Estilla]

Как сделать чтобы из двух книг, одна работала только на фоне?
А то когда в фоновой книге встречаются методы Select, возникает ошибка.

[alibek]

Не делай Select. Без него можно (и нужно) обойтись.

[Estilla]

Не делай Select. Без него можно (и нужно) обойтись.

а что же вместо него, если у меня конструкция:

Код: Выделить всё


Range("K3").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Range("K2") = Selection.Row


Так?

Код: Выделить всё


Range("K2") = Range("K3").End(xlDown).Offset(1, 0).Row


[KL]

Так?

Код: Выделить всё

Range("K2") = Range("K3").End(xlDown).Offset(1, 0).Row


Именно!!!

[Estilla]

А можно что-то сделать , чтобы при срабатывании макроса в одной книге вторая книга не блокировалась, т.е. не появлялись песочные часы?

[KL]

А можно пример макроса, который блокирует вторую книгу?

[Estilla]

А можно пример макроса, который блокирует вторую книгу?

так любой блокирует, любой который больше секунды длится, остальное просто незаметно.
при этом блокируется не только вторая книга , а вообще весь эксель висит, пока макрос не завершит работу.

[KL]

Дык в 99% случаев макрос, который больше секунды длится - плохой макрос Исходя из твоего первоначального вопроса, легко придти к выводу, что решение в оптимизации твоего кода. Отсюда повторная просьба - "А можно пример макроса, который блокирует вторую книгу?"

Если надо гадать, то попробуй следующее:

1) как уже было указано, убери весь Select
2) убедись в том, что циклы нельзя заменить одиночной операцией (очень часто это не сразу очевидно, но особенно при присвоении значений ячейкам, массивам и лист/комбо-боксам)
3) добавь в код след. инструкции вначале и вконце:

Код: Выделить всё

Sub MyMacro()
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With
   
    ' Мой основной код...
   
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
    End With
End Sub

[Estilla]

Дык в 99% случаев макрос, который больше секунды длится - плохой макрос

ок. спасибо. попробую разбить на мелкие куски.

[KL]

ок. спасибо. попробую разбить на мелкие куски.

На всякий случай - я ничего подобного не предлагал и не думаю, что это оптимальное решение, но за отсутствием конкретного кода большего сказать не могу.

[Estilla]

ок. спасибо. попробую разбить на мелкие куски.

На всякий случай - я ничего подобного не предлагал и не думаю, что это оптимальное решение, но за отсутствием конкретного кода большего сказать не могу.

мелкие куски не в плане кол-во строк, а в смысле обработки более мелкого объема информации. У меня макросы небольшие по строкам, но обрабатывают большое кол-во информации, отсюда и долгое выполнение самих макросов.
Вот один из них, который обрабатывает около 2000 строк, вычленяет из каждой строки нужные цены и другие параметры, запихивает в другой лист в особой последовательности, включая сортировку:
На его выполнение уходит 6 минут и 30 секунд.

Код: Выделить всё


Sub Prices()
Dim priceCell As Range
Dim ri As Integer
Dim ii_start As Integer
Dim ii_end As Integer
Dim sellsell As Boolean
Dim formula1 As String
sellsell = False
    ii = 2779
    jj = 2779
    ri = 1825
Application.ScreenUpdating = False
Range(Cells(ii, 1), Cells(5000, 2)).ClearContents
Range(Cells(ii, 4), Cells(5000, 13)).ClearContents
For Each priceCell In Workbooks(strCurrentWbook).Worksheets("сделки").Range("I" & ri & ":I" & fEnd_row())
    ii_start = Range("D1") + 1
        If priceCell.Offset(0, -1) = "Купля" And priceCell.Offset(0, -2) = "Фирма" Then
            sellsell = False
            For i = 1 To priceCell.Offset(0, 1)
                Range("A" & ii) = priceCell.Value
                ii = ii + 1
            Next i
        ElseIf priceCell.Offset(0, -1) = "Продажа" And priceCell.Offset(0, -2) = "Фирма" Then
            ii_end = ii - 1
            If sellsell = False Then
            'Сортировка
                Range(Range("A" & ii_start), Range("A" & ii_start).End(xlDown)).Sort Key1:=Range("A" & ii_start), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
            End If
            For j = 1 To priceCell.Offset(0, 1)
                Cells(jj, 2) = priceCell.Value
                With Cells(jj, 2)
                    .Offset(0, 10) = priceCell.Offset(0, -7)
                    .Offset(0, 11) = priceCell.Offset(0, -6)
                    .Offset(0, 10).NumberFormat = "m/d/yyyy"
                End With
                jj = jj + 1
            Next j
            sellsell = True
                With Cells(jj - 1, 2)
                    .Offset(0, 3).FormulaR1C1 = "=sum(R3C3:R" & jj - 1 & "C3)"
                    .Offset(0, 3).NumberFormat = "0.00"
                    .Offset(0, 4) = .Offset(0, 10)
                    .Offset(0, 4).NumberFormat = "m/d/yyyy"
                    .Offset(0, 5).FormulaR1C1 = "=ЧИСТРАБДНИ(R3C12,RC6)"
                    .Offset(0, 6).FormulaR1C1 = "=RC[-3]/RC[-1]"
                    .Offset(0, 6).NumberFormat = "0.00"
                    .Offset(0, 7).FormulaR1C1 = "=50000/(RC[-1]*20)"
                    .Offset(0, 7).NumberFormat = "0"
                End With
        End If
Next priceCell
Range(Range("A" & ii_start), Range("A" & ii_start).End(xlDown)).Sort Key1:=Range("A" & ii_start), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
Application.ScreenUpdating = True
End Sub


[KL]

Вот один из них, который обрабатывает около 2000 строк, вычленяет из каждой строки нужные цены и другие параметры, запихивает в другой лист в особой последовательности, включая сортировку:
На его выполнение уходит 6 минут и 30 секунд.

Если избавишь меня от необходимости моделировать твой файл и реконструировать данные, выложив образец файла, то пожалуй попробую привести твои 6 минут и 30 секунд к ~1 секундe

[Estilla]

Если избавишь меня от необходимости моделировать твой файл и реконструировать данные, выложив образец файла, то пожалуй попробую привести твои 6 минут и 30 секунд к ~1 секундe

проблема, файл весит около 100 Мег и там просто паутина: всё друг от друга зависит + данные подкачиваются извне, вообщем проблема.
прям интрига: в одну секунду превратить
хоть намекни как?
У меня в голове два варианта:
1. Раздробить операции на более мелкие в виде нескольких модулей
2. Каждый раз уменьшать обрабатываемые данные, подводя итоги.
всё это с учетом вышеуказанных рекомендаций

[KL]

Если избавишь меня от необходимости моделировать твой файл и реконструировать данные, выложив образец файла, то пожалуй попробую привести твои 6 минут и 30 секунд к ~1 секундe

проблема, файл весит около 100 Мег и там просто паутина: всё друг от друга зависит + данные подкачиваются извне, вообщем проблема.
прям интрига: в одну секунду превратить
хоть намекни как?
У меня в голове два варианта:
1. Раздробить операции на более мелкие в виде нескольких модулей
2. Каждый раз уменьшать обрабатываемые данные, подводя итоги.
всё это с учетом вышеуказанных рекомендаций

Попробуй начать со следующего:

1) перестать сортировать данные 2221 раз и сделай это в конце процедуры.

2) перестать форматировать ячейки 2221 х 9 раз и сделай это в конце процедуры.

3) использовать расширенный фильтр для получения рангов по условиям = "Фирма", = "Купля", = "Продажа".

4) отключить пересчет формул.

5) есть еще соображения, но нужно понимать структуру листа, исходные данные и конечный результат

[KL]

кстати, еще неплохо бы разъяснить функцию fEnd_row() на предмет эффективности

[KL]

Но вобще-то если файл весит около 100 Мег и там просто паутина: всё друг от друга зависит + данные подкачиваются извне, то гораздо важнее срочно предпринять след. шаги во избежание неприятных сюрпризов:

1) дробить файл
2) уничтожать графические объекты
3) уничтожать графики и сводные таблицы
4) уничтожать все виды форматирования ячеек
5) избавляться от ссылок
6) избавляться от летучих формул, напр. использующих функци: ТДАТА, СЕГОДНЯ, СМЕЩ, ДВССЫЛ, СЛЧИС, ИНФОРМ, ЯЧЕЙКА
7) избавляться от матричных формул, типа {=...} или =СУММПРОИЗВ((E1:E21="a")*(F1:F21="b"))
8) избавляться от функций точного поиска, напр.: СЧЁТЕСЛИ, СУММЕСЛИ и/или ВПР, ДПР, ПОИСКПОЗ с последним параметром ЛОЖЬ
9) отказаться от полного столбца как параметра в функциях, напр: СЧЁТЕСЛИ(А:А;23)
10) рассмотреть возможность приобретения след. надстройки: http://www.decisionmodels.com/fastexcel.htm
11) читать все здесь:
http://www.decisionmodels.com/calcsecrets.htm
http://www.decisionmodels.com/optspeed.htm
http://www.decisionmodels.com/memlimits.htm

[Estilla]

кстати, еще неплохо бы разъяснить функцию fEnd_row() на предмет эффективности

да там фигня: берет одно значение из ячейки другого листа.

Код: Выделить всё


Public Function fEnd_row() As Integer
fEnd_row = Workbooks(strCurrentWbook).Worksheets("ini").Range("b2")
End Function


[Estilla]

...срочно предпринять след. шаги во избежание неприятных сюрпризов:...

Ух, огромное спасибо, для меня это всё ново, т.к. недавно начал программить, есть над чем работать.
Постараюсь Учесть все пункты, хоть и не всё ко мне подходит для моего файла, поскольку формулы почти не используются.
Я надеялся что в экселе, есть какая-то программная отключалка зависимости книг при работе макроса. Ну раз нет, тогда буду оптимизировать код.

[Estilla]

Попробуй начать со следующего:
1) перестать сортировать данные 2221 раз и сделай это в конце процедуры.
2) перестать форматировать ячейки 2221 х 9 раз и сделай это в конце процедуры.
3) использовать расширенный фильтр для получения рангов по условиям = "Фирма", = "Купля", = "Продажа".
4) отключить пересчет формул.
5) есть еще соображения, но нужно понимать структуру листа, исходные данные и конечный результат

1) к сожалению это необходимость в моем случае
4) - это сила! Только благодаря одному этому отключению из 6 минут 30 секунд стал обрабатывать за 40 секунд.

[KL]

1) к сожалению это необходимость в моем случае

Не могет такого быть раз сортируешь только по одному параметру. Но даже если бы их было 256, все равно это не 2221

Ну раз до 40 сек упало - с тебя пиво (шли в Мадрид - до востребования)

[Estilla]

1) к сожалению это необходимость в моем случае

Не могет такого быть раз сортируешь только по одному параметру. Но даже если бы их было 256, все равно это не 2221

Ну раз до 40 сек упало - с тебя пиво (шли в Мадрид - до востребования)

не дойдет, по дороге расплескается грузчики или почтовики так случайно откроют(ется)

[Estilla]

рано порадовался. Отмена автоматической калькуляции убила напрочь всю сортировку, т.е. неверно стали данные выводится на листе.

[KL]

:) рано порадовался. Отмена автоматической калькуляции убила напрочь всю сортировку, т.е. неверно стали данные выводится на листе.

Я же говорил - сначала надо решать вопрос с сортировкой.

[Estilla]

:) рано порадовался. Отмена автоматической калькуляции убила напрочь всю сортировку, т.е. неверно стали данные выводится на листе.

Я же говорил - сначала надо решать вопрос с сортировкой.

А как это понимать? При отмене калькуляции все экселевские методы не работают что ли?

p.s. А что значит : Microsoft MVP?

[KL]

А как это понимать? При отмене калькуляции все экселевские методы не работают что ли?

Нет, но возможно что сортировка в твоем коде происходит по полю содержащему формулу. И если в процессе работы кода, ячейки используемые формулой изменяются, то это никак не отразится на рез-те формулы до ее пересчета. Попробуй пересчитывать ключевую ячейку (ту, которая нах. на пересечении текущей строки и сортируемого столбца) на каждом этапе основного цикла (напр.: Range("A1").Calcualte), хотя это и чудовищное варварство :-О

p.s. А что значит : Microsoft MVP?

см.:
http://www.mvps.ru/Program/Default.aspx
https://mvp.support.microsoft.com/

[Estilla]

Range("A1").Calcualte[/b]), хотя это и чудовищное варварство :-О

ТОчно, есть такая ячейка, из которой берется значение которое является начальной строкой в каждой сортировке и это значение меняется. Но это я лох, что записываю в ячейку это значение, а не присваиваю переменной.

[Estilla]

...
1) как уже было указано, убери весь Select
2) убедись в том, что циклы нельзя заменить одиночной операцией (очень часто это не сразу очевидно, но особенно при присвоении значений ячейкам, массивам и лист/комбо-боксам)

А DoEvents в данном случае не помощник? Я имею в виду случай когда выполняется макрос в одной книге, то вторая приэтом не доступна из-за процесса ожидания выполнения макроса.