Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Как сослаться на ячеку из VBA если её адрес может меняться - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Как сослаться на ячеку из VBA если её адрес может меняться (Макросы/Sub)
Как сослаться на ячеку из VBA если её адрес может меняться
bmv98rus Дата: Понедельник, 16.01.2017, 22:53 | Сообщение № 1
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4099
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
Условия: лист не защищенный от изменений. VBA script, который что-то делает с листом, допустим с ячекой B2. Неважно, пользовательский это уже файл или в разработке, когда шаблон часто меняется и каждый раз вносит изменения нет желания.

Какие методы можно использовать, чтоб типа не потерять "из вида" ячейку в случае вставки столбца. строки? Желательно сохранить совместимость с разными версиями
приходит на ум :
1. Именовать область и Range(<Name>) / [Name]
2. Делать лист натроек (settings) и там , сославшись на B2, получать текстом адрес в ячеке скажем D3 ,после чего Range(setings.Range("D3"),value) . Правда не дай боже потом что-то сдвинуть на листе settings!!!!
3 Искать её по какому либо признаку, но хорошо если это признак есть.
4. ????

Есть варианты?


Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Вторник, 17.01.2017, 07:39
 
Ответить
СообщениеУсловия: лист не защищенный от изменений. VBA script, который что-то делает с листом, допустим с ячекой B2. Неважно, пользовательский это уже файл или в разработке, когда шаблон часто меняется и каждый раз вносит изменения нет желания.

Какие методы можно использовать, чтоб типа не потерять "из вида" ячейку в случае вставки столбца. строки? Желательно сохранить совместимость с разными версиями
приходит на ум :
1. Именовать область и Range(<Name>) / [Name]
2. Делать лист натроек (settings) и там , сославшись на B2, получать текстом адрес в ячеке скажем D3 ,после чего Range(setings.Range("D3"),value) . Правда не дай боже потом что-то сдвинуть на листе settings!!!!
3 Искать её по какому либо признаку, но хорошо если это признак есть.
4. ????

Есть варианты?

Автор - bmv98rus
Дата добавления - 16.01.2017 в 22:53
buchlotnik Дата: Понедельник, 16.01.2017, 23:23 | Сообщение № 2
Группа: Заблокированные
Ранг: Участник клуба
Сообщений: 3442
Репутация: 929 ±
Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG
так а чем имена не устраивают? самое простое и очевидное, ИМХО
И можно не [vba]
Код
Range("Name")
[/vba] а просто [vba]
Код
[Name]
[/vba]
 
Ответить
Сообщениетак а чем имена не устраивают? самое простое и очевидное, ИМХО
И можно не [vba]
Код
Range("Name")
[/vba] а просто [vba]
Код
[Name]
[/vba]

Автор - buchlotnik
Дата добавления - 16.01.2017 в 23:23
_Boroda_ Дата: Понедельник, 16.01.2017, 23:34 | Сообщение № 3
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Искать её по какому либо признаку, но хорошо если это признак есть

Я обычно так и делаю. А признак есть всегда, иначе как Вы эту ячейку глазами на листе находите? Признак может быть не в самой ячейке, а вычисляться относительно других ячеек.
Например, ячейка ниже на 3 строки от ячейки, которая является шапкой первой таблицы и в которой написано "Сумма ИТОГО" (это мы находим столбец), а строку шапки мы определяем как ту строку, где в столбце В написано "номенклатура".
Был бы файл с пояснениями - можно было бы подробнее расписать


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
Искать её по какому либо признаку, но хорошо если это признак есть

Я обычно так и делаю. А признак есть всегда, иначе как Вы эту ячейку глазами на листе находите? Признак может быть не в самой ячейке, а вычисляться относительно других ячеек.
Например, ячейка ниже на 3 строки от ячейки, которая является шапкой первой таблицы и в которой написано "Сумма ИТОГО" (это мы находим столбец), а строку шапки мы определяем как ту строку, где в столбце В написано "номенклатура".
Был бы файл с пояснениями - можно было бы подробнее расписать

