Out of memory vba excel что делать

Обновлено: 05.01.2025

Я нахожусь на Excel 2010, по общему признанию, очень большом листе (400 тысяч строк х 20 столбцов).

Мой код направлен на:

загрузите весь лист в массив
исследуйте каждую строку по определенному критерию
строки, которые соответствуют требованиям, копируются в другой массив
наконец верните второй массив обратно на другой лист
второй массив в конечном итоге будет примерно 90% от исходного

Я написал определение двух переменных массивов как вариантов И попытался инициализировать их, дважды скопировав содержимое листа.

первая копия работает, но ко второй я попал с ошибкой "Out of memory".

Есть какие-нибудь идеи, если есть обходной путь? или это просто ограничение VBA/ Excel.

Есть ли способ не предопределять / инициализировать целевой массив, а вместо этого позволить ему "grow" при каждой успешной квалификации критериев? (В масштабе такого масштаба).

2 ответа

У меня есть ошибка out of memory при доступе. Мой DB составляет около 20 Мб и содержит около 100 000 строк в разных таблицах. Это началось сегодня днем, когда я захожу в Редактор VBA, я ничего не могу отредактировать, потому что он удалит текст, который я только что набрал, и всплывет Out of.

Я запускаю код VBA на большой электронной таблице. Как очистить память между процедурами/вызовами, чтобы предотвратить возникновение проблемы out of memory? Спасибо

Как показывают комментарии к вашему посту, эта ошибка происходит из-за нехватки рабочей памяти.

Каждая переменная типа Variant потребляет 16 байт, поэтому ваш код требует огромного объема памяти. Поэтому одним из способов решения этой проблемы является увеличение физической памяти на вашем компьютере.

Другое решение-фильтрация данных по определенному количеству строк.

Конечно, вы можете использовать подход row by row, но я предполагаю, что вы используете переменную массива для ускорения вашего кода, поэтому я не рекомендую использовать подход row by row.

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

Массивы-это определенно правильный путь, так что выбор стоит между ними:

Загрузка данных пакетами, а затем запуск обработки на непрерывном наборе данных *( жизнеспособны до тех пор, пока не будут получены большие объемы данных - возможно, около 8 млн элементов в зависимости от вашей системы)
Загрузка данных в пакеты, а затем запуск обработки только в пакете ( жизнеспособно для произвольного объема данных )

Edit: я вижу, что вы 400k * 20, что раздвигает границы варианта 1. Вы не можете иметь никакого выбора, кроме как рефакторинг кода и процесс загрузки пакета (по сравнению с нагрузкой, партии тогда процесс вместе)

Это должно быть хорошо до очень больших наборов данных, так как ошибка нехватки памяти сначала связана не с размером самого массива, а скорее с чтением с листа .
Если вы получаете ошибку нехватки памяти из-за размера самого массива, то:
- у вас не будет другого выбора, кроме как использовать 64-bit Excel;
- Или (лучше) рефакторинг вашей процедуры для обработки данных в кусках (Вариант 2 выше).
Ниже мы загружаем данные пакетами в один массив путем рекурсивной загрузки данных пакетами. Попробуйте - преимущества наличия одного массива в конце означают, что вам не нужно реструктурировать rest вашего кода.

Пример варианта 1:

Похожие вопросы:

Я отлаживал проблему, упомянутую в нескольких вопросах other* на SO, и заметил странное поведение во время процесса отладки. Поведение: Испытал ошибку out of memory при вставке сложных формул.

на прошлой неделе мы столкнулись с ошибкой out of memory в нашей производственной среде. Эта ошибка out of memory возникает, возможно, раз в неделю, и текущий обходной путь заключается в перезапуске.

У меня есть ошибка out of memory при доступе. Мой DB составляет около 20 Мб и содержит около 100 000 строк в разных таблицах. Это началось сегодня днем, когда я захожу в Редактор VBA, я ничего не.

Я запускаю код VBA на большой электронной таблице. Как очистить память между процедурами/вызовами, чтобы предотвратить возникновение проблемы out of memory? Спасибо

При импорте нескольких txt-файлов через VBA в Excel я сталкиваюсь с предупреждением out of memory, связанным с .Refresh BackgroundQuery:=False . Ровно в 723 правильно импортированных текстовых.

Я пытаюсь объявить новую переменную в VBA для Excel. У меня есть модель excel, которая имеет 9 модулей и 7 модулей класса. Каждый модуль действительно большой, в среднем в каждом модуле объявлено 60.

У меня есть список локальных файлов .HTM , которые я хочу разобрать с помощью Selenium в VBA (Excel 2013). Я сталкиваюсь с двумя проблемами но сначала немного примерного кода: Sub openSelenium() Dim.

Откройте редактор VBA
Потом в меню Tools->References ищите ссылки на библиотеки, помеченные словом "MISSING", и замените их на соответствующие более ранних версий

Уменьшение количества Public variables здесь вряд ли поможет.

