Приемы программирования в среде Visual basic for application MS office (стр. 10 )

ActiveWorkbook.Close False

Иначе подготавливаем место для сохранения данных на листе «Эксперимент». Для этого определяем переменную ic = 1 (номер первого столбца на листе «Эксперимент») и ищем свободный столбец с приращением в 2 столбца:

ic = 1

Do While Not IsEmpty(TW. Cells(1, ic))

ic = ic + 2

Loop

Теперь вносим начальные данные по серии – дату, время и имя файла

TW. Cells(1, ic) = Date

TW. Cells(1, ic + 1) = Time

TW.Cells(2, ic) = ActiveWorkbook.Name

Остается перенести сами данные, и сосчитать их количество. Для этого используем две переменные ir = 1 – текущий номер строки в файле данных и ke = 0 – число экспериментов в серии. Хотя можно воспользоваться и одной переменной с прибавлением или отниманием 1. Потом в цикле с предусловием переносим данные:

ir = 1: ke = 0

Do While Not IsEmpty(AW.Cells(ir, 1)) Or _

Not IsEmpty(AW. Cells(ir, 2))

TW. Cells(ir + 3, ic) = AW. Cells(ir, 1)

TW. Cells(ir + 3, ic + 1) = AW. Cells(ir, 2)

ir = ir + 1: ke = ke + 1

Loop

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

ActiveWorkbook. Close False

TW. Cells(3, ic) = ns

В полном виде программа показана ниже:

Public Sub Get_Dates()

R = Application. Dialogs(xlDialogOpen).Show

If R Then

Set AW = ActiveWorkbook. Sheets(1)

Set TW = ThisWorkbook. Sheets("Эксперимент")

If Not IsEmpty(AW. Cells(1, 1)) And _
Not IsEmpty(AW. Cells(1, 2)) Then

ic = 1

Do While Not IsEmpty(TW. Cells(1, ic))

ic = ic + 2

Loop

TW. Cells(1, ic) = Date

TW. Cells(1, ic + 1) = Time

TW. Cells(2, ic) = ActiveWorkbook. Name

ir = 1: ke = 0

Do While Not IsEmpty(AW. Cells(ir, 1)) Or _
Not IsEmpty(AW. Cells(ir, 2))

TW. Cells(ir + 3, ic) = AW. Cells(ir, 1)

TW. Cells(ir + 3, ic + 1) = AW. Cells(ir, 2)

ir = ir + 1: ke = ke + 1

Loop

ActiveWorkbook. Close False

TW. Cells(3, ic) = ns

Else

MsgBox "На первом листе книги данных нет", _
vbInformation + vbOKOnly

End If

Else

MsgBox "Для работы укажите файл с данными", _
vbInformation + vbOKOnly

End If

End Sub

Здесь мы рассмотрели приемы записи и считывания данных на листах книги. Наряду с этими приемами мы можем управлять непосредственно событиями, как книги, так и отдельными ее листами. Для этого мы должны писать свой программный код в специальные модули самих объектов. Причем в них обычно пишутся программы обработки событий, которые характерны этим объектам. Так, например, для книги могут быть реализованы события:

· Activate – происходит в момент активации листа. В этот момент мы может настроить вид листа (выбрать масштаб просмотра, определить активную ячейку, вывести определенную область листа на экран и т. п.);

· Deactivate – противоположное событие, происходящее перед выбором другого листа. Здесь можно проверить правильность введенных данных, и при выявленных ошибках вернуть пользователя для их исправления, выполнить какие-либо расчеты, которые требуют времени и были деактивированы при вводе данных на листе, и другие операции;

· Change – в момент изменения данных в ячейках листа. Здесь обычно проводят проверки правильности ввода данных;

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

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

Свои события имеют и книги. Чаще всего используются события управления книгой:

· Open – в момент открытия книги. Здесь настраиваются вид оболочки Excel, запускаются пользовательские формы, выполняются первоначальные расчеты и подготовка памяти для дальнейших работ;

