Автоматизация рутины в Microsoft Excel при помощи VBA / Geektimes. Приветствую всех. В этом посте я расскажу, что такое VBA и как с ним работать в Microsoft Excel 2. VBA (Visual Basic for Applications) — это упрощенная версия Visual Basic, встроенная в множество продуктов линейки Microsoft Office. Она позволяет писать программы прямо в файле конкретного документа. Вам не требуется устанавливать различные IDE — всё, включая отладчик, уже есть в Excel. Еще при помощи Visual Studio Tools for Office можно писать макросы на C# и также встраивать их. Спасибо, Fire. Storm. Сразу скажу — писать на других языках (C++/Delphi/PHP) также возможно, но требуется научится читать, изменять и писать файлы офиса — встраивать в документы не получится. А интерфейсы Microsoft работают через COM. Чтобы вы поняли весь ужас, вот Hello World с использованием COM. Поэтому, увы, будем учить Visual Basic. В новых версиях (Excel 2007 и новее) для этого нужно сначала. Давайте разберем приведенный выше в качестве примера макрос . Полный курс бесплатных видеороликов, посвященных основам автоматизации в Excel, смотрите на моем сайте: . Следовательно, овладев VBA для Excel, вы сможете сразу. Рассмотрим в качестве примера фрагмент кода, приведенный выше. Не подскажете как в Excel 2007 можно записать последовательность нажатия . Применимо к: Excel 2010 . Это пример технологии IntelliSense, то есть редактор Visual Basic . Программирование. Язык VBA: 3.2. Пример: 3.3. Ссылки на примеры помещены в текст как объекты Excel. Другие пособия по. Меню в Excel 2007 существенно отличается от прежней версии. Там, вместо обычных . Макросы в Excel: полезные надстройки и функции Excel VBA, примеры пользовательских функций и заготовки для макросов VBA Excel, . Чуть- чуть подготовки и постановка задачи. Итак, поехали. Открываем Excel. Для начала давайте добавим в Ribbon панель «Разработчик». В ней находятся кнопки, текстовые поля и пр. Недавно мне потребовалось красиво оформить прайс- лист, выглядевший, как таблица. Идём в гугл, набираем «прайс- лист» и качаем любой, который оформлен примерно так (не сочтите за рекламу, пожалуйста): То есть требуется, чтобы было как минимум две группы, по которым можно объединить товары (в нашем случае это будут Тип и Производитель — в таком порядке). Для того, чтобы предложенный мною алгоритм работал корректно, отсортируйте товары так, чтобы товары из одной группы стояли подряд (сначала по Типу, потом по Производителю). Результат, которого хотим добиться, выглядит примерно так: Разумеется, если смотреть прайс только на компьютере, то можно добавить фильтры и будет гораздо удобнее искать нужный товар. Однако мы хотим научится кодить и задача вполне подходящая, не так ли? Кодим. Для начала требуется создать кнопку, при нажатии на которую будет вызываться наша програма. Кнопки находятся в панели «Разработчик» и появляются по кнопке «Вставить». Вам нужен компонент формы «Кнопка». Нажали, поставили на любое место в листе. Далее, если не появилось окно назначения макроса, надо нажать правой кнопкой и выбрать пункт «Назначить макрос». Назовём его Format. Price. Важно, чтобы перед именем макроса ничего не было — иначе он создастся в отдельном модуле, а не в пространстве имен книги. В этому случае вам будет недоступно быстрое обращение к выделенному листу. Нажимаем кнопку «Новый». И вот мы в среде разработки VB. Также её можно вызвать из контекстного меню командой «Исходный текст»/«View code». Перед вами окно с заглушкой процедуры. Можете его развернуть. Код должен выглядеть примерно так: Sub Format. Price()End Sub. Напишем Hello World: Sub Format. Price()Msg. Box . Кто его знает — может смело пропустить этот раздел до конца. Основное отличие Visual Basic от Pascal/C/Java в том, что команды разделяются не ; , а переносом строки или двоеточием (: ), если очень хочется написать несколько команд в одну строку. Чтобы понять основные правила синтаксиса, приведу абстрактный код. Примеры синтаксиса' Процедура. Ничего не возвращает' Перегрузка в VBA отсутствует. Sub foo(a As String, b As String)' Exit Sub ' Это значит . Вовращает Integer. Function Length. Sqr(x As Integer, y As Integer) As Integer' Exit Function Length. Sqr = x * x + y * y. End Function. Sub Format. Price()Dim s. 1 As String, s. As Strings. 1 = . Впрочем, редактор Вас поправит Dim i As Integer' Цикл всегда состоит из нескольких строк For i = 1 To 1. CStr(i) ' Конвертация чего угодно в String If i = 5 Then Exit For. Next i Dim x As Doublex = Val(. При копировании кода из IDE (в английском Excel) есь текст конвертируется в 1. Latin- 1. Поэтому, если хотите сохранить русские комментарии — надо сохранить крокозябры как Latin- 1, а потом открыть в 1. Грабли- 2. VB позволяет использовать необъявленные переменные, я всегда в начале кода (перед всеми процедурами) ставлю строчку Option Explicit. Эта директива запрещает интерпретатору заводить переменные самостоятельно. Грабли- 3. Глобальные переменные можно объявлять только до первой функции/процедуры. Локальные — в любом месте процедуры/функции. Еще немного дополнительных функций, которые могут пригодится: In. Pos, Mid, Trim, LBound, UBound. Также ответы на все вопросы по поводу работы функций/их параметров можно получить в MSDN. Надеюсь, что этого Вам хватит, чтобы не пугаться кода и самостоятельно написать какое- нибудь домашнее задание по информатике. По ходу поста я буду ненавязчиво знакомить Вас с новыми конструкциями. Кодим много и под Excel. В этой части мы уже начнём кодить нечто, что умеет работать с нашими листами в Excel. Для начала создадим отдельный лист с именем result (лист с данными назовём data). Теперь, наверное, нужно этот лист очистить от того, что на нём есть. Также мы «выделим» лист с данными, чтобы каждый раз не писать длинное обращение к массиву с листами. Sub Format. Price()Sheets(. Они создаются функцией Range и возвращают объект типа Range. У него есть всё необходимое для работы с данными и/или оформлением. Кстати сказать, свойство Cells листа — это тоже Range. Примеры работы с Range. Sheets(. Итак, у каждой строчки листа data, начиная со второй, есть некоторые данные, которые нас не интересуют (ID, название и цена) и есть две вложенные группы, к которым она принадлежит (тип и производитель). Более того, эти строки отсортированы. Пока мы забудем про пропуски перед началом новой группы — так будет проще. Я предлагаю такой алгоритм: Считали группы из очередной строки. Пробегаемся по всем группам в порядке приоритета (вначале более крупные)Если текущая группа не совпадает, вызываем процедуру Add. Group(i, name), где i — номер группы (от номера текущей до максимума), name — её имя. Несколько вызовов необходимы, чтобы создать не только наш заголовок, но и всё более мелкие. После отрисовки всех необходимых заголовков делаем еще одну строку и заполняем её данными. Для упрощения работы рекомендую определить следующие функции- сокращения: Function Get. Col(Col As Integer) As String. Get. Col = Chr(Asc(. В начале процедуры её следует сделать равной единице. Еще нам потребуется переменная- «текущая строка в data», массив с именами групп текущей предыдущей строк. Потом можно написать цикл «пока первая ячейка в строке непуста». Глобальные переменные. Option Explicit ' про эту строчку я уже рассказывал. Dim Cur. Row As Integer. Const Groups. Count As Integer = 2. Const Data. Count As Integer = 3. Format. Price. Sub Format. Price()Dim I As Integer ' строка в data Cur. Row = 1. Dim Groups(1 To Groups. Count) As String. Dim Pr. Groups(1 To Groups. Count) As String Sheets(. Можно любоваться первой версией. Некрасиво, но похоже. Давайте разбираться с форматированием. Сначала изменим процедуру Add. Header: Sub Add. Header(Ty As Integer, Name As String)Sheets(. Тут уже нам требуется работать со всеми объединёнными ячейками, иначе бордюр будет только у одной: Поэтому чуть- чуть меняем код с добавлением стиля границ: Sub Add. Header(Ty As Integer, Name As String)With Sheets(. Это легко: В начале Format. Price. Dim I As Integer ' строка в data. Cur. Row = 0 ' чтобы не было пропуска в самом начале. Dim Groups(1 To Groups. Count) As String. В цикле расстановки заголовков. If Groups(I2) < > Pr. Groups(I2) Then. Cur. Row = Cur. Row + 1. Dim I3 As Integer. В точности то, что и хотели. Надеюсь, что эта статья помогла вам немного освоится с программированием для Excel на VBA. Домашнее задание — добавить заголовки . Подсказка: Cur. Row = 0. Cur. Row = 1. Файл можно скачать тут (min. Dropbox). Не забудьте разрешить исполнение макросов. Если кто- нибудь подскажет человеческих файлохостинг, залью туда. Спасибо за внимание. Буду рад конструктивной критике в комментариях. UPD: Перезалил пример на Dropbox и min. UPD2: На самом деле, при вызове процедуры с одним параметром скобки можно поставить. Либо использовать конструкцию Call Foo(«bar», 1, 2, 3) — тут скобки нужны постоянно.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
March 2018
Categories |