Макросы в excel примеры: Макросы для Excel с исходными кодами и описанием функций

TutorExcel.Ru — Полезные приемы работы в Excel — все о работе с макросами, диаграммами, графиками, формулами, таблицами и другие фишки.






Функция ВПР в Excel

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













Замена результата формулы на значение в Excel

Разберем как быстро сделать и получить результат формулы в виде значения (в любом: текстовом, числовом и прочих вариантах) в Excel.





tutorexcel Приемы
Комментариев нет


Замена результата формулы на значение в ExcelЧитать









Функция ЕСЛИ в Excel

Изучим функцию ЕСЛИ в Excel, которая позволяет в зависимости от выполнения условия возвращать разные значения, подробно разберем примеры с одним и несколькими условиями.













Комментарии к ячейке в формуле в Excel

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













Умная таблица в Excel

Узнаем все особенности умных таблиц в Excel, чем же они так хороши и какие преимущества они дают при работе с данными.













Группировка данных в сводной таблице в Excel

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





tutorexcel Сводные таблицы
2 комментария


Группировка данных в сводной таблице в ExcelЧитать









Радиальная гистограмма в Excel

Разберем интересную интерпретацию радиальной гистограммы в Excel позволяющая сравнивать несколько показателей и эффектно визуализировать данные.













Выделение столбцов или строк на диаграмме в Excel

Рассмотрим пример как можно визуально выделить столбец или строку на диаграмме в Excel, обладающий уникальным свойством (например, наибольшее или наименьшее значение).





tutorexcel Диаграммы
Комментариев нет


Выделение столбцов или строк на диаграмме в ExcelЧитать









Визуализация диаграммы с помощью фигур и рисунков в Excel

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





tutorexcel Диаграммы, Приемы
2 комментария


Визуализация диаграммы с помощью фигур и рисунков в ExcelЧитать









Как закрепить область в Excel?

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





tutorexcel Основы
1 коментарий


Как закрепить область в Excel?Читать



Страница 1 из 1212345…10…»Последняя »

Макрос на VBA Excel – Формируем документы по шаблону | Info-Comp.ru

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

Немного поясню задачу, допустим, нам необходимо сформировать какие-то специфические документы по шаблону массово, т. е. в итоге их получится очень много, как я уже сказал выше, например, по каждому сотруднику. И это нужно сделать непосредственно в Excel, если было бы можно это сделать в Word, то мы бы это сделали через «Слияние», но нам нужно именно в Excel, поэтому для этой задачи мы будем писать макрос.

Мы с Вами уже выгружали данные по шаблону через клиент Access из базы MSSql 2008 в Word и Excel вот в этой статье —  Выгрузка данных из Access в шаблон Word и Excel. Но сейчас допустим, у нас данные располагаются в базе, в клиенте которой нельзя или слишком трудоемко реализовать такую задачу, поэтому мы просто выгрузим необходимые данные в Excel и на основе таких данных по шаблону сформируем наши документы.

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

Напомню, что на данном сайте тема VBA Excel уже затрагивалась, например, в материале – Запрет доступа к листу Excel с помощью пароля

И так приступим!

Пишем макрос на VBA Excel по формированию документов

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

Примечание! Программировать будем в Excel 2010.

И для начала приведем исходные данные, т.е. сами данные и шаблон

Данные.

Лист, на котором расположены эти данные так и назовем «Данные»

Шаблон.

Лист, на котором расположен шаблон, тоже так и назовем «Шаблон»

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

Это делается очень просто, выделяете необходимую ячейку или диапазон, и жмете правой кнопкой мыши и выбираете «Присвоить имя», пишите имя ячейки и жмете «ОК»


Свои поля я назвал следующим образом:

  • ФИО – fio;
  • № — number;
  • Должность – dolgn;
  • Адрес проживания – addres;
  • Тел. № сотрудника – phone;
  • Комментарий – comment.

Код макроса на VBA Excel

Для того чтобы написать код макроса, открывайте на ленте вкладку «Разработчик», далее макросы.

Примечание! По умолчанию данной вкладке в Excel 2010 может и не быть, чтобы ее отобразить нажмите правой кнопкой по ленте пункт меню «Настройка ленты»

затем, в правой области поставьте галочку напротив пункта «Разработчик»

После вкладка разработчик станет отображаться на ленте.

Далее, когда Вы откроете вкладку разработчик и нажмете кнопку «Макросы» у Вас отобразится окно создания макроса, Вы пишите название макросы и жмете «создать».

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

Sub Карточка()
'Книга
NewBook = ""
' Путь, где будут храниться наши карточки
' Т.е. в той папке, откуда запустился файл с макросом
Path = ThisWorkbook. Path
' Выбираем лист с данными
Sheets("Данные").Select
' Запускаем цикл, скажем на 100000 итераций
' Начиная со второй строки, не учитывая заголовок
For i = 2 To 100000
' Выйдем из него, когда фамилии закончатся, т.е. строки
If Cells(i, 1).Value = "" Then
        i = 100000
    Exit For
End If
' Имя файла карточки, назовем по фамилии
Name_file = Path & "\" & Sheets("Данные").Cells(i, 1).Value & ".xls"
‘Выбираем лист с шаблоном
Sheets("Шаблон").Select
' Присваиваем значения нашим ячейкам, по именам которые мы задавали
    Range("fio").Value = Sheets("Данные").Cells(i, 1).Value & " " & _
        Sheets("Данные").Cells(i, 2).Value & " " & Sheets("Данные").Cells(i, 3).Value
    Range("number").Value = Sheets("Данные").Cells(i, 4).Value
    Range("addres").Value = Sheets("Данные").Cells(i, 5).Value
    Range("dolgn").Value = Sheets("Данные").Cells(i, 6).Value
    Range("phone"). Value = Sheets("Данные").Cells(i, 7).Value
    Range("comment").Value = Sheets("Данные").Cells(i, 8).Value
    ' Копируем все
    Cells.Select
    Selection.Copy
    ' Создаем новую книгу или делаем ее активной 
    If NewBook = "" Then
        Workbooks.Add
        NewBook = ActiveWorkbook.Name
    Else
        Workbooks(NewBook).Activate
        Cells(1, 1).Select
    End If
    ' Вставляем данные в эту книгу
    Application.DisplayAlerts = False
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ' Сохраняем с нашим новым названием
    ActiveWorkbook.SaveAs Filename:= _
    Name_file, FileFormat:=xlExcel8, _
    Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
    CreateBackup:=False
    NewBook = ActiveWorkbook.Name
    Application.DisplayAlerts = True
    ' Снова активируем файл с макросом и выбираем лист
    Workbooks("Макрос.xls"). Activate
    Sheets("Данные").Select
' Переходим к следующей строке
Next i
' Закроем книгу
Workbooks(NewBook).Close
' Выведем сообщение об окончании
MsgBox ("Выполнено!")
End Sub

Теперь осталось выполнить этот макрос, для этого откройте вкладку разработчик->макросы->выполнить наш макрос:

и после выполнения у Вас в той же папке появится вот такие файлы

Вот с таким содержимым:

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

Примеры макросов Visual Basic для работы с массивами

Резюме

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

Дополнительная информация

Корпорация Майкрософт предоставляет примеры программирования только для иллюстрации, без явных или подразумеваемых гарантий. Это включает, но не ограничивается, подразумеваемые гарантии товарного состояния или пригодности для конкретной цели. В этой статье предполагается, что вы знакомы с демонстрируемым языком программирования и инструментами, которые используются для создания и отладки процедур. Инженеры службы поддержки Майкрософт могут помочь объяснить функциональность конкретной процедуры, но они не будут изменять эти примеры, чтобы обеспечить дополнительную функциональность или создавать процедуры в соответствии с вашими конкретными требованиями. ПРИМЕЧАНИЕ. В процедурах Visual Basic для приложений слова после апострофа (‘) являются комментариями.

