如何在Excel VBA中使用相对名称

Question

许多关于Excel的“高级”（也就是VBA）教程，无论是网络上的还是Excel自带的VBA帮助文件，都鼓励我们使用

Range("B2:B10")

该方法（准确地说：对象）用于选择单元格或获取值。通常在同一位置，他们会补充说明可以使用预定义的名称，这样也完全可以。

Range("valuesabove")

另一方面，我热爱相对定义单元格名称的强大功能。它们使编写和处理大型复合公式以及基本上引用几乎任何内容变得更加容易。

然而，相对名称在我们习惯的Range("valuesabove")方法中不起作用。

通常（在工作表上使用时），相对名称是相对于当前选定的单元格或使用它们的单元格的。

在VBA的Range()对象中，情况并非如此。默认情况下，Range相对于WorkSheet对象，通常是ActiveSheet。但是，ActiveSheet由其左上角单元格A1表示。这就是Range相对于它的原因。这就是为什么绝对名称($C$23)可以使用它，而相对名称("向左移动一列，向上移动两行")不能使用它的原因。

所以我的问题是： 那么我该如何利用VBA中相对名称的强大功能呢？

---

编辑：

认识到我的问题不太清楚（谢谢你们不知疲倦地评论），让我尝试以特定的形式表达并澄清术语：

在Excel工作表上，使用名称引用单元格或基于单元格值的函数定义计算值非常方便。

在Excel中，对单元格的引用可以是相对、绝对或混合的。这也适用于创建名称。因此，我们可以谈论绝对、相对或混合名称（当然是指引用）。

这里多次使用了绝对名称（使用Excel的“跟踪先驱”功能创建）：

名称“名字”= $D$2

这里多次使用了相对名称：

名称为"upright24"，例如当选定单元格A7时= C3（没有$符号！）。但是根据所选单元格或区域，这会不断变化。您可以在名称管理器中检查它！（Ctrl + F3）

这就是我们可以考虑的混合名称：

名称为"rel_serialnumber"，例如当选择单元格C6时= $B6。其行（6）根据所选单元格或区域而不断更改。

相对名称或混合名称的创建明确基于创建名称时的活动单元格。绝对名称的创建自然不依赖于光标位置。

请注意，

• 绝对名称意味着相对于被引用的单元格的动态偏移量，该单元格是唯一的

• 相对名称意味着相对于被引用单元格的静态偏移量，因此始终会根据名称使用的位置而改变

• 混合名称意味着相对于被引用单元格的混合（或半动态）偏移量，其行或列因此始终会根据名称使用的位置而改变，而另一个则始终保持不变（在一个方向上的偏移量始终为零）。

---

好的，现在这是个问题。我有一个类似数据库的Excel表格，其中我将行处理为记录，列作为属性字段。用户使用方式如下：通过将光标放在所需记录的行中的任何单元格中来“选择记录”。然后，他按下一个大的命令按钮，启动我的VBA宏。这意味着要打开一个准备好的骨架文件，并将从所选记录中定义的一些特定单元格（顺便说一句，这些单元格由绝对名称定义）的某些值（由混合名称定义）填入其中。

由于在VBA中认为Range("name")可以使用（见上文），因此我认为Range("relativename")或Range("mixedname")也将同样有效，同时自动依赖于活动单元格。
我错了。
只有Range("absolutename")以人们期望的方式工作！请参见上文的解释。

我需要一个函数/方法/对象，它可以像Range("absolutename")一样方便地与"relativename"或"mixedname"一起使用。

- Greenberet

---

不太清楚你的问题，在 VBA 中，你可以写成 dim valuesabove as Range，然后 valuesabove = range("B2:B10")，现在你可以将 valuesabove 用作该范围。 - Steven Martin

你是在谈论R1C1表示法吗？比如，如果我想让D4=C3*10，我可以使用类似这样的代码：Range("D4").FormulaR1C1 = "R[-1]C[-1]*10"？你能举个VBA代码行的例子，说明它没有达到你想要的效果吗？ - Adam

我不想设置任何单元格的公式。我想通过类似于Range("B_cell_in_current_row")的方式获取当前选定单元格所在行的单元格B的值。 - Greenberet

好问题。始终存在一个活动单元格。而且我特别想使用用户在运行宏之前选择的活动单元格（其行）。我正在寻找一种基于给定名称和ActiveCell的方法来实现。 - Greenberet

