Changes

Excel Macros for pace

3,764 bytes added, 22:58, 6 February 2010

Created page with 'Excel Macros are very powerful, but can be dangerous. Never execute macros from an untrusted source or that you have not reviewed the code! This is page is intended for the techn…'

Excel Macros are very powerful, but can be dangerous. Never execute macros from an untrusted source or that you have not reviewed the code! This is page is intended for the technically proficient ;}

==Installation==
These instructions for installing on Excel 2007.
# Open Excel
# If you don’t see a tab marked ‘developer’
## Click on the round ‘window’ button in the very top left of the Excel window and select ‘Excel Options’
## Click on the ‘Popular’ tab
## Select the check box ‘Show developer tab in the Ribbon’
## Click OK
# Click on the developer tab in the ribbon
# Click on the ‘Visual Basic’ button
# Paste the macro shown below into the text window
# Click on File, then ‘Close and return to Microsoft Excel’

==Usage==
Excel will interpret a pace as a time by default, so entering 7:40 will become 7:40:00 AM, which will mess things up. Enter any paces with a preceding quotation mark such as ‘7:40 to make it textual.
To convert a pace to the number of seconds use =HMS2S(cell), such as =HMS2S(B1). To convert seconds to a pace use S2MS for minutes:seconds, or S2HMS for hours:minutes:seconds. If you wanted to add five seconds per mile, use =S2MS(HMS2S(b1)+5)

==The Macros==
Function S2HMS(TotalSeconds)
Dim Hours As Long
Dim minutes As Long
Dim Seconds As Long
Seconds = TotalSeconds

Hours = Seconds / (60 * 60)
If Hours > Seconds / (60 * 60) Then
Hours = Hours - 1
End If
Seconds = Seconds - (Hours * (60 * 60))

minutes = Seconds / (60)
If minutes > Seconds / (60) Then
minutes = minutes - 1
End If
Seconds = Seconds - (minutes * (60))

S2HMS = Format(Hours) + ":" + Format(minutes, "00") + ":" + Format(Seconds, "00")

End Function

Function S2MS(TotalSeconds)
Dim minutes As Integer
Dim Seconds As Integer
Seconds = TotalSeconds

minutes = Seconds / (60)
If minutes > Seconds / (60) Then
minutes = minutes - 1
End If
Seconds = Seconds - (minutes * (60))

S2MS = Format(minutes) + ":" + Format(Seconds, "00")

End Function

Function S2MST(TotalSeconds)
Dim Hours As Integer
Dim minutes As Integer
Dim Seconds As Integer
Seconds = TotalSeconds

Hours = Seconds / (60 * 60)
If Hours > Seconds / (60 * 60) Then
Hours = Hours - 1
End If
Seconds = Seconds - (Hours * (60 * 60))

minutes = Seconds / (60)
If minutes > Seconds / (60) Then
minutes = minutes - 1
End If
Seconds = Seconds - (minutes * (60))

If Hours = 0 Then
S2MST = Format(minutes) + " " + Format(Seconds, "00")
ElseIf Hours = 1 Then
S2MST = Format(minutes) + "." + Format(Seconds, "00")
ElseIf Hours = 2 Then
S2MST = Format(minutes) + ":" + Format(Seconds, "00")
Else
S2MST = Format(minutes) + "+" + Format(Seconds, "00")
End If
End Function

Function HMS2S(HMSStr)
Dim TimeParts() As String
TimeParts = Split(HMSStr.Text, ":")
Dim Hours As Long
Dim minutes As Long
Dim Seconds As Long
Dim Offset As Long
Offset = 0
Hours = 0

If UBound(TimeParts) > 1 Then
Hours = TimeParts(0)
Offset = 1
End If

minutes = TimeParts(Offset)
Seconds = TimeParts(Offset + 1)
HMS2S = (Hours * 60 * 60) + (minutes * 60) + Seconds

End Function

Function PaceToMPH(PaceStr)
Dim PaceParts() As String
PaceParts = Split(PaceStr.Text, ":", 2)
Dim minutes As Integer
Dim Seconds As Integer
Dim mph As Double
Dim Pace As Double

minutes = PaceParts(0)
Seconds = PaceParts(1)
Pace = minutes * 60 + Seconds
mph = 60 * 60 * (1 / Pace)
PaceToMPH = mph

End Function

• Newer edit →

Mediawiki

Bureaucrat, administrator

60

edits