Рубрика: VBA

Replace
Возвращает строку, в которой указанная подстрока заданное число раз заменена другой подстрокой.

Задание

Введите фразу Приветствую тебя мой друг в ячейку А1 и замените слово друг на слово брат. Результат запишите в ячейку А2.

Sub myReplace()4

Cells(1, 2).Value = Replace(Cells(1, 1).Value, "äðóã", "áðàò")
End Sub

Результат:

Split
Возвращает одномерный массив (с индексацией от нуля), содержащий указанное число подстрок.

Задание. Введите имена в ячейку А1 через пробел и запишите их в столбец ниже разбив по пробелу.

Sub MySplit()
arr = Split(Cells(1, 1).Value, " ")
Cells(2, 1).Value = arr(0)
Cells(3, 1).Value = arr(1)
Cells(4, 1).Value = arr(2)
Cells(5, 1).Value = arr(3)
Cells(6, 1).Value = arr(4)
End Sub

Результат:

 

StrComp
Возвращает -1, 0 или 1 в зависимости от результата сравнения строк.

Ноль означает что строки равны. -1 означает что первая строка меньше второй 1 означает что первая больше второй.

Sub myStrComp()
Res = StrComp(Cells(1, 1).Value, Cells(1, 2).Value)
If (Res = 0) Then
Cells(1, 3).Value = "Строки равны"
Else
Cells(1, 3).Value = "Строки не равны"
End If

End Sub

Задание

Ввести в ячейки A1 и А2 две строки и сравнить их с помощью StrComp

Trim
Возвращает строку, содержащую копию указанной строки без пробелов в начале и конце.

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

Задание:

Написать макрос по проверке наличия пробелов в начале или в конце ячейки

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

InStr

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

Пример

res=InStr(«Tech on the Net», «T»)

Вернет 1. Это позиция буквы T во фразе Tech on the Net

Задание:

Ввести два значения в разные ячейки и найти одно значение в другом. В третью ячейку вывести позицию в которой находится фраза.

Если результат ноль то вывести что совпадений не найдено.

Отображение вкладки «Разработчик»

• Откройте вкладку Файл.
• Выберите пункт Параметры.
• Выберите пункт Настроить ленту.
• В разделе Настройка ленты в списке Основные вкладки установите флажок Разработчик.

Что такое макрос в Excel и зачем он нужен?

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

Создание нового макроса

• Откройте вкладку Разработчик
• Выберите пункт Макросы
• Введите имя макроса
• Нажмите кнопку создать
• В открывшемся окне появится блок программы в котором будет находится программа макроса.

Sub Макрос1()

End Sub

 

Задание: 

1. Создайте макрос.

Запись макроса

• Откройте вкладку Разработчик
• Выберите пункт меню запись макроса
• Выполните в таблице действия которые вы хотите записать в макрос
• Нажмите остановить запись

 

• Режим запись макроса идеально подходит для исследования различных объектов и их методов языка VBA

Задание 

1. Создайте макрос. 
2. Выполните действия в книге Ексель. 
3. Остановите запись макроса.
4. Просмотрите его код.
5. Выполните макрос.

Создание пользовательской формы в VBA

Пользовательскую форму можно создать в VBA в меню insert->UserForm

Форма это Окно виндовс которое позволяет создавать пользовательский интерфейс для управления макросами.

Для запуска формы нужно воспользоваться методом Show.

Пример макроса который запускает форму.

Sub macros()

UserForm1.Show

End Sub

 

Чтобы открыть вкладку с макросами щелкните по Module1

Для того чтобы открыть редактор форм щелкните по UserForm1

Переменные в VBA

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

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

Для объявления или, другими словами, создания переменной используется ключевое слово Dim;

Dim x As Integer

Объявление целочисленной переменной x;

 

x=10

Запись значения 10 в переменную x;

 

x=x+10

Увеличение переменной x на 10

 

x=x-10

Уменьшение переменной x на 10

 

Dim x As Integer
Dim y As Integer
Dim z As Integer

Сложение двух переменных

z=x+y

 

 

Смена двух переменных местами.

z=x

x=y

y=z

 

Запись данных в ячейки таблицы использование объекта Range.

• Range(«A1»).Value = 10 — Запись значений в ячейку (10 – числовые данные записываются без кавычек)
• Range(«K9:R9»).Value = “Текст” – Запись значений в строку. (“Текст” – текстовые данные записываются в кавычках)
• Range(«K9:K29»).Value = 10 – Запись значений в столбец (Указываем диапазон адресов ячеек)
• Range(«K9:R29″).Value = 10 – Запись значений в выбранный диапазон (Указываем диапазон адресов ячеек)

Задание:

1. Записать значение в одну ячейку.
2. Заполнить нулями строку.
3. Заполнить нулями Столбец.
4. Записать различные текстовые значения в две ячейки.
5.  Записать различные числовые значения в две произвольные ячейки
6. Записать значения из ячеек в переменные x и y
7. Поменять местами переменные x и y
8. Поменять местами значения в ячейка из которых взяли x и y

Целочисленные переменные и математические операции

Dim x As Integer
x = 8.6 ' Обратите внимание в x будет записано значение 9
Dim y As Integer
y = 26
Range("A1").Value = "x + y"
Range("A2").Value = "x - y"
Range("A3").Value = "x * y"
Range("A4").Value = "x/y"
Range("B1").Value = x + y
Range("B2").Value = x - y
Range("B3").Value = x * y
Range("B4").Value = x / y

Обратите внимание на комментарий который оставлен в программе с помощью символа  ‘
Чаще комментируйте свои программы. 

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

Задание:

Создать макрос который выведет значения как на рисунке ниже.

 

