Out of memory vba excel что делать
Откройте редактор VBA
Потом в меню Tools->References ищите ссылки на библиотеки, помеченные словом "MISSING", и замените их на соответствующие более ранних версий
Уменьшение количества Public variables здесь вряд ли поможет.
Если не сможете самостоятельно решить проблему, выкладывайте файл.
Библиотек, помеченных словом "MISSING" нет. Проверил его еще на 2-ух других компьютерах: на одном такая же ошибка, а на другом работает. В файле было ограничение на количество открытий, я его убрал( чтобы в реестр при открытии Excel не добавлялась каждый раз единичка).
При чем не работает именно эта надстройка. всё остальное ОК.
Хочу открыть форму, код в которой проще некуда :
Чтобы не тратить своё и наше время, выкладывайте файл.
Какая у Вас версия Office?
Чем отличается тот компьютер, на котором работала надстройка, от Вашего, на котором не работает? (версия Office, список установленных компонентов Office, установленные среды разработки и т.п.)
__ Полезные надстройки для Excel. Парсинг сайтов и файлов.
Макросы любой сложности на заказ. Мониторинг цен конкурентов
Чтобы не тратить своё и наше время, выкладывайте файл.
Какая у Вас версия Office?
Чем отличается тот компьютер, на котором работала надстройка, от Вашего, на котором не работает? (версия Office, список установленных компонентов Office, установленные среды разработки и т.п.)
Прикрепляю файл.
Там вроде бы удалены модули для регистрации надстройки в реестр и создание меню. Надстройка далеко не моя, но если нужен полностью файл, то могу поискать исходник.
Обратитесь лучше к разработчику этой надстройки - Николаю Павлову
Он разрабатывает и продаёт эту надстройку уже не первый год.
К тому же, Вы ещё вчера задали вопрос в разделе, посвещённом этой надстройке. Лучше разработчика на Ваши вопросы никто не ответит.
__ Полезные надстройки для Excel. Парсинг сайтов и файлов.
Макросы любой сложности на заказ. Мониторинг цен конкурентов
Обратитесь лучше к разработчику этой надстройки - Николаю Павлову
Он разрабатывает и продаёт эту надстройку уже не первый год.
К тому же, Вы ещё вчера задали вопрос в разделе, посвещённом этой надстройке. Лучше разработчика на Ваши вопросы никто не ответит.
В том-то и дело, что файлик достался по наследству. Покупали его или нет, я не знаю. Судя по тому, что на других компьютерах работает, то скорее всего у меня проблема.
Вот и пытаюсь разобраться в чем дело.
Если ничего не поможет, то придется покупать. Только вот новые возможности не посмотришь. Кота в мешке покупать придется.
Демо версия с сайта у меня тоже не работает.
Тем не менее спасибо.
Прошу прощения за возможный некропостинг но возникла такая проблема, завтра сдавать практику а сегодня выскакивает ошибка Out of Memory, причем на пустом месте, все что написано выше я сделал но не помогло, пробовал запустить на ноуте, та же сказка
Ось Вин7 х64, Офис 2007
Очень нужна помощь, проект почти доделан и завтра его нужно сдать.
Выкладываю файлики(работа с бд в екселе)
завтра сдавать практику а сегодня выскакивает ошибка Out of Memory, причем на пустом месте |
Разве на пустом?
Зачем столько разных библиотек наподключали?
Что-то вы с компонентами на форме начудили - проект не компилируется.
Но - как только удалил форму DobProd (явно проблема в ней)- всё сразу заработало.
Видимо, на ней присутствовал какой-то "левый" элемент управления. (отобразить форму мне так и не удалось)
__ Полезные надстройки для Excel. Парсинг сайтов и файлов.
Макросы любой сложности на заказ. Мониторинг цен конкурентов
Возможно кому-то это поможет.
Exel System error &H8000FFFF - мое решение проблемы!
У меня выскакивала такая же проблема на рабочем компьютере - 2010 офис. При этом на домашнем компьютере все работало отлично (2013 офис). Как я решил проблему:
1. Сначала я по очереди удалял все формы (не забудьте сделать копию исходного файла). После каждого удаления я проверял работоспособность. В конце концов я нашел форму - с которой была проблема.
2. После этого на рабочем домашнем компьютере (где у меня все работало нормально) я экспортировал эту форму по очереди удаляя разные элементы формы и экспортируя форму в разных вариантах.
3. Экспортированные формы я перенес на проблемный компьютер и начал по очереди пробовать импортировать их в проект до тех пор, пока один из вариантов не импортировался успешно.
Таким образом я нашел проблемный элемент формы - в моем случае это оказался Image. Вся проблема была в том - что в этот объект была загружено изображение - которое почему то не поддерживалось 2010 офисом (но поддерживалось 2013-м).
Проблема в следующем: все данные с листа помещаю в массив, чтобы их быстрее обработать. На малых объемах все работает хорошо. На больших, близких к граничным, вылезает ошибка "RunTime Error 7 - Out of Memory".
Она вылезает или на ReDim массива, или на присвоении массиву диапазона с листа.
ПАМЯТИ 8 ГБ, Excel 64 РАЗРЯДНЫЙ, ОПЕРАЦИОНКА 64 РАЗРЯДНАЯ,Excel 2016
ошибку даёт Redim на 111 533 строках и 1635 столбцах
Код
ПОМОГИТЕ ПОЖАЛУЙСТА.
ЗАРАНЕЕ СПАСИБО.
Ошибка Microsoft Visual C++ Runtime Library - Runtime Error
Здравствуйте. Буду признательна за помощь в моей беде. Совсем в этом не разбираюсь. В Safari при.
Ошибка времени исполнения: Server Error in '/' Application, Runtime Error
Здравствуйте, через некоторое время после загрузки страницы если нажимаю на кнопку, то выдает.
[DCC Error] E2161 Error: RLINK32: Out of memory!
При компиляции вылаиет такая ошибка (сабж), да у меня много данных храниться в ресурсах, но при это.
Runtime Error 201:Range check error
Возникла ошибка(Runtime Error 201:Range check error) var a : array of longint; s.
Я выходил из такой ситуации следующим образом:
вычислял общее кол-во строк
определял кол-во проходов по массиву так, чтобы за один проход в массив загонялось не более 2000 строк(при условии 30 с лишним столбцов)
и так кусками обрабатывал.
Но у меня была ситуация такова, что мне надо было собрать результирующий массив с условиями, в результате чего результирующий массив был в разы меньше исходных данных. Память выдерживала без проблем и на 64 и на 32 битах.
Возможно дальше по коду у Вас идет присвоение массиву ячеек как объектов (а не значений) от чего и происходит перегруз (если только Вы не используете массив как раз именно с целью доступа к свойствам виртуальных ячеек).
Уверен, что если поискать по остальной части проекта, то можно аналогично оптимизировать.
В любом случае без всего кода тяжело что либо подсказать.
Утечка памяти может происходить где угодно.
Приведенный участок кода всего лишь информирует о том, что памяти больше нет.
Но он далеко не обязательно является основной причиной проблемы.
При выполнении макроса или просто работы с большим объемом данных в Exel - вылетает ошибка "Недостаточно памяти" (или "Недостаточно ресурсов", "Out of Memory"). Первым делом мы начинаем смотреть в диспетчере задач, сколько у нас используется памяти. Странно, мы видим, что свободной оперативной памяти предостаточно. Excel съедает не так уж и много памяти. Но почему же вылетает такая ошибка? Давайте сначала разберемся в памяти. Тогда нам станет почему она заканчивается. Мы знаем, что в компьютере есть оперативная память, жесткий диск, кэш в процессоре. Это физические устройства, которые находятся в распоряжении операционной системы. Операционная система выделяет необходимый объем памяти для каждого приложения. В диспетчере задач, отображается использование физической оперативной памяти. Приложения, в том числе Excel, оперирует виртуальной памятью. Что же такое виртуальная память? Виртуальная память - это объем памяти доступный приложению.
Непонятно? Давайте рассмотрим простой пример.
Возьмем первоклассника в первой четверти. В школьной программе дети изучают счет до десяти. Виртуальная память равна 10. Дети учатся решать примеры и задачи, используя числа до 10. Ребенок с легкостью может решить примеры: 2+5=7, 9-4=5. Но задача 9+4=? становится для него непосильной, потому что переполняется объем виртуальной памяти. Во второй четверти первоклассники изучают счет до двадцати. Виртуальная память увеличивается с 10 до 20. Наш ребенок может с легкостью решить пример 9+4=13, который в первой четверти вызывал у него затруднения. Объем физической памяти не связан с понятием виртуальной памяти. Объем задействованных нервных клеток головного мозга ребенка за одни каникулы не увеличился в два раза. При этом виртуальная память первоклассника за четверть выросла в 2 раза, а концу учебного года первоклассники оперируют числами до 100, т.е. виртуальная память вырастает на порядок. Давайте вернемся к нашему Excel-ю. Теперь мы понимаем, что когда Excel говорит, что у него заканчивается виртуальная память, он сообщает, что заканчивается его внутренняя память. О реальной физической оперативной памяти, Excel знает ровно столько, сколько знает (а точнее не знает) наше самосознание о нашем мозге. Вы можете запускать приложение Excel и открывать файлы, как на мощных компьютерах, так и на стареньких слабеньких компьютерах. Правда работать все будет с разной производительностью. Поэтому заглядывать в диспетчер задач, в данном случае не имеет особого смысла. Объем виртуальной памяти Excel по данным MSDN
Почему же происходит переполнение виртуальной памяти? Следует помнить, когда мы запускаем Excel, мы уже занимаем определенный объем виртуальной памяти. Под открытие файлов, надстроек, библиотек нам остается меньше максимально доступного объема памяти. Очевидная причина переполнения памяти: мы создаем большие массивы, коллекции и заполняем их данными. Не очевидная причина, когда мы не видим в коде большие массивы и коллекции заполненных данными. Например, мы в коде создаем некий массив данных, заполняем его, а по окончании выполнения процедуры (или функции) не стираем его. Если запускать данный код много раз, то на некоторой итерации может возникнуть переполнение виртуальной памяти. Во избежание подобной ошибки данной ошибки, рекомендую в конце выполнения кода очищать данные: Вроде бы, в VBA предусмотрена ограничение области видимости переменной, и по завершению выполнения кода процедуры или функции не глобальные переменные перестают существовать. Но к сожалению, при большом повторении процедур, память может переполняется. Встречаются ситуации, когда за собой не очищают память, другие процедуры, которые не доступны разработчику. В данном случае мы ничего сделать не сможем, за исключением мониторинга объема памяти.
I'm on Excel 2010, on an admittedly very large sheet (400k rows X 20 columns).
My code aims to:
- load the entire sheet into an array
- examine every row for a certain criteria
- rows which qualify are copied to another array
- finally return the second array back to another sheet
- the second array will end up being roughly 90% of the original
I wrote a definition of two variable arrays as variants And tried to initialize them by copying the sheet's content twice.
first copy works, but by the second one I hit an error of "Out of memory".
Any ideas if there's a workaround? or is this just a limitation of VBA/ Excel.
Is there a way to not pre-define / initialize the destination array, and instead, let it "grow" with every successful qualification of the criteria? (On a scale of this magnitude).
maybe add memory/RAM capacity to your computer. i'm working with 8 GB. On a windows with 64 bit, you can have more memory, too.
I'd suggest a slightly different approach: rather than load everything into arrays, you could process one row at a time. Read it, check it, write it (if correct), then move to the next. This would significantly reduce the VBA working memory.
You might need 64bit Excel. I had the out-of-memory issue due to dealing with tons of large variant; switching to 64bit Excel works like a charm. No need to upgrade the hardware at all.
3 Answers 3
As comments on your post indicate, this error comes from shortage of working memory.
Each Variant type variable consumes 16 bytes, this is the reason your code require vast amount of memory. So one way to solve this problem is increase physical memory on your computer.
Other solution is filtering data by certain amount of rows.
Of course you can go with row by row approach, but I assume you use array variable to speed up your code, so I do not recommend to use row by row approach.
Working row by row is extremely slow so this is not a viable solution for such a large dataset.
Arrays are definately the way to go so the choice is between:
- Loading the data in batches then running your processing on a contiguous data set *(viable until large amounts of data - perhaps around 8M elements depending on your system)
- Loading the data in batches then running your processing on the batch only (viable for an arbitrary amount of data)
Edit: I see you are 400k * 20 which is pushing the boundaries of Option 1. You may have no choice but to refactor your code and load and process by batch (vs. load by batch then process together)
- This should be fine until very large datasets as the Out of Memory error is at first not from the size of the array itself but rather from reading from the worksheet.
- If you get an Out of Memory error from the size of the array itself, then:
- you will have no choice but to either use 64-bit Excel;
- Or (better) to refactor your procedure to process the data in chunks (Option 2 above).
The below loads the data in batches into a single array by recursively loading the data in batches. Try it - the benefits of still having one array at the end mean you don't have to restructure the rest of your code.
Example of Option 1:
All the suggestions above suggest that there is not enough memory. My compueter has enough memory and I also once tried to switch to 64 bit Excel. I must assume that there is a common Excel bug. The most (doesn't assure that it happens every time) secure method for me is following: Due to the built in effect that my Excel files crashed frequently I separated data in .xlsx files and all macros into AddIns. This unconvenient way reduced the crashes almost to zero. However, whenever I need to do an upadate on the AddIn I first unload the AddIn, then modify that source file of the AddIn save this .xlsb file and then save it as .xlam. When I try to close the source file of the Addin (the .xlsb-file) I'm asked if I want to save a new copy or overwrite the changes. Whatever selection I take I get mostly the 'Out of memory' message. Sometimes I can continue to work, sometimes Excel crashes and starts new. Maybe this description explains that the message has nothing to do with too little memory or too much data allocated.
Not the answer you're looking for? Browse other questions tagged vba excel or ask your own question.
Related
Hot Network Questions
To subscribe to this RSS feed, copy and paste this URL into your RSS reader.
Site design / logo © 2022 Stack Exchange Inc; user contributions licensed under cc by-sa. rev 2022.5.6.42057
I am running VBA code on a large spreadsheet. How do I clear the memory between procedures/calls to prevent an "out of memory" issue occurring?
Its likely that you need to look at the structure of your VBA, and particularly any very large arrays/strings it might be working with - break these down into batches to limit the total usage of memory by your code at any one time.
Release Variant, Object whenever possible (Erase, Set object = nothing) . ReDim them to a more reasonable size, loop them in buffer size. But most probably reason is the spreadsheet too large (check in task manager if it's taking >500M RAM before running any macro) . You may want to open a read-only spreadsheet, remove all unused Sheets ( This will free a lot of memory)
You could use 64bit office to get round the issue temporarily but you still need to address the problem like mentioned my most people.
You might not even be having memory issues - I've encountered VBA reporting "Out of Memory" errors, when the root cause was a function in an add-on DLL I had written raising an exception, thinking VBA would report that to the user. Apparently "Out of Memory" can also be VBA's way of saying "I don't know WTF to do about this"!
7 Answers 7
The best way to help memory to be freed is to nullify large objects:
Also note that global variables remain allocated from one call to another, so if you don't need persistence you should either not use global variables or nullify them when you don't need them any longer.
However this won't help if:
- you need the object after the procedure (obviously)
- your object does not fit in memory
Another possibility is to switch to a 64 bit version of Excel which should be able to use more RAM before crashing (32 bits versions are typically limited at around 1.3GB).
Besides holding the least amount of reference in VBA, I think it's very important to reduce the amount of data in Excel. Remove all unused sheets, unused Cell, conditional formatting, coloring, validation, filter, e.t.c. (I had a case where the excel itself is using ~ 1.1GB of memory without any macro. ) So it's important to state VBA & Excel itself share the 1.3GB of memory in Excel 2007 or below.
Absolutely - weird formatting in unused cells for example can cause a book to go from 30kB to several MB.
@assylias - IMHO, adding Set obj = Nothing to the end of a procedure (where obj is dimmed in) won't free additional resources as the object is terminated by VBA garbage collector anyway. However, if obj is not needed anymore earlier in the sub, setting it to nothing will free the memory earlier.
64 bits Excel has its issues too, i run on a 16GB Ram computer and excel has the audacity to say "out of memory" when nearly half (8GB) is still available.
I've found a workaround. At first it seemed it would take up more time, but it actually makes everything work smoother and faster due to less swapping and more memory available. This is not a scientific approach and it needs some testing before it works.
In the code, make Excel save the workbook every now and then. I had to loop through a sheet with 360 000 lines and it choked badly. After every 10 000 I made the code save the workbook and now it works like a charm even on a 32-bit Excel.
If you start Task Manager at the same time you can see the memory utilization go down drastically after each save.
It seems that Excel basically purges the memory when you save the workbook. Pretty much like sql purges the transaction log when committing data.
hey @ArneLarsson you save my day. This work like a charm. And it is true, Excel release memory after saving.
I know this is old but wanted to point out that Excel 2016+ no longer releases resources on save. this used to be a good work around!
Answer is you can't explicitly but you should be freeing memory in your routines.
Some tips though to help memory
- Make sure you set object to null before exiting your routine.
- Ensure you call Close on objects if they require it.
- Don't use global variables unless absolutely necessary
I would recommend checking the memory usage after performing the routine again and again you may have a memory leak.
If you operate on a large dataset, it is very possible that arrays will be used. For me creating a few arrays from 500 000 rows and 30 columns worksheet caused this error. I solved it simply by using the line below to get rid of array which is no longer necessary to me, before creating another one:
Also if only 2 columns out of 30 are used, it is a good idea to create two 1-column arrays instead of one with 30 columns. It doesn't affect speed, but there will be a difference in memory usage.
Found this thread looking for a solution to my problem. Mine required a different solution that I figured out that might be of use to others. My macro was deleting rows, shifting up, and copying rows to another worksheet. Memory usage was exploding to several gigs and causing "out of memory" after processing around only 4000 records. What solved it for me?
Added that at the beginning of my code (be sure to make it true again, at the end) I knew that would make it run faster, which it did.. but had no idea about the memory thing.
After making this small change the memory usage didn't exceed 135 mb. Why did that work? No idea really. But it's worth a shot and might apply to you.
I had a similar problem that I resolved myself. I think it was partially my code hogging too much memory while too many "big things"
in my application - the workbook goes out and grabs another departments "daily report".. and I extract out all the information our team needs (to minimize mistakes and data entry).
I pull in their sheets directly. but I hate the fact that they use Merged cells. which I get rid of (ie unmerge, then find the resulting blank cells, and fill with the values from above)
I made my problem go away by
a)unmerging only the "used cells" - rather than merely attempting to do entire column. ie finding the last used row in the column, and unmerging only this range (there is literally 1000s of rows on each of the sheet I grab)
b) Knowing that the undo only looks after the last ~16 events. between each "unmerge" - i put 15 events which clear out what is stored in the "undo" to minimize the amount of memory held up (ie go to some cell with data in it.. and copy// paste special value. I was GUESSING that the accumulated sum of 30sheets each with 3 columns worth of data might be taxing memory set as side for undoing
Yes it doesn't allow for any chance of an Undo. but the entire purpose is to purge the old information and pull in the new time sensitive data for analysis so it wasn't an issue
Читайте также: