excel - 如何使用表中不同行的值运行 Excel 宏？

Question

我正在编写一个 Excel 宏来从电子表格中的数据发送电子邮件。数据在一个表格中，每列提供不同的变量来创建电子邮件(收件人:、抄送:、主题、附件等)。

我有宏可以在表格的一行上做我想做的事。我的问题是:

如何缩放 VBA 代码以适用于表格的每一行？我希望每行中都有一个超链接，以使用该行中的数据运行宏。下面是我的一小段代码作为示例:

Sub SendMail()
Dim xContractNumber As String
xContractNumber = Worksheets("Program Info").Range("L10").Value
End Sub

在上面的示例中，我想要一个使用“程序信息”表第 10 行中的数据运行宏的超链接......以及另一个使用第 11 行中的数据运行宏的按钮或链接，依此类推。

Answer 1

这个答案试图结合每个人已经给出的许多好的答案和评论。下面的代码以简洁的方式包含按钮和超链接的功能。

此代码可以放在一个单独的模块中:

Sub SendMailByButton()
  SendMailForRow ActiveSheet.Buttons(Application.Caller).TopLeftCell.Row
End Sub

Sub SendMailForRow(ByVal r As Long)
  If r < 1 Then Exit Sub 'Failsafe in case the row number is invalid

  Dim xContractNumber As String, xValueInColumnM As String, xValueInColumnN As String

  xContractNumber = ActiveSheet.Cells(r, 12).Value 'Col 12 is col "L"
  xValueInColumnM = ActiveSheet.Cells(r, 13).Value
  xValueInColumnN = ActiveSheet.Cells(r, 14).Value
  '...etc.
  '...Rest of code to send the actual email
End Sub

如果使用按钮， SendMailByButton必须附加到每个按钮的单击事件，上面的代码就足够了。

如果使用手动添加的超链接，上面的代码需要在 中用下面的代码来补充。片材模块对于使用超链接的每个工作表(在您的情况下，您可能只需要将此代码添加到一个工作表的模块中)...

'This event is fired when the hyperlink is clicked
Private Sub Worksheet_FollowHyperlink(ByVal target As Hyperlink)
  On Error Resume Next
  SendMailForRow target.Range.Row
End Sub

每个手动超链接都必须链接到它所在的同一单元格(即链接到“本文档中的位置”，单元格引用设置为其当前单元格)。

问题仍然存在，您将需要为表中的每一行手动创建一个按钮或超链接，这可能很麻烦，尤其是在行数很多或行数将来会增加的情况下。

避免这个问题的一种方法是在表格顶部有一个额外的按钮，允许用户为每行数据自动创建按钮和/或超链接(如果表格缩小，则删除多余的按钮或超链接)。这可能需要您发布一个单独的问题。

绕过这个问题的另一种方法是完全放弃按钮，而是使用带有原生 HYPERLINK 的 Excel 公式。功能(替换“常规”链接)。在这种情况下，将不再需要上面的 FollowHyperlink 事件处理程序，但您需要添加以下函数(它可以进入 SendMailForRow 所在的同一模块中)...

Function SendMailByHLink()
  SendMailForRow ActiveCell.Row
  Set SendMailByHLink = ActiveCell
End Function

然后，您必须在每一行(在您想要超链接的列中)创建一个 Excel 公式，如下所示...

=HYPERLINK("#SendMailByHLink()", "Send email")

输入此公式将在单元格中自动生成一个超链接，它会告诉 Excel 执行函数 SendMailByHLink单击超链接时。 “#”后面的函数应该返回链接的目标，这就是为什么 SendMailByHLink返回 ActiveCell以确保焦点保持在该单元格上(如果您愿意，可以返回另一个单元格，例如 ActiveCell.Offset(, -2)，以便在单击链接后将用户带到同一行中的单元格 2 列)。返回前 ActiveCell到 Excel， SendMailByHLink将执行电子邮件发送代码。

使用 HYPERLINK 公式的好处是您可以轻松地在表格中的所有行上下复制/粘贴公式。因此，如果您的表的大小增加，用户所要做的就是将 HYPERLINK 公式复制/粘贴到新行中。如果表格缩小，用户还可以删除多余的 HYPERLINK 公式。如果数据位于 an official Excel table 上，甚至可以让 Excel 自动复制公式。由 using a calculated column .

对不起所有额外的解释。如果您专注于代码块，您会发现解决方案比看起来更简单。