在这里完成VBA新手 .
我正在尝试调整我在Ron de Bruin网站上找到的代码（bmail9.html是具体的教程/示例） . 代码很简单，在de Bruin的文件中完美运行 .
我能够使其适应工作簿的辅助工作表[interface2]上的Excel表[tbl_countdown]中的列名[Days until Liquidation due] . （这是相关文件的链接.1）但是，当我尝试将焦点转移到主要工作表[interface]中Excel表[tbl_interface]中的列名[Days to Liquidation due]时，代码具有失败了：它崩溃了Excel（2013） . （This is a link to a screenshot of the error message I get on opening the file.）一旦我重新打开它，就会生成一条错误消息 . 而且通常情况下（尽管不是100％的时间），无论我选择'End'还是'Debug'，它都会崩溃 .
Option Explicit
Private Sub Worksheet_Calculate()
Dim FormulaRange As Range
Dim countdownRange As Range
Dim NotSentMsg As String
Dim MyMsg As String
Dim SentMsg As String
Dim MyLimit As Double
Dim countdownFeedbackOffset As Double
NotSentMsg = "Not Sent"
SentMsg = "Sent"
'Below the MyLimit value it will run the macro
MyLimit = 1
'Set the range with Formulas that you want to check
This is the line that fails and crashes everything
Set FormulaRange = Worksheets("interface").Range("tbl_interface[Liquidation in]")
countdownFeedbackOffset = 3
On Error GoTo EndMacro:
For Each FormulaCell In FormulaRange.Cells
With FormulaCell
If IsNumeric(.Value) = False Then
.Offset(0, 3).Value = "Not numeric"
MyMsg = "Not numeric"
Else
If .Value < MyLimit Then
MyMsg = SentMsg
If .Offset(0, countdownFeedbackOffset).Value = NotSentMsg Then
Call Mail_adv_liq_reminder
'Call Mail_with_outlook1
End If
Else
MyMsg = NotSentMsg
End If
End If
Application.EnableEvents = False
.Offset(0, 3).Value = MyMsg
Application.EnableEvents = True
End With
Next FormulaCell
ExitMacro:
Exit Sub
EndMacro:
Application.EnableEvents = True
MsgBox "Some Error occurred." _
& vbLf & Err.Number _
& vbLf & Err.Description
结束子
需要明确的是，这里的'ask'是为了弄清楚如何正确调用命名范围（而不是硬编码列名），以便代码对来自该接口表的列的任何未来添加或替换都是健壮的 . 但是我失败了，所以进入图像描述时，他们正在崩溃Excel .