Calculating elapsed time between starting and closing excel

[mokhtar]

hi all
i'm try Calculating elapsed time between starting and closing excel with 2 codes

Private Sub Workbook_Open()

Dim StartTime As Double

'Remember time when macro starts
  StartTime = Timer

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim StartTime As Double
Dim SecondsElapsed As Double

'Determine how many minutes  from  starting  and closing excel 
MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")                 
'Notify user in minutes 

MsgBox " This worbook open  for " & MinutesElapsed & " minutes", vbInformation     

End Sub

Any suggestions.... my experience of macros is limited

Thanks in advance for all your help!!

[Paul_Hossler]

If you put this in the ThisWorkbook module it should do what you're looking for

Because you had each of your Dim's INSIDE the Sub/End Sub their scope would be limited to just that sub, even though the name is the same

By putting them outside the subs, but inside the module, the subs will share the variables

Option Explicit

'Outside the subs, but inside the module
Dim StartTime As Date
Dim EndTime As Date
Dim Elapsed As Double

Private Sub Workbook_Open()
    'Remember time when macro starts
    StartTime = Now
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    EndTime = Now
    Elapsed = EndTime - StartTime
    MsgBox "This worbook open  for " & Format(Elapsed, "hh:mm:ss") & " minutes", vbInformation + vbOKOnly
End Sub

[mokhtar]

This works perfectly
Thanks a lot Mr. Paul_hoosler for this great code and your explanation

is there a way to do that :

if Elapsed time < 60 seconds msgBox become to "This worbook open for hh:mm:ss seconds"

if Elapsed time > 60 seconds msgBox become to "This worbook open for hh:mm:ss minutes"
Thanks again !!

[Paul_Hossler]

A little more complicated, but I like the formatting better

Option Explicit
 
 'Outside the subs, but inside the module
Dim StartTime As Date
Dim EndTime As Date
Dim Elapsed As Double
 
Private Sub Workbook_Open()
     'Remember time when macro starts
    StartTime = Now
End Sub
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim iMinutes As Long
    Dim dblSeconds As Double
    EndTime = Now
    Elapsed = 86400 * (EndTime - StartTime)
    If Elapsed < 60 Then
        MsgBox "This workbook open  for " & Format(Elapsed, "#0.0") & " seconds", vbInformation + vbOKOnly
    Else
        iMinutes = Elapsed / 60
        dblSeconds = Elapsed - (60 * iMinutes)
        MsgBox "This workbook open  for " & Format(iMinutes, "#") & ":" & Format(dblSeconds, "00") & " minutes", vbInformation + vbOKOnly
    End If
    
End Sub

[hitball11a]

'm trying to email a section of a pivot table to my boss as requested. The goal is to send to more than one person just the part of the pivot table that concerns each individual.
I am very inexperienced with VBA and I want to start learning. My boss doesn't know how to do it so I really want to help him.

[mokhtar]

I'm sorry for being late in my reply ..
last code works perfectly too
Thanks a lot Mr. Paul_hoosler
Thanks for all your help!!

[Paul_Hossler]

@hitball11a --


It would be better to start your own thread for a new topic instead of adding to an existing one

More people will see the new topic

Use the [+Post New Topic] button at the top


Capture.JPG