Changes

Excel Macros for pace

844 bytes removed, 21:25, 7 February 2010
m
no edit summary
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. 1 Open Excel 2 If you don’t see a tab marked ‘developer’  http://jfsavage.smugmug.com/photos/783147707_je8uM783147705_JdKMk-O.jpg 2.1 Click on the round ‘window’ button in the very top left of the Excel window
http://jfsavage.smugmug.com/photos/783147634_9aYsj-O.jpg
http://jfsavage2.smugmug.com/photos/783141751_GX4AM-O.jpg2 Select ‘Excel Options’
http://jfsavage.smugmug.com/photos/783147754_WpFmi783147707_je8uM-O.jpg
http://jfsavage2.smugmug.com/photos/783147770_SoM3Q-O.jpg3 Click on the ‘Popular’ and select the check box ‘Show developer tab in the Ribbon’
http://jfsavage.smugmug.com/photos/783147748_YD3UM783147634_9aYsj-O.jpg
http://jfsavage2.smugmug.com/photos/783147761_e6qYL-O.jpg4 Click OK
3 Click on the developer tab in the ribbon
http://jfsavage.smugmug.com/photos/783147705_JdKMk-O.jpg
4 Click on the ‘Visual Basic’ button
http://jfsavage.smugmug.com/photos/783147634_9aYsj783147714_FvrH7-O.jpg
5 Right click on the VBAProject, select insert, then module
http://jfsavage.smugmug.com/photos/783147695_Yx9qc783141751_GX4AM-O.jpg
http://jfsavage.smugmug.com/photos/783147707_je8uM-O.jpg6 Paste the macro shown below into the text window
http://jfsavage.smugmug.com/photos/783147714_FvrH7783147748_YD3UM-O.jpg
 ==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# 7 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.
 http://jfsavage.smugmug.com/photos/783147754_WpFmi-O.jpg  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) http://jfsavage.smugmug.com/photos/783147770_SoM3Q-O.jpg 
==The Macros==
 
Function S2HMS(TotalSeconds)
Dim Hours As Long
End Function
Function S2MS(TotalSeconds)
Dim minutes As Integer
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
End Function
Function PaceToMPH(PaceStr)
Dim PaceParts() As String