# Difference between revisions of "Excel Macros for pace"

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 ;}

## 1 Installation

These instructions for installing on Excel 2007.

1. Open Excel
2. If you don’t see a tab marked ‘developer’
1. Click on the round ‘window’ button in the very top left of the Excel window and select ‘Excel Options’
2. Click on the ‘Popular’ tab
3. Select the check box ‘Show developer tab in the Ribbon’
4. Click OK
3. Click on the developer tab in the ribbon
4. Click on the ‘Visual Basic’ button
5. Paste the macro shown below into the text window

## 2 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)

## 3 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
```