Автор - _Boroda_
Дата добавления - 16.01.2017 в 23:34
bmv98rus Дата: Вторник, 17.01.2017, 00:09 | Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4099
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
buchlotnik,
Тут только вопрос совместимости, честнго говоря не помню когда стало можно [vba]
Код
[Name]
[/vba] я могу ошибаться но c 2010 кажется , да и с именованными областями кажется на книгу можно было назначить, а сейчас на отдельный лист. Хотя возможно я это с Libre, чтоб он был быстрее доработан :-), путаю.

_Boroda_,
Я общий случай обсуждаю. Пример дал но , для иллюстрации. Ну а глаз и мозг - как вы мельком за пару секунд узнаете кадр из фильма? Доли секунд хрен знает сктлько операци. сравнений .... ....

Это не идельный пример, но .... Форму (CTRL+f или A4 select ) попросили привязать H столбцу + В самом коде используется смещение на 2 относительно столбца Артикул. Все ок, но тут просят удалить скрытые столбцы 3 и 5. Также заказчикволен менять колонтитул. Изменить то код не проблема, но вопрос, как сделать чтоб его не менять. Тут и так есть метка Header. Повесить якоря жесткие есть желание вот и задал вопрос.
P.S. Код прошу не критиковать и так помощь от перевода строки в номер переросла через 3 промежуточных варианта к собсвенной форме поиска. отказать было неудобно :-),
К сообщению приложен файл: Search8.7.xlsm (53.8 Kb)


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщениеbuchlotnik,
Тут только вопрос совместимости, честнго говоря не помню когда стало можно [vba]
Код
[Name]
[/vba] я могу ошибаться но c 2010 кажется , да и с именованными областями кажется на книгу можно было назначить, а сейчас на отдельный лист. Хотя возможно я это с Libre, чтоб он был быстрее доработан :-), путаю.

_Boroda_,
Я общий случай обсуждаю. Пример дал но , для иллюстрации. Ну а глаз и мозг - как вы мельком за пару секунд узнаете кадр из фильма? Доли секунд хрен знает сктлько операци. сравнений .... ....

Это не идельный пример, но .... Форму (CTRL+f или A4 select ) попросили привязать H столбцу + В самом коде используется смещение на 2 относительно столбца Артикул. Все ок, но тут просят удалить скрытые столбцы 3 и 5. Также заказчикволен менять колонтитул. Изменить то код не проблема, но вопрос, как сделать чтоб его не менять. Тут и так есть метка Header. Повесить якоря жесткие есть желание вот и задал вопрос.
P.S. Код прошу не критиковать и так помощь от перевода строки в номер переросла через 3 промежуточных варианта к собсвенной форме поиска. отказать было неудобно :-),

Автор - bmv98rus
Дата добавления - 17.01.2017 в 00:09
_Boroda_ Дата: Вторник, 17.01.2017, 00:16 | Сообщение № 5
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
И что конкретно хочется-то?


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеИ что конкретно хочется-то?

Автор - _Boroda_
Дата добавления - 17.01.2017 в 00:16
buchlotnik Дата: Вторник, 17.01.2017, 00:32 | Сообщение № 6
Группа: Заблокированные
Ранг: Участник клуба
Сообщений: 3442
Репутация: 929 ±
Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG
Цитата
не помню когда стало можно
а может, как в КВН у Камызяков "не не помню, а не знаю..." на 2003 пашет на ура (ЛИЧНО ПРОВЕРИЛ, не поленился)
Цитата
а сейчас на отдельный лист
не-а - можно на книгу, а можно и на лист


Сообщение отредактировал buchlotnik - Вторник, 17.01.2017, 00:37
 
Ответить
Сообщение
Цитата
не помню когда стало можно
а может, как в КВН у Камызяков "не не помню, а не знаю..." на 2003 пашет на ура (ЛИЧНО ПРОВЕРИЛ, не поленился)
Цитата
а сейчас на отдельный лист
не-а - можно на книгу, а можно и на лист

Автор - buchlotnik
Дата добавления - 17.01.2017 в 00:32
bmv98rus Дата: Вторник, 17.01.2017, 00:46 | Сообщение № 7
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4099
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
buchlotnik,
Хм. буквально на днях 2007 взбрыкнул, но по любому, не в форме записи дело.

