Краткая история языка VBA

Несмотря на новизну языка Visual Basic for Applications, история его проявления почти так же стара, как и вся компьютерная промышленность. Про язык VBA можно сказать, что он является диалектом языка BASIC, который появился в начале 60-х.

Хотя по сегодняшним понятиям язык BASIC был довольно ограниченным и, как теперь говорят, варварским, он был прост для изучения и очень скоро получил широкое распространение. Версии BASIC выпускался для всех типов компьютеров. Язык GWBASIC производство компании Microsoft был одним из первых языков программирования для современных персональных компьютеров. Он поставлялся со всеми операционными системами MS DOS до 5-й версии. Ранние персональные компьютеры производства компании IBM даже имели версию BASIC, встроенную в ПЗУ.

С годами первоначальная версия BASIC была существенно доработана. Менялась технология программирования, и вместе с ней под влиянием разработчиков программного обеспечения менялся сам BASIC. Современный его диалект включает многие черты и свойства, характерные для более поздних и совершенных языков, таких как Pascal, C и C++.

В конце 80-х Microsoft выпускает существенно улучшенную версию BASIC, названную QuickBASIC во все версии MS DOS, начиная с 6-й (но не Windows 95).

После нескольких версий QuickBasic в 1992 году Microsoft выпускает Visual Basic for Windows.

Язык VBA в основном совпадает с Visual Basic for Windows, но имеет и существенное отличие. В частности, макросы VBA хранятся в файле документа того приложения, в котором вы создаете этот макрос.

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

3.3.2 Зачем изучать язык VBA.

На первый взгляд может показаться, что если вы можете записать макрос средствами программы Excel и потом воспроизвести его, то вам совсем не нужно учить язык VBA. Однако это не так. Записанный макрос не универсален, поскольку он может только повторить однажды выполненную последовательность команд, и только в том же самом порядке, в котором вы ее записали. С помощью VBA можно создать макрос, который будет проверять некоторые заданные вами условия и в зависимости от них менять последовательность или состав выполняемых команд.

Например, вы пытаетесь выполнить в программе Excel макрос, который должен открыть лист с названием Продажи. Если такого листа в текущей книге нет, макрос будет остановлен и Excel выдаст сообщение об ошибке. Но если вы отредактируете свой макрос с помощью VBA, он может сначала проверить наличие нужного листа, а в случае его отсутствия может даже его создать.

Если вам нужно циклически повторить несколько раз некоторую последовательность команд, записанный макрос - плохой помощник. Ведь при выполнении он повторит эту последовательность ровно столько раз, сколько раз вы повторили ее при записи - по крайней мере, до тех пор, пока вы не отредактируете его или не перезапишите.

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

Представьте себе, что вы записали макрос, который меняет ширину столбцов в листе Excel. Если вам требуется, чтобы он менял ширину всех трех столбцов вручную. Записанный макрос всегда будет менять ширину столбцов со второго по четвертый. Отредактировав записанный макрос с помощью VBA, вы добьетесь того, чтобы он спрашивал вас, сколько столбцов нужно обработать и какие именно. Вы даже сможете задать новую ширину.

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

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

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

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

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

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

Описывая полностью язык VBA можно затратить немало времени – этому посвящены объемные тома насчитывающие не одну сотню страниц. Поэтому поступим следующим образом: ограничимся описанием подпрограмм и функций которые были написаны для реализации поставленной задачи (идеи, алгоритма программ, описания операторов использованных в процессе написания этих подпрограмм и функций. Использование стандартных средств Excel (функций включенных в стандартный набор) оказалось недостаточно для решения задачи. Было необходимо сделать, например, так чтобы был автоматизирован поиск параметров по справочным таблицам, подстановка наименований электроприемников из каталога. Это возможно было организовать с помощью программирования, определения функций которые реализовывали бы подобные задачи.

Загружая файл Метод упорядоченных диаграмм.xls, и затем загружая редактор VBA, мы увидим следующую структуру проекта VBA: два модуля в которых содержатся основные процедуры и функции. Перечислим их и укажем их назначение:

Modul1

1) ПоискNэzv поиск промежуточного значения n_э* по значениям Р_1* и n₁

2) ПОИСКPном1 поиск промежуточного значения Рном1 используя диапазон C7:C16 (номинальные мощности электроприемников группы А)

