При выполнении макроса или просто работы с большим объемом данных в 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

Для 64-разрядной версии Excel 2010 не действует ограничение в 2 Gb

Microsoft Excel 2007 2Gb

Microsoft Excel 2003 1Gb

Microsoft Excel 2002 128Mb

Microsoft Excel 2000 64Mb

Почему же происходит переполнение виртуальной памяти?Следует помнить, когда мы запускаем Excel, мы уже занимаем определенный объем виртуальной памяти. Под открытие файлов, надстроек, библиотек нам остается меньше максимально доступного объема памяти.Очевидная причина переполнения памяти: мы создаем большие массивы, коллекции и заполняем их данными.Не очевидная причина, когда мы не видим в коде большие массивы и коллекции заполненных данными. Например, мы в коде создаем некий массив данных, заполняем его, а по окончании выполнения процедуры (или функции) не стираем его. Если запускать данный код много раз, то на некоторой итерации может возникнуть переполнение виртуальной памяти. Во избежание подобной ошибки данной ошибки, рекомендую в конце выполнения кода очищать данные:

tmpDim = Empty                'Опустошаем переменную

Erase tmpArr                  'Стираем массив

Set tmpObject = Nothing       'Стираем объект

Вроде бы, в VBA предусмотрена ограничение области видимости переменной, и по завершению выполнения кода процедуры или функции не глобальные переменные перестают существовать. Но к сожалению, при большом повторении процедур, память может переполняется.Встречаются ситуации, когда за собой не очищают память, другие процедуры, которые не доступны разработчику. В данном случае мы ничего сделать не сможем, за исключением мониторинга объема памяти.