邮件合并VBA代码加速：替代CalculationManual方案及编译错误排查

Let’s tackle your issues one by one—first the compilation error, then the speed bottlenecks, and finally the discrepancy between manual vs button-triggered runs.

1. Why You’re Getting the "Method or Data Member Not Found" Error #

That Application.Calculation line is your main culprit. Here’s the breakdown:

• Application.Calculation is an Excel-only property—it controls how Excel recalculates formulas. But your macro runs in Word’s VBA environment (you’re working with Word Mail Merge and an ActiveX CommandButton in a Word doc). Word’s Application object doesn’t have this member, even if you’ve referenced the Excel library (your code uses Word’s Application by default).

You also have another Excel-specific line that will cause issues later: Activesheet.DisplayPageBreaks—Word has no Activesheet object at all.

Quick Fix: Delete these lines entirely:

' Remove these Excel-only lines—they don't belong in Word VBA!
Application.Calculation = xlCalculationManual
'...
Application.Calculation = xlCalculationAutomatic
' And this line too:
Activesheet.DisplayPageBreaks = False
'...
Activesheet.DisplayPageBreaks = True

2. Proper Word VBA Speed-Up Settings #

Replace your broken speed-up block with Word-specific optimizations that actually work to reduce overhead:

' Word-focused code acceleration
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
ActiveDocument.TrackRevisions = False ' Temporarily disable revision tracking
ActiveDocument.SaveOptions.SaveFormat = wdFormatDocument ' Disable auto-format saves if enabled

And restore the default settings at the end of your macro:

' Restore Word's default behavior
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
ActiveDocument.TrackRevisions = True ' Re-enable if you had it turned on originally

3. Optimize the Mail Merge Record Lookup (Hit <3 Seconds) #

The biggest drag on your macro is likely the FindRecord method—it’s not efficient for large datasets. Here are two better approaches:

Option 1: Directly Iterate Through the Data Source #

For smaller datasets, looping through records directly is faster than FindRecord because you can exit the loop as soon as you find a match:

Private Sub CommandButton1_Click()
    ' Speed-up config
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.EnableEvents = False
    ActiveDocument.TrackRevisions = False

    Dim numRecord As Long ' Use Long instead of Integer to avoid overflow
    Dim myDCR As String
    Dim dsMain As MailMergeDataSource
    Dim i As Long
    
    myDCR = InputBox("Enter DCR:")
    If myDCR = "" Then Exit Sub ' Exit if user cancels or enters nothing
    
    Set dsMain = ActiveDocument.MailMerge.DataSource
    numRecord = 0
    
    ' Loop through records directly—faster than FindRecord
    With dsMain
        .ActiveRecord = wdFirstRecord
        For i = 1 To .RecordCount
            If .DataFields("DCR").Value = myDCR Then
                numRecord = i
                .ActiveRecord = i ' Jump to the matching record
                Exit For ' Stop searching once found
            End If
            .ActiveRecord = wdNextRecord
        Next i
    End With

    ' Restore settings
    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
    Application.EnableEvents = True
    ActiveDocument.TrackRevisions = True
    
    ' Optional: Notify user of results
    If numRecord > 0 Then
        MsgBox "DCR found at record " & numRecord, vbInformation
    Else
        MsgBox "DCR not found", vbExclamation
    End If
End Sub

Option 2: Use SQL for Instant Lookup (Best for Large Excel Data Sources) #

If your mail merge data comes from an Excel file, using an SQL query to filter the exact record will drastically outperform FindRecord—perfect for large datasets:

Private Sub CommandButton1_Click()
    ' Speed-up config
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.EnableEvents = False
    ActiveDocument.TrackRevisions = False

    Dim myDCR As String
    Dim conn As Object
    Dim rs As Object
    Dim sql As String
    Dim dataSourcePath As String
    
    myDCR = InputBox("Enter DCR:")
    If myDCR = "" Then Exit Sub
    
    ' Get the path to your Excel data source
    dataSourcePath = ActiveDocument.MailMerge.DataSource.Name
    
    ' Create ADODB objects (late binding, no extra references needed)
    Set conn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    
    ' Connection string for Excel (works for .xlsx and .xls files)
    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dataSourcePath & ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    
    ' SQL query to find your DCR (replace [Sheet1$] with your actual sheet name)
    sql = "SELECT * FROM [Sheet1$] WHERE DCR = '" & myDCR & "'"
    rs.Open sql, conn
    
    If Not rs.EOF Then
        ' Access fields directly here, or map back to the mail merge record if needed
        MsgBox "Found DCR: " & rs("DCR").Value, vbInformation
    Else
        MsgBox "DCR not found", vbExclamation
    End If
    
    ' Clean up resources
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing

    ' Restore settings
    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
    Application.EnableEvents = True
    ActiveDocument.TrackRevisions = True
End Sub

4. Why Manual Runs Are Faster Than Button-Triggered Runs #

This is a common quirk with ActiveX CommandButtons in Word. Try these fixes:

• Replace the ActiveX Button: ActiveX controls can trigger hidden events (like Document_SelectionChange) or have embedded overhead. Switch to a Form Control Button instead (under Developer tab > Controls > Legacy Tools > Form Controls).
• Check for Background Events: If your document has macros like Document_Open or SelectionChange, these might fire when you click the button but not when running manually. Our Application.EnableEvents = False line should block most of these, but double-check your ThisDocument module for extra events.
• Lighten the Document: Large files with excessive formatting, tracked changes, or embedded objects slow down button triggers. Delete unused content, accept/reject revisions, and compress images to reduce overhead.

内容的提问来源于stack exchange，提问作者James Knight