Как восстановить макросы в excel

Обновлено: 04.07.2024

Предположим, Вы скачали файл с данного сайта с примером макроса, открываете файл, но макросы в нем не работают! Как такое может быть? Неужели автор сайта выложил неработающий пример и не знает об этом? И тем более как может случиться такое, что Вы перенесли со своего ПК на другой ПК файл с рабочими макросами на флешке, запускаете, а он не работает. Почему? Вроде все правильно делаете, да ведь и у Вас работает на ПК. И начинает закрадываться мысль: "А чем же этот ПК и Excel на нем такие особенные?" . А ответ может быть прост - перед выполнением макроса не было разрешено выполнение этих самых макросов. Да, в большинстве случаев по умолчанию выполнение макросов в Excel отключено. И их надо включить. Для этого:

Excel 2003:
Сервис-Безопасность-Уровень макросов "Низкий"
Excel 2007:
Кнопка Офис-Параметры Excel (Excel Options) -Центр управления безопасностью (Trust Centr) -Параметры центра управления безопасностью (Trust Centr Settings) -Параметры макросов (Macro Settings) -Разрешить все макросы (Enable All Macros)
Excel 2010-2016:
Файл (File) -Параметры (Options) -Центр управления безопасностью (Trust Centr) -Параметры центра управления безопасностью (Trust Centr Settings) -Параметры макросов (Macro Settings) -Разрешить все макросы (Enable All Macros)

Если макросы уже включены, но некоторые макросы отказываются работать (как правило те, которые записаны в модулях листов и книг и запускаются при наступлении определенного события - Worksheet_SelectionChange (выделение ячеек), Worksheet_Change (изменение значений ячеек) и т.п.), то скорее всего когда-то вы выполнили какой-нибудь макрос и он отключил отслеживание событий. Тогда надо в любую активную книгу добавить модуль(переходим в редактор VBA( Alt + F11 ) -Insert -Module), вставить в него приведенный ниже код и запустить:

Sub Reset_Events() Application.EnableEvents = True End Sub

чтобы запустить(выполнить) данный макрос макрос необходимо установить курсор в любое место между Sub и End Sub и нажать клавишу F5 .

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

В области навигации щелкните макрос правой кнопкой мыши и выберите конструктор.

На вкладке Конструктор в группе Сервис нажмите кнопку По шагам.

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

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

Выполните одно из следующих действий:

Чтобы выполнить макрокопрос, показанный в диалоговом окне Пошаговое действие макроса, нажмите кнопку Шаг.

Чтобы остановить макрос и закрыть диалоговое окно, нажмите кнопку Остановить все макрос.

Чтобы отключить пошаговую по

Замечания

Чтобы начать пошаговую по

Чтобы начать пошаговую Чтобы добавить в макрос SingleStep, с помощью следующей процедуры:

В каталоге макрокопроса в правой части окна конструктора макроса в поиске по запросу SingleStep. Если каталог действий не отображается, включайте его на вкладке Конструктор, а затем в группе Показать или скрыть щелкните Каталог действий.

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

Сохраните макрос, нажав кнопку Сохранить на панели быстрого доступа.

Предполагается, что при нажатии кнопки Выполнить пошаговая установка обычно продолжается до того места, где была вставлена макрос Шаг. В макрос можно добавить несколько действий SingleStep. При нажатии кнопки Продолжить в диалоговом окне Пошаговое действие макроса пошаговое отключение будет отключено до тех пор, пока не будет происходить следующая макрокоанда Шаг.

Если пошаговая поша Если вы запустите другой макрос, Access снова отобразит диалоговое окно Пошаговое решение макроса. Чтобы отключить пошаговое решение, нажмите кнопку Продолжить в диалоговом окне Пошаговое решение макроса или на вкладке Конструктор в группе Инструменты выберите пошаговое решение, чтобы он не выделялся.

Когда вы открываете книгу, которая была повреждена, приложение Excel автоматически запускает режим восстановления файлов и пытается открыть заново и одновременно восстановить ее.

Если сделать это не удалось, вы можете попробовать восстановить ее данные другими способами. В качестве профилактической меры рекомендуется чаще сохранять книгу и создавать ее резервную копию при каждом сохранении. Вы также можно настроить приложение Excel так, чтобы оно автоматически создавало файл восстановления через определенные промежутки времени. При этом у вас всегда будет доступ к рабочей копии книги, если оригинал окажется случайно удален или поврежден.

Восстановление поврежденной книги вручную

