从VBA中的命名范围获取值 [英] Getting a value from a named range in VBA

问题描述

我想从命名范围中检索一个值.想象一个具有X列和Y行的命名范围.我想返回一个值，例如，从第2列第3行返回.我遇到的问题是，如果我编写代码并运行它，Excel会引发错误.如果我将代码写入监视窗口，它会返回正常.见下文

I want to retrieve a value from named range. Imagine a named range that has X columns and Y rows. I want to return a value, e.g., from column 2, row 3. The issue I experience is that if I write the code and run it, Excel throws an error. If I write the code into the watch window, it returns fine. See below

...
Dim NamedRange As Variant: NamedRange = Range(NamedRangeName)
...
Dim ReturnValue As Object
Set ReturnValue = NamedRange(RowIndex, ColumnToRetrieveIndex) 'Throws Run-time error 424. Object required

如果我写NamedRange(RowIndex，ColumnToRetrieveIndex)在监视窗口中，我可以看到该单元格的正确值.

If I write NamedRange(RowIndex, ColumnToRetrieveIndex) into the watch window, I can see the correct value of the cell.

我不太了解VB，所以我想这是某种语法错误，我想将其传递到ReturnValue中，但我无法弄清楚.

I don't know VB much so I guess it's just some kind of syntax error how I want to pass it into the ReturnValue but I just can't figure it out.

推荐答案

使用此

ThisWorkbook.Names("myNamedRange").RefersToRange(1,1)

要从命名范围"myNamedRange"中的第一个单元格获取值

To get the value from the first cell in the named range "myNamedRange"

使用 ThisWorkbook.Names ，您可以访问当前工作簿中所有工作表的所有命名范围.使用 RefersToRange ，您可以获得对实际范围的引用.

With ThisWorkbook.Names you can access all named ranges of all the sheets within the current workbook. With RefersToRange you get a reference to the actual range.

这篇关于从VBA中的命名范围获取值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！