· Группа событий Before… Close, Save, Print и другие. В них можно выполнить действия перед выполнением указанных выше операций закрытия книги, печати и сохранения или проверить правомочность этих действий и отказаться от их выполнения;

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

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

Для программы построения графиков добавим событие Open, в котором запишем проверку на открытие листа «Данные». Для события листа «Данные» в событие SelectionChange введем контроль ввода данных только в разрешенные ячейки. И последняя проблема – поставить элемент управления для построения графика. Посмотрим, как выполнить эти исправления. Для этого открываем файл «VBA_График.xls», входим в редактор VBA, и открываем модуль «ЭтаКнига» (рис.29). В окне модуля (слева) выбираем в левом выпадающем списке объект –WorkBook и в правом событие Open.

Рис.29. Выбор модуля «ЭтаКнига» и ввод кода в событие Open

В предложенную заготовку вписываем одну строку:

ThisWorkbook.Sheets("Данные").Activate

Теперь переходим к «Лист2(Данные)», выбираем объект WorkSheet и событие SelectionChange (Рис.30). Здесь надо подумать об алгоритме работы программы. Нам надо сделать так, чтобы программа могла проверить разрешен ввод данных в эту ячейку или нет.

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

Рис.30. Выбор модуля «Лист2(Данные)» и событие SelectionChange

Реально нам надо искать нужные ячейки в области от «B2:G4». Попробуем записать макрос, который будет искать нужные нам ячейки. Для этого выполняем все необходимые операции в режиме записи макроса, как было показано выше.

Вызываем команду СервисðМакросыðНачать запись, соглашаемся с предложенным именем макроса и начинаем записывать саму программу:

· Выделяем диапазон от ячейки B2 до G4;

· Вызываем команду поиска ПравкаðНайти…;

· На панели Найти и заменить нажимаем кнопку Параметрыð;

· На открывшейся расширенной панели поиска нажимаем кнопку Формат;

· В открывшемся окне Найти формат открываем закладку Вид и выбираем необходимый нам цвет;

· Подтверждаем выбор цвета кнопкой Ok и нажимаем кнопку Найти далее;

· Завершаем запись макроса.

Переходим в редактор VBA и видим там новый модуль «Модуль2», в нем созданный нами макрос.

Range("B2:G4").Select

Application. FindFormat. Interior. ColorIndex = 35

Selection. Find(What:="", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=True).Activate

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

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target. Interior. ColorIndex = 35 Then Exit Sub

Ok = False

If Target. Row < 2 Then

r = 2: Ok = True

ElseIf Target. Row > 4 Then

r = 4: Ok = True

Else

r = Target. Row

End If

If Target. Column < 2 Then

c = 2: Ok = True

ElseIf Target. Column > 7 Then

c = 7: Ok = True

Else

c = Target. Column

End If

If Ok Then Cells(r, c).Activate

Range("B2:G4").Find(What:="", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=True).Activate

End Sub

Разберем его более подробно. В первой строке проверяем, удовлетворяет нас ячейка, которая в данный момент выбрана, и выходим из программы, если это так. Дальше проверяем, находится ли активная ячейка внутри диапазона поиска. Если это не так, выбираем ближайшую к ней ячейку внутри диапазона, и делаем эту ячейку активной. Переменная Ok сообщает о необходимости перемещения активной ячейки внутри диапазона. Теперь можно выполнять поиск необходимой ячейки.

Ясно, что для полнофункциональной версии работы программы надо вывести на лист элемент управления, который будет вызывать подпрограмму построения графика. Воспользуемся формами для управления объектами. Вызовем стандартную панель инструментов Формы, и выберем кнопку. Нарисуем ее на листе ниже заголовков для графика. Как только мы завершаем рисование кнопки, открывается меню выбора макроса, который будет привязан к этой кнопке, и выбираем MakeGraf. После этого предлагается изменить заголовок кнопки. Пишем «Строим график», и получаем кнопку вида . Можно настроить и ряд других параметров, если вызвать с помощью контекстного меню свойства этого объекта.