На вкладке Файл выберите команду Открыть.

В Excel 2013 или Excel 2016 щелкните место, где находится таблица, и нажмите кнопку Обзор.

В диалоговом окне Открытие выберите поврежденную книгу, которую нужно открыть.

Щелкните стрелку рядом с кнопкой Открыть и выберите команду Открыть и восстановить.

Выполните одно из указанных ниже действий.

Чтобы восстановить максимально возможное количество данных книги, нажмите кнопку Восстановить.

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

Восстановление данных из поврежденной книги

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

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

Чтобы восстановить данные, когда книга открыта в приложении Excel, выполните одно из перечисленных ниже действий.

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

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

На вкладке Файл выберите команду Открыть.

Дважды щелкните имя книги, которую необходимо открыть в приложении Excel.

Примечание: При этом будет восстановлена последняя сохраненная версия книги. Все изменения, которые могли привести к повреждению файла, будут удалены. Дополнительные сведения о восстановлении более ранних версий книги см. в статье Автоматическое сохранение и восстановление файлов Office.

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

Для сохранения книги в формате SYLK сделайте следующее.

Откройте вкладку Файл и выберите команду Сохранить как.

В списке Тип файла выберите пункт SYLK (Symbolic Link), а затем нажмите кнопку Сохранить.

Примечание: При использовании формата SYLK сохраняется только активный лист книги.

На вкладке Файл выберите команду Открыть.

Выберите сохраненный SLK-файл, а затем нажмите кнопку Открыть.

Примечание: Чтобы увидеть SLK-файл, вам может понадобиться выбрать вариант Все файлы или Файлы SYLK в списке Тип файлов.

На вкладке Файл выберите команду Сохранить как.

В поле Тип файла выберите Книга Excel.

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

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

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

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

Чтобы настроить пересчет вручную в Excel, выполните указанные ниже действия.

Убедитесь, что в Excel открыта новая пустая книга. В противном случае выполните указанные ниже действия.

На вкладке Файл нажмите кнопку Создать.

В разделе Доступные шаблоны выберите вариант Пустая книга.

На вкладке Файл нажмите кнопку Параметры.

В категории Формулы в группе Параметры вычислений установите переключатель в положение Вручную.

На вкладке Файл выберите команду Открыть.

Выберите поврежденную книгу и нажмите кнопку Открыть.

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

Для этого выполните указанные ниже действия.

На вкладке Файл выберите команду Открыть.

Выберите папку, содержащую поврежденную книгу, скопируйте имя файла, а затем нажмите кнопку Отмена.

Откройте вкладку Файл и выберите пункт Создать.

В разделе Доступные шаблоны выберите вариант Пустая книга.

В ячейке A1 новой книги введите =имя_файла!A1, где имя_файла — это имя поврежденной книги, которое вы скопировали в действии 2, а затем нажмите клавишу ВВОД.

Совет: Достаточно ввести только имя книги без расширения файла.

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

Если появится диалоговое окно Выбор листа, выберите нужный лист, а затем нажмите кнопку ОК.

Выберите ячейку A1.

На вкладке Главная в группе Буфер обмена выберите Копировать.

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

На вкладке Главная в группе Буфер обмена нажмите кнопку Вставить.

Пока диапазон ячеек выбран, на вкладке Главная в группе Буфер обмена снова нажмите кнопку Копировать.

На вкладке Главная в группе Буфер обмена щелкните стрелку под кнопкой Вставить, затем в группе Вставить значения выберите Значения.

Примечание: При вставке значений удаляются ссылки на поврежденную книгу и остаются только данные.

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

Для использования макроса выполните указанные ниже действия

Введите на листе модуля следующий код макроса:

Вставьте новый лист в книгу и переименуйте его в ChartData.

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

Примечание: Ее можно внедрить в лист или поместить на отдельный лист диаграммы.

Запустите макрос GetChartValues.

Данные из диаграммы будут помещены на лист ChartData.

Автоматическое сохранение резервной копии книги

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

На вкладке Файл выберите команду Сохранить как.

В Excel 2013 или Excel 2016 щелкните место, где находится таблица, и нажмите кнопку Обзор.

Щелкните стрелку вниз рядом с элементом Сервис, который находится около кнопки Сохранить, и выберите пункт Общие параметры.

Установите флажок Всегда создавать резервную копию.

Дополнительные сведения о сохранении и восстановлении более ранних версий книги, а также о восстановлении новых книг (которые еще не были сохранены) см. в статье Восстановление файлов Office.