3) КОЭФМАКС функция поиска значения коэффициента максимума по значениям средневзвешенного коэффициента использования (ячейка Е17) и приведенного числа электроприемников (K7)

 

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

Modul2

1) ПОИСКN1 – функция поиска промежуточного значения n₁

2) Подст – процедура служащая для подстановки наименований электроприемников из каталога в расчетную таблицу

 

Также открывая значки объектов Лист1, Лист2, Лист3 мы увидим одинаковые процедуры вызываемые нажатием кнопки «Просчитать».

Опишем эти функции.

1) Функция ПоискNэzv поиск промежуточного значения n_э* по значениям Р_1* и n_1*

Идея состоит в том чтобы реализовать поиск n_э* по справочной таблице приведенной в Приложении 2.

Это можно реализовать следующим образом. Разбить таблицу на равные интервалы по Р_1* и по n_1*. Т. е. например (см. Приложение 2) будем считать что на интервалах 0.975<Р_1* =<1 и 0<n_1*.<0.0075 n_э* = 0.005, затем при 0.925<Р_1* =<0.975 и 0<n_1*.<0.0075 n_э* = 0.005 b и т.д. Это в принципе удовлетворяет условиям округления.

Приведем текст программы с комментариями

 

Public Function ПоискNэzv(Pzv As Double, n1zv As Double) ‘заголовок

Dim znach As Double ‘описание переменной

 

If Pzv <= 1 And Pzv > 0.975 Then ‘проверка условия

Select Case n1zv ‘работка вариантов выбора

Case 0 To 0.0075

znach = 0.005

Case 0.0075 To 0.0175

znach = 0.009

Case 0.0175 To 0.0275

znach = 0.02

Case 0.0275 To 0.0375

znach = 0.03

Case 0.0375 To 0.0475

znach = 0.04

Case 0.0475 To 0.055

znach = 0.05

Case 0.055 To 0.065

znach = 0.06

Case 0.07 To 0.09

znach = 0.08

Case 0.09 To 0.125

znach = 0.09

Case 0.125 To 0.175

znach = 0.14

Case 0.175 To 0.225

znach = 0.19

Case 0.225 To 0.275

znach = 0.24

Case 0.275 To 0.325

znach = 0.29

Case 0.325 To 0.375

znach = 0.33

Case 0.375 To 0.425

znach = 0.38

Case 0.425 To 0.475

znach = 0.43

Case 0.475 To 0.525

znach = 0.48

Case 0.525 To 0.575

znach = 0.52

Case 0.575 To 0.625

znach = 0.57

Case 0.625 To 0.675

znach = 0.62

Case 0.675 To 0.725

znach = 0.66

Case 0.725 To 0.775

znach = 0.71

Case 0.775 To 0.825

znach = 0.76

Case 0.825 To 0.875

znach = 0.8

Case 0.875 To 0.99

znach = 0.85

Case Is > 0.99

znach = 0.95

End Select

End If

 

If Pzv <= 0.975 And Pzv > 0.925 Then

Select Case n1zv

Case 0 To 0.0075

znach = 0.005

Case 0.0075 To 0.0175

znach = 0.011

Case 0.0175 To 0.0275

znach = 0.02

Case 0.0275 To 0.0375

znach = 0.03

Case 0.0375 To 0.0475

znach = 0.04

Case 0.0475 To 0.055

znach = 0.05

Case 0.055 To 0.065

znach = 0.06

Case 0.07 To 0.09

znach = 0.08

Case 0.09 To 0.125

znach = 0.1

Case 0.125 To 0.175

znach = 0.16

Case 0.175 To 0.225

znach = 0.21

Case 0.225 To 0.275

znach = 0.26

Case 0.275 To 0.325

znach = 0.32

Case 0.325 To 0.375

znach = 0.37

Case 0.375 To 0.425

znach = 0.42

Case 0.425 To 0.475

znach = 0.47

Case 0.475 To 0.525

znach = 0.53

Case 0.525 To 0.575

znach = 0.57

Case 0.575 To 0.625

znach = 0.63

Case 0.625 To 0.675

znach = 0.68

Case 0.675 To 0.725

znach = 0.73

Case 0.725 To 0.775

znach = 0.78

