Difference between revisions of "Excel Macros for pace"

From Fellrnr.com, Running tips
Jump to: navigation, search
User:Mediawiki (User talk:Mediawiki | contribs)
(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…')
 
User:Fellrnr (User talk:Fellrnr | contribs)
m
 
(5 intermediate revisions by 2 users not shown)
Line 3: Line 3:
 
==Installation==
 
==Installation==
 
These instructions for installing on Excel 2007.
 
These instructions for installing on Excel 2007.
# Open Excel
+
 
# If you don’t see a tab marked ‘developer’
+
1 Open Excel
## Click on the round ‘window’ button in the very top left of the Excel window and select ‘Excel Options’
+
 
## Click on the ‘Popular’ tab
+
2 If you don’t see a tab marked ‘developer’  
## Select the check box ‘Show developer tab in the Ribbon’
+
 
## Click OK
+
[[File:783147705 JdKMk-O.jpg]]
# Click on the developer tab in the ribbon
+
 
# Click on the ‘Visual Basic’ button  
+
2.1 Click on the round ‘window’ button in the very top left of the Excel window  
# Paste the macro shown below into the text window
+
 
# Click on File, then ‘Close and return to Microsoft Excel’
+
[[File:783147634 9aYsj-O.jpg]]
 +
 
 +
2.2 Select ‘Excel Options’  
 +
 
 +
[[File:783147707 je8uM-O.jpg]]
 +
 
 +
2.3 Click on the ‘Popular’ and select the check box ‘Show developer tab in the Ribbon’  
 +
 
 +
[[File:783147634 9aYsj-O.jpg]]
 +
 
 +
2.4 Click OK
 +
 
 +
3 Click on the developer tab in the ribbon  
 +
 
 +
[[File:783147705 JdKMk-O.jpg]]
 +
 
 +
4 Click on the ‘Visual Basic’ button  
 +
 
 +
[[File:783147714 FvrH7-O.jpg]]
 +
 
 +
5 Right click on the VBAProject, select insert, then module
 +
 
 +
[[File:783141751 GX4AM-O.jpg]]
 +
 
 +
6 Copy and paste the macros shown below into the text window. You can copy one, some or all of the macros.
 +
 
 +
[[File:783147748 YD3UM-O.jpg]]
 +
 
 +
7 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:00 will become 7:00:00 AM, which will mess things up. Enter any paces with a preceding quotation mark such as ‘7:00 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)
+
 
 +
[[File:783147754 WpFmi-O.jpg]]
 +
 
 +
 
 +
To convert a pace to the number of seconds use =HMS2S(cell), such as =HMS2S(B2). 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(B2)+5)
 +
 
 +
[[File:783147770 SoM3Q-O.jpg]]
 +
 
 +
==Saving a file with Macros==
 +
To save an excel file that contains macros, you have to save it as a '.xslm' file.
 +
 
 +
[[File:786106685 MJMUP-O.jpg]]
 +
 
 +
==Loading a file containing macros==
 +
By default, macros are disabled when you load an excel file. You will see a security warning with an options box. To enable the macros, click on 'options' and select 'enable this content'.
 +
 
 +
If you do not enable the content, the macros will result in an Excel error '#NAME?'
 +
 
 +
[[File:786107613 kSYvf-O.jpg]]
 +
 
 +
==Troubleshooting==
 +
 
 +
If you get an error saying #VALUE? like this
 +
 
 +
[[File:786110756 4dKCW-O.jpg]]
 +
 
 +
The problem is probably that you have the wrong data type. In the case above, the macro S2MS (seconds to Minutes:Seconds) requires an integer number of seconds, not a 'minutes:seconds' value. Equally MS2S requires Minutes:Seconds to work correctly.
 +
 
 +
==Brief Documentation==
 +
{| {{table}}
 +
|-
 +
| Macro || Usage
 +
|-
 +
|S2HMS ||Convert number of seconds to hours:minutes:seconds
 +
|-
 +
|S2MS ||Convert number of seconds to minutes:seconds
 +
|-
 +
|HMS2S||Convert hours:minutes:seconds or minutes:seconds to number of seconds
 +
|-
 +
|PaceToMPH||Convert minutes:seconds as minutes/mile to miles per hour
 +
|}
 +
 
  
 
==The Macros==
 
==The Macros==
 +
 
  Function S2HMS(TotalSeconds)
 
  Function S2HMS(TotalSeconds)
 
     Dim Hours As Long
 
     Dim Hours As Long
Line 54: Line 126:
 
     S2MS = Format(minutes) + ":" + Format(Seconds, "00")
 
     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
 
  End Function
  
Line 121: Line 164:
 
          
 
          
 
  End Function
 
  End Function
 +
 +
==Credits==
 +
Many thanks to Mark E for his hard work in testing these macros and these instructions!
 +
==See Also==
 +
* [[Excel Training Log]]

Latest revision as of 07:11, 3 January 2012

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 Copy and paste the macros shown below into the text window. You can copy one, some or all of the macros.

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:00 will become 7:00:00 AM, which will mess things up. Enter any paces with a preceding quotation mark such as ‘7:00 to make it textual.

783147754 WpFmi-O.jpg


To convert a pace to the number of seconds use =HMS2S(cell), such as =HMS2S(B2). 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(B2)+5)

783147770 SoM3Q-O.jpg

3 Saving a file with Macros

To save an excel file that contains macros, you have to save it as a '.xslm' file.

786106685 MJMUP-O.jpg

4 Loading a file containing macros

By default, macros are disabled when you load an excel file. You will see a security warning with an options box. To enable the macros, click on 'options' and select 'enable this content'.

If you do not enable the content, the macros will result in an Excel error '#NAME?'

786107613 kSYvf-O.jpg

5 Troubleshooting

If you get an error saying #VALUE? like this

786110756 4dKCW-O.jpg

The problem is probably that you have the wrong data type. In the case above, the macro S2MS (seconds to Minutes:Seconds) requires an integer number of seconds, not a 'minutes:seconds' value. Equally MS2S requires Minutes:Seconds to work correctly.

6 Brief Documentation

Macro Usage
S2HMS Convert number of seconds to hours:minutes:seconds
S2MS Convert number of seconds to minutes:seconds
HMS2S Convert hours:minutes:seconds or minutes:seconds to number of seconds
PaceToMPH Convert minutes:seconds as minutes/mile to miles per hour


7 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

8 Credits

Many thanks to Mark E for his hard work in testing these macros and these instructions!

9 See Also