Всем привет, меня зовут Андрей, это снова я!
Рассмотрим пример: решение квадратных уравнений.
Известно, что один из основных способов решения квадратных уравнений связан с нахождением дискриминанта:
Здесь D - это сам дискриминант, а буквы a, b и c - это коэффициенты в квадратном уравнении.
Например, если квадратное уравнение имеет вид:
- то это значит, что a=7, b=-5, c=4.
А дискриминант в этом случае будет равен: (-5)^2 - 4 * 7 * 4 = 25-112=-87.
Да, дискриминант отрицательный, поэтому само квадратное уравнение не имеет решений. Но эта статья не об этом, а о том, как можно имена, содержащиеся в эксель, использовать в макросах.
А начнем с того, что присвоим эти самые имена нескольким ячейкам эксель и вначале их будем использовать в самих ячейках эксель.
Можно взять четыре клетки Эксель (любые), и присвоить им имена:
a_
b_
c_
d_
Например:
Увеличим тот фрагмент, где есть строка формул и формула ячейки D2.
Итак, дискриминант, то есть ячейка D2, автоматически вычисляется по формуле и зависит от значений тех ячеек, где содержатся имена a_, b_, c_.
Напомню, что при создании зависимостей в эксель существуют такие понятия, как "влияющие ячейки" и "зависимые ячейки". Влияющая ячейка — это такая ячейка, которая ссылается на формулу в другой ячейке. Например, если в ячейке А10 находится формула = B10+C10, то ячейки B10 и С10 являются влияющими на ячейку А10. Зависимая ячейка — это ячейка, которая содержит формулу. Например, если в ячейке А10 находится формула =B10+C10, то ячейка А10 является зависимой от ячеек B10 и C10.
А применительно к нашему примеру, можно сказать так: ячейка D2 является зависимой (D2 - это та ячейка, в которой осуществляется расчет формулы дискриминанта и находится нужная нам формула
[=(b_)^2-4*a_*c_]). А влияющие - это те ячейки, которым присвоены имена:
a_
b_
c_
Если влияющей ячейке присвоено имя, тогда вместо адреса этой ячейки можно смело называть то имя, которое ей присвоено. То же самое можно сказать и о зависимой ячейке, но имена влияющих ячеек имеют более важное значение (если зависимая ячейка содержит адреса влияющих ячеек, то внутри формулы можно заменить адреса влияющих ячеек на их имена).
Если с формулами и именами внутри ячеек эксель все достаточно просто, то с именами в макросах дело обстоит чуть-чуть сложнее.
Допустим, что нужно использовать переменные не в ячейках эксель, а внутри макросов.
Есть несколько вариантов получения формулы дискриминанта в макросе.
Рассмотрим снова тот же пример, который связан с расчетом дискриминанта. Внутри макросов есть несколько способов расчета, рассмотрим несколько основных вариантов.
Первый вариант - это создание переменных внутри самого макроса, которые и не будут совсем зависеть от всего того, что находится на листе эксель. Вот один из вариантов такого макроса:
Sub Дискр_1()
a = 12
b = -8
c = -9
d = b ^ 2 - 4 * a * c
MsgBox (d)
End Sub
- Здесь коэффициенты a, b и c уже не те, что были в предыдущем примере (хотя при желании все эти коэффициенты можно так заменить, чтобы он были равны коэффициентам предыдущего примера).
- Здесь рассчитывается дискриминант для уравнения:
12x^2 - 8x - 9 = 0. Кстати, в данном случае дискриминант будет равен не -87, а 496:
Вычисление дискриминанта - это просто пример. Он приведет потому, что в реальной жизни могут встретиться любые задачи, в том числе и такие, когда для расчета какого-то значения в макросе приходится брать любые числа, а ведь часто приходится брать и такие числа, которых может и не быть на листе эксель.
А теперь разберем и другие варианты. То есть такие, когда макросу для вычислений нужно брать те числа, что находятся на листе эксель. Для решения этой задачи есть несколько вариантов.
Один вариант заключается в том, что можно использовать адреса тех ячеек, в которых находятся исходные данные для формулы (будем использовать адреса зависимых ячеек, независимо от того, присвоены ли этим ячейкам какие-то имена).
Вот текст этого макроса:
Sub Дискр_2()
a = Cells(2, 1)
b = Cells(2, 2)
c = Cells(2, 3)
d = b ^ 2 - 4 * a * c
MsgBox (d)
End Sub
Результатом работы этого макроса уже будет число -87, но это и не удивительно, мы же используем те числа, для которых мы уже заранее вычислили дискриминант.
Но часто бывает так, что один файл эксель содержит несколько листов, при этом зависимые ячейки иногда могут находиться на разных листах эксель. Чтобы не думать о том, на каких листах эксель и по каким именно адресам находятся нужные нам ячейки, лучше присвоить нужным ячейкам адреса (это, кстати, мы уже сделали), а потом использовать эти адреса в макросе.
Как это сделать грамотно и правильно? Нужно сделать так, чтобы макрос сам нашел среди всех листов эксель нужные нам имена, и преобразовать их, присвоив им другие имена, которые можно будет использовать внутри нашего макроса.
Вот каким будет макрос, вычисляющий дискриминант указанным способом:
Sub Дискр_3()
a = ThisWorkbook.Names("a_").RefersToRange.Value
b = ThisWorkbook.Names("b_").RefersToRange.Value
c = ThisWorkbook.Names("c_").RefersToRange.Value
d = b ^ 2 - 4 * a * c
MsgBox (d)
End Sub
В результате выполнения этого макроса мы тоже получим -87.
Но если мы исправим значения в зависимых ячейках, а потом снова запустим макрос, то результат уже будет другим (если, конечно, при новых коэффициентах дискриминант и правда станет другим).
Имена переменных, которые мы присвоили внутри макроса, могут как полностью совпадать с теми именами, что были ранее присвоены в книге эксель (в диспетчере имен), так и отличаться от них.
Даже если бы макрос был таким:
Sub Дискр_3()
a_ = ThisWorkbook.Names("a_").RefersToRange.Value
b_ = ThisWorkbook.Names("b_").RefersToRange.Value
c_ = ThisWorkbook.Names("c_").RefersToRange.Value
d_ = b_ ^ 2 - 4 * a_ * c_
MsgBox (d_)
End Sub
мы бы получили тот же самый ответ, потому что тут нет никакого конфликта имен. Даже если имя переменной в макросе совпадает с именем переменной в книге эксель (в диспетчере задач). Это разные имена. Фраза RefersToRange внутри этого макроса позволяет установить зависимость между нужными нам именами.
Ну и, конечно же, можно привести и еще один способ отображения нужного нам значения какой-то переменной. Если мы точно знаем, что имя присвоено влияющей ячейке и точно знаем, какое именно имя ей присвоено, (речь идет именно о той ячейке, которая содержит то самое число, которое нас интересует, а в нашем случае это та ячейка, в которой содержится расчет дискриминанта), тогда можно не производить вычисления в макросе, а просто показать значение нужной нам ячейки.
Применительно к нашему случаю, мы получим вот такой короткий макрос:
Sub Дискр_4()
d_ = ThisWorkbook.Names("d_").RefersToRange.Value
MsgBox (d_)
End Sub
- А еще лучше сделать так: добавить строку, которая будет объявлять новую переменную и указать ее тип данных, например:
Sub Дискр_4()
Dim d_ As Double
d_ = ThisWorkbook.Names("d_").RefersToRange.Value
MsgBox (d_)
End Sub
Итак, вначале добавляем переменную d_ и указываем для нее тип данных (Double). Это означает: значения с двойной точностью, разрядность 64 бита.
Мы специально показали несколько возможных вариантов решения одной и той же проблемы, потому что в реальной жизни может пригодиться тот или иной вариант.
Например, в одной какой-то задаче будут присвоено имя влияющей ячейке, в другой задаче - зависимым ячейкам, и так далее.
Конечно же, вычисление дискриминанта - это всего лишь пример, но использование имен книги в макросах будет производиться аналогичным образом.