Если не сможете самостоятельно решить проблему, выкладывайте файл.

Библиотек, помеченных словом "MISSING" нет. Проверил его еще на 2-ух других компьютерах: на одном такая же ошибка, а на другом работает. В файле было ограничение на количество открытий, я его убрал( чтобы в реестр при открытии Excel не добавлялась каждый раз единичка).
При чем не работает именно эта надстройка. всё остальное ОК.
Хочу открыть форму, код в которой проще некуда :
Чтобы правильно задать вопрос, надо знать большую часть ответа.
Чтобы не тратить своё и наше время, выкладывайте файл.

Какая у Вас версия Office?

Чем отличается тот компьютер, на котором работала надстройка, от Вашего, на котором не работает? (версия Office, список установленных компонентов Office, установленные среды разработки и т.п.)

Чтобы не тратить своё и наше время, выкладывайте файл.

Какая у Вас версия Office?

Чем отличается тот компьютер, на котором работала надстройка, от Вашего, на котором не работает? (версия Office, список установленных компонентов Office, установленные среды разработки и т.п.)
Прикрепляю файл.
Там вроде бы удалены модули для регистрации надстройки в реестр и создание меню. Надстройка далеко не моя, но если нужен полностью файл, то могу поискать исходник. Чтобы правильно задать вопрос, надо знать большую часть ответа.
Обратитесь лучше к разработчику этой надстройки - Николаю Павлову

Он разрабатывает и продаёт эту надстройку уже не первый год.

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

Обратитесь лучше к разработчику этой надстройки - Николаю Павлову

Он разрабатывает и продаёт эту надстройку уже не первый год.

К тому же, Вы ещё вчера задали вопрос в разделе, посвещённом этой надстройке. Лучше разработчика на Ваши вопросы никто не ответит.
В том-то и дело, что файлик достался по наследству. Покупали его или нет, я не знаю. Судя по тому, что на других компьютерах работает, то скорее всего у меня проблема.
Вот и пытаюсь разобраться в чем дело.
Если ничего не поможет, то придется покупать. Только вот новые возможности не посмотришь. Кота в мешке покупать придется.
Демо версия с сайта у меня тоже не работает.
Тем не менее спасибо. Чтобы правильно задать вопрос, надо знать большую часть ответа. Прошу прощения за возможный некропостинг но возникла такая проблема, завтра сдавать практику а сегодня выскакивает ошибка Out of Memory, причем на пустом месте, все что написано выше я сделал но не помогло, пробовал запустить на ноуте, та же сказка
Ось Вин7 х64, Офис 2007
Очень нужна помощь, проект почти доделан и завтра его нужно сдать.
Выкладываю файлики(работа с бд в екселе)

завтра сдавать практику а сегодня выскакивает ошибка Out of Memory, причем на пустом месте

Разве на пустом?

Зачем столько разных библиотек наподключали?

Что-то вы с компонентами на форме начудили - проект не компилируется.
Но - как только удалил форму DobProd (явно проблема в ней)- всё сразу заработало.
Видимо, на ней присутствовал какой-то "левый" элемент управления. (отобразить форму мне так и не удалось)
Последний раз редактировалось EducatedFool; 03.06.2010 в 22:32 .
Возможно кому-то это поможет.

Exel System error &H8000FFFF - мое решение проблемы!

У меня выскакивала такая же проблема на рабочем компьютере - 2010 офис. При этом на домашнем компьютере все работало отлично (2013 офис). Как я решил проблему:

1. Сначала я по очереди удалял все формы (не забудьте сделать копию исходного файла). После каждого удаления я проверял работоспособность. В конце концов я нашел форму - с которой была проблема.
2. После этого на рабочем домашнем компьютере (где у меня все работало нормально) я экспортировал эту форму по очереди удаляя разные элементы формы и экспортируя форму в разных вариантах.
3. Экспортированные формы я перенес на проблемный компьютер и начал по очереди пробовать импортировать их в проект до тех пор, пока один из вариантов не импортировался успешно.

Таким образом я нашел проблемный элемент формы - в моем случае это оказался Image. Вся проблема была в том - что в этот объект была загружено изображение - которое почему то не поддерживалось 2010 офисом (но поддерживалось 2013-м).

Я запускаю код VBA на большой электронной таблице. Как очистить память между процедурами/вызовами, чтобы предотвратить возникновение проблемы "из памяти"?

ОТВЕТЫ

Ответ 1

Лучший способ помочь освободить память - это аннулировать большие объекты:

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

Однако это не поможет, если:
- вам нужен объект после процедуры (очевидно)
- ваш объект не помещается в память
Еще одна возможность - перейти на 64-разрядную версию Excel, которая должна иметь возможность использовать больше оперативной памяти перед сбоем (32-разрядные версии обычно ограничены примерно 1,3 ГБ).

Ответ 2

Я нашел обходное решение. Поначалу казалось, что потребуется больше времени, но на самом деле все становится более плавным и быстрым из-за меньшего количества обмена и доступной памяти. Это не научный подход, и он нуждается в некотором тестировании, прежде чем он будет работать.

В коде сделайте Excel время от времени сохраняйте книгу. Мне пришлось пройти через лист с 360 000 строк, и он сильно задохнулся. После каждых 10 000 я сделал код, сохраняя книгу, и теперь он работает как шарм даже в 32-разрядном Excel.

Если вы запустите диспетчер задач одновременно, вы можете увидеть, что использование памяти резко падает после каждого сохранения.

Ответ 3

Ответ: вы не можете явно, но вы должны освобождать память в своих подпрограммах.

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

Ответ 4

Если вы работаете с большим набором данных, очень возможно, что массивы будут использоваться. Для меня это создало несколько массивов из 500 000 строк и 30 столбцов. Я решил это просто, используя приведенную ниже строку, чтобы избавиться от массива, который больше не нужен мне, прежде чем создавать еще один:

Также, если используется только 2 столбца из 30, рекомендуется создать два массива с 1 столбцом вместо одного с 30 столбцами. Это не влияет на скорость, но будет разница в использовании памяти.

Ответ 5

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

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

Я прячу их листы напрямую. но я ненавижу тот факт, что они используют Merged cells. от которых я избавляюсь (то есть unmerge, затем нахожу полученные пустые ячейки и заполняю значения сверху)

Я поставил свою проблему на

a), не смешивая только "используемые ячейки", а не просто пытается сделать целую колонку. т.е. нахожу последнюю использованную строку в столбце и снимая только этот диапазон (буквально 1000 строк на каждом из лист я хватаю)

b) Зная, что отмена только смотрит за последними

16 событиями. между каждым "unmerge" - я помещаю 15 событий, которые очищают то, что хранится в "undo", чтобы свести к минимуму объем памяти (вверх) т.е. перейдите в какую-либо ячейку с данными в ней.. и скопируйте//вставьте специальное значение. Я УВЕРЕН, что накопленная сумма в 30 листов, каждая из которых содержит 3 столбца, может облагать память, установленную как часть для отмены

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

Я использую код VBA в большой электронной таблице. Как очистить память между процедурами /вызовами, чтобы предотвратить возникновение проблемы «недостаточно памяти»?

Лучший способ освободить память - обнулить большие объекты:

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

Однако это не поможет, если:
- вам нужен объект после процедуры (очевидно)
- ваш объект не помещается в памяти
Еще одна возможность - перейти на 64-разрядную версию Excel, которая должна иметь возможность использовать больше оперативной памяти перед сбоем (32-разрядные версии обычно ограничены объемом около 1,3 ГБ).

Я нашел обходной путь. Сначала казалось, что это займет больше времени, но на самом деле все работает плавнее и быстрее из-за меньшего количества перестановок и большего объема доступной памяти. Это не научный подход, и он требует некоторого тестирования, прежде чем он заработает.

В коде заставьте Excel время от времени сохранять книгу. Мне пришлось перебрать лист с 360 000 строк, и он сильно задохнулся. После каждых 10 000 я заставлял код сохранять рабочую книгу, и теперь он работает как прелесть даже в 32-разрядной версии Excel.

Если вы одновременно запустите диспетчер задач, вы увидите, что использование памяти резко снижается после каждого сохранения.

Ответ - вы не можете явно, но вы должны освободить память в своих подпрограммах.

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

Если вы работаете с большим набором данных, вполне возможно, что будут использоваться массивы. Для меня создание нескольких массивов из 500 000 строк и 30 столбцов рабочего листа вызвало эту ошибку. Я решил это, просто используя строку ниже, чтобы избавиться от массива, который мне больше не нужен, перед созданием другого:

Также, если используются только 2 столбца из 30, рекомендуется создать два массива из 1 столбца вместо одного с 30 столбцами. Это не влияет на скорость, но будет разница в использовании памяти.

У меня была похожая проблема, которую я решил сам . Я думаю, что это был частично мой код слишком много памяти, в то время как слишком много "больших вещей"

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

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

Я заставил мою проблему уйти

а) объединение только «использованных ячеек», а не просто попытка сделать весь столбец . то есть поиск последней использованной строки в столбце и объединение только этого диапазона (буквально тысячи строк на каждом из лист я хватаю)

б) Зная, что отмена присматривает только за последними

16 событиями . между каждым "unmerge" - я поместила 15 событий, которые очищают то, что хранится в "отмене", чтобы минимизировать количество удерживаемой памяти ( то есть перейдите к какой-либо ячейке с данными в ней . и скопируйте //вставьте специальное значение . Я УГАДАЛ, что накопленная сумма в 30 листов каждый с данными в 3 столбца может облагать налогом память, установленную в качестве стороны для удаления

Да, это не дает никаких шансов на отмену . но вся цель состоит в том, чтобы очистить старую информацию и извлечь новые данные, чувствительные ко времени, для анализа, чтобы это не было проблемой

Читайте также: