как писать vba скрипты

Oбъявление функций и процедур

Как вызвать сценарий в VBscript

Веб страница с активным содержимым, всегда делится как бы на две части: Первая, это html код самой страницы содержащий необходимые элементы управления (ссылки, кнопки, формы для ввода информации и т.д.), и Вторая, сценарии на скрипте, которые начинают работать при различных событиях происходящих с элементами управления. Это можно назвать главной отличительной особенностью программ для веб страниц, от обычных исполняемых приложений.

Рассмотрим основные приемы вызова сценариев, или процедур.

Сценарий вызываемый неявно:

Такой способ, по моему мнению, самый простой. Указываем имя оператором name=»имя» в теге характерризующим элемент управления, это же «имя» пишем в вызываемой процедуре и через знак подчеркивания пишем событие, которое должно произойти для исполнения сценария.

Вызов сценария определяемый в элементе управления:

Такой способ обрадует приверженцев Java Script. Среди его преимуществ, перед первым методом можно отметить то, что один и тот же сценарий можно вызывать из разных элементов управления.

Автоматический вызов сценария:

Код программы в этом случае выполнится сразу после загрузки документа.

Вызов из другой процедуры:

Все способы, кроме автоматического вызова сценария, чисто визуально будут выглядеть так:

Основные типы данных VBscript

Наверное, те кто знаком со «старым» бейсиком, еще помнят, что для обозначения символьной переменной надо было писать знак бакса или солнышка (a$=»привет»). В VBscript этого делать не следует, он имеет только один тип данных, называемый VARIANT. Он может содержать как символы так и числа разной точности. Поэтому для того, чтобы точно определить тип данных, желательно использовать функции преобразования. К примеру, текст вводимый в поле формы имеет текстовый формат, и попытка сложить цифру «3» с цифрой «2» приведет к результату «32»:

Поэтому надо сделать так, чтобы переменные summatext1 и summatext2 были преобразованы в целые числа функцией Cint(переменная):

Подтипы данных VARIANT.

Некоторые правила VBscript

В заключении следует упомянуть некоторые основные приемы при написании кода скрипта.

Если код не умещается в одной строке, то можно использовать символ продолжения строки:

Для «склеивания» двух численных переменных лучше использовать знак «&» чем «+», т.е. a=»при» b=»вет» тогда a&b=»привет»

Код сценария желательно, на всякий случай, заключать в теги комментария

Заключение