所以您想要一个更紧凑的版本 my_Value = Cells(ActiveCell.Row, Range("Dogs").Column).Value 吗？ - Adam

Answer 1

我认为我找到了一个合适且紧凑的解决方案。这是：

Names("mixedname").RefersToRange

虽然不如Range("mixedname")简短，但它确实提供了预期的值。

更新：

如果您想要将源工作簿中的相对命名单元格值复制到目标工作簿中的相对命名单元格，并使用一行代码，则此解决方案大多无用。这是因为Names()依赖于光标的实际位置，而这取决于当前活动的工作簿是哪一个，在大多数情况下，这对其他工作簿来说并不合适。

在这种情况下，必须存储名称的非固定部分：

sourcerow = ActiveCell.Row [...] 'opening a wbk, this also makes it the active one [...] Names("dest").RefersToRange = mysheet.Cells(sourcerow, mybook.Names("src").RefersToRange.Column)

- Greenberet

Answer 2

看起来你正在寻找 Range.Offset() 的相关内容。http://msdn.microsoft.com/en-us/library/office/ff840060%28v=office.15%29.aspx

然而，你也可以这样实现：

'Your example Range(Col_B_in_current_row) as
Range("B" & ActiveCell.Row).Select

'Your example Range("B2:B10") -> Range("valuesabove") as
Range("B2:B10").Offset(-1, 0).Select

似乎已经存在一个相对简单的语法来实现此功能。

- Chrismas007

---

您IP地址为143.198.54.68，由于运营成本限制，当前对于免费用户的使用频率限制为每个IP每72小时10次对话，如需解除限制，请点击左下角设置图标按钮（手机用户先点击左上角菜单按钮）。 - Greenberet

那么使用 Range("B" & ActiveCell.Row).Select 去到你需要去的地方怎么样？ - Chrismas007

OP 不想使用 "B" 或数字偏移量 - 他想使用列的名称：Cells(ActiveCell.Row, Range("Dogs").Column)。 - Adam

@Adam，那他为什么不能直接说“B”呢？如果它总是B，那么我的代码Range("B" & ActiveCell.Row).Select就是最简单的选择。 - Chrismas007

@Adam - 你说得对。我在 Q 上做了相当多的额外工作，所以现在它更易懂了。感谢你的鼓励话语。 - Greenberet

Answer 3

要引用相对于另一个范围的范围，您可以使用以下语法：

myRange.Range("namedRange")

注意：仅当行偏移量和列偏移量均为正数时，此方法才有效。例如，如果命名范围的“引用”公式为“=Offset(A1,r,c)”，则如果r或c为负数，则上述语法将引发错误。但是，如果两者均为正数，则可以使用该方法。

这种不对称性对于VBA来说很不幸，但却是常态...

要引用当前活动单元格所在行的第三列，请使用以下代码：

ActiveCell.EntireRow.Range("C1")

要引用相对于ActiveCell偏移1行和3列的单元格：

ActiveCell.Range("C2")

显然，您可以在 VBA 中使用相同的语法与 Selection 对象或任何其他范围值一起使用。

- Cool Blue

Answer 4

Private Sub Worksheet_Change(ByVal Target as Range) If Not Intersect(Target.Address,ThisWorkbook.Sheets('sheetname).Range('RangeName)) Is Nothing Then _ '在此处执行您想要的操作。 ThisWorbook.Sheets('sheetname).Range('RangeName).Offset(0,Target.Row) End If End Sub

这应该会让您走上正确的道路（这非常不清楚）。使用工作表更改事件将用户工作表选择和更改带入VBA模块。将其放入相关工作表中。

- cronos2546

Answer 5

我曾经遇到过同样的问题，但是我设法解决了——有点儿。我不知道下面这个简单示例有什么不同之处，但它可以工作。起初我以为选择很重要，但实际上不是——即使不改变活动单元格，它也可以工作。

（但是在我的主要电子表格中，我仍然无法让它正常工作。）

命名范围："TestName" = Sheet1!$H1 H1:H10中的值 = 1,2,3,4,5,6,7,8,9,10

子程序测试() Dim x As Integer For x = 0 To 10 Range("A1").Offset(x, 0).Value = Range("A1").Offset(x, 0).Range("Testname").Value Next x End Sub

结果：A1:A10 = 1,2,3,4,5,6,7,8,9,10

- Justin McCartney