RSS

«Специальная вставка» в Excel. Горячие клавиши для специальной вставки на VBA

   Пользователи, которые постоянно работают с Excel, наверняка сталкивались с проблемой копирования данных ячейки и их вставки в другие книги (листы), а именно, проблема в том, что при копировании формул и функций, возникают проблемы смещения, да и форматы ячеек могут мешать. Чтобы это избежать в Excel для этого предусмотрена «Специальная вставка«, и возможно копию вставить как: «Значения«, «Формулы«, «Форматы» и т.д. Вот только на варианты этой вставки почему-то не предусмотрены «горячие клавиши», а без «горячих клавиш» очень замедляется процесс работы. Я решил для себя проблему с помощью макросов и сейчас расскажу как.

Итак, создадим новую книгу, сохраним ее под именем «Вставка.xls» и перейдем в Редактор Visual Basic (Alt+F11).
Вставим новый модуль «Insert->Module» (как создать макросы, описано здесь). Теперь добавим туда следующий код:

Sub ВставитьЗначАнгл()
‘Сочетание клавиш: Ctrl+q
     ВставитьКак 1
End Sub

Sub ВставитьЗначРус()
‘Сочетание клавиш: Ctrl+й
     ВставитьКак 1
End Sub

Sub ВставитьФормулаАнгл()
‘ Сочетание клавиш: Ctrl+e
     ВставитьКак 2
End Sub

Sub ВставитьФормулаРус()
‘ Сочетание клавиш: Ctrl+у
     ВставитьКак 2
End Sub

Private Sub ВставитьКак(id As Byte)
  If Application.CutCopyMode = xlCopy Then ‘проверка скопированы ячейки или нет
       Select Case id
             Case 1: Selection.PasteSpecial Paste:=xlPasteValues ‘вставляет в выбранную ячейку значение
             Case 2: Selection.PasteSpecial Paste:=xlPasteFormulas ‘вставляет в выбранную ячейку формулу
        End Select
  End If
End Sub

Процедуры «ВставкаЗначАнгл» и «ВставкаЗначРус» вставляют скопированную ячейку как значение.
Процедуры «ВставкаФормулаАнгл» и «ВставкаФормулаАнгл» вставляют как формулы.

Процедура «ВставитьКак (id as byte)»

id — идентификатор команды, указывающий как необходимо вставить (1- Значения, 2 — Формулы, ….)

В условии If проверятся, есть ли скопированные ячейки(-ка) в приложении Excel (Application.CutCopyMode = xlCopy, для определения «Вырезать» значение xlCut).

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

Если есть скопированные ячейки, то Select Case id:

  • если id = 1, то выполняем команду Selection.PasteSpecial Paste:=xlPasteValues (вставка значения)
  • если id = 2,  то Selection.PasteSpecial Paste:= xlPasteFormulas (вставка формулы)
  • и т.д.

Как работает Select Case читаем в статье «Пример работы с оператором Select..Case в VBA «.

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

В процедурах «ВставкаЗначАнгл«, «ВставкаЗначРус«, «ВставкаФормулаАнгл» и «ВставкаФормулаАнгл» происходит вызов процедуры ВставитьКак с указанием в ней идентификатора команды 1 или 2.

Закрываем редактор Visual Basic и переходим в окно управления макросами «Сервис->Макрос->Макросы…» (Alt+F8). Мы видим наши четыре макроса:

Теперь необходимо каждому макросу присвоить горячую клавишу. Запуск макросов в Excel может осуществляться с помощью комбинации «Ctrl + назначенная клавиша«. Из клавиш, сочетающихся с ctrl, я нашел свободные Q(Й) и E(У). Для удобства в самый раз, нажатие левой рукой(правая с мышей) и клавиши не далеко друг от друга, вот правда сосед опасный, ctrl+w — закрывает книгу.

Выбираем макрос «ВставкаЗначАнгл«, жмем кнопку «Параметры…«

в открывшемся окне, там где «сочетание клавиш», указываем «q«. Жмем «ОК«. Затем присвоим клавишу «й» для «ВставкаЗначРус«.

Для чего такое назначение клавиш? 

Просто в Excel, сочетание CTRL с одной и той же клавишей, но в разных раскладках(RU, EN), почему-то отличаются. Поэтому, для того чтоб вставка произошла при любой раскладке клавиатуры, и сделано по два макроса с присвоенными им клавишами в разной раскладке.

Аналогично присвойте клавиши «e» и «у» процедурам «ВставкаФормулаАнгл» и «ВставкаФормулаАнгл«.

Все! Теперь можете опробовать работу макросов.

Для того чтобы макросы работали при запуске Excel во всех книгах я подключил их как «Надстройку». Чтобы это сделать необходимо:
Сохранить нашу книгу «Вставка.xls», в формат надстройки — «Файл->Сохранить как«, в списке тип файла выбрать «Надстройка Microsoft Office Excel (*.xla)«

Затем, в Excel необходимо открыть окно «Надстройки» (Сервис->Надстройки…)
Щелкнуть «Обзор» и выбрать сохраненную нами надстройку «Вставка.xla«. В списке должна появится новая надстройка «Вставка», если не стоит галочка, то отметьте ее. Жмем ОК.

Данный макрос также работает и в более поздних версиях Excel (2007-2010). Где найти окно «Надстройки» в Excel 2007-2010 читайте в статье «Открываем панель «Разработчик» в Excel 2010«.

А на этом все. Благодарю за внимание. Надеюсь статья для Вас будет полезна. 

Прикрепленный файл: hot_special_paste.zip