Запись макроса в MS Excel

Запись макроса в MS Excel

Задание 1

С помощью макрорекордера записать макрос в MS Excel. Назначить горячие клавиши для выполнения макроса.

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

Выбираем меню Сервис > Макрос > Начать запись.

Выполняем центрирование содержимого активной ячейки (ПКМ > Формат ячеек > вкладка Выравнивание).

Останавливаем запись.

Запускаем редактор VBA (Alt+F11), получаем готовый код.

Макрос1()

' Макрос1 Макрос

' Макрос записан 13.05.2011

' Сочетание клавиш: Ctrl+мSelection

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlCenter

.WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = FalseWith

End Sub

Выбираем меню Сервис > Настройка, вкладка Команды, Категория Макросы, команда Настраиваемая кнопка, перетаскивем ее на панель инструментов.

Диалоговое окно Настройка НЕ ЗАКРЫВАЕМ, щелкаем по созданной кнопке ПКЛ (правой клавишей мыши), выбираем из контекстного меню Назначить макрос

Рисунок 1 - Назначение макроса кнопке

Задание 2

Создать функции пользователя. На рабочем листе создать таблицы значений функций. Просчитать 10 значений для каждой функции (интервал и шаг выбрать самостоятельно).

Определяем пользовательские функции (пишем их в уже созданном Module1 из Задания 1).

Function y(x As Single) As Single= Cos(3 * Application.Pi * x) + Sin(5 * Application.Pi * x)Functionz(x, y As Single) As Single= ((1 + x ^ 4 + x ^ 2) ^ (1 / 3) + y) / (1 + y)Functiong(x As Single) As Singlex < 1 Then= Abs((x ^ 2 - 5) / x)= x + 10IfFunction

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

Рисунок 2

Задание 3

Создать функцию и процедуру с использованием оператора Select Case.

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

если продукции продано меньше, чем на C1 руб., то премия составит k1% от стоимости реализованной продукции;

если продукции продано не меньше, чем на C1 руб., но меньше, чем на C2 руб., то премия составит k2%;

если продукции продано не меньше, чем на C2 руб., но меньше, чем на C3 руб., то премия составит k3%.;

если продукции продано не меньше, чем на C3 руб., то премия составит k4%.

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

Процедура запрашивает данные с помощью оператора InputBox и выдает результат с помощью оператора MsgBox.

Function mycase(x As Long)Case xIs < 10000= x * 0.0110000 To 20000= x * 0.01520000 To 40000= x * 0.03Else= x * 0.045SelectFunctionmycase1()x, p As Double= InputBox("введите сумму")Case xIs < 10000= x * 0.0110000 To 20000= x * 0.01520000 To 40000= x * 0.03Else= x * 0.045Select

Работа процедуры:

Рисунок 3

Задание 4

Создать процедуры с использованием операторов цикла For/Next и Do/Loop.

а) Найти сумму и произведение первых n членов ряда (использовать цикл For/Next).

б) Определить количество членов ряда, необходимых для достижения суммой значения k (использовать цикл Do/Loop).

Результат работы процедуры выдать с помощью оператора MsgBox.

Ryad()

Dim s, n, i As Bytep As Long= 0= 1= 1i = 2 To 16 Step 2= s + i= p * ii

MsgBox ("Сумма=" & s & Chr(13) & "Произведение=" & p)

End SubMSum()n, s As Byte= 1= 2= s + 2= n + 1While s <= 110

MsgBox ("Сумма = " & s & Chr(13) & "Достигнута на " & n & " члене ряда")Sub

Результаты:

Рисунок 4

оператор массив макрос значение

Задание 5

Создать процедуры с использованием операторов цикла For/Next и For/Each.

а) Значения элементов массива берутся из ячеек рабочего листа (ячейки должны быть предварительно заполнены). Результат вычислений вывести в ячейку рабочего листа. Для вычислений использовать цикл For/Next.

б) Размер массива вводится с помощью оператора InputBox. Массив заполнить случайными числами с помощью функции Rnd. Массив вывести в ячейки рабочего листа. Результат вычислений вывести с помощью оператора MsgBox. Для вычислений использовать цикл For/Each.