Чтобы заполнить массив, а затем скопировать его на рабочий лист

  1. Откройте новую книгу и вставьте лист модуля Visual Basic.

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

    Sub Sheet_Fill_Array()
   Dim myarray как вариант
   myarray = Массив (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
   Range("a1:a10").Value = Application.Transpose(myarray)
Конец сабвуфера

  3. Выберите Лист1.

  4. В меню «Инструменты» выберите «Макрос», а затем нажмите «Макросы».

  5. В диалоговом окне «Макрос» выберите Sheet_Fill_Array, а затем нажмите кнопку «Выполнить».

Для получения значений из рабочего листа и заполнения массива

  1. Введите значения на Лист1 в ячейки A1:A10.

  2. На листе модуля Visual Basic введите следующий код:

    Подпрограмма from_sheet_make_array()
   Затемнить этот массив как вариант
   этот массив = Диапазон ("a1: a10"). Значение

   counter = 1 'структура цикла для просмотра массива
   Пока счетчик <= UBound (этот массив)
      MsgBox этот массив (счетчик, 1)
      счетчик = счетчик + 1
   Венд
Конец сабвуфера

  3. org/ListItem">

    Выберите Лист1.

  4. В меню «Инструменты» выберите «Макрос», а затем нажмите «Макросы».

  5. В диалоговом окне "Макрос" щелкните from_sheet_make_array , а затем щелкните Выполнить.

Для передачи и получения массива

  1. На листе модуля введите следующий код:

    Sub pass_array()
   Затемнить этот массив как вариант
   этотмассив = Выбор.Значение
   receive_array (этот массив)
Конец сабвуфера

Sub Receive_array (этот массив)
   счетчик = 1
   Пока счетчик <= UBound (этот массив)
      MsgBox этот массив (счетчик, 1)
      счетчик = счетчик + 1
   Венд
Конец сабвуфера

  2. org/ListItem">

    Выберите Лист1 и выделите диапазон A1:A10.

  3. В меню «Инструменты» выберите «Макрос», а затем нажмите «Макросы».

  4. В диалоговом окне "Макрос" выберите pass_array, а затем нажмите кнопку "Выполнить".

Для сравнения двух массивов

  1. Создайте два именованных диапазона на Листе1. Назовите один диапазон1 и другой диапазон2.

    Например, выделите диапазон ячеек A1:A10 и назовите его range1; выделите диапазон ячеек B1:B10 и назовите его range2.

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

    Подпрограмма compare_two_array()
   Затемнить этот массив как вариант
   Dim thatarray как вариант

   этотмассив = Диапазон ("диапазон1"). Значение
   тотмассив = Диапазон ("диапазон2"). Значение
   счетчик = 1
   Пока счетчик <= UBound (этот массив)
      х = этот массив (счетчик, 1)
      y = тот массив (счетчик, 1)
      Если х = у Тогда
         MsgBox "да"
      Еще MsgBox "нет"
      Конец, если
      счетчик = счетчик + 1
   Венд
Конец сабвуфера

  3. Выберите Лист2.

  4. В меню «Инструменты» выберите «Макрос», а затем нажмите «Макрос».

  5. В диалоговом окне «Макрос» нажмите compare_two_array и нажмите «Выполнить».

    Вы увидите одно окно сообщения для каждого сравнения.

Для заполнения динамического массива

  1. На листе модуля введите следующий код:

    Sub fill_array ()

   Затемнить этот массив как вариант
   number_of_elements = 3 'количество элементов в массиве

   'необходимо исправить ниже, чтобы установить размер
   Изменить размер этого массива (от 1 до числа_элементов) как целое число
   'изменить этот размер массива
   счетчик = 1
   наполнить = 7
   Для counter = 1 To number_of_elements
      этот массив (счетчик) = fillmeup
   Следующий счетчик

   counter = 1 'этот цикл показывает, что было заполнено
   Пока счетчик <= UBound (этот массив)
      MsgBox этот массив (счетчик)
      счетчик = счетчик + 1
   Венд

Конец сабвуфера

  2. org/ListItem">

    В меню «Инструменты» выберите «Макрос», а затем нажмите «Макросы».

  3. В диалоговом окне "Макрос" выберите fill_array, а затем нажмите кнопку "Выполнить".

ПРИМЕЧАНИЕ. Изменение переменной "number_of_elements" определит размер массива.

макросов и примеров сценариев Excel

Все примеры включены в загрузку PyXLL.

Текстовая версия 

 """
 Примеры PyXLL: автоматизация
Рабочий лист PyXLL и функции меню могут вызывать обратно в Excel
 с помощью COM-API* для Excel*.
В дополнение к COM API есть несколько функций Excel.
 выставлены через PyXLL, которые позволяют вам запрашивать информацию о
 текущее состояние Excel без использования COM.
Excel использует разные политики безопасности для разных типов
 зарегистрированных в нем функций. В зависимости от
 тип функции, которую вы можете или не можете сделать
 несколько обращений к Excel.
Функции меню и макросы регистрируются как «команды».
 Команды могут свободно обращаться в Excel и вносить изменения в
 документы. Они эквивалентны подпрограммам VBA.
Функции рабочего листа регистрируются как «функции». Эти
 ограничены в своих возможностях. Вы будете в состоянии
 вернуться в Excel, чтобы прочитать значения, но не изменить
 что-либо. Большинство функций Excel, доступных через PyXLL
 не будет работать в функциях рабочего листа. Это эквивалентно
 к функциям VBA.
Существует третий тип функции — эквивалент макролиста.
 функции. Это функции рабочего листа, которым разрешено
 делать большинство вещей, макрос-функция (команда) будет разрешена
 делать. Их нельзя использовать легкомысленно, так как они могут сломаться.
 расчетные зависимости между ячейками, если нет
 используется осторожно.
* Поддержка Excel COM была добавлена ​​в Office 2000. Если вы
 используя более раннюю версию, эти примеры COM не будут работать.
 """
импортировать pyxll
 из pyxll импортировать xl_menu, xl_func, xl_macro
журнал импорта
 _log = logging.getLogger(__name__)
#
 # Получение COM-объекта Excel
 #
 # В PyXLL есть функция xl_app. Это возвращает приложение Excel
 # либо как объект win32com.client.Dispatch, либо как
 # объект comtypes (какой пакет com используется, можно установить в
 # файл конфигурации). По умолчанию используется win32com.
 #
 # Лучше использовать это, чем
 # win32com.client.Dispatch("Excel.Приложение")
 # так как это всегда будет правильный дескриптор - т.е. дескриптор
 # в правильный экземпляр Excel.
 #
 # Для получения дополнительной информации о win32com см. проект pywin32
 # на исходнике.
 #
 # Объектная модель Excel такая же, как в COM, так и в VBA.
 # поэтому обычно просто написать что-нибудь
 # на питоне, если вы знаете, как это сделать на VBA.
 #
 # Дополнительные сведения об объектной модели Excel
 # см. MSDN или обозреватель объектов в редакторе Excel VBA.
 #
 из pyxll импортировать xl_app
#
 # Простой пример функции меню, которая изменяет
 # содержимое выбранного диапазона.
 #
@xl_menu("тест win32com", sub_menu="Другие примеры")
 защита win32com_menu_test():
 # получить текущий выделенный диапазон и установить текст
 выбор = xl_app().Выбор
 selection.Value = "Привет!"
 pyxll.xlcAlert("Некоторый текст был записан в текущую ячейку")
#
 # Макросы также можно использовать для обратного вызова в Excel, когда
 # элемент управления активирован.
 #
 # Они работают так же, как макросы VBA, вы просто назначаете
 # их в элемент управления в Excel по имени.
 #
@xl_macro
 кнопка def_example():
 XL = XL_приложение ()
 диапазон = xl.Range("button_output")
 диапазон.значение = диапазон.значение + 1
@xl_macro
 защита checkbox_example():
 XL = XL_приложение ()
 check_box = xl.ActiveSheet.CheckBoxes(xl.Caller)
 если check_box.Value == 1:
 xl.Range("checkbox_output").Value = "ПРОВЕРЕНО"
 еще:
 xl.Range("checkbox_output").Value = "Установите флажок"
@xl_macro
 деф полоса прокрутки_пример():
 XL = XL_приложение ()
 вызывающий абонент = xl. Caller
 полоса прокрутки = xl.ActiveSheet.ScrollBars(xl.Caller)
 xl.Range("scrollbar_output").Value = полоса прокрутки.Value
#
 # Функции рабочего листа также могут вызывать обратно в Excel.
 #
 # Функция 'schedule_call' должна использоваться для выполнения
 # фактическая работа обратного вызова в Excel после того, как Excel
 # закончил расчет. В противном случае Excel может заблокироваться в ожидании
 # функция, которая должна завершиться перед тем, как разрешить COM-объект
 # для изменения листа, что вызовет тупиковую блокировку.
 #
 # Чтобы иметь возможность вызывать xlfCaller из функции рабочего листа,
 # функция должна быть объявлена ​​как эквивалент листа макросов
 # функция путем передачи macro=True в xl_func.
 #
 # Если ваша функция изменяет рабочий лист Excel, это может вызвать
 # перерасчет, так что вы должны позаботиться о том, чтобы не
 # вызвать бесконечный цикл, который приведет к зависанию Excel.
 #
 # Доступ к свойству 'address' возвращенной XLCell
 # by xlfCaller требует, чтобы эта функция была листом макросов
 # эквивалентная функция.