Difference between revisions of "Excel Macros for pace"

From Fellrnr.com, Running tips
Jump to: navigation, search
User:Mediawiki (User talk:Mediawiki | contribs)
m
User:Mediawiki (User talk:Mediawiki | contribs)
m
Line 1: Line 1:
 
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 ;}
 
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 ;}
  
http://jfsavage.smugmug.com/photos/783147707_je8uM-O.jpg
+
==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/783147705_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://jfsavage.smugmug.com/photos/783147634_9aYsj-O.jpg
  
http://jfsavage.smugmug.com/photos/783141751_GX4AM-O.jpg
+
2.2 Select ‘Excel Options’
  
http://jfsavage.smugmug.com/photos/783147754_WpFmi-O.jpg
+
http://jfsavage.smugmug.com/photos/783147707_je8uM-O.jpg
  
http://jfsavage.smugmug.com/photos/783147770_SoM3Q-O.jpg
+
2.3 Click on the ‘Popular’ and select the check box ‘Show developer tab in the Ribbon’
  
http://jfsavage.smugmug.com/photos/783147748_YD3UM-O.jpg
+
http://jfsavage.smugmug.com/photos/783147634_9aYsj-O.jpg
  
http://jfsavage.smugmug.com/photos/783147761_e6qYL-O.jpg
+
2.4 Click OK
  
 +
3 Click on the developer tab in the ribbon
  
 
http://jfsavage.smugmug.com/photos/783147705_JdKMk-O.jpg
 
http://jfsavage.smugmug.com/photos/783147705_JdKMk-O.jpg
  
 +
4 Click on the ‘Visual Basic’ button
  
http://jfsavage.smugmug.com/photos/783147634_9aYsj-O.jpg
+
http://jfsavage.smugmug.com/photos/783147714_FvrH7-O.jpg
  
 +
5 Right click on the VBAProject, select insert, then module
  
http://jfsavage.smugmug.com/photos/783147695_Yx9qc-O.jpg
+
http://jfsavage.smugmug.com/photos/783141751_GX4AM-O.jpg
  
http://jfsavage.smugmug.com/photos/783147707_je8uM-O.jpg
+
6 Paste the macro shown below into the text window
  
http://jfsavage.smugmug.com/photos/783147714_FvrH7-O.jpg
+
http://jfsavage.smugmug.com/photos/783147748_YD3UM-O.jpg
  
 
+
7 Click on File, then ‘Close and return to Microsoft Excel’
==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==
 
==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.  
 
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)
+
 
 +
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==
 
==The Macros==
 +
 
  Function S2HMS(TotalSeconds)
 
  Function S2HMS(TotalSeconds)
 
     Dim Hours As Long
 
     Dim Hours As Long
Line 68: Line 78:
 
      
 
      
 
  End Function
 
  End Function
 
+
 
  Function S2MS(TotalSeconds)
 
  Function S2MS(TotalSeconds)
 
     Dim minutes As Integer
 
     Dim minutes As Integer
Line 83: Line 93:
 
      
 
      
 
  End Function
 
  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)
 
  Function HMS2S(HMSStr)
 
     Dim TimeParts() As String
 
     Dim TimeParts() As String
Line 133: Line 114:
 
          
 
          
 
  End Function
 
  End Function
 
+
 
  Function PaceToMPH(PaceStr)
 
  Function PaceToMPH(PaceStr)
 
     Dim PaceParts() As String
 
     Dim PaceParts() As String

Revision as of 17:25, 7 February 2010

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’

783147705_JdKMk-O.jpg

2.1 Click on the round ‘window’ button in the very top left of the Excel window

783147634_9aYsj-O.jpg

2.2 Select ‘Excel Options’

783147707_je8uM-O.jpg

2.3 Click on the ‘Popular’ and select the check box ‘Show developer tab in the Ribbon’

783147634_9aYsj-O.jpg

2.4 Click OK

3 Click on the developer tab in the ribbon

783147705_JdKMk-O.jpg

4 Click on the ‘Visual Basic’ button

783147714_FvrH7-O.jpg

5 Right click on the VBAProject, select insert, then module

783141751_GX4AM-O.jpg

6 Paste the macro shown below into the text window

783147748_YD3UM-O.jpg

7 Click on File, then ‘Close and return to Microsoft Excel’

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.

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)

783147770_SoM3Q-O.jpg


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