时间戳

首页 > 解决方案 > 如何使用表中不同行的值运行 Excel 宏？

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

问题描述

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

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

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

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

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

标签： excelvba

解决方案

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

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

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单击链接后，列回到同一行）。返回ActiveCellExcel 之前，SendMailByHLink将执行电子邮件发送代码。

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

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

推荐阅读