Excel 5.0: заметки на полях
Часть первая, неформальная *Решатель электронных таблиц Excel: взгляд шутника, дериватора и эстета
*Пример 1. Поиск корня алгебраического уравнения
*Примеры 2-4. Задача старушки “Наири”: поиск минимума функции
*Пример 5. Решение системы линейных алгебраических уравнений
*Пример 6. Транспортная задача
*Пример 7. Задача целочисленного линейного программирования
*Выводы
*Литература:
*Часть вторая, формальная
*Некоторые общие замечания по Excel 5.0 (сравнение с Excel 4.0)
*Переход к VBA
*Сравнение VBA c ML
*А теперь о самой первой причине. Чем VBA лучше макроязыка?
*Сравнение VBA с VB
*Решатель электронных таблиц Excel: взгляд шутника, дериватора и эстета
Рассказывают такую историю.
Воздушный шар вырвался из облаков. Летящие увидели на земле человека и закричали: “Где мы находимся?!”. “Вы находитесь в корзине воздушного шара”, - таков был ответ. Его дал математик. Только от математика можно услышать совершенно точный и совершенно бесполезный ответ.
Статьи, просто информирующие читателя о новых программных продуктах, подобны ответу математика если не своей точностью, то своей бесполезность. Для серьезного разговора места в журнале мало. Часто вместе со статьей выходит новая версия программы с новыми возможностями, требующая нового анализа (феномен света угасшей звезды).
Есть особый род шутников (а к ним относит себя и автор), получающих удовольствие от попыток розыгрыша компьютера. “Много ты, машина о себе воображаешь. Посмотрим-ка, проглотишь ли ты вот это!?”, - так или примерно так думают они, вводя в ЭВМ заведомо “неправильную” информацию. Анализ реакции компьютера на подобные шутки пользователя может дать не меньше, чем классический разбор программы.
Автор надеется, что читатель уже заинтригован названием и началом статьи. Смысл термина дериватор в ней зашифрован. Теперь несколько слов о ее теме.
В статье будет рассмотрено решение в среде электронных таблиц Microsoft Excel 5.0 (русская версия) семи задач из области вычислительной математики. Примеры взяты не из пакета Excel 5.0 (а там есть схожие задачи), а из других источников [1-3] и подобраны так, чтобы можно было показать не легкость и изящество их решения, а возникающие при этом осложнения и пути их преодоления (да простят автора за это сотрудники московского отделения Microsoft, предоставившие ему для опробования экземпляр Excel 5.0). К примерам, прилагаемым к пакетам фирмами-разработчиками, следует относится с большой опаской. Тут можно вспомнить эпохальную машину “Наири-2”, в матобеспечении которой была программа “Поиск минимума функций”. Так вот, после опробования в ее названии слово “функций” приходилось менять на слово “функции”, т.к. минимум искался только у встроенной в программу функции и больше ни у какой другой.
Автор никогда бы не осмелился так “отблагодарить” фирму Microsoft, если б не тот факт, что Решатель (Solver), интегрированный в пакет Excel и реализующий технологию “Что-если”, фирма Microsoft приобрела у сторонних организаций - у фирм
Frontline System и Optimals Methods.Пример 1. Поиск корня алгебраического уравнения
Есть хорошее правило - при отладке и испытании новой программы нужно подсунуть ей задачу, решение которой заранее известно.
Рис. 1 - это таблица с результатами поиска корня простейшего уравнения X
2 - 3 = 0 командой Подбор параметра... из меню Сервис. После ее отдачи на дисплей выводится диалоговое окно (рис. 2), в котором пользователь заполняет три поля: “Установить в ячейке” (указывается ячейка В5), ”Значение” (0) и ”Изменяя ячейку” (В4). Четвертый установочный параметр - исходное значение Х (там было 3), от которого начинается поиск корня, записывается в ячейке В4 самой электронной таблицы. В таблицах, иллюстрирующих статью, изменяемые пользователем (исходные) величины отмечены курсивом. Полужирный шрифт отмечает комментарии.Из рис. 1 видно (см. строки 2-5), что команда Подбор параметра... сработала правильно.
В записках Сергея Довлатова отмечено, что семья - это когда по шуму в ванной можно догадаться, кто там моется. К сожалению, ни в Help’е, ни в руководстве пользователя пакета Excel ничего не сказано о том, какие вычислительные методы заложены в Решатель. Приходится догадываться по “шуму в ванной” - по результатам тестирования команды Подбор параметра... через изменение тройки входных параметров: вид анализируемого уравнения, значение начального приближения (у нас оно равно трем) и точность расчета. Последний входной параметр пользователь менять не может, что чревато досадными ошибками: если в нашей задаче о корне уравнения Х
2 - 3 = 0 умножить его левую часть на коэффициент 0,0001 (см. строки 7-9 на рис. 1), то искомый корень “размажется” на недопустимо широком интервале оси Х, и никакая сила не заставит машину найти нужное решение - плюс-минус квадратный корень из трех.В описанной простейшей задаче кроется подвох, связанный с разным подходом к ней прикладных математиков и специалистов, использующих численные методы для решения своих прикладных проблем. Первые связывают точность расчетов с величиной функции. Вторым же на функцию наплевать (они часто даже не представляют, в каком диапазоне она меняется). Главное, чтобы достигнутое расчетом значение аргумента находилось как можно ближе к тому месту, где функция меняет свой знак, т.е. к корню уравнения. Из-за этого нередко приходится отказываться от стандартных методов вычислительной математики и “изобретать велосипед” - писать свою собственную программу поиска корня методом секущих, Ньютона, половинного деления и т.д., ориентируясь на свойства конкретной моделируемой системы.
Function Y(X)
Y = X ^ 2 - 3 ‘ Анализируемое уравнение
End Function
Sub Кнопка1_Щелкнуть() ' Процедура "Метод секущих" (шум в ванной)
TOL = ,0001 ‘ Точность расчета
X = [B4] ‘ Первое приближение к корню
If Abs(Y(X)) < TOL Then
Root = X
Else
If X = 0 Then h = TOL Else h = X * TOL
X0 = X: X1 = X + h
Do ‘ Цикл приближения к корню
Root = X1 - Y(X1) * (X1 - X0) / (Y(X1) - Y(X0))
X0 = X1: X1 = Root ‘ Подготовка к следующему приближению
Loop Until Abs(Y(X1)) <= TOL
End If
[B4] = Root ‘ Корень
End Sub
Изменение точности расчета (и многое другое) допустимо при использовании второй команды Решателя - команды Поиск решения..., после отдачи которой появляется одноименное диалоговое окно - см. рис. 3. Эта команда намного перекрывает первую команду - команду Подбор параметра..., которая лишняя и оставлена в меню Сервис, по-видимому, для совместимости со старыми версиями Excel’а. Работая с командой Поиск решения..., можно менять не только точность, но и другие параметры поиска - см. рис. 4. Но опять же процесс приближений к корню ориентирован не на значение аргумента, а на значение функции, что чревато новыми ошибками.
С поиском корня команда Поиск решения... справилась, посмотрим, “проглотит” ли она оптимизационные задачи?!
Примеры 2-4. Задача старушки “Наири”: поиск минимума функции
В специальной литературе приводятся три тестовые функции (функция Розенброка, функция Пауэлла и двухмерная экспоненциальная функция), с которых рекомендуют начинать испытания программ поиска минимума [1-3]. Если компьютер эту тройку “разъяснит”, то можно ... проводить следующие испытания. Если же проверка завершится неудачей, то программа отбраковывается. С функциями Розенброка и Пауэлла (см. рис. 5 и 6) Решатель справится, но за две попытки: сначала выдается неверный результат, после чего пользователь, знающий правильный ответ (функция-то тестовая), должен еще раз отдать команду Поиск решения...
, но уже от новой начальной точки - от ложного ответа первого приближения. Попытки автора за счет изменения параметров поиска (максимальное время счета, максимальное число итерации, точность и т.д. - см. рис. 4) избежать “привала” на ложном решении не привели к успеху. Ничего не было бы страшного в таком своеобразном двухэтапном (с привалом) процессе, если бы Решатель не выдавал пользователю ложную информацию о том, что после первого этапа “Решение найдено, все ограничения выполнены”. Тут вспоминается знаменитая эпиграмма А.С.Пушкина на Фаддея Булгарина:Не то беда, Авдей Флюгарин,
Что родом ты не русский барин,
Что на Парнасе ты цыган...
Беда, что скучен твой роман.
Не то беда, что ты, Решатель, не решил простейшую оптимизационную задачу. Более мощные специализированные пакеты часто тут оказываются бессильными. Нет и не может быть универсальной программы-панацеи, годной на все случаи. Беда, что неверный ответ сопровождается дезинформирующим сообщением.
А вот с двухмерной экспоненциальной функцией (рис. 7) вышел полный конфуз. Чья здесь вина - пакета Excel или автора, не сумевшего подобрать соответствующие параметры поиска, трудно сказать. Но факт остается фактом - задача осталась нерешенной. Автор даже не хотел помещать этот пример в статье (“слово - серебро, молчание - золото”), но три фактора заставили поменять решение. Во-первых, Excel Solver - это детище не Microsoft, а другой фирмы и мы уже это отмечали. Во-вторых, московская группа поддержки Excel, куда обратился автор, не дала ответа, а переправила примеры дальше в Мюнхен - в европейский филиал Microsoft. Из Мюнхена пришел ответ, смысл которого заключался в том, что это не вина пакета Excel, а вина ... функции - не нужно быть такой сложной и противной. В-третьих, автор надеется, что кто-то из читателей в этой проблеме разберется и даст правильный ответ. А пока помещаем ниже простейшую Basic-процедуру, взятую из [2] и легко решающую эту проблему
' Процедура “Два шага”
Const N = 2 ' Функция двухмерная
Dim X(0 To N)
Sub Кнопка1_Щелкнуть()
X(1) = [B4]: X(2) = [B5] ' Начальная точка
Ymin = ДвуЭксп(X(1); X(2)) ' Допущение
D = 1: DMin = ,0001: J = 0
Do While D > Dmin ' Цикл уменьшения шага
Do ' Цикл приближения к минимуму
P = 0
For I = 1 To N ' Перебор координат
For X0 = -D To D Step 2 * D 'Два шага
X(I) = X(I) + X0: Y = ДвуЭксп(X(1); X(2))
If Y < Ymin Then J = I: P = X0: Ymin = Y
X(I) = X(I) - X0
Next
Next
X(J) = X(J) + P ' Переход к новой точке
Loop Until P = 0
D = D / 2 ‘ Уменьшаем шаг в два раза
Loop
[B4] = X(1): [B5] = X(2) ' Найденный минимум
End Sub
Эта процедура реагирует на нажатие кнопки, расположенной в таблице на рис. 7. Она выдает правильный ответ по всем трем пробным функциям из любой начальной точки, хотя в нее заложен простейший алгоритм. Решатель пакета Excel (как, кстати, программы MathCAD и Eureka [3,4]) часто попадают в ловушки - в точки максимумов и перегибов.
Пример 5. Решение системы линейных алгебраических уравнений
Есть особый род извращенцев... Автору не хватило духа вынести этот термин в заголовок статьи и он его заменил нейтральным -
derivatio, отклонение. Читатель, может быть, до этого места статьи и не доберется, а об авторе подумает черти что. Так вот, есть особый род дериваторов, получающих удовольствие от попыток решения на компьютере задач программными средствами, для этих целей прямо не предназначенными. “А вы ноктюрн сыграть смогли бы на флейте водосточных труб!? А вы корни системы алгебраических уравнений найти смогли бы в среде Решателя пакета Excel!?”По идее (и здесь нет никакого, пардон, извращения) поиск корня системы линейных алгебраических уравнений должен быть базовой задачей любого Решателя, т.к. многие задачи вычислительной математики так или иначе упираются в эту рутинную проблему. Попробуем-ка решить в среде Excel классическую задачу из рассказа А.П.Чехова “Репетитор”.
“Купец купил 138 арш. черного и синего сукна за 540 руб. Спрашивается, сколько аршин купил он того и другого, если синее стоило 5 руб. за аршин, а черное 3 руб.?”
На рис. 8 помещена электронная таблица с решением задачи о купце и сукне через команду Поиск решения... . Cама система уравнений записана в одноименном диалоговом окне (см. рис. 2) в виде двух ограничений:
B6=B4*B9+B5*B10
B7=B9+B10
В качестве изменяемых ячеек были указаны ячейки B9:B10, где хранятся неизвестные системы - аршинаж, если так можно выразится, синего и черного сукна. В задаче о купце и сукне целевой функции нет, но если диалоговое окно "Поиск решения" вызывается впервые, то все его поля будут пустыми, требующими заполнений. Все, кроме одного - самого первого - "Установить целевую ячейку", в котором будут записаны координаты активной ячейки таблицы, где находился курсор. Решатель считает, что последняя информация, введенная пользователем в таблицу, - это целевая функция. Поэтому-то в диалоговом окне "Поиск решения" в поле "Установить целевую ячейку" автоматически выставляется соответствующая ссылка. Ни в документации, ни в Help'е пакета Excel нет ни слова о том, что технология "Что-если" может реализовываться без целевой функции. На эту удочку и попался автор, введя в ячейку B12 фиктивную целевую функцию
B9*B10*1E-100,
которая на всем разумном диапазоне изменения значений ячеек B9 и B10 удовлетворяет условиям точности, если переключатель в окне “Поиск решения” поставлен на позицию “Минимальное значение”. После такого обманного финта Решатель “отстанет” от целевой функции-пустышки и займется ограничениями - решением задачи, с которой он быстро справится: до отдачи команды Найти решение... ячейки B9 и B10 на рис. 8 были пустыми.
Прием подсовывания компьютеру “пустышки” (прием, лишний раз радующий пользователя, напоминая ему, что машина - это машина, а человек - это человек, царь не только живой, но и неживой природы) в ходу не только при работе с Excel, но и при использовании другого популярного Решателя - пакта MathCAD фирмы MathSoft [4]. Нередко в решаемых системах неизвестных оказывается больше чем уравнений. Человек знает, что это не ошибка, но пакет MathCAD тем не менее настойчиво требует, чтобы число неизвестных не превышало числа уравнений и неравенств (ограничений), зажатых между ключевым словом
while 1=1 do begin
...
if... then break; (continue)
...
end;
Впервые безметочная конструкция “цикл с выходом из середины“ была введена в язык Basic
(Do... If... Then Exit Do... Loop), а затем перекочевала в язык Pascal (версия Borland Pascal 7.0 - cм. выше). Казалось бы циклы дальше развивать некуда и незачем. Но нет! В языке Basic, интегрированном в Excel 5.0, появился новый вид цикла For Each параметр In множество... Next, заставляющий по-новому классифицировать циклы в программировании, опираясь на особенности дуэта “пользователь-компьютер”.Цикл 1. Ни компьютер (система программирования), ни человек не знают, когда и где цикл должен быть прерван. Эта конструкция реализуется на языке Basic двумя ключевыми словами
Do и Loop, между которыми зажато тело цикла.Цикл 2. Компьютер не знает, а человек знает, когда и где цикл должен быть прерван. В этом случае в тело цикла 1 программистом вкрапливаются ключевые слова
Exit Do (Basic) или breack-continue (Pascsl и C).Цикл 3. И человек, и машина знают, сколько раз нужно выполнить циклические действия. В этом случае тело цикла обрамляется ключевыми словами
For... To... [Step] и Next (Basic). Возможен гибрид циклов второго и третьего типа за счет ввода в цикл с параметром ключевых слов Exit For, обеспечивающих условное или безусловное прерывание цикла.Как понимает читатель, логическая таблица “знает-не знает”, положенная в основу вышеизложенной классификации циклов, до сих пор была не полной. Язык Basic пакета Exel 5.0 восполняет эту брешь новым циклом
For Each параметр In множество... Next, предназначенным для ситуаций, когда компьютер знает, сколько раз нужно повторить какие-то действия, а человек не знает или не хочет утруждать себя знанием такой чепухи. Пример: необходимо закрыть открытые электронные книги и открыть закрытые. Компьютер знает, что у него открыто, а что закрыто, человеку же пересчитывать все это не с руки. Вот тут-то и пригодится цикл For Each параметр In множество... Next.Цикл первого типа нереален исходя и из практических, и из эстетических соображений. Во-первых, нет ничего вечного под луной тем более вечных циклов, а во-вторых, число три (три вида циклов) изящней числа четыре. Автору уже приходилось отмечать [7], что в основу цифровой вычислительной техники положены числа 3 и 7, а не числа 2 (бит) и 8 (байт).
Забегая вперед - к примерам 6 и 7, отметим, что и там описанный прием отказа от целевой функции будет оправдан, если необходимо подобрать начальные параметры оптимизации - опорную точку поиска. Ведь часто бывает так, что Решатель не в силах одновременно и оптимизировать решение, и выполнить ограничения. В этом случае задача решается поэтапно: сначала вводятся все новые и новые ограничения, а в конце вставляется целевая функция. Но часто и до ввода целевой функции дело не доходит: пользователь удовлетворяется тем, что выполнены ограничения, руководствуясь мудрой пословицей: “Не до жиру, быть бы живу”. Целевую функцию-пустышку вводить, конечно не надо. Достаточно отавить в диалоговом окне "Поиск решения" поле "Установить целевую ячейку" пустым.
Помещаем ниже Basic-процедуру поиска корней системы
N линейных алгебраических уравнений, коэффициенты которых записаны в матрице (в двухмерном массиве) А, а корень записывается в вектор (в одномерный массив) Х.‘Pешение системы линейных алгебраических уpавнений
Const N = 2 ‘ Число уравнений (неизвестных)
Dim A(1 To N; 1 To N + 1); X(1 To N)
Sub Кнопка1_Щелкнуть()
‘ Заполнение массивов
A(1; 1) = [B4]: A(1; 2) = [B5]: A(1; 3) = [B6]
A(2; 1) = 1: A(2; 2) = 1: A(2; 3) = [B7]
‘ Решение системы
For J = 1 To N - 1
For I = J + 1 To N
A(I; J) = -A(I; J) / A(J; J)
For K = J + 1 To N
A(I; K) = A(I; K) + A(I; J) * A(J; K)
Next
A(I; N + 1) = A(I; N + 1) + A(I; J) * A(J; N + 1)
Next
Next
X(N) = A(N; N + 1) / A(N; N)
For J = N - 1 To 1 Step -1
P = A(J; N + 1)
For I = J + 1 To N
P = P - X(I) * A(J; I)
Next
X(J) = P / A(J; J)
Next
[B9] = X(1): [B10] = X(2) ‘ Ответ
End Sub
Вторая причина, заставившая автора поместить здесь эту классическую программу - это желание показать непоследовательность развития языка Basic и незавершенность логической схемы циклов, описанной выше. В программе решения системы алгебраических уравнений должны быть задействованы циклы
For Each параметр In множество... Next вместо циклов For... To... Step... Next. Но! Компьютер знает сколько раз выполнить цикл, но не знает, как его выполнить, с какого конца. Кроме того, на языке Basic нет перечисляемого типа данных. Помимо этого... Тут автор немного запутался в мыслях. Поэтому оставим “философские“ рассуждения и перейдем к новой задаче.Автора заставила взяться за эту оптимизационную задачу одна легковесная фраза на стр. 688 руководства пользователя Excel: “... но очевидно, что перевозить товары на близкие расстояния дешевле”. Если б это было так, то никакой проблемы под названием “Транспортная задача” в вычислительной математике не было бы, а расчет сводился бы к примитиву: сначала отвезем все, что допустимо ограничениями, по самому дешевому маршруту, затем по маршруту, второму по дешевизне, и т.д. Но не будем голословными и проиллюстрируем парадокс и сложность транспортной задачи конкретным примером.
В городе есть два склада телевизоров и два магазина. Необходимо ежедневно с первого склада вывозить 50 телевизоров, а со второго - 70. Первый магазин при этом получает 40 телевизоров, а второй - 80. Спрашивается, как нужно организовать работу транспорта, чтобы затраты на перевозку были минимальными.
Таблица с решением этой задачи с помощью Решателя пакета Excel показана на рис. 9. И что удивительно, по самому дешевому маршруту (со второго склада в первый магазин - 800 руб/шт) ничего не надо перевозить, если необходимо добиться минимальных суммарных затрат (134000 руб/сут). Этим парадоксом может воспользоваться хозяин транспортного предприятия. Для этого нужно в окне “Поиск решения” (см. рис. 3) соответствующий переключатель переставить с позиции “минимальное значение” на позицию “максимальное значение” и отдать команду “Выполнить”. Но Решатель, как бы чувствуя нечистую игру, подведет хитреца: с точки минимума план перевозок на точку максимума сдвинуть невозможно. Решение как бы сидит в некой локальной потенциальной яме, обусловленной принятой методикой оптимизации и точностью вычислений. Попытки автора варьировать параметрами поиска в соответствующем диалоговом окне (см. рис. 4) не привели к успеху. Но это еще опять же полбеды. Настоящая беда в том, что Решатель, как и в задачах 1-3, оставляя решение в точке минимума, по-прежнему нахально врет в глаза: ”Решение найдено, все ограничения выполнены”
.Примеры данной статьи - это тесты, т.е. задачи, решение которых заранее известно. Но в реальной жизни все обстоит гораздо сложней. “Mendax in unom, mendax in omnibus - солгавший в одном, лжет во всем”, - так утверждает римское право. Люди смягчили этот закон и говорят: “Единожды солгавший, кто поверит тебе?!” Но верить приходится, принимая во внимание, что абсолютной истины, к сожалению, а может быть к счастью, нет: у компьютера и у человека разные понятия о том, что такое решение задачи. Это мы уже отметили на элементарном примере 1 поиска корня алгебраического уравнения. В примере 6 “количество” этого противоречия перешло в “качество”. Для Решателя, по-видимому, нет большой разницы между минимумом (134000 руб/сут) и максимумом (142000 руб/сут). Второе число (максимум) можно получить (это подсказка хозяину транспортного предприятия), пойдя на двойную хитрость - выставив перед решением задачи не только переключатель на позицию “Максимальное значение” (см. рис. 3), но и взяв за основу другую опорную точку в ячейках В15:В18 - не нули, а сотни (явно завышенный план). От нулей в точку максимума затрат на перевозку можно также попасть, если не делать остановки на точке минимума.
При решении транспортной задачи (см. рис. 9) стартовая опорная точка (план перевозок) была равна 0,0,0 и 0 т/сут. Но это не установка автора, а умолчание пакета Excel: если ячейки пустые (а так и было в начале решения примера 6 - ячейки В15: В18 были пустые), то Решатель считает, что в них хранятся нули. А это не просто ошибка, а методическая ошибка - при пустых изменяемых ячейках Решатель должен отказываться выполнять задание, требуя от пользователя каких-то начальных установок. Пусть это будут те же нули, но пусть пользователь задумается над тем, “от какой печки нужно начинать плясать”, чтобы оптимальным образом решить задачу.
Популярный "школьный" Решатель - математический пакет Eureka фирмы Borland [3] по умолчанию начинает “плясать” не от нуля, а от единицы, что тоже нередко приводит к курьезам и сбоям. "Вузовский" Решатель MathCAD [3, 4] никаких умолчаний не допускает: начальное приближение будет лишним только тогда, когда заранее известно, что решение одно, когда, например, матричным способом решается система линейных алгебраических уравнений.
Кстати, сам пакет Excel далеко не всегда считает, что пустая ячейка - это ячейка с нулем. Иначе функции обработки списков (СУММ, MIN, MAX и др.) работали бы неправильно - не игнорировали бы пустые ячейки, считывая из них нули.
Транспортную задачу реальную, а не учебную как в примере 6 рекомендуется решать в несколько этапов. Вначале без целевой функции необходимо сделать “приcтрелки” - прощупать область существования решений, а уж потом оптимизировать задачу, взяв за опорную точку какой-либо план перевозок, удовлетворяющий ограничениям и расположенный недалеко от оптимума. Ограничения B15:B18=целое, кстати говоря, в задаче 6 лишние, т.к. экстремальные планы перевозок (134000 и 142000 руб/сут) сами по себе целочислены, а переход от минимума к максимуму проходит без эксцессов. Отсюда напрашивается вывод: ограничения на целочисленность следует вводить в задачу одним из последних.
Кстати говоря, совсем уж зарвавшийся хозяин транспортного предприятия может химичить не только с целевой функцией, но и с ограничениями типа B15:B18>=0, снимая их и организуя тем самым встречные перевозки.
Пример 7. Задача целочисленного линейного программирования
В оптимизационных задачах приходится сталкиваться не только с дилеммой “Правда-Ложь“ (см. выше), но и с дилеммой “Правда-Полуправда”, которую (дилемму) мы рассмотрим на примере поиска оптимального плана выпуска компьютеров.
Задачу линейного программирования почти всегда разбирают не в чистом математическом виде, а облекая ее в форму конкретной производственной задачи. Так получается интересней и наглядней: “Служил Гаврила почтальоном...”, “Служил Гаврила хлебопеком...” и т.д., если вспомнить сюжет Ильфа и Петрова. Автор помещает эту статью в журнал ”КомпьютерПресс”, поэтому “Гаврила” - это задача линейного программирования, а его текущая специальность - это специальность аудитории, какой о линейном программировании рассказывают.
Итак (см. рис. 10), необходимо запланировать выпуск компьютеров четырех типов с известной ценой каждого (см. ячейки G12:G15) и известным расходом комплектующих (ячейки B12:F15). В запасе есть определенный набор микросхем пяти типов (ячейки B4:B8). Ситуация отнюдь не вымышленная. Многие компьютерные фирмы часто ограничивают свое производство, ожидая от поставщиков (от фирмы Intel, например) комплектующих, чтобы можно было написать на компьютере “Intel Inside”. Это выражается системой семи ограничений:
B4 >= B12*B18+B13*B19+B14*B20+B15*B21
B5 >= C12*B18+C13*B19+C14*B20+C15*B21
B6 >= D12*B18+D13*B19+D14*B20+D15*B21
B7 >= E12*B18+E13*B19+E14*B20+E15*B21
B8 >= F12*B18+F13*B19+F14*B20+F15*B21
B18:B21 >= 0
B18:B21 = целое
Учитывая норов Решателя, проявленный в транспортной задаче, когда он зациклился на минимуме, возьмем в качестве опорной точки заведомо завышенный план выпуска компьютеров - 100, 100, 100 и 100 шт. После этого Решатель легко находит оптимальный план (100, 0, 20 и 0 шт.), максимизирующий общее число компьютеров (120). Но это будет уже не Ложью, но еще и не Правдой, а Полуправдой: более глубокий анализ задачи 7 показывает, что теже 120 компьютеров можно выпускать сотнями других вариантов. Какой из них самый оптимальный?
Разбирая задачу о купце и сукне (система линейных алгебраических уравнений), мы отметили, что разработчики Решателя вводят пользователей в заблуждение, уверяя его в том, что целевая функция имеет статус обязательного параметра. Число целевых функций может и должно быть не только меньше, но и больше единицы. А чтобы Решатель в них не запутался, целевым функциям нужно дать ранг: сначала оптимизируется первая, затем вторая, потом третья и т.д. Но трех, по-видимому, будет достаточно и не только потому, что три - красивое число [7].
Ранг, выстраивающий запланированные пользователем действия в некую очередь, используется в среде Excel, например, для сортировки записей в базе данных. Глубина сортировки ограничена тремя полями: сначала записи сортируются по полю, которому пользователь дал высший ранг, потом по полю со вторым рангом, если оно отмечено, а потом уже по третьему полю. Если пользователю трех полей сортировки мало (что встречается довольно редко), то можно перейти на ручной режим глубокой сортировки: сначала отсортировываются записи по полю с наименьшим рангом, потом по полю с предпоследним по значимости рангом и т.д. до поля с высшим рангом сортировки. Изъян такой технологии известен: если необходимо увеличить глубину сортировки еще на одно поле, то всю работу приходится начинать сначала.
Описанный способ поэтапной (поранговой) оптимизации плана выпуска компьютеров зафиксирован в таблице на рис. 10. Сначала ведется максимизация общей стоимости компьютеров (ячейки D25:D28), затем полученный результат используется в качестве опорной точки новой оптимизации (ячейки Е25:Е28) по новой целевой функции (число компьютеров) - по функции первого ранга. Так и был получен оптимальный план выпуска компьютеров (120 штук) с более высокой общей стоимостью (с более оптимальным использованием имеющихся ресурсов).
Кстати говоря, ранг важности было бы неплохо присваивать и ограничениям, накладываемым на задачу. Ведь в реальной жизни одни ограничения имеют статус табу, нарушить которое недопустимо ни при каких обстоятельствах, другие же можно рассматривать как пожелания. Решатель же пакета Excel имеет свои собственные представления о важности ограничений, и при анализе не полностью решенной задачи нередко оказывается так, что какое-нибудь пустяковое ограничение выполнено, а самое важное - нет. Ограничения несложно учесть и при реализации метода "Два шага" (см. программу выше) вводом штрафных санкций.
Задачу, сформулированную в таблице на рис.10, можно решить не ранговой оптимизации, а вводом единственной, но “хитрой“ целевой функции - произведения числа компьютеров на общую их стоимости. Но к этому способу решения как бы душа не лежит. Термин “целевая функция” идет от слова “цель”, а она (цель) должна иметь четкий физический смысл, чего нет в “хитром” произведении общей суммы на общую стоимость.
Понятие “физический смысл”, только что затронутое нами, наталкивает на идею совершенствования механизма электронных таблиц, связанную с вводом в ее ячейки не только значений величин, но и их размерностей (метры, килограммы, секунды и т.д.). Эта идея уже реализована в упоминавшихся пакетах MathCAD и Eureka, где можно дополнить правильность ввода формул проверкой соответствия размерностей: метры невозможно сложить с килограммами [3,4]. В среде Excel и в средах других электронных таблиц зачатки размерности есть, но они применяются только при выводе чисел в денежном формате, что объясняется финансовой (в первую очередь) направленностью расчетов. Таблицы на рис. 8, 9 и 10 содержат величины с размерностью (руб, арш, руб/арш, шт/сут, руб/шт и т.д.), но проверка в формулах ограничивается лишь тем, чтобы число не складывалось с текстом и не более того.
Кстати, общую стоимость компьютеров (см. ячейку D30 на рис.10) можно еще существенно увеличить, сняв с задачи ограничение D2>=0. Тогда появятся отрицательные значения числа компьютеров первого типа. Это можно рассматривать и как ошибку, и как еще одну “хитрую” подсказку пользователю: “Купи на стороне дешевые компьютеры первого типа, вытащи из них дефицитные микросхемы и пусти их на производство более дорогих ЭВМ. План по валу в рублях будет перевыполнен!”.
Собака после выстрела охотника и команды “Искать!” может вернуться с тремя результатами:
1. Собака приносит подстреленную дичь.
2. Собака, израсходовав отпущенный лимит времени или услышав призывной сигнал охотника, возвращается с пустыми зубами.
3. Охотник получает “трофей” - стрелянный пыж или рваный башмак, но собачьи глаза и хвост выдают собачью хитрость.
Есть особый род эстетов, получающих удовольствие от попыток облагораживания технического текста цитатами из художественной литературы. Но не этот смысл слова "эстет" подчеркнут в названии первой части статьи. Автору уже доводилось признаваться своим читателям [6], что для него в программной среде огромную роль играют не только чисто технические параметры (быстродействие, богатство встроенных функций и т.д.), но и "красота" продукта. Под красотой же следует понимать не только цветовую палитру, например, или изящество окон и иконок, но и то наслаждение, какое программа может доставлять при решении даже простейших задач. И не только своими новыми, ранее неведомыми возможностями, но и своими ... ошибками. Автор попытался было описать всю эту гамму чувств, но его опередил Карел Чапек своим рассказом “Игла”, где необыкновенно точно передан дух работы с таблицами - и не важно, бумажные они или электронные. Помимо этого, чапековский отрывок даст понять, причем тут охотник.
Когда я еще работал в бухгалтерии и составлял, бывало, полугодовой отчет, подчас случалось, что цифры не сходятся. Однажды в наличности не хватило трех геллеров. Конечно, я мог просто положить в кассу эти три геллера, но это была бы неправильная игра. С бухгалтерской точки зрения это было бы неспортивно. Надо найти, в каком счете допущена ошибка, а счетов у нас было четырнадцать тысяч. И скажу вам, когда я брался за баланс, мне всегда хотелось, чтобы там обнаружилась какая-нибудь ошибка. Тогда я, бывало, оставался на службе хоть на всю ночь. Положу перед собой кучу бухгалтерских книг и берусь за дело. И для меня колонки цифр становились не цифрами, они преображались просто необыкновенно. То мне казалось, что я карабкаюсь по этим колонкам вверх, словно на крутую скалу, то я спускаюсь по ним, как по лестнице, в глубокую шахту. Иногда я чувствовал себя охотником, который продирается сквозь чащу цифр, чтобы изловить пугливого и редкого зверя - эти самые три геллера. Я воображал себя охотником, который бродит по горам, по долам, среди росистых кустиков черники. И до того мне в такие минуты становилось хорошо от этого ощущения движения и силы, такое я чувствовал вокруг себя волнующее приволье, словно и в самом деле переживал необыкновенное приключение. Целыми ночами я мог охотиться за тремя геллерами, и когда находил их, то даже не думал, что это лишь жалкие гроши. Это была добыча, и я шел спать, торжествующий и счастливый...
Работа с Решателем пакета Excel во многом напоминает ружейную охоту. Пользователь формирует таблицу, пишет целевую функцию, вводит ограничения, выставляет параметры поиска (заряжает ружье, выслеживает дичь), а затем щелкает по кнопке “Выполнить” в окне "Поиск решения" (нажимает спусковой крючок ружья). Собака (Решатель) прыгает в воду и возвращается с тремя результатами:
1. Pешение найдено.
2. Решение не найдено.
3. Решатель пытается подсунуть пользователю что-то такое, что только издали напоминает правильный результат.
При этом ситуация осложняется тем, что пользователь пакета Excel в отличие от охотника, который знает, что такое дичь, часто не знает как выглядит правильный результат. У Решателя, к сожалению, нет глаз и хвоста, по которым можно определить, что принесено к ногам охотника - пользователя Excel.
Оптимизация в среде Решателя Excel - это хождение на охоту с чужой собакой, способности и повадки которой неизвестны. “Своя собака” - это методы и алгоритмы, написанные на языке Basic и вставленные в модуль рабочей книги, по которым решение ищется не вслепую, а наверняка. А если писать программы недосуг, то вот семь советов по работе с Решателем Excel:
1. Найдя решение, еще раз заставьте Решатель найти его уже от новой опорной точки.
2. Перед решением оптимизационной задачи с ограничениями прогоните ее без целевой функции для того, чтобы оценить область существования решений.
3. Начинайте поиск оптимального решения от одной из точек, найденной в п.2 и лежащей недалеко от оптимума.
4. Вводите ограничения постепенно: ввели первое - нашли решение, ввели второе - уточнили решение и т.д.
5. Начинайте решение оптимизационной задачи с целочисленными аргументами без ограничений на целочисленность. Нецелочисленный ответ всегда будет полезен, т.к. (1) от него можно будет начать поиск целочисленного решения, (2) можно удовлетвориться нецелочисленным решением (появление совместители, например, в задаче об оптимальном штатном расписании) и (3) числа можно округлить вручную, если целочисленное решение не находится.
6. Проверяйте правильность найденных минимумов и максимумов построением графиков и поверхностей, где эти особые точки видны. Благо пакет Excel имеет богатый набор деловой графики. Если аргументов у функции больше двух, то постройте графики сечений через точку оптимума по всем координатам и убедитесь, что там все частные производные равны нулю.
7. Имейте набор Basic-процедур и функциц для решения конкретных оптимизационных задач не лобовой атакой через использование Решателя, а более осмысленными способами. Три подобные программы приведены в статье.
1. Банди Б. Методы оптимизации. Вводный курс: Пер. с англ. М.: Радио и связь, 1988
2. Очков В., Пухначев Ю. 128 советов начинающему программисту. 2-е издание. М.: Энергоатомиздат, 1992
3. Очков В., Пухначев Ю. Уроки для пользователей IBM PC. М.: Финансы и статистика 1992
4. Очков В. Сказ про то, как MathCAD задачу решал. КомпьютерПресс, N 1, 1995
5. Очков В. Turbo Pascal 7.0. Взгляд со стороны. КомпьютерПресс, N 7, 1993
6. Очков А., Очков В. Visual Basic и формула Вирта. КомпьютерПресс, N 7-8, 1994
7. Очков В. Рахаев М. Этюды на языках QBasic, QuickBasic и Basic Compiler. М.: Финансы и статистика, 1995
Очков В.
Некоторые общие замечания по Excel 5.0 (сравнение с Excel 4.0)
Программа Microsoft Excel Setup стала более информационной, а процесс установки более управляемым. Кроме того, если установить программу Setup на жесткий диск, то затем можно добавлять или убирать любые компоненты Microsoft Excel без обращения к дистрибутиву на дискетах. При удалении всей программы или ее части не происходит “засорения” диска, поскольку программа Setup сама стирает все ненужные файлы, а при совместном использовании некоторых файлов (например, графических фильтров) другими программами, выдаст запрос на их удаление.
В отличие от предыдущих версий в Microsoft Excel 5.0 имеется лишь один тип файлов - рабочая книга, в которой могут содержаться рабочие листы, листы диаграмм и макросов, но при этом все листы подшиты в рабочую книгу. Такой подход позволяет не только упростить работу с несколькими документами за счет быстрого доступа к каждому листу через ярлычки в нижней части листа, но и позволяет работать с листами, объединенными в группу. При использовании группы действия производимые на одном из листов группы (ввод данных, форматирование ячеек и пр.) автоматически повторяются на всех листах группы, что упрощает оформление нескольких однотипных по структуре листов. Кроме того, новый тип ссылок (объемные ссылки) позволяет создавать сводные документы на основе данных из нескольких листов без ввода громоздких формул с внешними ссылками.
Большую помощь при анализе данных окажет Мастер сводных таблиц, с помощью которого можно легко выбрать нужные данные из документа и представить их сводной таблицей в удобной для работы форме. Сводную таблицу можно редактировать, изменяя ее структуру и внешний вид, добавлять итоговые строки, группировать и сортировать данные.
Весьма полезным при серьезной работе с Microsoft Excel является пришедшая из Microsoft Word возможность сохранять к рабочей книге сводную информацию о теме, авторе, ключевых словах. Эту информацию можно использовать при поиске файла на диске, или выяснения его назначения.
Новые возможности программы по форматированию текста позволяют существенно улучшить внешний вид документа, что избавляет от необходимости использования текстового редактора. Теперь в одной ячейке можно использовать шрифты разных начертаний и размеров, а также применить надстрочный и подстрочный шрифт.
Существенным доработкам и усовершенствованиям подвергся и пользовательский интерфейс. К полосе быстрого доступа (ToolBar) добавлены новые кнопки для форматирования текста, установки рамок ячеек, изменения масштаба изображения на экране. Весьма упростилась процедура форматирования ячеек: все действия по изменению их внешнего вида доступны по одной команде. Окошко ссылок из чисто информационного превратилось в очень полезный элемент управления, позволяющий определить имя для ячейки или интервала и быстро перейти к нужной части таблицы. Кроме того, можно очень легко и гибко управлять внешним видом Microsoft Excel и многочисленными сервисными функциями, разрешая или запрещая их использование и изменяя их параметры.
Расширенные возможности защиты документов с использованием многоуровневых паролей позволяют разграничить доступ к частям одной рабочей книги, установив для них различные пароли.
Перечисленные изменения можно рассматривать как “поступательное” развитие программы от версии к версии - принципиальных отличий от версии 4.0 в приведенной выше информации не имеется. Дальнейшее изложение посвящается именно принципиальному, можно сказать “стратегическому” изменению в Microsoft Excel.
Microsoft Excel является универсальной программой в том смысле, что ее пользователями могут быть люди, для которых общим является только необходимость выполнения расчетов, сами же расчеты могут относиться к совершенно различным областям человеческой деятельности. Естественно, что задачи, решаемые, например, студентом технического вуза и менеджером строительной фирмы совершенно различны. Возникает необходимость в рамках одной программы удовлетворить как можно больше потенциальных пользователей.
Можно в каждой новой версии программы встраивать инструменты для новых групп пользователей. Недостатки этого подхода очевидны.
Второй подход заключается в разработке некоего базового набора инструментов, используя которые можно решить любую задачу конкретного пользователя, дополнив их средствами, позволяющими упростить использование и расширение этого базового набора.
В системе Microsoft Excel используется второй способ. Пользователю предоставляется набор базовых средств - функций таблицы и команд управления программой. Пользователь может использовать функции таблицы непосредственно при записи формул в ячейках или при создании своих собственных функций (функций пользователя), расширяющих базовый набор. Используя команды управления, можно управлять программой или создавать свои собственные команды.
Во всем вышеизложенном нет ничего принципиально нового - во всех версиях Microsoft Excel существовали средства создания команд и функций пользователя. Для этого использовались макросы (далее - ML). Принципиальным отличием версии 5.0 является изменение средства разработки макросов - в Microsoft Excel встроена специальная версия языка программирования Visual Basic. Полное название нового средства программирования -
Microsoft Visual Basic Programming System, Application Edition, сокращенно VBA.Для пользователей предыдущих версий Microsoft Excel нет оснований беспокоиться по этому поводу. Microsoft Excel 5.0 сохраняет полную совместимость с ранними версиями, правильно работает с любыми документами, созданными, например, в версии 2.0. Все макросы, созданные ранее, будут работать, т.к. макроязык Excel 4.0 полностью поддерживается. Кроме того, он расширен для использования всех новых возможностей версии 5.0. Но хорошим советом всем пользователям Excel будет совет перейти на новый язык программирования. Во первых VBA намного лучше (почему - чуть дальше). А во вторых это связанно с тем, что макроязык документируется только в справочной системе Excel, и кроме того в документации содержится сообщение о том, что в макроязык следующих версий не будут вносится изменения для поддержки новых возможностей системы. В документации содержится ряд советов по переходу на VBA для пользователей макроязыка.
Подобные кардинальные перемены в серьезной коммерческой программе должны иметь серьезные причины - необходимость переобучения пользователей весьма сложная проблема.
Некоторыми из причин являются следующие.
Во-первых. Теперь на листах диаграмм и рабочих листах пользователя может размещать кроме кнопок и другие элементы управления Windows - списки, полосы прокрутки и различные переключатели, а на специальных листах диалога создавать модальные диалоговые окна, содержащие помимо прочих элементов управления строки ввода.
Кроме того, существует возможность полностью изменять существующие и создавать новые меню и панели инструментов, а так же расширять справочную систему. Подобные возможности программы позволяют создавать рабочие книги, предстающие перед пользователем не в виде простого набора таблиц и диаграмм, а по сути своей являющимися законченными интерактивными приложениями.
Макроязык не соответствует требованиям, предъявляемым в настоящее время к средствам разработки приложений - в первую очередь это касается надежности и скорости разработки программ.
Во-вторых, Excel 5.0 не является первой программой фирмы Microsoft, использующей вариацию языка Basic в качестве встроенного языка. Например, в Microsoft Word используется Word Basic. Пользователи вправе получить помимо однотипного интерфейса и однотипный язык программирования для различных программ. Кроме того, в Windows 95 будет встроен язык программирования Visual Basic. Возможно, что VBA является языком, который фирма Microsoft реализует во всех своих программах, нуждающихся в средствах программирования, и кроме того предоставит возможность использовать его в своих программах другим разработчикам.
А теперь о самой первой причине. Чем VBA лучше макроязыка?
Язык Visual Basic, лежащий в основе VBA, является полноценным языком программирования, в отличии от макроязыка. Для “полноценности” макроязыку не хватало многого, например: возможности создания типизированных переменных с областью видимости, процедур и функций, имеющих типизированные параметры вызова, а также констант, индексируемых многомерных динамических массивов, возможности введения пользовательских типов. В VBA это все есть. Набор управляющих конструкций макроязыка, по сравнению с Visual Basic, выглядит довольно бедно.
Реализации языка Basic фирмы Microsoft известны своими мощными средствами отладки. VBA не исключение. В коде можно ставить точки останова - условные и безусловные, допустимо пошаговое выполнение программ, просмотр и измение значения переменных. О подобных средствах при работе с макроязыком можно было только мечтать.
Следует отметить, что макросы Visual Basic пишутся не в ячейках таблицы макросов, а на специальных листах рабочей книги - в модулях. Лист-модуль представляет собой текстовый редактор, а макрос, написанный на VBA является просто текстом, как это и принято в большинстве языков программирования. При вводе строки программы, содержащей синтаксическую, ошибку выдается предупреждение, во всех правильно введенных строках автоматически производится цветовое выделение языковых конструкций и перевод отдельных символов в верхний или нижний регистры.
При использовании макроязыка существовал ряд задач, относимых пользователями к разряду весьма сложных - например создание диалоговых окон или обращение к динамическим библиотекам Windows. Теперь диалоги можно создавать прямо в рабочей книге, а работать с ними весьма просто. К функциям, находящихся в динамических библиотеках, можно обращаться так же как и к любым процедурам и функциям, написанным на VBA. Необходимо только соответствующим образом описать нужную функцию.
Средства обработки ошибок периода исполнения (например деление на нуль или несоответствие типа данных), представленные в VBA, позволят существенно повысить надежность создаваемых программ и упростить работу конечных пользователей с ними. Программист может указать, в какое место следует передать управление при возникновении ошибки. При возникновении ошибки программа выяснит причины ошибки, постарается устранить их и выполнить снова команду, вызвавшую ошибку, или прервать свою работу. Подобных средств обработки ошибок в макроязыке нет.
Но принципиально VBA от макроязыка отличает то, что Visual Basic является объектно-ориентированным языком программирования. Идеология ООП, реализованная в VBA, отличается от идеологии таких языков программирования, как C++ или Pascal. В Visual Basic нет возможности создания новых типов объектов или порождения потомков от существующих. Но пользователь получает большой набор готовых объектов. Например для VBA это элементы Excel - рабочие книги, листы, ячейки, диаграммы и т.д. Используя свойства (Properties) и методы (Methods) объектов, программист управляет системой Microsoft Excel и ее элементами. В макроязыке это делалось совсем по другому - при помощи командно-эквивалентных функций. Применение объектов позволит сделать программу более понятной, а ее разработку более простой.
В заключении статьи хотелось бы сравнить VBA с системой программирования Visual Basic 3.0 (далее VB). Подобное сравнение вполне логично хотя бы в связи со сходностью названий.
VBA является встроенным языком программирования и решаемые при помощи его задачи органично связаны с управлением конкретной программой. В свою очередь VB является системой программирования предназначенной для создания универсальных приложений. Совместимости между VBA и VB на уровне законченных программ не существует, т.е. программа, созданная на VB не может выполнятся под управлением VBA, и на оборот. Но VB и VBA используют общий язык программирования Visual Basic и в связи с этим перенос конкретных процедур и функций из VB в VBA вполне возможен.
В VBA отсутствует ряд объектов, существующих в VB, например, формы. Отсутствие в VBA форм объясняется тем, что элементы управления размещаются на рабочих листах или листах диалога рабочей книги, и в связи с этим способ работы с элементами управления отличается от принятого в VB - в VBA элементы управления располагаются в коллекциях
.С другой стороны VBA предоставляет программисту большое число объектов, предназначенный для управления Excel - рабочие книги, листы, ячейки и т.д. Подобные объекты, естественно, отсутствуют в VB.
Кроме того, VBA вносит ряд нововведений в язык программирования Visual Basic. Нововведений весьма полезных, и очевидно все они появятся в VB 4.0. Рассмотрим некоторые из них более подробно.
Раньше в языках программирования Visual Basic и Microsoft Basic не было возможности продлить ввод строки программы в следующей строке исходного текста, что порождало ряд проблем, в частности, с программой было не очень удобно работать и, кроме того, на язык накладывлись ограничения по максимальной длине строки. В VBA продлить строку можно, поставив в ее конце символ подчеркивания. Например:
Sub Exampl1()
Dim A
A = “В VBA можно продолжить ввод строки программы, “ + _
“поставив в конце строки символ ‘_’.”
MsgBox A
End Sub
Не совсем понятен выбор символа ‘_’ в качестве признака продления строки. Больше подошел бы просто знак переноса ‘-‘, как это сделано в Мicrosoft Fortran.
Следующие новшества будут очень полезны при работе с объектами. Теперь для выполнения одного и того и того же действия со всеми элементами коллекции или массива существует оператор цикла For Each (см, часть 1). Например, следующий код производит изменение имен всех рабочих листов в рабочей книге:
Sub Exampl2()
Dim A As Worksheet
For Each A In Worksheets
A.Name = “My_” + A.Name
Next A
End Sub
В случаях, когда необходимо изменить сразу несколько свойств объекта, поможет новый оператор With, пришедший в Visual Basic из языка Pascal, и выполняющий аналогичные действия. Например:
Type MyType
Name As String
Number As Integer
End Type
Dim A(1 To 10) As MyType
Sub Exampl3()
Dim i
For i = 1 To 10
With A(i)
.Name = “Examl_Name”
.Number = 0
End With
Next i
End Sub
Ряд методов, предназначенных в основном для создания новых объектов, имеет большое число передаваемых им аргументов. Не во всех случаях существует необходимость задавать значения всех аргументов и возможность держать в голове порядок их передачи методу. Решению этой проблемы помогут поименованные аргументы - при вызове метода можно установить только необходимые параметры, явно указав их имя, остальные аргументы получат значение по умолчанию. Например:
Sub Exampl4()
dialogSheets(1).ListBoxes(4).AddItem Text:=”New Item”
End Sub
Следует отметить, что не все аргументы методов можно использовать как поименованные. Ряд аргументов не имеет значения по умолчанию, и наличие поименованных аргументов указывается в документации для каждого метода.
Теперь программист, использующий Visual Basic, может создавать процедуры и функции с необязательными формальными параметрами и c произвольным списком формальных параметров при помощи операторов Optional и ParamAray. Например:
Sub OptExampl(A; Optional B)
If IsMissing(B) Then
B = “[отсутствует]”
End If
MsgBox “A=” & A & “ B=” & B
End Sub
Sub Exapl5()
Call OptExampl(1)
Call OptExampl(1; 2)
End Sub
Sub ParamExapl(A; ParamArray B())
Dim P, S
S = S & A
For Each P In B
S = S & “, “ & P
Next
MsgBox S
End Sub
Sub Exapl6()
Call ParamExapl(1)
Call ParamExapl(1; 23)
Call ParamExapl(1; 234; “Hello”)
End Sub
Типом B в обоих примерах должен быть Variant.
Раньше при работе с пользовательскими типами не было возможности создавать поля типа динамический массив, что иногда создавало ряд серьезных проблем. Теперь эта возможность доступна программисту. Например:
Type MyType
B() As Variant
End Type
Sub Exapl7()
Dim A As MyType
ReDim A.B(1)
MsgBox Str(UBound(A.B))
ReDim Preserve A.B(50)
MsgBox Str(UBound(A.B))
End Sub