"Специальная вставка" в 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