excel - 从不同的工作表调用子时抛出 400

Question

我收到一个非常奇怪的 400 错误，我无法解释。

我有两张床单

Reconciliation Reporting (指向子“ThisWorkbook.ImportRawData”的按钮

Trading Day Processes , 在其上实际发生导入，还有一个指向“ThisWorkbook.ImportRawData”的按钮

当我单击工作表 Trading Day Processes 中的按钮时一切正常，没有问题。当单击工作表中的按钮时 Reconciliation Reporting然后出现 400。

我已经追踪了错误。当我将这部分注释掉时，一切正常。我找不到这个 400 错误的原因，以下部分可能导致。

tradingDaySheet.Range(Cells(lastRow, 1), Cells(lastRow, 70)).Borders(xlEdgeTop).LineStyle = xlContinuous
tradingDaySheet.Range(Cells(lastRow, 1), Cells(lastRow, 70)).Borders(xlEdgeTop).Weight = xlThick
tradingDaySheet.Range(Cells(lastRow, 1), Cells(lastRow, 70)).Borders(xlEdgeBottom).LineStyle = xlContinuous
tradingDaySheet.Range(Cells(lastRow, 1), Cells(lastRow, 70)).Borders(xlEdgeBottom).Weight = xlThick
tradingDaySheet.Range(Cells(lastRow, 1), Cells(lastRow, 70)).Borders(xlEdgeLeft).LineStyle = xlContinuous
tradingDaySheet.Range(Cells(lastRow, 1), Cells(lastRow, 70)).Borders(xlEdgeLeft).Weight = xlThick
tradingDaySheet.Range(Cells(lastRow, 1), Cells(lastRow, 70)).Borders(xlEdgeRight).LineStyle = xlContinuous
tradingDaySheet.Range(Cells(lastRow, 1), Cells(lastRow, 70)).Borders(xlEdgeRight).Weight = xlThick 

ImportRawData sub (我已删除与此错误无关的代码。

Sub ImportRawData()
' main function to importing from _data into Trading Day Processes
    Dim Workbook As Workbook
    Set Workbook = ThisWorkbook
    Set tradingDaySheet = Workbook.Worksheets("Trading Day Processes")

    ' variable needed for importing data
    Dim i As Integer
    Dim m As Integer
    Dim TDcurrentRow As Long
    Dim DAnumDataRows As Integer
    Dim MANnumDataRows As Integer
    Dim TDstartRow As Long
    Dim TDendRow As Integer
    Dim currentDatai As Integer
    ' variable to check if a row was importet successfully
    Dim importStatus As Boolean

    ' set the starting row in the Trading Day Processes Sheet
    TDstartRow = 11
    TDcurrentRow = TDstartRow
    ' get the amount of rows to import
    DAnumDataRows = CountDataRows
    ' set the end row
    TDendRow = TDstartRow + DAnumDataRows

    ' get the mount of rows for manual entries
    MANnumDataRows = CountManualRows



    ' check if the sheet is clean otherwise throw message
    If IsEmpty(tradingDaySheet.Range("C11").Value) = True Then

         ' Import Automatic processes
        For i = 1 To DAnumDataRows
            importStatus = ImportNextRow(i, TDcurrentRow, False)
            TDcurrentRow = TDcurrentRow + 1
        Next i

        ' Import Manual processes
        For m = 1 To MANnumDataRows

            importStatus = ImportNextRow(m, TDcurrentRow, True)
            TDcurrentRow = TDcurrentRow + 1
        Next m

        ' Create End of Day Balance
        CreateEndOfDayRow (TDcurrentRow)

        ' Create P&L Sheet
        'CreatePandLReporting (TDstartRow,TDcurrentRow)

    Else
        MsgBox "The _data sheet has not been cleared. Please clean the sheet first."
    End If
    MsgBox "Import Done. Happy reconciling"

End Sub

Sub 调用函数 CreateEndOfDayRow()。我已经删除了一些与此错误无关的代码(否则太长):

Function CreateEndOfDayRow(lastRow As Long)
' The function creates the end of day balance after all intraday processes have been imported
    Dim Workbook As Workbook
    Set Workbook = ThisWorkbook
    Set dataSheet = Workbook.Worksheets("_data")
    Set tradingDaySheet = Workbook.Worksheets("Trading Day Processes")
    Dim startRow As Integer
    Dim startRowIncStartBalance As Integer
    Dim rowDiff As Integer
    startRowIncStartBalance = 10
    startRow = 11

    ' calc difference between first and last row for automatic formulas
    rowDiff = lastRow - startRow
    rowDiffIncStartBalance = lastRow - startRowIncStartBalance

    tradingDaySheet.Cells(lastRow, 1).Value = "EOD Balance"

    tradingDaySheet.Cells(lastRow, 70).NumberFormat = FormattingModule.FormatHelper("Percentage")


    ===== CUT OUT CODE =======

      ====>The following lines seem to cause the error

     ' put fat boarder around balances
     tradingDaySheet.Range(Cells(lastRow, 1), Cells(lastRow, 70)).Borders(xlEdgeTop).LineStyle = xlContinuous
     tradingDaySheet.Range(Cells(lastRow, 1), Cells(lastRow, 70)).Borders(xlEdgeTop).Weight = xlThick
     tradingDaySheet.Range(Cells(lastRow, 1), Cells(lastRow, 70)).Borders(xlEdgeBottom).LineStyle = xlContinuous
     tradingDaySheet.Range(Cells(lastRow, 1), Cells(lastRow, 70)).Borders(xlEdgeBottom).Weight = xlThick
     tradingDaySheet.Range(Cells(lastRow, 1), Cells(lastRow, 70)).Borders(xlEdgeLeft).LineStyle = xlContinuous
     tradingDaySheet.Range(Cells(lastRow, 1), Cells(lastRow, 70)).Borders(xlEdgeLeft).Weight = xlThick
     tradingDaySheet.Range(Cells(lastRow, 1), Cells(lastRow, 70)).Borders(xlEdgeRight).LineStyle = xlContinuous
     tradingDaySheet.Range(Cells(lastRow, 1), Cells(lastRow, 70)).Borders(xlEdgeRight).Weight = xlThick

     SetLastRow (lastRow)

End Function

也许这与错误使用工作表有关？如上所述，当从同一张表中调用 Sub 时，一切正常。

Answer 1

很可能是因为

tradingDaySheet.Range(Cells(lastRow, 1), Cells(lastRow, 70))....

所有这些 Cells Range引用被隐式限定为 ActiveSheet作为工作表引用，而您需要它是 tradingDaySheet
所以解决方案将使用显式 Worksheet引用您的 Range目的

tradingDaySheet.Range(tradingDaySheet.Cells(lastRow, 1), tradingDaySheet.Cells(lastRow, 70))....

等等

一种更优雅(更简洁)的方法是使用 With ... End With句法:

With tradingDaySheet ' reference wanted sheet object
    .Range(.Cells(lastRow, 1), .Cells(lastRow, 70)).... ' all object references beginning with a dot (`.`) are implicitly referencing the object in the `With` statement
    ....
End With

可以进一步推到:

With tradingDaySheet ' reference wanted sheet object
    With .Range(.Cells(LastRow, 1), .Cells(LastRow, 70)) ' reference referenced sheet Range object
        .Borders(xlEdgeTop).LineStyle = xlContinuous
        .Borders(xlEdgeTop).Weight = xlThick
        .Borders(xlEdgeBottom).LineStyle = xlContinuous
        .Borders(xlEdgeBottom).Weight = xlThick
        .Borders(xlEdgeLeft).LineStyle = xlContinuous
        .Borders(xlEdgeLeft).Weight = xlThick
        .Borders(xlEdgeRight).LineStyle = xlContinuous
        .Borders(xlEdgeRight).Weight = xlThick
    End With
End With