Сбой Excel при циклическом переборе большого диапазона для обновления ячеек с помощью вызова функции
У меня есть цикл для обновления ячеек с помощью библиотеки DLL Visual Studio. Это работает, но Excel дает сбой, когда я перебираю большие диапазоны (200 ячеек). Функция dll оценивается после поиска двух параметров для каждой отдельной ячейки. Вызов функции медленный, но будет работать для всех параметров, заданных для любых ячеек диапазона. Я могу запустить его для RANGE(N100:W110) и для RANGE(N110:W119), но при попытке RANGE(N100:W119) происходит сбой. Я использую 32-битную версию Excel, так как мне нужно предоставить ее пользователям как с 32-битной, так и с 64-битной версией.
Вот мой макрос:
Sub updatecurves()
Dim curvesheet As Worksheet
Dim My_setcell, c As Range
Dim My_invua, My_uascale, My_uyscale, My_beta, My_sigma, My_KD, My_zD, My_nint, My_nstehfest, My_flag
Dim Neuman72
Dim MyMsg
Set curvesheet = Sheets("neuman72 vs Aq")
Application.ScreenUpdating = False
My_uascale = Application.Evaluate(Names("uascale").Value)
My_uyscale = Application.Evaluate(Names("uyscale").Value)
My_sigma = 0.0001
My_KD = Application.Evaluate(Names("KD").Value)
My_zD = Application.Evaluate(Names("zD").Value)
My_nint = Application.Evaluate(Names("testnint").Value)
My_nstehfest = Application.Evaluate(Names("nstehfest").Value)
My_flag = Application.Evaluate(Names("flag").Value)
For Each c In curvesheet.Range("N100:W110").Cells
My_invua = Cells(c.Row, "A").Value
My_beta = Cells("2", c.Column)
Neuman72 = Neuman72ForEXL(My_invua, My_uascale, My_uyscale, My_beta, My_sigma, My_KD, My_zD, My_nint, My_nstehfest, My_flag) + expint(1 / (4 * My_invua))
c.Value = Neuman72
Next
Application.ScreenUpdating = True
' some comments
End Sub
Спасибо за Ваш интерес. Я не пробовал устанавливать ручной режим расчета во время выполнения, так как думал, что без обновления таблица не изменится..? Код Neuman72ForEXL представляет собой скомпилированную dll из Visual Studio — это q длиной, поскольку представляет собой интеграцию функции обратного преобразования Лапласа (не очень эффективно закодированной) — около 200 строк. Попробую переключить режим расчета...
ОК. Перед функцией (и обратно после нее) переменные, переданные в функцию, были затемнены как Double или Long, для параметра Calculation установлено значение xlManual, а для EnableEvents — значение false, и возникла та же проблема. Симптом все еще присутствует - т.е. он работает в двух диапазонах по 100 ячеек, но не в большем диапазоне.
Что вы подразумеваете под «сбоями»? Ошибка выполнения? Excel закрывается полностью? Связано ли это с вызовом DLL? Действительно ли это связано с большим количеством данных, которые вы обрабатываете, или, возможно, одна из обрабатываемых ячеек содержит недопустимые данные, которые обрабатываются неправильно (например, пустая ячейка, ячейка, содержащая строку вместо числа , число, выходящее за пределы допустимого диапазона...).
Похоже, проблема в самой DLL, а не в Excel. Обновление 200 ячеек с помощью VBA должно быть проще простого, если только расчеты не являются действительно сложными и громоздкими. Проверьте, что предложил @FunThomas, и убедитесь, что все 200 значений верны. Кроме того, попробуйте пошагово отладить свой , чтобы увидеть, в каком именно цикле происходит сбой вашего кода.
Признак сбоя заключается в том, что через несколько минут после появления символа занятости «вращающегося колеса» Excel исчезает, а затем перезагружается с последней сохраненной версией. Похоже, проблема связана с размером диапазона, поскольку два поддиапазона охватывают все ячейки большего диапазона и правильно оцениваются для каждого поддиапазона. Я отключил большинство надстроек, чтобы сэкономить память. Когда я попытался обновить весь диапазон (N3:W119), я получил сообщение об ошибке «доступные ресурсы». На листе имеются два крупных разбросанных (с плавными линиями) графика кривых с 22 кривыми в одном и 16 кривыми в другом.
Значения, которые он вычисляет, верны, когда он пытается сделать 300, используя другую структуру цикла, он правильно их рассчитывает, дает мне сообщение, загружает значения, дает мне другое сообщение, чтобы сказать, что все готово, а затем зависает с ячейками, заполненными серым или черным цветом, и невоспроизводимо отображает обновленный (правильный) график - похоже, у него проблемы с усердной работой! Для 300 ячеек я оцениваю до 300*5*12*64000000 оценок функций (обычно exp-функций) + еще 300*100, вызываемых в одну и ту же dll с другой записью во вторую экспортированную функцию. Он получает каждый результат примерно за 0,5 с.
Для 300 ячеек я оцениваю до 300*5*12*64000000. Я не знаю точного содержимого вашего инструмента, но если вам нужно выполнить более 3 миллиардов операций на одну ячейку (5*12*64000000 = 3,840. 000.000) Я почти уверен, что Excel для этого не подойдет. Попробуйте перейти на R или Python
да F&B&B - операции выполняются в скомпилированной функции cpp в dll - я использую Excel для отображения результатов - я упомянул количество операций, чтобы отметить, что вызовы dll занимают немного времени, поэтому макрос Excel тратит довольно много времени время ожидания завершения оценок. Как вы можете видеть в макросе, Excel не требует многого от себя. Я бы заподозрил dll, вот только она правильно оценивает все вызовы функций, но это не устраняет проблемы с памятью в dll? Однако Excel зависает и делает это после завершения всех вызовов функций... Но, возможно, это dll
Я понимаю вашу точку зрения, но поскольку проблема связана с используемой вами dll, мы не можем воспроизвести проблему на наших компьютерах и найти решение. Любой намек или ответ, вероятно, будет похож на стрельбу в темноте.
@FoxfireAndBurnsAndBurns - вы были совершенно правы, и я обвинял симптом в том, что я не искал свою ошибку... Это был небольшой динамический массив, оставленный без удаления[] -ed, который проходил через память при многоразовом вызове... Спасибо вам за то, что указали мне туда, куда мне нужно было посмотреть! Отмечаю ли я комментарии как решения?
Рад, что вы нашли решение. Вы не можете пометить какой-либо комментарий как решение, но можете ответить на свой вопрос, который может помочь будущим пользователям. Просто попытайтесь подробно описать, где произошла ошибка и как вы ее исправили, и примите свой собственный ответ.
Ответы 1
ОК. Спасибо @foxfire-and-burns-and-burns. Я был убежден, что проблема связана с Excel, и они указали, что мне следует посмотреть на скомпилированную cpp dll, которую я вызывал из VBA, как neuman72ForEXL(...) в VBA моего вопроса. Это была утечка памяти в подфункции, которая вызывалась очень много (>миллиардов) раз и в конечном итоге израсходовала доступные ресурсы и вышла из строя: ''' v = новый двойной [n]; // выделяем длину массива n ''' нужный ''' удалить [] v; // освобождаем массив в конце, чтобы предотвратить утечку памяти ''' Всегда нужно скептически относиться к собственному кодированию и помнить, что команда Excel с меньшей вероятностью будет распространять ошибки!
Добро пожаловать в СО. Какой код
Neuman72ForEXL? Почему вы объявляете почти все переменные какVariantвместо конкретных типов? Кроме того, вы пробовали установить режим расчета вручную, обновить, а режим расчета снова перевести в автоматический режим?