Как узнать адрес ячейки excel vba
VBA Excell. Как узнать внутри функции адрес ячейки из которой функция вызвана?
Подробнее. Аргументы функции находятся в соседнем столбце начинась за 10 строк до "строки вызова функции" и до "строки вызова функции" необходимо произвести с ними некоторые "сложные" действия. Необходимо знать "строку вызова функции".
Originally posted by Guest
VBA Excell. Как узнать внутри функции адрес ячейки из которой функция вызвана?
Подробнее. Аргументы функции находятся в соседнем столбце начинась за 10 строк до "строки вызова функции" и до "строки вызова функции" необходимо произвести с ними некоторые "сложные" действия. Необходимо знать "строку вызова функции".
спустя 3 года :D :
Function addr(r As Variant)
addr = r.DirectDependents.Address(ReferenceStyle:=xlR1C1)
End Function
r - любой аргумент из этой же строки
Ну раз пошла такая пьянка, то и я внесу свою лепту :
Примечание : Свойство ThisCell появилось только в MS Excel XP, так что рекомендую обратить внимание на второй вариант, тем более, что именно его я иногда использовал.
Originally posted by pashulka
Ну раз пошла такая пьянка, то и я внесу свою лепту :
Примечание : Свойство ThisCell появилось только в MS Excel XP, так что рекомендую обратить внимание на второй вариант, тем более, что именно его я иногда использовал.
pashulka, а второй вариант - это какой? Тот, который привёл sergeyvg, или тот, который привёл Uncle_AL?
еще вопрсик, а у самого EXCELL есть функция (или переменная) чтобы узнать имя книги, аналог ThisWorkbook.Name в VBA, для передачи в качестве параметра в функцию ?
Стандартной функции рабочего листа, которая бы возвращала имя рабочей книги нет . Однако, использовав стандартную функцию рабочего листа =ЯЧЕЙКА() можно написать такую формулу :
Примечание :
- В вышеопубликованной формуле можно вместо ПОИСК() использовать НАЙТИ()
- Если использовать именованную формулу или дополнительную ячейку, то формулу можно серьёзно сократить.
- Только, что созданная и несохранённая рабочая книга не имеет полного имени и следовательно формула не будет возвращать корректный результат.
Спасибо, значит таки CELL, лениво хелп прочитал,
в английском =MID(CELL("filename");SEARCH("[";CELL("filename"))+1;SEARCH("]";CELL("filename"))-SEARCH("[";CELL("filename"))-1) - работает
вот и именнованная =FILENAME нарисовалась :)
Что касается именованной формулы, то я намекал на нижеприведённый вариант, где ИмяП - это именованная формула : =ЯЧЕЙКА("ИмяФайла")
Комментарий : Довольно часто в различных формулах используется одна и таже составляющая. Именно в таких случаях предложенный мною вариант будет наиболее оптимальным.
В Вашем конкретном случае, в роли именованной формулы, конечно может выступать вся формула целиком, но лично мне нравится именно мой вариант, так как используя именно его, можно полученить и другие составляющие, например имя рабочего листа (правда для этого именованную формулу лучше всего немного изменить и добавить ссылку на ячейку, так как это сделано в самом первом примере)
Предлагаю вернуться к вопросу об именах и создать именованную формулу, например ФайлИмя, использовав любой из трёх нижеопубликованных вариантов :
=ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(16)
=ПОЛУЧИТЬ.ДОКУМЕНТ(88)
=ПОЛУЧИТЬ.ЯЧЕЙКУ(66)
Затем в любой ячейке рабочего листа ввести =ФайлИмя и забыть о решении этого вопроса с использованием VBA …
вид формулы действительно зависит от конкретной задачи, мне нужно было имя файла, поэтому ссылка лишняя, если необходимо узнать имя листа, то это другое дело, но на именованную ячейку ссылаться не хочу, поскольку она может быть, а может и нет, надежнее R1C1, эта всегда будет :) ;
вероятнее всего понадобится ссылка на лист с базой данных, но у меня на таких листах именованные ячейки не водятся, это роскошь для листов с исходными и расчетных листов, кстати и на последних именованные диапазоны давно вымерли;
а вообще приятно встретить людей серьезно понимающих EXCELL :)
Originally posted by pashulka
.
=ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(16)
=ПОЛУЧИТЬ.ДОКУМЕНТ(88)
=ПОЛУЧИТЬ.ЯЧЕЙКУ(66)
можно ли привести английские аналоги ?
Uncle_AL, Дело в том, что когда я предлагаю то или иное решение, то ориентируюсь также и на людей, которые могут читать этот топик в дальнейшем, поэтому и предлагаю использовать всевозможные подварианты, нюансы и т.п. Естественно, что право окончательного и безусловного выбора всегда остаётся за Вами и другими читателями топика.
=GET.WORKBOOK(16)
=GET.DOCUMENT(88)
=GET.CELL(66)
можно ли пояснить,
в Вашей версии имеются такие функции ?
у себя (2003) не нахожу
Да у меня в MS Excel 97, 2000 они есть, впрочем они должны быть и у Вас, но вызвать их можно только используя макролисты или имена.
Впрочем, возможно что последней возможности в MS Excel XP, 2003 уже и нет .
На всякий случай, посмотрите небольшой пример :
нет, не работает, это все какого-то одноразового действия, 1-й раз отрабатывает, потом, если поменять имя файла, значение имени уже не меняется;
вероятно EXCELL записывет значения таких имен на момент создания или модификации (если зайти в окно определения имен и активировать путь) и больше не меняет (считаются константой);
самое интересное, что про GET в хелпе нет ни слова, а хорошая функция, если бы еще на обычном листе работала.
В ячейке отображается результат, который возвращает именованная формула на момент ввода. Если Вы хотите изменить результат, то можно выделить ячейку, которая содержит именованную формулу и нажать клавиши F2 и ENTER.
Возможно в Вашем случае, гораздо проще остановить свой выбор на первом варианте, с использованием стандартной функции рабочего листа =ЯЧЕЙКА()/CELL()
Uncle_Al
Попробуйте, вместо =GET.CELL(66) использовать =GET.CELL(66;$A$1) . это решит проблему переименования рабочей книги, прада только до тех пор, пока Вы не удалите рабочий лист, ссылка на который будет наличествовать в именованной формуле.
Originally posted by pashulka
Возможно в Вашем случае, гораздо проще остановить свой выбор на первом варианте, с использованием стандартной функции рабочего листа =ЯЧЕЙКА()/CELL()
наверное так,
файлы бывают достаточно большие, надежность в этом случае играет важную роль.
странно, но =GET.CELL(66;$A$1) перестает динамически работать, если переименовать лист, не помогает ни ввод нового имени, ни обновление (изменение) адреса
Действительно странно, потому что при переименовании рабочего листа ссылка на него в именованной формуле меняется автоматически и в этом нет ничего странного, так как после ввода =GET.CELL(66;$A$1) формула принимает вид =GET.CELL(66;Sheet1!$A$1), где Sheet1 - это имя активного листа
P.S. Только для обладателей руссифицированной версии MS Excel вместо =GET.CELL() нужно использовать =ПОЛУЧИТЬ.ЯЧЕЙКУ()
Returns a String value that represents the range reference in the language of the macro.
Syntax
expression.Address (RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo)
expression A variable that represents a Range object.
Parameters
Name | Required/Optional | Data type | Description |
---|---|---|---|
RowAbsolute | Optional | Variant | True to return the row part of the reference as an absolute reference. The default value is True. |
ColumnAbsolute | Optional | Variant | True to return the column part of the reference as an absolute reference. The default value is True. |
ReferenceStyle | Optional | XlReferenceStyle | The reference style. The default value is xlA1. |
External | Optional | Variant | True to return an external reference. False to return a local reference. The default value is False. |
RelativeTo | Optional | Variant | If RowAbsolute and ColumnAbsolute are False, and ReferenceStyle is xlR1C1, you must include a starting point for the relative reference. This argument is a Range object that defines the starting point. |
Remarks
If the reference contains more than one cell, RowAbsolute and ColumnAbsolute apply to all rows and columns.
Example
The following example displays four different representations of the same cell address on Sheet1. The comments in the example are the addresses that will be displayed in the message boxes.
Support and feedback
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.
Возвращает строковое значение, представляющее ссылку на диапазон на языке макроса.
Синтаксис
выражение.Адрес (RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo)
выражение: переменная, представляющая объект Range.
Параметры
Имя | Обязательный или необязательный | Тип данных | Описание |
---|---|---|---|
RowAbsolute | Необязательный | Variant | Значение True, чтобы возвратить часть строки ссылки в качестве абсолютной ссылки. Значение по умолчанию — True. |
ColumnAbsolute | Необязательный | Variant | Значение True, чтобы возвратить часть столбца ссылки в качестве абсолютной ссылки. Значение по умолчанию — True. |
ReferenceStyle | Необязательный | XlReferenceStyle | Стиль ссылки. Значение по умолчанию — xlA1. |
External | Необязательный | Variant | Значение True, чтобы вернуть внешнюю ссылку. Значение False, чтобы вернуть локальную ссылку. Значение по умолчанию — False. |
RelativeTo | Необязательный | Variant | Если RowAbsolute и ColumnAbsolute имеют значение False, а ReferenceStyle — xlR1C1, необходимо включить начальную точку для относительной ссылки. Этот аргумент является объектом Range, определяющим начальную точку. |
Примечания
Если ссылка содержит более одной ячейки, аргументы RowAbsolute и ColumnAbsolute применяются ко всем строкам и столбцам.
Пример
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Допустим, у нас есть два открытых файла: «Книга1» и «Книга2», причем, файл «Книга1» активен и в нем находится исполняемый код VBA.
В общем случае при обращении к ячейке неактивной рабочей книги «Книга2» из кода файла «Книга1» прописывается полный путь:
Удобнее обращаться к ячейке через свойство рабочего листа Cells(номер строки, номер столбца), так как вместо номеров строк и столбцов можно использовать переменные. Обратите внимание, что при обращении к любой рабочей книге, она должна быть открыта, иначе произойдет ошибка. Закрытую книгу перед обращением к ней необходимо открыть.
Теперь предположим, что у нас в активной книге «Книга1» активны «Лист1» и ячейка на нем «A1». Тогда обращение к ячейке «A1» можно записать следующим образом:
Точно также можно обращаться и к другим ячейкам активного рабочего листа, кроме обращения ActiveCell, так как активной может быть только одна ячейка, в нашем примере – это ячейка «A1».
Если мы обращаемся к ячейке на неактивном листе активной рабочей книги, тогда необходимо указать этот лист:
Имя ярлыка может совпадать с основным именем листа. Увидеть эти имена можно в окне редактора VBA в проводнике проекта. Без скобок отображается основное имя листа, в скобках – имя ярлыка.
Обращение к ячейке по индексу
К ячейке на рабочем листе можно обращаться по ее индексу (порядковому номеру), который считается по расположению ячейки на листе слева-направо и сверху-вниз.
Например, индекс ячеек в первой строке равен номеру столбца. Индекс ячеек во второй строке равен количеству ячеек в первой строке (которое равно общему количеству столбцов на листе, зависящему от версии Excel) плюс номер столбца. Индекс ячеек в третьей строке равен количеству ячеек в двух первых строках плюс номер столбца. И так далее.
Для примера, Cells(4) та же ячейка, что и Cells(1, 4). Используется такое обозначение редко, тем более, что у разных версий Excel может быть разным количество столбцов и строк на рабочем листе.
По индексу можно обращаться к ячейке не только на всем рабочем листе, но и в отдельном диапазоне. Нумерация ячеек осуществляется в пределах заданного диапазона по тому же правилу: слева-направо и сверху-вниз. Вот индексы ячеек диапазона Range(«A1:C3»):
Обращение к ячейке Range("A1:C3").Cells(5) соответствует выражению Range("B2") .
Обращение к ячейке по имени
Если ячейке на рабочем листе Excel присвоено имя (Формулы –> Присвоить имя), то обращаться к ней можно по присвоенному имени.
Допустим одной из ячеек присвоено имя – «Итого», тогда обратиться к ней можно – Range("Итого") .
Запись информации в ячейку
Содержание ячейки определяется ее свойством «Value», которое в VBA Excel является свойством по умолчанию и его можно явно не указывать. Записывается информация в ячейку при помощи оператора присваивания «=»:
Чтобы переменной присвоить диапазон ячеек, она должна быть объявлена как Variant, Object или Range:
Чтобы было понятнее, для чего переменная создана, объявляйте ее как Range.
Присваивается переменной диапазон ячеек с помощью оператора Set:
В выражении Range(Cells(3, 4), Cells(26, 18)) вместо чисел можно использовать переменные.
Для присвоения диапазона ячеек переменной можно использовать встроенное диалоговое окно Application.InputBox, которое позволяет выбрать диапазон на рабочем листе для дальнейшей работы с ним.
Адресация ячеек в диапазоне
К ячейкам присвоенного диапазона можно обращаться по их индексам, а также по индексам строк и столбцов, на пересечении которых они находятся.
Индексация ячеек в присвоенном диапазоне осуществляется слева направо и сверху вниз, например, для диапазона размерностью 5х5:
1 | 2 | 3 | 4 | 5 |
6 | 7 | 8 | 9 | 10 |
11 | 12 | 13 | 14 | 15 |
16 | 17 | 18 | 19 | 20 |
21 | 22 | 23 | 24 | 25 |
Индексация строк и столбцов начинается с левой верхней ячейки. В диапазоне этого примера содержится 5 строк и 5 столбцов. На пересечении 2 строки и 4 столбца находится ячейка с индексом 9. Обратиться к ней можно так:
Читайте также: