Excel2007，VBA：零售6.5.1057——Application.WorksheetFunction.Average()

抱歉，格式與我的瀏覽器不合作。

版 Windows 10 專業版

版本 21H2 作業系統構建 19044.1586 Excel 2007，VBA：零售 6.5.1057 - Forms3：12.0.6723.500

關注點：

Sheets("SUMMARY").Cells(Rows.Count, 3).End(xlUp).Offset(1, 0) = _
                        Application.WorksheetFunction.Average(irng)

當上面的代碼被“手動”（F5）使用時，在下面的模塊中，它每次都能完美運行。但是，當“計時器”觸發/啟用時，程式會在該行中斷/停止。

我不知道如何解決這個問題。我已經將代碼分布在多個程序中以進行故障排除；根本沒有幫助我的事業。

尊敬。

    Option Explicit
    Public RunWhen As Double
    Public Const cRunWhat = "kcal"  ' the name of the procedure to run
    Sub StartTimer()
    RunWhen = TimeSerial(23, 45, 0)
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
        Schedule:=True
    End Sub

    Private Sub kcal()
    Sheets("SUMMARY").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = Date
    Sheets("SUMMARY").Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Value = Sheets("TODAY_(24hr)").Range("E40").Value

    kcal2
    End Sub

    Sub kcal2()
    Dim i As String
    Dim irng As Range
    i = Sheets("SUMMARY").Cells(Rows.Count, 2).End(xlUp).Address
    Set irng = Range("B2:" & i)

    Sheets("SUMMARY").Cells(Rows.Count, 3).End(xlUp).Offset(1, 0) = Application.WorksheetFunction.Average(irng)
 
    ActiveWorkbook.SaveCopyAs Filename:=ActiveWorkbook.Path & "\Back_Up\Bak-Up_" & Format(Now, "yyyymmdd") & "_m" & ActiveWorkbook.Name

    StartTimer


    End Sub

uj5u.com熱心網友回復：

應用程式.OnTime

• 如果在包含此代碼的作業簿中不應該發生這種情況，請將所有出現的 替換ThisWorkbook為正確的作業簿，例如ActiveWorkbook.

Option Explicit

Public RunWhen As Double
Public Const cRunWhat = "kcal"  ' the name of the procedure to run

Sub StartTimer()
    RunWhen = TimeSerial(23, 45, 0)
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
        Schedule:=True
End Sub

Private Sub kcal()
    Dim sws As Worksheet: Set sws = ThisWorkbook.Worksheets("TODAY_(24hr)")
    Dim dws As Worksheet: Set dws = ThisWorkbook.Worksheets("SUMMARY")
    dws.Cells(dws.Rows.Count, "A").End(xlUp).Offset(1, 0).Value = Date
    dws.Cells(dws.Rows.Count, "B").End(xlUp).Offset(1, 0).Value _
        = sws.Range("E40").Value
    kcal2
End Sub

Sub kcal2()
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("SUMMARY")
    Dim lRow As Long: lRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    Dim irng As Range: Set irng = ws.Range("B2:B" & lRow)

    ws.Cells(ws.Rows.Count, "C").End(xlUp).Offset(1, 0).Value _
        = Application.WorksheetFunction.Aggregate(1, 6, irng)
    
    Dim FolderPath As String: FolderPath = ThisWorkbook.Path & "\Back_Up\"
    If Len(Dir(FolderPath, vbDirectory)) = 0 Then MkDir FolderPath
    
    ThisWorkbook.SaveCopyAs Filename:=FolderPath & "Bak-Up_" _
        & Format(Now, "yyyymmdd") & "_m" & ThisWorkbook.Name

    StartTimer

End Sub

uj5u.com熱心網友回復：

嘗試這個：

Sub kcal2()
    
    Dim irng As Range
    
    With Sheets("SUMMARY")  'make sure all ranges are tied to a specific sheet
         Set irng = .Range("B2:B" & .Cells(.Rows.Count, 2).End(xlUp).Row)
         .Cells(.Rows.Count, 3).End(xlUp).Offset(1, 0) = _
                        Application.WorksheetFunction.Average(irng)
    End With
    ActiveWorkbook.SaveCopyAs Filename:=ActiveWorkbook.Path & _
        "\Back_Up\Bak-Up_" & Format(Now, "yyyymmdd") & "_m" & ActiveWorkbook.Name

    StartTimer

End Sub

uj5u.com熱心網友回復：

對原始“已發布\損壞”代碼的實際修復是更改 -

Set irng = Range("B2:" & i)

- 到 -

 Set irng = Sheets("SUMMARY").Range("B2:" & i)    

While\當使用錯誤行時

Set irng = Range("B2:" & i)    

如果以及何時 ActiveSheet -- 是任何其他作業表

Sheets("SUMMARY")    

該專案/程式將嘗試并應用

Sheets("SUMMARY").Cells(Rows.Count, 3).End(xlUp).Offset(1, 0) = Application.WorksheetFunction.Average(irng)   

到那個 ActiveSheet，而不是

Sheets("SUMMARY")    

ActiceSheet，當不是 Sheets("SUMMARY") 時，在指定的 [irng] 中沒有 [Average()] 可以處理的資料。<--這就是代碼失敗的原因。通過將更改為

Set irng = Sheets("SUMMARY").Range("B2:" & i)    

這是指定“確切”哪個表[irng]應該是[Set]。一些東西

Set irng = Range("B2:" & i)    

沒有完成。

謝謝大家的貢獻。

標籤：擅长 vba excel-2007

上一篇：VBA從Outlook中獲取電子郵件太慢了

下一篇：Gradle TestNG：如何列出單元測驗涉及的類？