Использование относительных ссылок
Как известно, в Excel ограничена поддержка «трехмерных рабочих книг». Например, если при написании формулы необходимо сослаться на другой рабочий лист в книге, то в формулу нужно включить имя соответствующего рабочего листа. Однако при попытке копирования этой формулы с одного листа на другой ссылка на лист не изменяется, как это происходит в реальной трехмерной рабочей книге. Для решения этой проблемы можно применить пользовательскую функцию dhSheetOf f set, код которой выглядит следующим образом (листинг 2.71).
Листинг 2.71. Функция dhSheetOffset
Function dhSheetOffset(offset As Integer, cell As Range) As
Variant
' Возврат корректного значения ячейки cell листа, смещение _
которого относительно текущего задано переменной offset
dhSheetOffset = Sheets(Application.Caller.Parent.Index _
+ offset).Range(cell.Address)
End Function
Данная функция имеет два аргумента. Первый аргумент – это ссылка на лист; он может быть положительным, нулевым или отрицательным (например, для ссылки на предыдущий лист нужно указать -1). Второй аргумент – это ссылка на конкретную ячейку. Для использования функции можно применять формулу:
=dhSheetOffset(-1;A9)
В данном случае в активной ячейке будет получено значение ячейки А9, расположенной на предыдущем рабочем листе (то есть если текущий лист – Лист2, то будет получено значение ячейки А9 листа Лист1).
При использовании данной функции необходимо учитывать следующее: если рабочий лист содержит листы диаграмм, то при ссылке на ячейку в листе диаграммы будет получено сообщение об ошибке.
При необходимости можно усовершенствовать данную функцию. Ниже приведен код функции dhSheetOf f set2, игнорирующей все листы рабочей книги, которые не являются рабочими (листинг 2.72).
Листинг 2.72. Функция dhSheetOffset2
Function dhSheetOffset2(offset As Integer, cell As Range) As
Variant
' Корректировка смещения (чтобы ссылка была на рабочий лист)
Do While TypeName(Sheets(cell.Parent.Index + offset)) _
<> «Worksheet»
If offset > 0 Then
' Пропускаем лист и проходим вперед по книге
offset = offset + 1
Else
' Пропускаем лист и проходим назад по книге
offset = offset – 1
End If
Loop
' Возврат корректного значения ячейки cell листа, смещение _
которого относительно текущего задано переменной offset _
с пропуском листов с диаграммами
dhSheetOffset2 = Sheets(cell.Parent.Index _
+ offset).Range(cell.Address)
End Function
У данной функции аргументы и порядок использования такие же, как и у рассмотренной выше функции dhSheetOffset.