Sub MasSum()Mas(3, 4)s, i, j As Byte= 0i = 1 To 3j = 1 To 4(i, j) = Cells(i, j)(Mas(i, j) > 1) And (Mas(i, j) < 5) Then= s + 1 'считаем количествоIfji(4, 4) = sSubMasSum2()Mas(3, 4)i, j, s As Byte, d As Variant

Dim n, m As Byte= InputBox("введите количество строк массива")= InputBox("введите количество столбцов массива")

s = 0i = 1 To nj = 1 To m(i, j) = Int(Rnd * 10)

Cells(i + 5, j) = Mas(i, j) 'вывод массива с 6 строки

If (Mas(i, j) > 1) And (Mas(i, j) < 5) Then= s + 1 'считаем количествоIfji("колво = " & s)

End Sub

Рисунок 5

Задание 6

Создать пользовательскую форму для заполнения базы данных. Содержимое базы данных определить самостоятельно.

Форма должна содержать текстовые поля, текстовые поля со счетчиками, выпадающие списки,  флажки, переключатели, кнопки.

Размер таблицы базы данных должен быть ограничен. Например, таблица может содержать до 20 записей. Если таблица уже содержит 20 строк, то при попытке добавления новой записи выдавать сообщение о том, что таблица переполнена.

Записать макрос для построения шаблона таблицы на рабочем листе. Шапка таблицы соответствует полям формы. Если на рабочем листе уже существует таблица, то при выполнении макроса она должна быть удалена.

Записать макрос для удаления выделенной строки таблицы и очистки всей таблицы.

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

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

Создать защиту от неправильного ввода в поля формы. Например, при попытке ввода в поле, предназначенное для ввода фамилии, чисел выдавать сообщение об ошибке.

Перед внесением данных в таблицу проверять, все ли поля формы заполнены. Если есть незаполненные поля, выдать сообщение с предупреждением.

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

Задание выполнено на листе «Задание 6». Тексты процедур и функций находятся в модуле Module1 и формы UserForm1 файла КР в1.xls.

Рисунок 6 - Форма

Рисунок 7 - Пункты меню

Основные процедуры и функции Формы:

'проверка все ли поля заполнены

prov() As Boolean

prov = TrueTextBox1 = "" Then prov = FalseTextBox2 = "" Then prov = FalseTextBox3 = "" Then prov = FalseComboBox1 = "" Then prov = FalseComboBox2 = "" Then prov = False

End Function

'Добавить

Sub CommandButton1_Click()iRow As Integer 'последния строкаiStr As String

iRow = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1

If iRow >= 23 Then("БД переполнена" & Chr(13) & "Выполните очистить БД")

UserForm1.Hideprov = False Then

MsgBox ("Все поля должны быть заполнены")

Else("A" & CStr(iRow + 1)).Select.Value = TextBox1.Text.Offset(0, 1).Value = TextBox2.Text.Offset(0, 2).Value = TextBox3.Text.Offset(0, 3).Value = ComboBox1.Text.Offset(0, 4).Value = ComboBox2.TextOptionButton1.Value = True Then= "Тверсия"IfOptionButton2.Value = True Then= "Аверсия"= "Рверсия"If.Offset(0, 5).Value = iStrCheckBox1.Value = True Then= "Да"= "Нет"If.Offset(0, 6).Value = iStr

'обработка счетчика

Private Sub SpinButton1_SpinDown().Value = SpinButton1.Value

End SubSub SpinButton1_SpinUp().Value = SpinButton1.ValueSub

'в поле ГодИзд ограничим нажатие клавиш только числовой частью клавиатуры

Private Sub TextBox3_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)(KeyAscii < 48) Or (KeyAscii > 57) Then= 0

End IfSub

'Заполнение выпадающего списка Жанр при инициализации формы

Sub UserForm_Initialize()

OptionButton1.Value = True.Clear.AddItem ("Фантастика").AddItem ("Фэнтези").AddItem ("Юмор").AddItem ("Классика").AddItem ("Ужас").ListIndex = 0.Clear.AddItem ("AVI").AddItem ("MPEG4").AddItem ("DivX").ListIndex = 0Sub