Строковые переменные String

Dim book1 As String
Dim book2 As String
book1 = "Книга1"
book2 = "Книга2"
Range("A1").Value = book1+"; "+book2

Результат выполнения программы

Задание:

1. Сложить две строковые переменные и записать в ячейку A1
2. Записать любые текстовые значения в ячейки А2 А3
3. Поменять значения ячеек A2 и А3 местами.

Double Дробные числа

Dim x As Double
x = 5.5
MsgBox "Значение переменной х=" & x

`Оператор & создает текстовую строку из различных типов данных

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

 Задание:

1. Объявить две переменные с плавающей точкой. 
2. Сложить их.
3. И результат вывести в ячейку А1
4. Объявить две целочисленные переменные и записать в них дробные значения. 
5. Сложить их.
6. Результат вывести в ячейку A2

Циклы For Next

Немного терминологии

1. Последовательность инструкций, предназначенная для многократного исполнения, называется телом цикла.
2. Единичное выполнение тела цикла называется итерацией.
3. Выражение определяющее, будет в очередной раз выполняться итерация, или цикл завершится, называется условием выхода или условием окончания цикла
4. Переменная, хранящая текущий номер итерации, называется счётчиком итераций цикла или просто счётчиком цикла.Исполнение любого цикла включает первоначальную инициализацию переменных цикла, проверку условия выхода, исполнение тела цикла и обновление переменной цикла на каждой итерации. Кроме того, большинство языков программирования предоставляют средства для досрочного управления циклом, например, операторы завершения цикла, то есть выхода из цикла независимо от истинности условия выхода

'Простой цикл:
Dim i As Integer
For i = 1 To 100
Range("C" & i).Value = i
Next i

'Цикл с шагом:
For i = 1 To 100 Step 2
Range("B" & i).Value = i
Next i

 

`Цикл с записью значений в ячейки
For i = 1 To 100
Cells(i, i) = i
Next i

`Вложенный цикл с записью значений в ячейки
Dim j As Integer
For i = 1 To 100
For j = 1 To 100
Cells(i, j) = i * j
Next j
Next i

' для преждевременного выхода из цикла можно использовать Exit For

Задание:

1. Вывести в столбец значения от 0 до 100
2. Вывести в столбец значения от 100 до 0
3. Вывести в столбец значения от 60 до 100
4. Вывести в столбец значения от -39 до 100
5. Вывести в строку четные числа
6. Вывести в строку нечетные числа.
7. Вывести в строку ряд 100 200 300 …..

 

Оператор if — Оператор ветвления

Оператор ветвления применяется в случаях, когда выполнение или невыполнение некоторого набора команд должно зависеть от выполнения или невыполнения некоторого условия. Ветвление — одна из трёх (наряду с последовательным исполнением команд и циклом) базовых конструкций структурного программирования.

В зависимости от значения выражения будет выполнена та или иная группа операторов.

If выражение then

Выполняем программу

endif

В качестве выражения могут быть следующие операторы

A=B    —  РАВНО

A<B    —  МЕНЬШЕ

A>B    —  БОЛЬШЕ

A<=B —  МЕНЬШЕ ИЛИ РАВНО

A>=B — БОЛЬШЕ ИЛИ РАВНО

A<>B — НЕ РАВНО

Полная запись оператора if

Dim I as integerIf count = 0 Then 
…
ElseIf count = 1 
…
Else 
…
End If

 

Задание

1. Записать в таблицу произвольные значения.
2. С помощью цикла поменять знак у значений которые больше пяти.
3. Записать произвольные числа в два столбца.
4. Записать сумму двух ячеек первых двух столбцов в третий столбец используя циклы.

 

Вложенный цикл с условиями.

For i = 1 To 100
    For j = 1 To 100
        Cells(i, j) = ""
        If i * j > 1000 Then
            Cells(i, j) = ">"
        ElseIf i = 100 Or j = 100 Then
            Cells(i, j) = "100"
        Else
            Cells(i, j) = "<"
        End If
    Next j
Next i

Преобразование типов данных

Очень часто возникает необходимость преобразовать один тип данных в другой.

Для этого в Бейсике есть набор функций для преобразования типов данных.

 

CDbl(значение) — Преобразует в double

CInt(значение) — Преобразует в Int

CStr(значение) — Преобразует в строку

 

Задание 1.

В ячейки А1-А10 записать произвольные значения. Вывести в ячейку А11 ячейки А1-А10 через точку с запятой

Задание 2. 

Объявить целочисленные переменные A и B и задать им значения 34 и 16.

Записать в столбец А

А+B=

A-B=

A*B=

A/B=

В столбец B вывести значения расчетов.

Задание 3

В цикле вывести значения от 1 до 100 в столбец А

Задание 4

В цикле вывести значения от -50 до 50 в столбец А

Задание 5

Вывести в столбец А в цикле все четны числа от 1 до 20

Задание 6

Задать произвольные значения в ячейках A1-A20

Записать в эти ячейки три раза 10 и посчитать сколько раз встречается цифра 10 в ячейках А1-А20

Результат вывести в ячейку А21

Задание 7

В столбцы А и B записать по 10 произвольных значений.

В столбец C вывести значения которые являются уникальными для столбца А и для B

В столбец D вывести значения которые есть в обоих столбцах А и B

Задание 8.

Ввести произвольные значения в столбец А

и развернуть их в строку 1.

Задание 9

Записать в таблицу размером 10×10 нули и единицы в шахматном порядке

010101
101010
010101

Задание 10

записать в таблицу 10х10 значения координат ячеек

1;1 1;2 1;2
2;1 2;2 2;3

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