Свойство диапазона не возвращает правильное значение для относительного именованного диапазона в VBA Excel 365
Я назвал диапазон в качестве идентификатора и установил его адрес как =Materiais!$B3. для столбца он фиксирован, но для строки это будет зависеть от текущей позиции.
Например, когда я нажимаю на ячейку G8, диспетчер имен определяет правильное значение ячейки, как вы можете видеть на изображении ниже. в данном случае буква е.
когда я пытаюсь получить это значение через код VBA, это не приносит никакой пользы.
это код VBA, который я использую:
Sub ValorRange()
MsgBox Range("Materiais!ID").Value
End Sub
должно было вывести букву е, как это делает Name Manager.
Я думаю, что этого не произошло в Excel 2010, но сейчас я не могу провести этот тест. Я использую версию Excel 365.
заранее спасибо.
Буду признателен за вашу помощь.
GSerg, ActivateCell возвращает значение ячейки B1, как вы можете видеть ниже:
@DecimalTurn Я не получаю никаких ошибок. Я получаю значение ячейки B1.
@GSerg Sub ValorRange() находится в общем модуле. Я изменил свой код на Application.Range("Materiais!ID"), но он все равно не сработал. он берет значение из ячейки B1.
Ответы 2
Этот новый код должен работать. Я добавил «rngLimit» и метод VBA Intersec, чтобы проверить, содержится ли «rng» в «rngLimit».
Sub ValorRange()
Dim ws As Worksheet ' Worksheet object
Dim rng As Range ' Range object
Dim rngLimit As Range ' Range object
Set ws = Sheets("Materiais") ' Sets ws to worsheet
Set rng = ws.Names("ID").RefersToRange ' Gets range referenced by named cell
Set rngLimit = ws.Range("B3:B100") ' Gets limited range
' If intersection between rng and B3:B100 exists, shows rng value
If Not Intersect(rng, rngLimit) Is Nothing Then
MsgBox rng.Value
Else
' Set default value or throws an error
MsgBox "Error or default value"
End If
End Sub
В более общей версии вы можете использовать «ws», «rng» и «rngLimit» в качестве параметров функции «ValorRange».
Специально использовать Names возникает редко. Если на этом листе есть именованный диапазон в области книги, он не найдет его.
@GSerg, все сработало отлично! большое спасибо! но почему должно быть так? можешь мне это объяснить? если я не ошибаюсь, я бы сделал то же самое, но в Excel 2010, и это сработало. Я никогда не делал этого раньше.
@AlexBoorjs stackoverflow.com/q/2175962/11683. Специально так делать не обязательно, можно ws.Range("ID").
@GSerg ws.Range("ID") возвращает значение ячейки B1. это не работает. могу ли я, например, разграничить Range("Materiais!ID") внутри ячеек от B3 до B100? потому что адрес =Materiais!$B3 позволяет мне получить имя столбца, если ActiveCell находится в строке 2.
@AlexBoorjs Он не может вернуть B1 в соответствии с тем, что вы показали. Я не уверен, откуда вы знаете, что он возвращает B1, потому что он пуст. У вас есть два имени с именем ID, оба привязаны к листам: одно — Materiais, другое — Estoque. Когда вы попросите Sheets("Materiais") разрешить Range("ID"), вам будет предоставлена ячейка B8.
@GSerg, как вы можете видеть на изображении, которое я добавил сверху, Range("Materiais!ID") возвращает xxx, значение, которое я ввел в ячейку B1, чтобы провести тест. но ws.Names("ID").RefersToRange.Value возвращает букву е, как и ожидалось.
Ответ @AlexBoorjs Нельсона правильный, но он неполный, поскольку не объясняет, почему Range не работает. Я дал ответ, чтобы уточнить, почему это происходит.
@DecimalTurn, ты прав! Решение Нельсона работает отлично. именно так я и буду делать с этого момента.
Я пытаюсь убедить себя, что знаю, как работают диапазоны в Excel-VBA, но на самом деле я раньше с этим не сталкивался. Здесь вы действительно имеете относительный именованный диапазон, который будет обновляться в зависимости от активной ячейки, когда вы используете его внутри формулы Excel.
Поскольку они изменяются в зависимости от активной ячейки, вам, вероятно, следует избегать их использования внутри кода VBA, поскольку это может внести непредсказуемость в поведение вашего кода или заставить вас несколько раз менять выбранную ячейку для проведения вычислений (обязательное упоминание Как избежать использования Select в Excel VBA).
Ваш именованный диапазон определяется как RC2 в нотации R1C1 и $B1 в нотации A1 (относительно ячейки A1). Чтобы оценить это значение, Excel нужна начальная ячейка для преобразования относительной позиции в абсолютную. Однако метод VBA Range не использует активную ячейку, он всегда использует A1, поэтому вы всегда получаете значение в B1 при использовании метода Range.
Ответ Нельсона работает, потому что вместо этого используется RefersToRange, который передает активную ячейку для вычисления положения на листе. Вот почему он работает так, как вы привыкли при использовании именованного диапазона внутри ячейки.
@AlexBoorj, спасибо за разъяснения!
@Нельсон, спасибо за оперативную помощь. У меня только один вопрос, связанный с адресом Range. Я все равно буду благодарен. могу ли я установить Range("Materiais!ID") для разделения внутри ячеек, например, от B3 до B100? потому что сейчас адрес =Materiais!$B3 позволяет мне получить имя столбца, если ActiveCell находится в строке 2.
@AlexBoorjs, см. отредактированный код в моем ответе. Saudações de SP!
@Нельсон, ок, Нельсон. Я проверю его. о кара и Браза. que meneiro! рссрс...
Вы поставили
Sub ValorRange()на лист, на котором нетMaterials? Тогда это не сработает.Application.Range("Materiais!ID")найдет ваш ассортимент из любого листа,Worksheet.Range("Materiais!ID")нет.