Управление надстройками в excel. Пошаговая инструкция по созданию надстройки Excel для ленты

Один из самых значимых навыков, которые я приобрел, работая финансовым аналитиком, является умение создавать специальные пользовательские надстройки для моего отдела и компании в целом. Этот навык позволил сохранить время и нервы для большого количества народа. Потратив более года, на то, чтобы научиться создавать первоклассные надстройки, я понял одну вещь – это совсем не сложно! И сегодня я хочу поделится с вами методом построения Excel надстройки, которая выглядит очень здорово, и которая приносит вашей карьере некоторые бонусы.

Оригинал статьи находится по ссылке

Данная статья делится на 5 простых шагов:

Шаг 1: Загрузить бесплатный шаблон (Я сделал всю рутину, которая отнимает время)

Шаг 2: Связать ваш макрос и описание с кнопкой на ленте

Шаг 3: Протестировать кнопку и убедиться, что она работает

Шаг 4: Выбрать иконку для кнопки (Micosoft предлагает их тысячи бесплатно)

К чему мы должны прийти в конечном итоге:

Шаг 1: Загрузить шаблон

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

Шаг 2: Связываем ваш макрос

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

1. Скрываем не используемые группы и кнопки.

Открываем редактор Visual Basic (с помощью нажатия кнопок Alt + F11). Так как мы будем создавать ленту с одной единственной кнопкой, а шаблон содержит 50 кнопок, нам необходимо скрыть остальные 49. Чтобы сделать это, переходим в модуль RibbonSetup в программу GetVisible. Вы увидите Select Case цикл, который проходит через каждую кнопку и указывает ленте, будет ли кнопка видимой или скрытой. Так как мы ходим оставить только одну кнопку видимой, меняем аргумент переменной MakeVisible на False у всех кнопок, кроме одной.

1. Добавляем код макроса

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

Возвращаемся в модуль RibbonSetup, переходим к процедуре RunMacro. Добавляем имя макроса, который мы добавили до этого, к соответствующей кнопке (необходимо переписать DummyMacro ).

1. Добавляем надпись подсказки к макросу

Хороший способ помочь пользователям вспомнить для чего кнопка нужна – добавить всплывающую подсказку. Подсказка – это надпись, которая появляется рядом с кнопкой, когда на нее наводят курсор мыши. Вы постоянно видите эти надписи у нормальной ленты, но могли не обращать внимания на них. Чтобы добавить подсказку к нашей кнопке, переходим в процедуру GetScreentip и меняем аргумент у переменной Screentip на текст, который бы описывал назначение данной кнопке. В нашем случае напишем Вставить только значения.

1. Добавляем имена вкладки, группы и кнопки

Чтобы закончить с настройкой кода, переходим в процедуру GetLabel, которая находится в модуле RibbonSetup. Точно так же, как мы добавляли текст вплывающей подсказки, вы можете добавить надписи, которые будут отображаться рядом с кнопкой на ленте.

К примеру, назовем нашу вкладку Company, нашу группу PasteSpecial, а кнопку Вставить значения. Из рисунка ниже понятно, что необходимо поменять аргумент переменной Labeling на текcт, который мы хотим отобразить на ленте.

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

Шаг 3: Проверка работоспособности

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

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

Шаг 4: Выбор иконок

Следующий шаг самый интересный – выбор иконок для надстройки. К счастью, Microsoft любезно предоставила полный доступ ко всем имеющимся иконкам в Microsoft Office.

Так как же получить все эти замечательные иконки? Для этого вам необходимо всего лишь сказать Microsoft, какую иконку вы планируете использовать, указав название иконки в коде макроса. Перейдите в процедуру GetImage и замените аргумент переменной RibbonImage у соответствующей строки. Так как в нашем примере мы имеем дело со вставкой, воспользуемся иконкой Вставить значения.

Как получить названия иконок?

Есть несколько ресурсов, где можно получить названия иконок для ленты, но лично я предпочитаю Excel файл, созданный компанией Microsoft, под названием Office 2007 Icons Gallery . В этом файле отображены все иконки, имеющиеся в наличии и находятся во вкладке Разработчик в 9 папках на ленте. Если вы наведете курсор мыши на иконку, программа отобразит всплывающую подсказку с названием иконки. Вам необходимо скопировать это имя дословно (с учетом регистра) и добавить его в макрос процедуры GetImage , как мы делали ранее.

Как изменить размер иконки?

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

Чтобы изменить размер иконки, перейдите в процедуру GetSize и измените текст у соответвующей кнопки на Large или Small.

Шаг 5: Сохранить файл как надстройку

Последний шаг, который необходимо сделать – это сохранить наш файл как надстройку. Надстройки Excel имеют расширение . xlam, так что убедитесь, что вы выбрали именно этот формат, когда будете сохранять книгу. После того, как вы сохранили файл надстройки, вы можете закрыть шаблон (.xlsm формат) и установить новую надстройку в программу. Для этого открываем любой файл Excel, переходим во вкладку Файл в пункт Параметры. Перед нами появится диалоговое окно Параметры Excel, переходим во вкладку Надстройки. Щелкаем кнопку Перейти, в появившемся окне ставим галочку напротив названия нашей надстройки и жмем кнопку OK.

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

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

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

