excel - 未定义子或函数 System.Reflection.TargetInvocationException

Question

我目前正在使用自动运行 excel 宏的应用程序。测试时我收到这个：

运行宏 RefreshAllPMWS 时出错，组件名称：模块 1 === 错误消息：未定义子或函数 组件名称：模块2 === 错误消息：未定义子或函数 System.Reflection.TargetInvocationException：异常已由目标引发调用。---> System.IndexOutOfRangeException：来自 HRESULT 的异常：0x800A0009 --- 内部异常堆栈跟踪结束 --- 在 System.RuntimeType.InvokeDispMethod(String name, BindingFlags invokeAttr, Object target, Object[] args, Boolean[] byrefModifiers , Int32 区域性, String[] namedParameters) 在 System.RuntimeType.InvokeMember(字符串名称, BindingFlags bindingFlags, Binder binder, Object 目标, Object[] providedArgs, ParameterModifier[] 修饰符, CultureInfo 区域性, String[] namedParams)

以下是模块 1 中的代码：

Option Explicit

Public Sub Month_End()


' Month_End Macro
' Updates Formulas for YTD ME to include the most recent day's PnL
'

    Range("D15").Select
    ActiveCell.FormulaR1C1 = "=+SUM(RC[2]:RC[106])"
    Range("D15").Select
    Selection.AutoFill Destination:=Range("D15:D88")
    Range("D15:D88").Select
    Range("B12").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("B15").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-54
    Range("D15").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWindow.SmallScroll Down:=60
    Range("B85:B88").Select
    Selection.Copy
    Range("D85:D88").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("G88").Select

End Sub

这是模块 2 中的代码：

Option Explicit

Public Sub ImportPrices()

    Dim RawData As String
    Dim RawFile As String
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    RawData = Sheets("Prices").Range("O1")
    RawFile = Sheets("Prices").Range("O2")
        
   If Dir(RawData) <> "" Then
     
    Workbooks.Open Filename:=RawData
        Range("a:i").Copy
        ThisWorkbook.Activate
        Sheets("Prices").Range("A1").PasteSpecial
    
    Application.CutCopyMode = False
        Windows(RawFile).Close savechanges:=False
    Else
        MsgBox "Please make sure " & RawFile & " is in the Raw Data folder!"
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
        Calculate
        End
    End If
    
    Calculate
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    
End Sub

这是宏 RefreshAllPMWS：

Public Sub RefreshAllPMWS()
Dim objRange As Range
Dim objSheet As Worksheet

For Each objSheet In ThisWorkbook.Worksheets
    If InStr(1, objSheet.Range("A1").Value2, "<?") Then
        RefreshNormalSheet objSheet.Name
    End If
Next

Application.Calculate
RefreshAllPivotCache
Application.Calculate
ImportPrices

End Sub

这是 RefreshAllPivotCache 的代码

Public Sub RefreshAllPivotCache()
    Dim oCache As PivotCache
    Application.Calculate

    For Each oCache In ThisWorkbook.PivotCaches
        'do not refresh pricing profile if skipped
        oCache.Refresh
     Next
End Sub

标签： excelvba