_Boroda_,
Думал, может есть способ, который не очивиден и его знают.

Именованные области -пока самые лучшие якоря из всех вариантов,
пока и туда пользователь не залез.


Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Вторник, 17.01.2017, 00:47
 
Ответить
Сообщениеbuchlotnik,
Хм. буквально на днях 2007 взбрыкнул, но по любому, не в форме записи дело.

_Boroda_,
Думал, может есть способ, который не очивиден и его знают.

Именованные области -пока самые лучшие якоря из всех вариантов,
пока и туда пользователь не залез.

Автор - bmv98rus
Дата добавления - 17.01.2017 в 00:46
_Boroda_ Дата: Вторник, 17.01.2017, 00:51 | Сообщение № 8
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Дубль два -
И что конкретно хочется-то?


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеДубль два -
И что конкретно хочется-то?

Автор - _Boroda_
Дата добавления - 17.01.2017 в 00:51
buchlotnik Дата: Вторник, 17.01.2017, 00:54 | Сообщение № 9
Группа: Заблокированные
Ранг: Участник клуба
Сообщений: 3442
Репутация: 929 ±
Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG
Цитата
пока и туда пользователь не залез
дык имена программно скрываются, а так поддержу Александра
Цитата
И что конкретно хочется-то?


Сообщение отредактировал buchlotnik - Вторник, 17.01.2017, 00:54
 
Ответить
Сообщение
Цитата
пока и туда пользователь не залез
дык имена программно скрываются, а так поддержу Александра
Цитата
И что конкретно хочется-то?

Автор - buchlotnik
Дата добавления - 17.01.2017 в 00:54
bmv98rus Дата: Вторник, 17.01.2017, 01:16 | Сообщение № 10
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4099
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
buchlotnik, _Boroda_,
Какие методы можно использовать, чтоб типа не потерять "из вида" ячейку

интерсовало то всего, есть ли что, кроме перечисленного?


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщениеbuchlotnik, _Boroda_,
Какие методы можно использовать, чтоб типа не потерять "из вида" ячейку

интерсовало то всего, есть ли что, кроме перечисленного?

Автор - bmv98rus
Дата добавления - 17.01.2017 в 01:16
_Boroda_ Дата: Вторник, 17.01.2017, 01:31 | Сообщение № 11
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Господи, как я устал!
Дубль три -
И что конкретно хочется-то?


Какую ячейку, когда не потерять, в какой части кода, ...

Ну почему Вы считаете, что для того, чтобы ответить на Ваш вопрос, мы так жаждем внимательно просмотреть все Ваши макросы и догадаться о том, что Вам нужно? У меня, например, есть гораздо более привлекательные занятия в полвторого ночи.


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеГосподи, как я устал!
Дубль три -
И что конкретно хочется-то?


Какую ячейку, когда не потерять, в какой части кода, ...

Ну почему Вы считаете, что для того, чтобы ответить на Ваш вопрос, мы так жаждем внимательно просмотреть все Ваши макросы и догадаться о том, что Вам нужно? У меня, например, есть гораздо более привлекательные занятия в полвторого ночи.

Автор - _Boroda_
Дата добавления - 17.01.2017 в 01:31
bmv98rus Дата: Вторник, 17.01.2017, 07:58 | Сообщение № 12
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4099
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
_Boroda_,
Вопрос был не про конкретный случай или пример. Вопрос был про существующие методы, и если иных кроме 1,2,3 нет то тему можно закрыть.


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщение_Boroda_,
Вопрос был не про конкретный случай или пример. Вопрос был про существующие методы, и если иных кроме 1,2,3 нет то тему можно закрыть.

Автор - bmv98rus
Дата добавления - 17.01.2017 в 07:58
Alex_ST Дата: Вторник, 17.01.2017, 08:03 | Сообщение № 13
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3199
Репутация: 606 ±
Замечаний: 0% ±

