У Джеффа есть сложная книга Excel, в которой VBA используется для создания множества отчетов в формате PDF. Он отлично работает в 32-разрядной версии Excel 2013 на нескольких компьютерах. И наоборот, на нескольких других компьютерах, работающих под управлением Office 365, в середине вывода Джефф получает ошибку «Недостаточно памяти».

Таким образом, макросы отлично работают в старой версии Excel, но не в новейшей версии. (И снова Джефф протестировал это на нескольких компьютерах, поэтому результаты можно повторить.) Джефф задается вопросом, есть ли известные утечки памяти или различия в макросах в Office 365, которые могут вызывать эту проблему.

Как правило, использование памяти связано с одной основной проблемой — переменными и тем, как они используются в вашем макросе. Это особенно важно для массивов переменных. Всегда полезно убедиться, что вы объявили все свои переменные (используйте директиву Option Explicit, чтобы помочь в этом отношении) и проверьте размеры вашего массива, чтобы убедиться, что вы не пытаетесь объявить безумно огромный массив.

Переменные объекта также могут использовать много памяти. Если вы используете ключевое слово Set для присвоения объекта переменной, убедитесь, что вы установили для переменной объекта значение Nothing, когда закончите. Это особенно важно внутри циклов — если каждый проход цикла приводит к объявлению другой объектной переменной, если вы не можете очистить эту переменную во время каждой итерации цикла, вы можете очень быстро использовать память.

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

Можно было бы подумать, что в ситуации Джеффа использование памяти не будет большой проблемой. В конце концов, он переходит с 32-битной версии Excel на (более чем вероятно) 64-битную версию. Это позволяет Excel использовать больше памяти, чем когда-либо прежде. Таким образом, можно было бы подумать, что ошибки «Out of Memory» будут менее вероятными, чем более вероятными.

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

На протяжении многих лет Microsoft документировала, что эта конкретная память является общей по своей природе и может быть вызвана чем угодно. (В моей книге это делает сообщение об ошибке бесполезным, но кто я такой, чтобы спрашивать Microsoft? Разочарование!)

Еще одна вещь, которую я хотел бы сделать (не видев кода Джеффа), — это убедиться, что вы не программируете линейно. Другими словами, разбейте свой код на отдельные подпрограммы, которые выполняют небольшие дискретные задачи. Затем вы можете вызывать каждую подпрограмму из главной управляющей процедуры. Преимущество этого заключается в управлении памятью за счет объема.

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

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

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

Другим преимуществом такой модульности кода является то, что если вы все же столкнетесь с ошибкой — даже с вообще бесполезным «Out of Memory»

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

Если это все еще не решает проблему, я видел несколько отчетов о том, что при сохранении книги VBA вынуждает VBA выполнять сборку мусора в вашем пространстве памяти. Из-за этого некоторые люди предлагают, чтобы ваш макрос сохранял книгу время от времени, а не только в конце макроса. Я не знаю, насколько точен этот отчет, но если вы уже используете свой макрос для сохранения книги, не повредит, если он будет сохраняться чаще, а не реже.

Следует упомянуть еще об одном. Если вы сейчас используете Office 365 бизнес премиум, он включает SharePoint Online и Excel Web App. Поэтому в этих версиях размер файла Excel ограничен 10 МБ. Если ваш файл довольно большой, он мог нормально работать в Excel 2013, но не работал в Office 365 Business Premium из-за этого ограничения размера файла.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (13619) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365.