CODEWITHSUNDEEP
excelvbaexcel-2007
query-01
query-01

Reputation: 11

Procedure breaks/stops at Application.WorksheetFunction.Average()

Edition Windows 10 Pro

Version 21H2
OS build 19044.1586
Excel 2007, VBA: Retail 6.5.1057 - Forms3: 12.0.6723.500

The concern:

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

When the above code is used 'manually' (F5), in the module below, it works. However, when the 'timer' triggers/engages, the procedure breaks/stops dead at that line.

I've spread the code over multiple procedures in an effort to troubleshoot; didn't help my cause.

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

Upvotes: 1

Views: 132

Answers (3)

query-01
query-01

Reputation: 11

The actual fix to the original 'posted\broken' code is to change --

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

-- To --

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

While\When using the erroneous line

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

if and when the ActiveSheet --was any other sheet than

Sheets("SUMMARY")

the project/procedure would try and apply

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

to that ActiveSheet, and not to

Sheets("SUMMARY")

which the ActiveSheet, when not Sheets("SUMMARY"), did not have data in the specified [ irng ] that [ Average() ] could process. <-- That is why the code failed. By making the change to

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

this is specifying 'exactly' which sheet [ irng ] should be [ Set ] to. Something that

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

did not accomplish.

Thank you all for your contributions.

Upvotes: 0

VBasic2008
VBasic2008

Reputation: 54767

Application.OnTime

  • If this shouldn't happen in the workbook containing this code, replace all occurrences of ThisWorkbook with the correct workbook e.g. 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

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166126

Try this:

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

Upvotes: 0

Related Questions