2003
Не, мужики...
В обращении к именованным диапазонам есть тонкости.
Всё проходит гладко и просто только если имя диапазона ВО ВСЕЙ КНИГЕ уникально. А вот если оно повторяется на нескольких листах (так обычно получается при дублировании листа целиком по ПКМ на ярлыке), то начинаются проблемы...
Я даже себе шпаргалку составил, чтобы на эти грабли неожиданно не наступить: [vba]
Код
Private Sub test_NamedRange()   ' на каком-то листе в ЭТОЙ книге создан именованный диапазон "NmRng"
' так ОБЫЧНО (но иногда вдруг начинает глючить) можно обратиться к нему из ЛЮБОГО модуля ЭТОЙ книги:
Debug.Print [NmRng].Value
Debug.Print [NmRng].Parent.Name
   ' так можно обратиться к нему ТОЛЬКО из модуля того листа, на котором расположен диапазон _
     и обычно получается - из стандартного модуля:
Debug.Print Range("NmRng").Value
Debug.Print Range("NmRng").Parent.Name
   ' так ВСЕГДА можно обратиться к нему из ЛЮБОГО модуля ЭТОЙ книги:
Debug.Print ThisWorkbook.Names("NmRng").RefersToRange.Value
Debug.Print ThisWorkbook.Names("NmRng").RefersToRange.Parent.Name
End Sub
[/vba]



С уважением,
Алексей
MS Excel 2003 - the best!!!


Сообщение отредактировал Alex_ST - Вторник, 17.01.2017, 08:04
 
Ответить
СообщениеНе, мужики...
В обращении к именованным диапазонам есть тонкости.
Всё проходит гладко и просто только если имя диапазона ВО ВСЕЙ КНИГЕ уникально. А вот если оно повторяется на нескольких листах (так обычно получается при дублировании листа целиком по ПКМ на ярлыке), то начинаются проблемы...
Я даже себе шпаргалку составил, чтобы на эти грабли неожиданно не наступить: [vba]
Код
Private Sub test_NamedRange()   ' на каком-то листе в ЭТОЙ книге создан именованный диапазон "NmRng"
' так ОБЫЧНО (но иногда вдруг начинает глючить) можно обратиться к нему из ЛЮБОГО модуля ЭТОЙ книги:
Debug.Print [NmRng].Value
Debug.Print [NmRng].Parent.Name
   ' так можно обратиться к нему ТОЛЬКО из модуля того листа, на котором расположен диапазон _
     и обычно получается - из стандартного модуля:
Debug.Print Range("NmRng").Value
Debug.Print Range("NmRng").Parent.Name
   ' так ВСЕГДА можно обратиться к нему из ЛЮБОГО модуля ЭТОЙ книги:
Debug.Print ThisWorkbook.Names("NmRng").RefersToRange.Value
Debug.Print ThisWorkbook.Names("NmRng").RefersToRange.Parent.Name
End Sub
[/vba]

Автор - Alex_ST
Дата добавления - 17.01.2017 в 08:03
bmv98rus Дата: Вторник, 17.01.2017, 08:41 | Сообщение № 14
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4099
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
Alex_ST,
Спасибо, я именно на дублировании и напоролся, что и послужило толчком к вопросу. Получается, что не якорь меняем, а правильно его используем.


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
СообщениеAlex_ST,
Спасибо, я именно на дублировании и напоролся, что и послужило толчком к вопросу. Получается, что не якорь меняем, а правильно его используем.

Автор - bmv98rus
Дата добавления - 17.01.2017 в 08:41
Alex_ST Дата: Вторник, 17.01.2017, 08:50 | Сообщение № 15
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3199
Репутация: 606 ±
Замечаний: 0% ±

2003
Это точно. С предсказуемым результатом обращаться к именованному диапазону на листе можно ТОЛЬКО из модуля этого листа.
Иначе в один из последующих моментов (и вовсе не обязательно, что при отладке) могут возникнуть проблемы.



С уважением,
Алексей
MS Excel 2003 - the best!!!
 
Ответить
СообщениеЭто точно. С предсказуемым результатом обращаться к именованному диапазону на листе можно ТОЛЬКО из модуля этого листа.
Иначе в один из последующих моментов (и вовсе не обязательно, что при отладке) могут возникнуть проблемы.

Автор - Alex_ST
Дата добавления - 17.01.2017 в 08:50
Alex_ST Дата: Вторник, 17.01.2017, 09:03 | Сообщение № 16
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3199
Репутация: 606 ±
Замечаний: 0% ±