Лабораторные задания по темам

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

1. Даны две целые переменные а, b. Составить фрагмент программы, после исполнения которого значения переменных поменялись бы местами (новое значение а равно старому значению b и наоборот).

2. Дано расстояние L в сантиметрах. Используя операцию деления нацело, найти количество полных метров в нем (1 метр = 100 см).

3. Дан размер файла в байтах. Используя операцию деления нацело, найти количество полных килобайтов, которые занимает данный файл (1 килобайт = 1024 байта).

4. Даны целые положительные числа A и B (A > B). На отрезке длины A размещено максимально возможное количество отрезков длины B (без наложений). Используя операцию взятия остатка от деления нацело, найти длину незанятой части отрезка A.

5. Определить, является ли данное целое число четным.

6. Определить, верно ли, что при делении неотрицательного целого числа а на положительное целое число b получается остаток, равный одному из двух заданных чисел r или s.

7. Дано двузначное число. Найти сумму и произведение его цифр.

8. Дано двузначное число. Вывести число, полученное при перестановке цифр исходного числа.

9. Дано натуральное число (n ≤99). Выяснить верно ли, что равно n2 кубу суммы цифр числа n.

10. Дано натуральное число n (n ≤ 100).

а) Сколько цифр в числе n?

б) Чему равна сумма его цифр?

в) Найти последнюю цифру числа n.

г) Найти первую цифру числа n.

д) В предположении, что n≥10, найти предпоследнюю цифру числа n.

Все входные и выходные данные в заданиях этой группы являются вещественными числами.

1. Дана сторона квадрата a. Найти его периметр P = 4·a.

2. Даны стороны прямоугольника a и b. Найти его площадь S = a·b и периметр P = 2·(a + b).

3. Дан диаметр окружности d. Найти ее длину L = π·d.

4. Дана длина ребра куба a. Найти объем куба V = a3 и площадь его поверхности S = 6·a2.

5. Даны длины ребер a, b, c прямоугольного параллелепипеда. Найти его объем V = a·b·c и площадь поверхности S = 2·(a·b + b·c + a·c).

6. Найти длину окружности L и площадь круга S заданного радиуса R: L = 2·π·R, S = π·R2.

7. Даны два неотрицательных числа a и b. Найти их среднее геометрическое, то есть квадратный корень из их произведения:

8. Даны два ненулевых числа. Найти сумму, разность, произведение и частное их квадратов.

9. Даны катеты прямоугольного треугольника a и b. Найти его гипотенузу c и периметр P: , .

10. Даны два круга с общим центром и радиусами R1 и R2 (R1 > R2). Найти площади этих кругов S1 и S2, а также площадь S3 кольца, внешний радиус которого равен R1, а внутренний радиус равен R2: ,, .

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

1. Дано целое число A. Проверить истинность высказывания: «Число A является положительным».

2. Дано целое число A. Проверить истинность высказывания: «Число A является нечетным».

3. Даны два целых числа: A, B. Проверить истинность высказывания: «Справедливы неравенства A > 2 и B ≤ 3».

4. Даны два целых числа: A, B. Проверить истинность высказывания: «Справедливы неравенства A ≥ 0 или B < –2».

5. Даны три целых числа: A, B, C. Проверить истинность высказывания: «Число B находится между числами A и C».

6. Даны два целых числа: A, B. Проверить истинность высказывания: «Хотя бы одно из чисел A и B нечетное».

7. Даны три целых числа: A, B, C. Проверить истинность высказывания: «Хотя бы одно из чисел A, B, C положительное».

8. Дано целое положительное число. Проверить истинность высказывания: «Данное число является четным двузначным».

9. Дано целое положительное число. Проверить истинность высказывания: «Данное число является нечетным трехзначным».

