03 Ноя 2009

В разделе Excel, уроки

Использование макросов Microsoft Excel для создания циклов

spreadsheetМакросы Office — это простые, легко адаптируемые и вместе с тем мощные инструменты, позволяющие пользователю экономить свое время и работать более продуктивно. Циклы, представляющие собой разновидность макросов, особенно полезны при работе с диапазонами данных. Возможно, вы уже что-то слышали о макросах, возможно, даже использовали их в ; как бы то ни было, пришла пора познакомиться с ними поближе.

Что такое макросы? Где они хранятся? Зачем они нужны? Очень хорошие вопросы. Макросы Excel — это фрагменты кода, которые хранятся в книге и позволяют автоматизировать некоторые процедуры систематизации данных. Они значительно облегчают работу и потому заслуживают вашего внимания.

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

  • Макрос: фрагмент кода, выполняющий определенное действие и имеющий уникальное имя.
  • VBA: Visual Basic для приложений — язык программирования макросов.
  • Модуль: контейнер для хранения макросов, закрепленный за книгой.

editorПредположим, вы хотите написать макрос. Первое, что необходимо сделать, — это открыть редактор Visual Basic. Выделите в меню Сервис пункт Макрос и выберите команду Редактор Visual Basic. (здесь и далее пункты меню даются по версии Microsoft Office 2003)

Почему это следует сделать в первую очередь? Дело в том, что редактор Visual Basic — это основной инструмент создания и редактирования кода VBA. Впрочем, это ясно из названия.
Следующий шаг: прежде чем писать макрос, необходимо решить, где он будет располагаться. Не забыли про модули? Они служат контейнерами для макросов и прикрепляются к книге с помощью объемлющего контейнера, называемого проектом VBA. Для добавления нового модуля в редакторе Visual Basic следует выбрать команду Module в меню Insert (меню редактора, а не электронной таблицы). При этом внутри основного окна редактора Visual Basic откроется пустое окно модуля.

modul

sub_macro_excelЧтобы написать собственно макрос, введите в окне модуля слово Sub. (Зачем? Неважно — просто таково правило.) Затем через пробел введите имя макроса. Например, если вы ввели Sub MyMacro, это означает, что создан макрос с именем MyMacro. Редактор Visual Basic очень умный: он автоматически вставит вслед за строкой Sub строку, содержащую End Sub, — не правда ли, удобно? Итак, у вас есть начало и конец, осталось лишь написать что-нибудь в промежутке — там, где как раз есть пустое место.

sub_macro_excel2Предположим, вы выполнили все эти действия и создали макрос. Поскольку сейчас между строкой Sub и строкой End Sub ничего нет, этот макрос на самом деле ничего и не будет делать. Чтобы он стал продуктивным, необходимо добавить код. Рассмотрим пример. Допустим, требуется вывести простое сообщение. Между строкой Sub и строкой End Sub введите:

MsgBox "Мой первый макрос."

Не подумайте, что в слове MsgBox содержатся опечатки, — это ключевое слово VBA для обозначения окна сообщения. Язык VBA требует скрупулезной точности: необходимо ввести буквально то, что написано выше, и обязательно поставить остальную часть текста этой строки кода в кавычки. Если выполнить этот макрос в Excel, появится окно сообщения, содержащее текст Мой первый макрос и кнопку ОК, которая позволяет закрыть окно. В ходе предстоящего практического занятия вы получите возможность написать и выполнить этот макрос.

Совет

Если вы хотите создать собственный пример кода, удобнее всего сначала записать макрос для требуемого действия. Выделите в меню Сервис пункт Макрос и выберите команду Начать запись. Закончив запись макроса, откройте Редактор Visual Basic и посмотрите, как действует его код. Выделите в меню Сервис пункт Макрос, выберите команду Макросы и найдите записанный макрос. Чтобы просмотреть его код, выделите имя макроса и нажмите кнопку Изменить.

Для дальнейшей работы вам потребуется загрузить файл с упражнением.

Загрузите его по ссылке: macros

На компьютере должен быть установлен Excel 2003

Добавление модуля и составление макроса


Упражнение 1. Добавление модуля

  1. Сначала необходимо открыть редактор Visual Basic. Выделите в меню Сервис пункт Макрос и выберите команду Редактор Visual Basic.Советы
    • Если в меню нет команды Макрос, подождите несколько секунд, и она появится.
    • После запуска редактора Visual Basic данные инструкции будут закрыты новым окном. Чтобы инструкции были всегда доступны, напечатайте эту страницу (щелкните правой кнопкой мыши и выберите в контекстном меню команду Печать).Закончив работать в редакторе Visual Basic, вернитесь в Excel, выбрав в меню File команду Close and Return to Microsoft Excel.
  2. В редакторе Visual Basic откройте меню Insert и выберите команду Module. Появится пустое окно. Тем самым в книгу добавлен модуль.

Примечание. Если после добавления модуля пустое окно не появилось, выберите в меню View редактора Visual Basic команду Code или нажмите клавишу F7.

Упражнение 2. Составление макроса

  1. Щелкните внутри пустого окна и введите Sub MyMacro. Нажмите клавишу ВВОД.
  2. При этом автоматически вставится строка End Sub , а после имени макроса добавится пара скобок. Код примет следующий вид:Sub MyMacro()
    End Sub
    Примечание. Пустые строки в VBA игнорируются. Также игнорируются строки, начинающиеся с апострофа, что позволяет включать в код комментарии, не влияющие на его выполнение. Помимо начального апострофа, комментарии выделяются зеленым цветом букв, так что их легко заметить на фоне обычного кода.
  3. В пустой строке между строкой Sub MyMacro() и строкой End Sub введите следующий код (в точности так, как он приводится здесь, включая кавычки).MsgBox "Мой первый макрос."
  4. Щелкните в строке Sub MyMacro() и нажмите клавишу F5.
  5. Ура! Появляется окно сообщения с текстом Мой первый макрос и кнопкой ОК.
  6. Закройте окно сообщения, нажав в нем кнопку ОК. Выберите в меню File команду Close and Return to Microsoft Excel, чтобы закрыть редактор Visual Basic, и затем закройте книгу. Будет предложено сохранить книгу; можете сделать это, если хотите вернуться к ней позже.

Продолжение — на следующей странице

Метки:, , , , ,

, , ,

  • Mikael Mkrtchyan

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

    Sub Macro6()» Macro6 Macro»    ActiveSheet.PivotTables(«PivotTable25″).PivotFields(«Destination»).ShowDetail _        = True    Application.CutCopyMode = False    ActiveSheet.PivotTables(«PivotTable25″).ChangePivotCache ActiveWorkbook. _        PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _        «C:Documents and SettingsAdministratorDesktopMayprojectMay 27-[All_traffic-2.xlsx]IN!R4C1:R351C15″ _        , Version:=xlPivotTableVersion12)End Sub

    Жирным записана крайняя точка области с которой должен работать записанный макрос. поскольку я каждый день в тот же Sheet добавляю новую информацию, получается что записанный макрос не охватывате новых записей так-как макросу заданы конкретные кординаты.
    Пожалуста помогите сделать так чтоб макрос всегда вуполнял свои действия охватывая новые записи (т.е авто превращялся в скажем R380C15 где количество строк это количество заполненных строк до первой пустой строки или Cell-а)

    заранее благодарен

  • Borisenkovm

    Уважаемые коллеги, хочу проконсультироваться по Макросам. Я начинающий, написал Макрос для переноса результатов анкетирования в электронную таблицу. На каждого опрашиваемого у меня отведено по две строчки таблицы. В ячейках таблицы я использую формулы для пересчета анкетных данных. На данный момент мой Макрос может заносить данные из анкет, расположенных на двух других страницах книги, в таблицу, расположенную на отдельном листе. После чего расчетные данные преобразует в числа, а ячейки с формулами переносит в две строки, расположенные ниже. Ячейки в анкетах обнуляются. К сожалению, я не могу придумать алгоритм, с помощью которого результаты опроса следующего испытуемого заносились бы в следующие строчки таблицы.  
    Подскажите, какой оператор использовать для этого. 

  • http://shperk.ru shperk

    К сожалению, помочь вот так, не видя самих макросов невозможно. Принципиального органичения на сто записей нет, то есть, скорее всего, оно предусмотрено алгоритмом.
    Можно попытаться его изменить. Создайте копию книги, откройте окно VBA и просто поиском поищите число 100. Если хоть немного понимаете Basic, то, надеюсь найдете это ограничение. Меняйте, тестируйте, если нет — всегда можно откатиться назад, вернув прежнее значение. Впрочем, даже если что-то напортачите — вы же работаете с копией.

    Второй вариант — просто тупо заведите для второй сотни вторую книгу.

  • Артем

    Помогите мне пожалуйста. Я работаю учителем на спецпредприятии, запись посещения работниками техзанятий производится в Exell с помощью макросов, я ввожу табельный номер работника и дату посещения, макрос автоматически находит этого работника в списке и ставит ему в соответствующей ячейке цифру «1». Очень удобная вещь! Но у меня появилась проблема: Предприятие увеличивается и число работников становится больше, а макрос находит работника по списку не более «100». Если записать работника по списку 101-ым, то макрос его не находит. В моей книге Exell 8 листов, Если добавить 9-ый лист и последующие, и разместить на нем список работников, то макрос не берет во внимание листы более 8-ми, и соответственно не находит этих работников. Как мне изменить настройки макросов, что бы число записываемых строк было более 100, а число листов было более 8-ми? Эту книгу записывал один програмист в 2009 году, и давно уволился. Помогите мне пожалуйста с этой проблеммой.

  • Pingback: 10 мест, где можно скачать бесплатные шаблоны PowerPoint « Не волнуйтесь, я сейчас все объясню!()

  • Pingback: » 2 источника бесплатных фотографий для PowerPoint *** "Не волнуйтесь, я сейчас все объясню!"()

  • Nik-ksenja-2007

    относительно понятно

  • ARix

    Здесь можно скачать excel макрос для переноса таблиц в html формат
    http://www.homepg.ru/index.php?option=com_conte

  • Pingback: » Включаем компьютер автоматически: проще, еще проще *** "Не волнуйтесь, я сейчас все объясню!"()

  • Pingback: » Бесплатные образовательные ресурсы от федерального правительства США *** "Не волнуйтесь, я сейчас все объясню!"()

  • Pingback: » Что за странные адреса вида R1C1 в Excel? *** "Не волнуйтесь, я сейчас все объясню!"()