Case 0.775 To 0.825

znach = 0.83

Case 0.825 To 0.875

znach = 0.88

Case 0.875 To 0.99

znach = 0.92

Case Is > 0.99

znach = 1

End Select

End If

………………………

программа далее состоит из нескольких блоков которые похожи друг на друга поэтому опустим несколько таких блоков

………………………

 

If Pzv <= 0.1 And Pzv > 0.175 Then

Select Case n1zv

Case 0 To 0.0075

znach = 0.34

Case 0.0075 To 0.0175

znach = 0.52

Case 0.0175 To 0.0275

znach = 0.71

Case 0.0275 To 0.0375

znach = 0.81

Case 0.0375 To 0.0475

znach = 0.86

Case 0.0475 To 0.055

znach = 0.9

Case 0.055 To 0.065

znach = 0.92

Case 0.07 To 0.09

znach = 0.94

Case 0.09 To 0.125

znach = 0.95

Case 0.125 To 0.175

znach = 1

Case 0.175 To 0.225

znach = 1

Case 0.225 To 0.275

znach = 1

Case 0.275 To 0.325

znach = 1

Case 0.325 To 0.375

znach = 1

Case 0.375 To 0.425

znach = 1

Case 0.425 To 0.475

znach = 1

Case 0.475 To 0.525

znach = 1

Case 0.525 To 0.575

znach = 1

Case 0.575 To 0.625

znach = 1

Case 0.625 To 0.675

znach = 1

Case 0.675 To 0.725

znach = 1

Case 0.725 To 0.775

znach = 1

Case 0.775 To 0.825

znach = 1

Case 0.825 To 0.875

znach = 1

Case 0.875 To 0.99

znach = 1

Case Is > 0.99

znach = 1

End Select

End If

 

ПоискNэzv = znach ‘присвоение функции значения перемнной

End Function ‘конец функции

В программе использовались следующие операторы:

Условный оператор

If условие Then оператор

 

Например, If N>=0 and N<=9 Then Print “Ok”

 

Описание переменных

Dim имя переменной As тип переменной

 

Оператор Select Case

 

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

 

Select Case переменная или выражение

Case оператор проверки условия

действие 1

.

.

.

Case......

действие n-1

End Select

 

Использовался тип переменных:

DoublePrecision “#”. Числа с точностью до 16 цифр и длиной до 300 символов. Вычисления приблизительны, а скорость небольшая. Используется для научных расчетов.

 

2) КОЭФМАКС функция поиска значения коэффициента максимума по значениям средневзвешенного коэффициента использования (ячейка Е17) и приведенного числа электроприемников (K7)

 

Реализуется по тому же принципу что и предыдущая, идея та же самая, отличие лишь в том что справочная таблица другая.

 

Текст программы:

 

Public Function КОЭФМАКС(КОЭФИСП As Double, Nэ As Double)

Dim km As Double

If КОЭФИСП >= 0.1 And КОЭФИСП < 0.125 Then

Select Case Nэ

Case 4

km = 3

Case 5

km = 3.23

Case 6

km = 3.04

Case 7

km = 2.88

Case 8

km = 2.72

Case 9

km = 2.56

Case 10 To 11

km = 2.42

Case 11 To 14

km = 2.24

Case 14 To 18

km = 1.99

Case 18 To 23

km = 1.84

Case 23 To 28

km = 1.71

Case 28 To 35

km = 1.62

Case 35 To 45

km = 1.5

Case 45 To 55

km = 1.4

Case 55 To 80

km = 1.32

Case 80 To 120

km = 1.21

Case 120 To 180

km = 1.17

Case 180 To 200

km = 1.15

Case Is > 200

km = 1

End Select

End If

………………………..

………………………..

If КОЭФИСП >= 0.75 And КОЭФИСП < 0.85 Then

Select Case Nэ

Case 4

km = 1.14

Case 5

km = 1.12

Case 6

km = 1.1

Case 7

km = 1.09

Case 8

km = 1.08

Case 9

km = 1.08

Case 10 To 11

km = 1.07

Case 11 To 14

km = 1.07

Case 14 To 18

km = 1.07

Case 18 To 23

km = 1.06

Case 23 To 28

km = 1.06

Case 28 To 35

km = 1.05

Case 35 To 45

km = 1.05