Автоматическое создание файла восстановления через определенные промежутки времени

Функция периодического создания файла для восстановления создает резервную копию книги на случай, если ее оригинал будет случайно удален или поврежден.

На вкладке Файл нажмите кнопку Параметры.

В категории Сохранение в группе Сохранение книг установите флажок Автосохранение каждые и укажите количество минут.

В поле Каталог данных для автовосстановления введите расположение, в котором необходимо сохранять файл восстановления.

Убедитесь, что флажок Отключить автовосстановление только для этой книги снят.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Всем нам приходится - кому реже, кому чаще - повторять одни и те же действия и операции в Excel. Любая офисная работа предполагает некую "рутинную составляющую" - одни и те же еженедельные отчеты, одни и те же действия по обработке поступивших данных, заполнение однообразных таблиц или бланков и т.д. Использование макросов и пользовательских функций позволяет автоматизировать эти операции, перекладывая монотонную однообразную работу на плечи Excel. Другим поводом для использования макросов в вашей работе может стать необходимость добавить в Microsoft Excel недостающие, но нужные вам функции. Например функцию сборки данных с разных листов на один итоговый лист, разнесения данных обратно, вывод суммы прописью и т.д.

Макрос - это запрограммированная последовательность действий (программа, процедура), записанная на языке программирования Visual Basic for Applications (VBA). Мы можем запускать макрос сколько угодно раз, заставляя Excel выполнять последовательность любых нужных нам действий, которые нам не хочется выполнять вручную.

Способ 1. Создание макросов в редакторе Visual Basic

Для ввода команд и формирования программы, т.е. создания макроса необходимо открыть специальное окно - редактор программ на VBA, встроенный в Microsoft Excel.

В старых версиях (Excel 2003 и старше) для этого идем в меню Сервис - Макрос - Редактор Visual Basic(Toos - Macro - Visual Basic Editor).
В новых версиях (Excel 2007 и новее) для этого нужно сначала отобразить вкладку Разработчик (Developer) . Выбираем Файл - Параметры - Настройка ленты (File - Options - Customize Ribbon) и включаем в правой части окна флажок Разработчик (Developer) . Теперь на появившейся вкладке нам будут доступны основные инструменты для работы с макросами, в том числе и нужная нам кнопка Редактор Visual Basic(Visual Basic Editor)

:

К сожалению, интерфейс редактора VBA и файлы справки не переводятся компанией Microsoft на русский язык, поэтому с английскими командами в меню и окнах придется смириться:

Макросы (т.е. наборы команд на языке VBA) хранятся в программных модулях. В любой книге Excel мы можем создать любое количество программных модулей и разместить там наши макросы. Один модуль может содержать любое количество макросов. Доступ ко всем модулям осуществляется с помощью окна Project Explorer в левом верхнем углу редактора (если его не видно, нажмите CTRL+R). Программные модули бывают нескольких типов для разных ситуаций:

Обычный макрос, введенный в стандартный модуль выглядит примерно так:

Давайте разберем приведенный выше в качестве примера макрос Zamena:

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

Способ 2. Запись макросов макрорекордером

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

Макрорекордер записывает только те действия, которые выполняются в пределах окна Microsoft Excel. Как только вы закрываете Excel или переключаетесь в другую программу - запись останавливается.
Макрорекордер может записать только те действия, для которых есть команды меню или кнопки в Excel. Программист же может написать макрос, который делает то, что Excel никогда не умел (сортировку по цвету, например или что-то подобное).
Если во время записи макроса макрорекордером вы ошиблись - ошибка будет записана. Однако смело можете давить на кнопку отмены последнего действия (Undo) - во время записи макроса макрорекордером она не просто возрвращает Вас в предыдущее состояние, но и стирает последнюю записанную команду на VBA.

Чтобы включить запись необходимо:

в Excel 2003 и старше - выбрать в меню Сервис - Макрос - Начать запись(Tools - Macro - Record New Macro)
в Excel 2007 и новее - нажать кнопку Запись макроса (Record macro) на вкладке Разработчик (Developer)

Затем необходимо настроить параметры записываемого макроса в окне Запись макроса:

