vba - 工作表选择上的 Excel vba 宏崩溃 - 1 周前工作

Question

我在这里和其他地方花了很多时间搜索，但我还没有找到答案。我在 Excel O365 中创建并使用了一个电子表格，用于管理待完成的测试积压，它还包括一个用于处理预测的选项卡。

在两个单独的宏中，选择工作表的过程会导致 Excel 崩溃。

它的行为就像工作表已在文件中重命名但未在 vba 中更新(我以前犯过的错误并以艰难的方式学习)但没有重命名，据我所知，没有任何变化几个月。然而，这大约在一周前开始出现问题，我无法弄清楚。

失败的示例代码:

Sub Send_Range_Or_Whole_Worksheet_with_MailEnvelope()
Dim AWorksheet As Worksheet
Dim Sendrng As Range
Dim rng As Range

'On Error GoTo StopMacro

With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With

'Fill in the Worksheet/range you want to mail
'Note: if you use one cell it will send the whole worksheet
Set Sendrng = ThisWorkbook.Sheets("Weekly Forecast").Range("A10:A78")

'Remember the activesheet
Set AWorksheet = ActiveSheet

With Sendrng
    ' Select the worksheet with the range you want to send
    .Parent.Select
    'Remember the ActiveCell on that worksheet
    Set rng = ActiveCell
    'Select the range you want to mail
    .Select
    ' Create the mail and send it
    ActiveWorkbook.EnvelopeVisible = True
    With .Parent.MailEnvelope
        ' Set the optional introduction field thats adds
        ' some header text to the email body.
        .Introduction = "See below for the weekly forecast. Thank you!"

         With .Item
            .To = "(redacted email address)"
            .Subject = "Weekly Forecast"
            .Send
        End With

    End With

    'select the original ActiveCell
    rng.Select
End With

'Activate the sheet that was active before you run the macro
AWorksheet.Select

StopMacro:
With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With
ActiveWorkbook.EnvelopeVisible = False

End Sub

“Set Sendrng =”语句是 Excel 在单步执行此宏时崩溃的地方。

示例 2 是一个穷人的一些数据日志，它只是从一个工作表中复制并粘贴到另一个工作表中以用作“快照”:

Sub RefreshAllData()

' Updated 28Nov2017: Added "add to backlog log" ability
' RefreshAllData Macro
' Turn off screen updating

Application.ScreenUpdating = False

' Refresh all data / queries
ActiveWorkbook.RefreshAll

'Calculate should update all pivot tables
Calculate

' Append latest backlog to the backlog log
Sheets("2.7").Select
Range("A60:D73").Select
Selection.Copy
Sheets("Backlog Log").Select
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False
Sheets("2.7").Select
Range("A1").Select

' Resume screen updating
Application.ScreenUpdating = True
End Sub

Sheets("2.7").Select 语句是 Excel 崩溃的地方。

我尝试过的事情:

您可以看到我有一个实例，其中工作表选择是直接的，而另一个实例是命名范围，两者都失败了。

我尝试重命名工作表并更新代码以反射(reflect)新名称，但这些也失败了。

我已重置宏安全性以强制它重新询问/重新启用宏，但这没有影响。

我对这个看似微不足道的问题束手无策，但这些节省了足够的手动时间，我真的很想弄清楚它们。任何帮助或指示将不胜感激。

Answer 1

你可以为你的第一个潜艇试试这个。修改MailMe范围到您的预测范围。

Option Explicit

Sub ForeCast()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Weekly Forecast")

'Modify the below range to send
Dim MailMe As Range: Set MailMe = ws.Range("A10:A78")

Application.ScreenUpdating = False

With MailMe
    ThisWorkbook.EnvelopeVisible = True
    With MailEnvelope
        .Introduction = "See below for the weekly forecast. Thank you!"
             With .Item
                .To = "(redacted email address)"
                .Subject = "Weekly Forecast"
                .Send
            End With
    End With
End With

ThisWorkbook.EnvelopeVisible = False
Application.ScreenUpdating = True

End Sub

如果代码位于包含您的床单的书中，则以下内容应该有效。请注意，您永远不需要 .Select或 .Selection移动/添加/更改/删除单元格/范围/工作表/书。

Sub RefreshAllData()

Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("2.7")
Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets("Backlog Log")

Application.ScreenUpdating = False
    ThisWorkbook.RefreshAll
    ws1.Range("A60:D73").Copy
    ws2.Range("A" & ws2.Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValuesAndNumberFormats
Application.ScreenUpdating = True

End Sub