Выбор компонентов при установке Excel

При первоначальной установке Excel или при последующем изменении (в составе пакета Microsoft Office) требуемые надстройки могут быть выбраны и установлены так же, как и любой другой компонент, входящий в Excel. Первоначальную установку можно инициировать различными способами (например, запустив программу setup.exe, расположенную на инсталляционном диске). Следуя указаниям инсталляционной программы, через ряд шагов откроется окно Установка Microsoft Office 2010 (Microsoft Office 2010) - рис. 1.

Для того чтобы установить/удалить надстройки после того, как Microsoft Office был установлен, сделайте следующее:

1. Закройте все работающие программы.
2. Выполните команду Windows Пуск → Настройка → Панель управления (Start → Settings → Control Panel).
3. Дважды щелкните по значку Установка и удаление программ (Add/Remove Programs), в результате чего откроется диалоговое окно Установка и удаление программ (Add/Remove Programs).
4. В списке установленных программ найдите и выделите строку Microsoft Office.
5. Нажмите кнопку Изменить (Change).
6. Через некоторое время на экране появится диалоговое окно Microsoft Office с подзаголовком Измените текущую установку Microsoft Office (Change your installation of Microsoft Office) (рис. 2). В этом окне выберите пункт Добавить или удалить компоненты
   (Add or Remove Features). В результате откроется диалоговое окно Microsoft Office с подзаголовком Параметры установки (рис. 1).

Если производится первоначальная установка Microsoft Office, убедитесь, что доступного места на диске достаточно, в противном случае смените диск (если имеется такая возможность) или уменьшите количество устанавливаемых программных компонентов - если места все равно не хватает, придется почистить диск или отказаться от установки.

При добавлении/удалении компонентов указываемое программой значение необходимого для установки дискового пространства не всегда соответствует действительности (поскольку в этом случае обычно большая часть компонентов уже установлена). Это окно содержит многоуровневый список, из которого можно выбрать добавляемые компоненты (рис. 3). «Спуститесь» по списку до уровня Microsoft Office\Microsoft Office Excel\Надстройки (Microsoft Office/Microsoft Office Excel\Add-ins) и выберите желаемый режим установки требуемых надстроек.

Для удаления не использующейся более надстройки выберите для нее режим Компонент недоступен (Not available).

После того как все необходимые установки будут сделаны, нажмите кнопку Продолжить (Continue), чтобы начать установку (переустановку). Надстройки будут доступны после завершения установки (и перезагрузки компьютера). Однако для непосредственного использования их еще необходимо загрузить.

Загрузка и выгрузка надстроек

Если надстройка уже установлена, то для использования дополнительных возможностей, предоставляемых ею, она должна быть загружена (запущена). Для запуска надстройки необходимо воспользоваться командой Файл → Параметры (File → Options). Далее в левой части открывшегося окна Параметры Excel (рис. 4) выбрать Надстройки → Перейти… (Add-Ins → Go…). Если в диалоговом окне Надстройки (Add-Ins) (рис. 5) отсутствует необходимая надстройка, следует нажать кнопку Обзор… (Browse…), чтобы указать ее местоположение вручную.

В случае если искомая надстройка не установлена, необходимо ее установить (Процесс установки надстроек см. ранее в этой статье.)

В диалоговом окне Надстройки (Add-Ins) установите флажок напротив требуемых надстроек и нажмите кнопку OK. После этого становятся доступными все дополнительные свойства и функции, связанные с выбранной надстройкой. При всех последующих запусках Excel будет загружаться и данная настройка. Для экономии памяти и повышения быстродействия можно выгрузить неиспользуемые надстройки. После выгрузки предоставляемые надстройкой полезные свойства и функции становятся недоступными, однако сама надстройка не исчезает и может быть легко запущена впоследствии. Выгрузка надстройки не освобождает занимаемую ею память, это произойдет только после перезапуска Excel.

Следует отметить, что начиная с последних версий Microsoft Excel, ряд надстроек, входивших в состав предыдущих версий, по разным причинам отсутствует, хотя появилось несколько новых надстроек (доступные надстройки представлены на рис. 5). По поводу надстроек, которые присутствовали в предыдущих версиях, но отсутствуют в Excel 2010/2013, в имеющейся на сайте Microsoft документации сказано, что недостающие надстройки предыдущих версий .

В основном, данный файл предназначен для подключения/удаления надстроек, выложенных на данном сайте на странице Мои надстройки .

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

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

Удалить надстройку -надстройка, выбранная в соответствующем поле, будет удалена и отключена. Так же будет удален файл помощи для данной надстройки.

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

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

Скачать программу

(305,8 KiB, 6 394 скачиваний)

Внимание: программа распространяется бесплатно. Распространение данной программы в коммерческих целях запрещено.