2003
К стати, сейчас пришёл в голову ещё один вариант, более-менее защищённый от неожиданностей (кроме удаления диапазона или листа с ним вообще, конечно).
Если Вам нужно обращаться к именованному диапазону на одном из листов книги из процедуры, размещённой в стандартном модуле, то можно попробовать сделать такой "финт ушами":
1. Дать листу нормальное CodeName вместо стандартного Лист1 (хотя это только для удобства написания кода)
2. Обращаться к диапазону по имени, указывая не имя листа, а CodeName, которое не смогут изменить пользователи, не знающие, что такое VBA, но желающие по какой-то причине изменить надпись на ярлыке листа.
Обращение получится типа [vba]
Код
Debug.Print MySheet.[NmRng].Value
Debug.Print MySheet.Range("NmRng").Value
[/vba]Чуть длиннее, но, похоже, должно работать
(Не проверял!!!)



С уважением,
Алексей
MS Excel 2003 - the best!!!
 
Ответить
СообщениеК стати, сейчас пришёл в голову ещё один вариант, более-менее защищённый от неожиданностей (кроме удаления диапазона или листа с ним вообще, конечно).
Если Вам нужно обращаться к именованному диапазону на одном из листов книги из процедуры, размещённой в стандартном модуле, то можно попробовать сделать такой "финт ушами":
1. Дать листу нормальное CodeName вместо стандартного Лист1 (хотя это только для удобства написания кода)
2. Обращаться к диапазону по имени, указывая не имя листа, а CodeName, которое не смогут изменить пользователи, не знающие, что такое VBA, но желающие по какой-то причине изменить надпись на ярлыке листа.
Обращение получится типа [vba]
Код
Debug.Print MySheet.[NmRng].Value
Debug.Print MySheet.Range("NmRng").Value
[/vba]Чуть длиннее, но, похоже, должно работать
(Не проверял!!!)

Автор - Alex_ST
Дата добавления - 17.01.2017 в 09:03
bmv98rus Дата: Вторник, 17.01.2017, 09:24 | Сообщение № 17
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4099
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
Alex_ST,

В случае с одиночным листом - да, при дулировании -не пройдет, точнее пройдет, но я слабо представляю код, который использует динамическое CodeName. В любом случае спасибо.

Вывод - нет лени! Пусть строка будет подлиннее, но понадежнее.


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
СообщениеAlex_ST,

В случае с одиночным листом - да, при дулировании -не пройдет, точнее пройдет, но я слабо представляю код, который использует динамическое CodeName. В любом случае спасибо.

Вывод - нет лени! Пусть строка будет подлиннее, но понадежнее.

Автор - bmv98rus
Дата добавления - 17.01.2017 в 09:24
Alex_ST Дата: Вторник, 17.01.2017, 09:29 | Сообщение № 18
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3199
Репутация: 606 ±
Замечаний: 0% ±

2003
динамическое CodeName
да нет проблем:[vba]
Код
Sub ttttt()
Debug.Print ActiveSheet.CodeName
End Sub
[/vba]



С уважением,
Алексей
MS Excel 2003 - the best!!!


Сообщение отредактировал Alex_ST - Вторник, 17.01.2017, 09:30
 
Ответить
Сообщение
динамическое CodeName
да нет проблем:[vba]
Код
Sub ttttt()
Debug.Print ActiveSheet.CodeName
End Sub
[/vba]

Автор - Alex_ST
Дата добавления - 17.01.2017 в 09:29
bmv98rus Дата: Вторник, 17.01.2017, 11:01 | Сообщение № 19
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4099
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
Alex_ST,

[offtop] ушли от темы. получить, получили, А что с ним потом делать, если это строка, как я понимаю. Еще раз спасибо. Давайте на этом закончим.


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
СообщениеAlex_ST,

[offtop] ушли от темы. получить, получили, А что с ним потом делать, если это строка, как я понимаю. Еще раз спасибо. Давайте на этом закончим.

Автор - bmv98rus
Дата добавления - 17.01.2017 в 11:01
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Как сослаться на ячеку из VBA если её адрес может меняться (Макросы/Sub)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!