Имя макроса - подойдет любое имя на русском или английском языке. Имя должно начинаться с буквы и не содержать пробелов и знаков препинания.
Сочетание клавиш - будет потом использоваться для быстрого запуска макроса. Если забудете сочетание или вообще его не введете, то макрос можно будет запустить через меню Сервис - Макрос - Макросы - Выполнить(Tools - Macro - Macros - Run) или с помощью кнопки Макросы (Macros) на вкладке Разработчик (Developer) или нажав ALT+F8.
Сохранить в. - здесь задается место, куда будет сохранен текст макроса, т.е. набор команд на VBA из которых и состоит макрос.:
- Эта книга - макрос сохраняется в модуль текущей книги и, как следствие, будет выполнятся только пока эта книга открыта в Excel
- Новая книга - макрос сохраняется в шаблон, на основе которого создается любая новая пустая книга в Excel, т.е. макрос будет содержаться во всех новых книгах, создаваемых на данном компьютере начиная с текущего момента
- Личная книга макросов - это специальная книга Excel с именем Personal.xls, которая используется как хранилище макросов. Все макросы из Personal.xls загружаются в память при старте Excel и могут быть запущены в любой момент и в любой книге.
После включения записи и выполнения действий, которые необходимо записать, запись можно остановить командой Остановить запись (Stop Recording) .

Запуск и редактирование макросов

Управление всеми доступными макросами производится в окне, которое можно открыть с помощью кнопки Макросы (Macros) на вкладке Разработчик (Developer) или - в старых версиях Excel - через меню Сервис - Макрос - Макросы (Tools - Macro - Macros) :
- Любой выделенный в списке макрос можно запустить кнопкой Выполнить(Run) .
- Кнопка Параметры(Options) позволяет посмотреть и отредактировать сочетание клавиш для быстрого запуска макроса.
- Кнопка Изменить(Edit) открывает редактор Visual Basic (см. выше) и позволяет просмотреть и отредактировать текст макроса на VBA.
Создание кнопки для запуска макросов

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

Кнопка на панели инструментов в Excel 2003 и старше

Откройте меню Сервис - Настройка (Tools - Customize) и перейдите на вкладку Команды (Commands) . В категории Макросы легко найти веселый желтый "колобок" - Настраиваемую кнопку (Custom button) :

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

Кнопка на панели быстрого доступа в Excel 2007 и новее

Щелкните правой кнопкой мыши по панели быстрого доступа в левом верхнем углу окна Excel и выберите команду Настройка панели быстрого доступа (Customise Quick Access Toolbar) :

Затем в открывшемся окне выберите категорию Макросы и при помощи кнопки Добавить (Add) перенесите выбранный макрос в правую половину окна, т.е. на панель быстрого доступа:

Кнопка на листе

Этот способ подходит для любой версии Excel. Мы добавим кнопку запуска макроса прямо на рабочий лист, как графический объект. Для этого:
- В Excel 2003 и старше - откройте панель инструментов Формы через меню Вид - Панели инструментов - Формы (View - Toolbars - Forms)
- В Excel 2007 и новее - откройте выпадающий список Вставить (Insert) на вкладке Разработчик (Developer)
Выберите объект Кнопка (Button) :

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

Создание пользовательских функций на VBA

Создание пользовательских функций или, как их иногда еще называют, UDF-функций (User Defined Functions) принципиально не отличается от создания макроса в обычном программном модуле. Разница только в том, что макрос выполняет последовательность действий с объектами книги (ячейками, формулами и значениями, листами, диаграммами и т.д.), а пользовательская функция - только с теми значениями, которые мы передадим ей как аргументы (исходные данные для расчета).

Чтобы создать пользовательскую функцию для расчета, например, налога на добавленную стоимость (НДС) откроем редактор VBA, добавим новый модуль через меню Insert - Module и введем туда текст нашей функции:

Обратите внимание, что в отличие от макросов функции имеют заголовок Function вместо Sub и непустой список аргументов (в нашем случае это Summa). После ввода кода наша функция становится доступна в обычном окне Мастера функций (Вставка - Функция) в категории Определенные пользователем (User Defined) :

После выбора функции выделяем ячейки с аргументами (с суммой, для которой надо посчитать НДС) как в случае с обычной функцией:

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

Как восстановить макросы в excel

Замечания

Восстановление поврежденной книги вручную

Восстановление данных из поврежденной книги

Автоматическое сохранение резервной копии книги

Автоматическое создание файла восстановления через определенные промежутки времени

Дополнительные сведения

Способ 1. Создание макросов в редакторе Visual Basic

Способ 2. Запись макросов макрорекордером

Запуск и редактирование макросов

Создание кнопки для запуска макросов

Создание пользовательских функций на VBA