1. в папку надстроек Excel, как правило она расположена по адресу:
   C:\Documents and Settings\Дмитрий\Application Data\Microsoft\AddIns ;
   для Windows 7: C:\Users\Дмитрий\AppData\Roaming\Microsoft\AddIns
2. в любое место на локальном диске;

Вместо "Дмитрий" - Ваша учетная запись на компьютере.
Запускаем Excel и выбираем:

Если надстройка была помещена в папку C:\Documents and Settings\Дмитрий\Application Data\Microsoft\AddIns , то она будет отображена в списке и Вам надо будет только выбрать её, поставив галочку.

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

После подключения и установки надстройки для версий Excel 2007 и старше на панели появится новая вкладка(если она еще не отображена) - "Надстройки" (Add-Ins) и на ней будут показаны элементы, относящиеся к данной надстройке. Для версий Excel 2003 и ниже - команды новой надстройки будут созданы вместе с новой панелью, которая должна отобразиться сразу после установки надстройки.

Вкладка "Надстрйоки" и меню появляются только в том случае, если это предусмотрено надстройкой. Если в надстройке не реализован визуальный интерфейс, а присутствуют лишь функции пользователя или процедуры, запускаемые не с панели - то панель не появится.

В этом вводном пошаговом руководстве показано, как создавать надстройки уровня приложения для Microsoft Office Excel. Функции, создаваемые в подобном решении, доступны для приложения независимо от того, какие книги открыты.

Применимость. Информация в этой статье относится к проектам надстроек VSTO для Excel. Дополнительные сведения см. в разделе .

В данном пошаговом руководстве рассмотрены следующие задачи:

Создание проекта надстройки VSTO Excel для Excel.

Написание кода с использованием объектной модели Excel, которая при сохранении книги добавляет в нее текст.

Построение и запуск проекта для тестирования.

Удаление завершенного проекта для прекращения автоматического запуска надстройки VSTO на компьютере разработчика.

Создание проекта надстройки VSTO Excel в Visual Studio

Запустите Visual Studio.

В меню Файл выберите пункт Создать , а затем команду Проект .

В области шаблонов разверните узел Visual C# или Visual Basic , а затем узел Office/SharePoint .

В развернутом узле Office/SharePoint выберите узел Надстройки Office .

В списке шаблонов проектов выберите Надстройку Excel 2010 или Надстройку Excel 2013 .

В поле Имя введите FirstExcelAddIn .

Нажмите кнопку ОК .

Visual Studio создает проект FirstExcelAddIn и открывает файл кода ThisAddIn в редакторе.

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

Частичное определение класса ThisAddIn . Этот класс предоставляет точку входа для кода и обеспечивает доступ к объектной модели Excel. Остальная часть класса ThisAddIn определяется в скрытом файле кода, изменять который не следует.

Обработчики событий ThisAddIn_Startup и ThisAddIn_Shutdown . Эти обработчики событий вызываются, когда Excel загружает и выгружает надстройку VSTO. Их можно использовать для инициализации надстройки VSTO в процессе ее загрузки, а также для освобождения используемых надбавкой ресурсов при ее выгрузке. Для получения дополнительной информации см. .

Добавление строки текста в сохраненную книгу

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

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

Void Application_WorkbookBeforeSave(Microsoft.Office.Interop.Excel.Workbook Wb, bool SaveAsUI, ref bool Cancel) { Excel.Worksheet activeWorksheet = ((Excel.Worksheet)Application.ActiveSheet); Excel.Range firstRow = activeWorksheet.get_Range("A1" ); firstRow.EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown); Excel.Range newFirstRow = activeWorksheet.get_Range("A1" ); newFirstRow.Value2 = ; }

Private Sub Application_WorkbookBeforeSave(ByVal Wb As Microsoft.Office.Interop.Excel.Workbook, ByVal SaveAsUI As Boolean , ByRef Cancel As Boolean ) Handles Application.WorkbookBeforeSave Dim activeWorksheet As Excel.Worksheet = CType (Application.ActiveSheet, Excel.Worksheet) Dim firstRow As Excel.Range = activeWorksheet.Range("A1" ) firstRow.EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown) Dim newFirstRow As Excel.Range = activeWorksheet.Range("A1" ) newFirstRow.Value2 = "This text was added by using code" End Sub

Если используется C#, добавьте в обработчик событий ThisAddIn_Startup указанный ниже код. Он используется для подключения обработчика событий Application_WorkbookBeforeSave к событию .

This .Application.WorkbookBeforeSave += new Microsoft.Office.Interop.Excel.AppEvents_WorkbookBeforeSaveEventHandler(Application_WorkbookBeforeSave);

Для изменения книги при ее сохранении в приведенных выше примерах кода используются следующие объекты:

Поле Application класса ThisAddIn . Поле Application возвращает объект , который представляет текущий экземпляр Excel.

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

Тестирование проекта

Нажмите клавишу F5 для построения и запуска проекта.

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