10. Проверить истинность высказывания: «Среди трех данных целых чисел есть хотя бы одна пара взаимно противоположных».

11. Дано трехзначное число. Проверить истинность высказывания: «Все цифры данного числа различны».

1. Петя встал утром в 7 часов, Коля ‑ на 13 минут раньше него, Сережа на 4 минуты позже Коли, а Саша встал на 10 минут позже Сережи. Кто встал раньше, Петя или Саша?

2. У Сережи 145 рублей. Он решил купить 3 блокнота по 48 рублей. Хватит ли денег на покупку?

3. Из куска ткани можно сшить 17 детских плащей, расходуя на каждый 2 м ткани. Можно ли из этого куска сшить 8 плащей для взрослых, расходуя по 4 м ткани на каждый плащ?

4. В коробке 3 кг лимонов и апельсинов. Лимоны вынули и взвесили отдельно. Их масса оказалась 1 кг 760 г. Каких цитрусовых больше и на сколько?

5. Валя взяла у подруги книгу на 3 дня. В первый день она прочитала А страниц, во второй день прочитала в В раз больше, чем в первый, в третий день она прочитала на С страниц меньше, чем во второй. Успела ли Валя прочитать книгу вовремя, если в книге 194 страницы? Решить задачу при следующих значениях переменных:

а) А = 28, В = 3, С = 25;

b) А = 30, В = 3, С = 16.

6. Мотоциклист должен прибыть в город через Т часов после выезда из пункта, находящегося на расстоянии S км от города. Успеет ли он вовремя прибыть в город, если в первый час он проедет А км, а все оставшееся время будет ехать со скоростью В км/ч? Решить задачу при следующих значениях переменных:

a) Т = 4, S = 320, А = 75, В = 70;

b) Т = 5, S = 300, А = 65, В = 60.

7. В театр пришло А зрителей. Из них В человек купили билеты по 30 рублей, остальные ‑ по 20 рублей. Получит ли театр прибыль, если расходы на спектакль составили Z рублей? Решить задачу при следующих значениях переменных:

a) А = 800, В = 300, Z = 15000;

b) А = 600, В = 100, Z = 15000.

8. Расстояние между городами А и В равно S. Из города А в город В выехал велосипедист со скоростью Vr, через Т часов вслед ему выехал 2-й велосипедист со скоростью Vt. Догонит ли 2-й велосипедист 1-го, прежде чем тот доедет до города B? Решить задачу при следующих значениях переменных:

a) S = 50, Vr = 12, ,= 16, Т = 2;

b) S = 50, Vr= 12, Vt = 16, Т= 1.

9. Дано целое число. Если оно является положительным, то прибавить к нему 1; если отрицательным, то вычесть из него 2; если нулевым, то заменить его на 10. Вывести полученное число.

10. Даны пять целых числа. Найти количество положительных и количество отрицательных чисел в исходном наборе.

11. Даны два числа. Вывести их на экран дисплея в порядке возрастания.

12. Даны два числа. Вывести на экран первое число, если оно больше второго, и оба числа, если это не так.

13. Даны две переменные вещественного типа: A, B. Перераспределить значения данных переменных так, чтобы в A оказалось меньшее из значений, а в B ‑ большее. Вывести новые значения переменных A и B.

14. Даны четыре числа. Найти разницу между максимальным ц минимальным числами.

15. Даны две переменные целого типа: A и B. Если их значения не равны, то присвоить каждой переменной сумму этих значений, а если равны, то присвоить переменным нулевые значения. Вывести новые значения переменных A и B.

16. Даны три числа. Меньшее из этих чисел заменить их полусуммой, а большее ‑ удвоенным произведением.

17. Даны три числа. Найти сумму двух наибольших из них.

18. Даны три числа. Выяснить, существует ли треугольник с такими сторонами. (Условие существования треугольника следующее: сумма любых двух сторон треугольника должна быть больше третьей стороны.)