Я уже говорил, что моей задачей не было рассказать все и вся про VBscript, в статье были приведены только справочные данные и основные методы, пригодные для написания простейших сценариев, без применения ActiveX технологии. Тем, кого заинтересовал этот язык программирования, можно порекомендовать книгу (из бумаги 🙂 «Изучаем VBscript» автор Пол Ломакс. Некоторые справочные данные я брал именно из нее.

При большой фантазии, на VBscript можно создать и полноценные приложения, как, к примеру Перекодировщик Rus-Translit или HTML редактор. Так что дерзайте!

Источник

Как создать макрос или скрипт VBA в Excel

Microsoft Excel позволяет пользователям автоматизировать функции и команды с помощью макросов и сценариев Visual Basic для приложений (VBA). VBA — это язык программирования Excel использует для создания макросов. Он также будет выполнять автоматические команды в зависимости от конкретных условий.

Макросы — это серия предварительно записанных команд. Они запускаются автоматически, когда дается определенная команда. Если у вас есть задачи в Microsoft Excel, которые вы часто выполняете, такие как учет, управление проектами или расчет заработной платы, автоматизация этих процессов может сэкономить много времени.

На вкладке «Разработчик» на ленте в Excel пользователи могут записывать щелчки мыши и нажатия клавиш (макросы). Однако некоторые функции требуют более глубокого написания сценариев, чем могут обеспечить макросы. Здесь сценарии VBA становятся огромным преимуществом. Это позволяет пользователям создавать более сложные сценарии.

В этой статье мы объясним следующее:

Включение скриптов и макросов

Прежде чем вы сможете создавать макросы или сценарии VBA в Excel, необходимо включить вкладку «Разработчик» в меню ленты. Вкладка «Разработчик» не включена по умолчанию. Чтобы включить это:

Макросы по умолчанию не включены и сопровождаются предупреждением, потому что они представляют собой компьютерный код, который может содержать вредоносное ПО.

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

Когда вы закончите использовать свои скрипты и макросы, отключите все макросы, чтобы предотвратить заражение других документов потенциально вредоносным кодом.

Создать макрос в Excel

Все действия, которые вы выполняете в Excel во время записи макроса, добавляются к нему.

Решите, где вы хотите сохранить макрос из следующих опций:

Когда закончите, нажмите ОК.

Конкретный пример макроса

Давайте начнем с простой таблицы для клиентов и того, сколько они должны. Мы начнем с создания макроса для форматирования листа.

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

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

Это будет выделять клетки, которые имеют баланс из-за. Мы добавили несколько клиентов без остатка из-за дальнейшей иллюстрации форматирования.

Давайте начнем с исходной электронной таблицы, прежде чем мы записали макрос, чтобы правильно отформатировать его. Нажмите «Макросы», выберите и запустите макрос, который вы только что создали.

Когда вы запускаете макрос, все форматирование выполняется за вас. Этот макрос, который мы только что создали, хранится в редакторе Visual Basic.

Пользователи могут запускать макросы несколькими различными способами. Читать Запустить макрос Узнать больше.

Узнайте больше о VBA

Чтобы узнать о VBA, нажмите Макрос на вкладке Разработчик. Найдите созданный вами файл и нажмите «Изменить».

Код, который вы видите в поле выше, создан при записи макроса.

Это также то, что вы будете запускать, если вы хотите отформатировать другие таблицы платежей клиентов таким же образом.

Создать кнопку, чтобы начать работу с VBA

Используя ту же таблицу, что и у клиентов, и сколько они должны, давайте создадим конвертер валют.

Добавить код для придания кнопке функциональности

Кодирование VBA не выполняется в интерфейсе Excel. Это делается в отдельной среде.

ActiveCell.Value = (ActiveCell * 1.28)

Наша цель в этом разделе — конвертировать валюту в нашей таблице. Сценарий выше отражает обменный курс от GBP к USD. Новое значение ячейки будет то, что в настоящее время там умножено на 1,28.

На приведенном ниже снимке экрана показано, как выглядит код в окне VBA после его вставки.

Это сработало?

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

Для этого примера я положил число 4 в клетку. После нажатия кнопки «Преобразовать» число изменилось на 5.12. Так как 4 раза 1,28 является 5,12, код был выполнен правильно.

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

Источник

Автоматизация рутины в Microsoft Excel при помощи VBA

В этом посте я расскажу, что такое VBA и как с ним работать в Microsoft Excel 2007/2010 (для более старых версий изменяется лишь интерфейс — код, скорее всего, будет таким же) для автоматизации различной рутины.

VBA (Visual Basic for Applications) — это упрощенная версия Visual Basic, встроенная в множество продуктов линейки Microsoft Office. Она позволяет писать программы прямо в файле конкретного документа. Вам не требуется устанавливать различные IDE — всё, включая отладчик, уже есть в Excel.

Еще при помощи Visual Studio Tools for Office можно писать макросы на C# и также встраивать их. Спасибо, FireStorm.

Сразу скажу — писать на других языках (C++/Delphi/PHP) также возможно, но требуется научится читать, изменять и писать файлы офиса — встраивать в документы не получится. А интерфейсы Microsoft работают через COM. Чтобы вы поняли весь ужас, вот Hello World с использованием COM.

Поэтому, увы, будем учить Visual Basic.

Чуть-чуть подготовки и постановка задачи

Итак, поехали. Открываем Excel.

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

Теперь давайте подумаем, на каком примере мы будем изучать VBA. Недавно мне потребовалось красиво оформить прайс-лист, выглядевший, как таблица. Идём в гугл, набираем «прайс-лист» и качаем любой, который оформлен примерно так (не сочтите за рекламу, пожалуйста):

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

Результат, которого хотим добиться, выглядит примерно так:

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

Кодим

Для начала требуется создать кнопку, при нажатии на которую будет вызываться наша програма. Кнопки находятся в панели «Разработчик» и появляются по кнопке «Вставить». Вам нужен компонент формы «Кнопка». Нажали, поставили на любое место в листе. Далее, если не появилось окно назначения макроса, надо нажать правой кнопкой и выбрать пункт «Назначить макрос». Назовём его FormatPrice. Важно, чтобы перед именем макроса ничего не было — иначе он создастся в отдельном модуле, а не в пространстве имен книги. В этому случае вам будет недоступно быстрое обращение к выделенному листу. Нажимаем кнопку «Новый».

И вот мы в среде разработки VB. Также её можно вызвать из контекстного меню командой «Исходный текст»/«View code».

Перед вами окно с заглушкой процедуры. Можете его развернуть. Код должен выглядеть примерно так:

Напишем Hello World:

Sub FormatPrice()
MsgBox «Hello World!»
End Sub

И запустим либо щелкнув по кнопке (предварительно сняв с неё выделение), либо клавишей F5 прямо из редактора.

Тут, пожалуй, следует отвлечься на небольшой ликбез по поводу синтаксиса VB. Кто его знает — может смело пропустить этот раздел до конца. Основное отличие Visual Basic от Pascal/C/Java в том, что команды разделяются не ;, а переносом строки или двоеточием (:), если очень хочется написать несколько команд в одну строку. Чтобы понять основные правила синтаксиса, приведу абстрактный код.

Примеры синтаксиса

Dim res As sTRING ‘ Регистр в VB не важен. Впрочем, редактор Вас поправит
Dim i As Integer
‘ Цикл всегда состоит из нескольких строк
For i = 1 To 10
res = res + CStr(i) ‘ Конвертация чего угодно в String
If i = 5 Then Exit For
Next i

Dim x As Double
x = Val( «1.234» ) ‘ Парсинг чисел
x = x + 10
MsgBox x

On Error GoTo Err ‘ При ошибке перейти к метке Err
x = 5 / 0
MsgBox «OK!»
GoTo ne

ne:
On Error GoTo 0 ‘ Отключаем обработку ошибок

‘ Циклы бывает, какие захотите
Do While True
Exit Do

Loop ‘While True
Do ‘Until False
Exit Do
Loop Until False
‘ А вот при вызове функций, от которых хотим получить значение, скобки нужны.
‘ Val также умеет возвращать Integer
Select Case LengthSqr(Len( «abc» ), Val( «4» ))
Case 24
MsgBox «0»
Case 25
MsgBox «1»
Case 26
MsgBox «2»
End Select

Грабли-1. При копировании кода из IDE (в английском Excel) есь текст конвертируется в 1252 Latin-1. Поэтому, если хотите сохранить русские комментарии — надо сохранить крокозябры как Latin-1, а потом открыть в 1251.

Грабли-2. Т.к. VB позволяет использовать необъявленные переменные, я всегда в начале кода (перед всеми процедурами) ставлю строчку Option Explicit. Эта директива запрещает интерпретатору заводить переменные самостоятельно.

Грабли-3. Глобальные переменные можно объявлять только до первой функции/процедуры. Локальные — в любом месте процедуры/функции.

Еще немного дополнительных функций, которые могут пригодится: InPos, Mid, Trim, LBound, UBound. Также ответы на все вопросы по поводу работы функций/их параметров можно получить в MSDN.

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

Кодим много и под Excel

В этой части мы уже начнём кодить нечто, что умеет работать с нашими листами в Excel. Для начала создадим отдельный лист с именем result (лист с данными назовём data). Теперь, наверное, нужно этот лист очистить от того, что на нём есть. Также мы «выделим» лист с данными, чтобы каждый раз не писать длинное обращение к массиву с листами.

Sub FormatPrice()
Sheets( «result» ).Cells.Clear
Sheets( «data» ).Activate
End Sub

Работа с диапазонами ячеек

Вся работа в Excel VBA производится с диапазонами ячеек. Они создаются функцией Range и возвращают объект типа Range. У него есть всё необходимое для работы с данными и/или оформлением. Кстати сказать, свойство Cells листа — это тоже Range.

Примеры работы с Range

Sheets( «result» ).Activate
Dim r As Range
Set r = Range( «A1» )
r.Value = «123»
Set r = Range( «A3,A5» )
r.Font.Color = vbRed
r.Value = «456»
Set r = Range( «A6:A7» )
r.Value = «=A1+A3»

Теперь давайте поймем алгоритм работы нашего кода. Итак, у каждой строчки листа data, начиная со второй, есть некоторые данные, которые нас не интересуют (ID, название и цена) и есть две вложенные группы, к которым она принадлежит (тип и производитель). Более того, эти строки отсортированы. Пока мы забудем про пропуски перед началом новой группы — так будет проще. Я предлагаю такой алгоритм:

Для упрощения работы рекомендую определить следующие функции-сокращения:

Function GetCol(Col As Integer ) As String
GetCol = Chr(Asc( «A» ) + Col)
End Function

Далее определим глобальную переменную «текущая строчка»: Dim CurRow As Integer. В начале процедуры её следует сделать равной единице. Еще нам потребуется переменная-«текущая строка в data», массив с именами групп текущей предыдущей строк. Потом можно написать цикл «пока первая ячейка в строке непуста».

Глобальные переменные

Option Explicit ‘ про эту строчку я уже рассказывал
Dim CurRow As Integer
Const GroupsCount As Integer = 2
Const DataCount As Integer = 3

FormatPrice

Sub FormatPrice()
Dim I As Integer ‘ строка в data
CurRow = 1
Dim Groups(1 To GroupsCount) As String
Dim PrGroups(1 To GroupsCount) As String

Теперь надо заполнить массив Groups:

На месте многоточия

И создать заголовки:

На месте многоточия в предыдущем куске

For I2 = 1 To GroupsCount
If Groups(I2) <> PrGroups(I2) Then
Dim I3 As Integer
For I3 = I2 To GroupsCount
AddHeader I3, Groups(I3)
Next I3
Exit For
End If
Next I2

Не забудем про процедуру AddHeader:

Перед FormatPrice

Теперь надо перенести всякую информацию в result

Подогнать столбцы по ширине и выбрать лист result для показа результата

После цикла в конце FormatPrice

Sheets( «Result» ).Activate
Columns.AutoFit

Всё. Можно любоваться первой версией.

Некрасиво, но похоже. Давайте разбираться с форматированием. Сначала изменим процедуру AddHeader:

Осталось только сделать границы. Тут уже нам требуется работать со всеми объединёнными ячейками, иначе бордюр будет только у одной:

Поэтому чуть-чуть меняем код с добавлением стиля границ:

Select Case Ty
Case 1 ‘ Тип
.Font.Bold = True
.Font.Size = 16
.Borders(xlTop).Weight = xlThick
Case 2 ‘ Производитель
.Font.Size = 12
.Borders(xlTop).Weight = xlMedium
End Select
.Borders(xlBottom).Weight = xlMedium ‘ По убыванию: xlThick, xlMedium, xlThin, xlHairline
End With
CurRow = CurRow + 1
End Sub

Осталось лишь добится пропусков перед началом новой группы. Это легко:

В начале FormatPrice

Dim I As Integer ‘ строка в data
CurRow = 0 ‘ чтобы не было пропуска в самом начале
Dim Groups(1 To GroupsCount) As String

В цикле расстановки заголовков

If Groups(I2) <> PrGroups(I2) Then
CurRow = CurRow + 1
Dim I3 As Integer

В точности то, что и хотели.

Надеюсь, что эта статья помогла вам немного освоится с программированием для Excel на VBA. Домашнее задание — добавить заголовки «ID, Название, Цена» в результат. Подсказка: CurRow = 0 CurRow = 1.

Файл можно скачать тут (min.us) или тут (Dropbox). Не забудьте разрешить исполнение макросов. Если кто-нибудь подскажет человеческих файлохостинг, залью туда.

Спасибо за внимание.

Буду рад конструктивной критике в комментариях.

UPD: Перезалил пример на Dropbox и min.us.

UPD2: На самом деле, при вызове процедуры с одним параметром скобки можно поставить. Либо использовать конструкцию Call Foo(«bar», 1, 2, 3) — тут скобки нужны постоянно.

Источник