Задача 4

Найти корень уравнения F(x) =0, где функция F(x) задается программистом и реализована на VBA.

  Можно ли сочетать программирование без программирования с "настоящим" программированием. Ответ — безусловно "да". Данный пример позволяет продемонстрировать эту возможность. Я покажу, что функцию, корень которой разыскивается, и ее производную можно написать на VBA. При вызове этих функций в формулах рабочего листа в качестве параметров им можно передавать ячейки таблицы, записанные как в абсолютных, так и в относительных адресах. Копирование таких формул производит прежний эффект. Решение задачи 4 обобщает решение задачи 3, распространяя его на те ситуации, когда функция, корень которой разыскивается, не может быть задана простой формулой, и ее нельзя описать с помощью формул рабочего листа. Такая ситуация достаточно типична, например, когда требуются циклические вычисления для получения значения функции. Все эти трудности снимаются, благодаря тому, что Excel позволяет вызывать в формулах рабочего листа функции, написанные на VBA . В качестве примера функции F(x) я буду использовать ту же функцию, что и в задаче 3, - полином 4-й степени. Это позволит нам сравнить два решения и убедиться в том, что решение, полученное с использованием программирования, выглядит даже более элегантно, я бы сказал. С другой стороны, заметьте, сохраняются все преимущества, которые дает машина вычислений Excel, не требующая программирования. Обратите внимание, пример демонстрирует передачу данных из рабочего листа в функцию, написанную на VBA и обратно.    

Для решения задачи 4 я написал на VBA две функции. Вот как они выглядят:

Public Function Polinom4(Cofs As Variant, X As Variant) As Variant
  'Вычисляет значение полинома 4-й степени в точке,
  'заданной параметром X.
  'Коэффициенты полинома передаются в первом параметре Cofs.
  'Значением параметра Cofs может быть объект Range.
  Polinom4 = (((Cofs.Cells(1) * X + Cofs.Cells(2)) * X + _
    Cofs.Cells(3)) * X + Cofs.Cells(4)) * X + Cofs.Cells(5)
End Function

Public Function Pr4(Cofs As Variant, X As Variant) As Variant
  'Вычисляет значение производной полинома 4-й степени в точке,
  'заданной параметром X.
  'Коэффициенты полинома передаются в первом параметре Cofs.
  'Значением параметра Cofs может быть объект Range.
  Pr4 = ((4 * Cofs.Cells(1) * X + 3 * Cofs.Cells(2)) * X + _
     2 * Cofs.Cells(3)) * X + Cofs.Cells(4)
End Function

Затем я практически повторил действия, предпринятые для решения задачи 3. Реально, конечно, я ничего не повторял, а просто создал копию листа 4 и внес в нее необходимые изменения. Эти изменения коснулись тех ячеек, в которых вычисляется значение функции и ее производной.  В ячейке B12 я заменил формулу, вычисляющую функцию, на вызов функции Polinom4:

“=Polinom4($C$9 : $G$9; A12)”

Аналогично в ячейке B26 появилась формула:

“=Polinom4($C$9 : $G$9; A26)”

а в ячейке C26:

            “=Pr4($C$9 : $G$9; A12)”

Обратите внимание, я передаю вызываемым функциям объект Range в качестве первого параметра, в котором записаны значения коэффициентов полинома. В качестве второго параметра также передается объект Range, но определяющий уже единственную ячейку. Заметьте, при передаче первого параметра я использовал абсолютные имена, поскольку коэффициенты полинома находятся в строго фиксированных ячейках и их адреса не должны меняться при копировании формулы. Для второго параметра использован относительный адрес, поэтому он должным образом будет меняться при копировании формул.

Наш пример интересен и тем, что в нем показано, как передается массив ячеек и отдельная ячейка  рабочего листа в функцию VBA. С другой стороны, результат вычисления функции передается в формулу рабочего листа. Заметьте, что хотя формальный параметр функций имеет тип Variant, ему можно  в качестве фактического параметра передавать объекты Range. В теле функции можно работать с этим параметром, как с объектом Range, вызывая его свойства и методы, как это делается в наших функциях, где вызывается свойство Cells этого объекта. Я еще вернусь в последующем к теме передачи информации между рабочим листом и процедурами и функциями VBA и остановлюсь на этом более подробно. В заключение, взгляните, как выглядит решение этой задачи. Поскольку ищутся корни той же функции, что и в задаче 3, то, чтобы избежать повторения рисунков, я привожу решение, в котором найден другой корень нашей функции:    

Рис. 6 Вычисление корня функции, заданной программой на VBA