а) 3, 4, 5;

b) 10, 8, 3.

19. При выполнении условия вычислить , в противном случае проверить условие и если оно выполняется, вычислить , в противном случае z=3.2.

20. Для данного целого x найти значение следующей функции f, принимающей значения целого типа:

1. Составить программу, которая и зависимости от порядкового номера дня недели выводит на экран его название (понедельник вторник.... воскресенье).

2. Дано целое число K. Вывести строку-описание оценки, соответствующей числу K (1 ‑ «плохо», 2 ‑ «неудовлетворительно», 3 ‑ «удовлетворительно», 4 ‑ «хорошо», 5 ‑ «отлично»). Если K не лежит в диапазоне 1–5, то вывести строку «ошибка».

3. Дан номер месяца ‑ целое число в диапазоне 1–12 (1 ‑ январь, 2 ‑ февраль и т. д.). Вывести название соответствующего времени года («зима», «весна», «лето», «осень»).

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

5. Составить программу, моделирующую работу продавца мороженого с выбором сорта мороженого.

6. Арифметические действия над числами пронумерованы следующим образом: 1 ‑ сложение, 2 ‑ вычитание, 3 ‑ умножение, 4 ‑ деление. Дан номер действия N (целое число в диапазоне 1–4) и вещественные числа A и B (В не равно 0). Выполнить над числами указанное действие и вывести результат.

7. Пусть элементами круга являются радиус (первый элемент), диаметр (второй элемент), длина окружности (третий элемент). Составить программу, которая по номеру элемента и по его значению вычисляла бы площадь круга.

8. Дата некоторого дня характеризуется двумя натуральными числами m (порядковый номер месяца) и n (число). По заданным m и n определить:

а) дату предыдущею дня (принять, что m и n не характеризуют 1 января);

б) дату следующего дня (принять, что m и n не характеризуют 31 декабря).

9. Для натурального числа k напечатать фразу "мы нашли k грибов в лесу", согласовав окончание слова "гриб" с числом k.

10. Работа светофора для водителей запрограммирована следующим образом: начиная с начала каждого часа, в течение трех минут горит зеленый сигнал, затем в течение одной минуты ‑ желтый, в течение двух минут ‑ красный, в течение трех минут ‑ опять зеленый и т. д. Дано вещественное число t, означающее время в минутах, прошедшее с начала очередного часа. Определить, сигнал какого цвета горит для водителей в этот момент.

1. Даны два целых числа A и B (A < B). Вывести в порядке убывания все целые числа, расположенные между A и B (не включая числа A и B), а также количество N этих чисел.

2. Дана числовая последовательность . Найти сумму отрицательных элементов при n=100.

3. Даны два целых числа A и B (A < B). Найти произведение всех целых чисел от A до B включительно.

4. Даны два целых числа A и B (A < B). Найти сумму квадратов всех целых чисел от A до B включительно.

5. Дана числовая последовательность. Найти произведение членов последовательности от пятого до двадцать пятого.

6. Дано действительное число х. Вычислить .

7. Дано целое число (N > 1) и две вещественные точки на числовой оси: A, B (A < B). Отрезок [A, B] разбит на N равных отрезков. Вывести H ‑ длину каждого отрезка, а также набор точек A, A + H, A + 2·H, A + 3·H, …, B, образующий разбиение отрезка [A, B].

8. Дано действительное число х. Вычислить .

9. Пусть Дано натуральное n. Получить an.

10. Пусть Дано натуральное n. Найти .

11. Пусть Найти первый член хn, для которого .

12. Даны действительные числа а, b, натуральное число n (b>а). Получить (f1 + ... + fn)×h, где .

13. Даны целые числа n, k (n≥k≥0). Вычислить .

1. Даны положительные числа A и B (A > B). На отрезке длины A размещено максимально возможное количество отрезков длины B (без наложений). Не используя операции умножения и деления, найти длину незанятой части отрезка A.