Calculating elapsed time between starting and closing excel

[mokhtar]

hi all
:think: 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


13244