Case 45 To 55

km = 1.04

Case 55 To 80

km = 1.03

Case 80 To 120

km = 1.02

Case 120 To 180

km = 1.02

Case 180 To 200

km = 1.01

Case Is > 200

km = 1

End Select

End If

 

If КОЭФИСП >= 0.85 And КОЭФИСП <= 0.9 Then

km = 1

End If

КОЭФМАКС = km

End Function

 

Используются те же операторы и типы переменных.

3) ПОИСКPном1 поиск промежуточного значения Рном1 используя диапазон C7:C16 (номинальные мощности электроприемников группы А)

 

Идея здесь состоит в реализации алгоритма нахождения Pном1 описанного в разделе 2

Мощности находятся в диапазоне C7:C16, нужно сначала найти максимальное значение, а затем сумму значений больше либо равных половине максимального

 

Текст программы

 

Public Function ПОИСКPном1(Диапазон As Range) ‘заголовок

Dim Cell As Object ‘описание переменных

Dim m As Double, N As Double

 

m = 0

N = 0

For Each Cell In Диапазон

If Cell.Value > m Then m = Cell.Value

Next Cell

For Each Cell In Диапазон

If Cell.Value >= m / 2 Then N = N + Cell.Value

Next Cell

ПОИСКPном1 = N

End Function

 

Операторы использованные в программе.

 

Для создания циклов объектного типа используется структура For Each (для каждого элемента…), которая применяется к массивам и наборам объектов. В VB такие циклы встречаются очень часто.

 

For Each элемент In набор

..........................................

Exit For

..........................................

Next элемент

 

Пременная Cell задается как объект.

 

4) ПОИСКN1 – функция поиска промежуточного значения n₁

 

Эта функция реализует поиск значения n₁ (см. раздел 2). Поиск этого значения вытекает из реализации предыдущей функции. Разница в том что необходимо найти сумму не мощностей больше или равных половине максимальной а сумму числа однотипных электроприемников мощности которых больше или равны половине максимальной среди номинальных мощностей. Это можно осуществить используя метод Offset.

 

Текст программы

 

Public Function ПОИСКN1(Диапазон1 As Range)

Dim Cell1 As Object, w As Double, q As Double

w = 0

q = 0

For Each Cell1 In Диапазон1

If Cell1.Value > w Then w = Cell1.Value

Next Cell1

For Each Cell1 In Диапазон1

If Cell1.Value >= w / 2 Then q = q + Cell1.Offset(0, -1).Value

Next Cell1

ПОИСКN1 = q

End Function

 

Используемые операторы

Метод Offset работает аналогично Cells, с тем отличием, что возвращаемый объект всегда задается относительно верхнего левого угла текущего объекта. Числа здесь на 1 меньше, чем в методе Cells. Метод Offset чаще всего используется для создания объекта относительно текущей активной ячейки в рабочей таблице.

4) Подст – процедура служащая для подстановки наименований электроприемников из каталога в расчетную таблицу

Наименования электроприемников заносятся в расчетную таблицу из списка находящегося на этом же листе, но подстановка параметров соответствующих наименованиям осуществляет данная процедура.

Текст процедуры.

 

Public Sub подст() ‘заголовок

Dim Cell1 As Object, Cell2 As Object ‘описание переменных

Dim Cell3 As Object, Cell4 As Object

For Each Cell1 In Range("A7:A16")

For Each Cell2 In Range("A30:A89")

If Cell1.Value = Cell2.Value Then ‘проверка равенства наименований в расчетной таблице и в списке

Cell1.Offset(0, 2).Value = Cell2.Offset(0, 1).Value

Cell1.Offset(0, 4).Value = Cell2.Offset(0, 2).Value

Cell1.Offset(0, 5).Value = Cell2.Offset(0, 3).Value

End If

Next Cell2

Next Cell1

For Each Cell3 In Range("A19:A23")

For Each Cell4 In Range("A30:A89")

If Cell3.Value = Cell4.Value Then

Cell3.Offset(0, 2).Value = Cell4.Offset(0, 1).Value

Cell3.Offset(0, 4).Value = Cell4.Offset(0, 2).Value

Cell3.Offset(0, 5).Value = Cell4.Offset(0, 3).Value

End If

Next Cell4

Next Cell3

End Sub