Difference between revisions of "Excel Macros for pace"
User:Mediawiki (User talk:Mediawiki | contribs) |
User:Fellrnr (User talk:Fellrnr | contribs) m |
||
(2 intermediate revisions by 2 users not shown) | |||
Line 8: | Line 8: | ||
2 If you don’t see a tab marked ‘developer’ | 2 If you don’t see a tab marked ‘developer’ | ||
− | + | [[File:783147705 JdKMk-O.jpg]] | |
2.1 Click on the round ‘window’ button in the very top left of the Excel window | 2.1 Click on the round ‘window’ button in the very top left of the Excel window | ||
− | + | [[File:783147634 9aYsj-O.jpg]] | |
2.2 Select ‘Excel Options’ | 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’ | 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 | 2.4 Click OK | ||
Line 26: | Line 26: | ||
3 Click on the developer tab in the ribbon | 3 Click on the developer tab in the ribbon | ||
− | + | [[File:783147705 JdKMk-O.jpg]] | |
4 Click on the ‘Visual Basic’ button | 4 Click on the ‘Visual Basic’ button | ||
− | + | [[File:783147714 FvrH7-O.jpg]] | |
5 Right click on the VBAProject, select insert, then module | 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. | 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’ | 7 Click on File, then ‘Close and return to Microsoft Excel’ | ||
Line 45: | Line 45: | ||
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. | 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. | ||
− | + | [[File:783147754 WpFmi-O.jpg]] | |
Line 52: | Line 52: | ||
If you wanted to add five seconds per mile, use =S2MS(HMS2S(B2)+5) | If you wanted to add five seconds per mile, use =S2MS(HMS2S(B2)+5) | ||
− | + | [[File:783147770 SoM3Q-O.jpg]] | |
==Saving a file with Macros== | ==Saving a file with Macros== | ||
To save an excel file that contains macros, you have to save it as a '.xslm' file. | 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== | ==Loading a file containing macros== | ||
Line 64: | Line 64: | ||
If you do not enable the content, the macros will result in an Excel error '#NAME?' | If you do not enable the content, the macros will result in an Excel error '#NAME?' | ||
− | + | [[File:786107613 kSYvf-O.jpg]] | |
==Troubleshooting== | ==Troubleshooting== | ||
Line 70: | Line 70: | ||
If you get an error saying #VALUE? like this | 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. | 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. | ||
Line 164: | 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 ;}
Contents
1 Installation
These instructions for installing on Excel 2007.
1 Open Excel
2 If you don’t see a tab marked ‘developer’
2.1 Click on the round ‘window’ button in the very top left of the Excel window
2.2 Select ‘Excel Options’
2.3 Click on the ‘Popular’ and select the check box ‘Show developer tab in the Ribbon’
2.4 Click OK
3 Click on the developer tab in the ribbon
4 Click on the ‘Visual Basic’ button
5 Right click on the VBAProject, select insert, then module
6 Copy and paste the macros shown below into the text window. You can copy one, some or all of the macros.
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.
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)
3 Saving a file with Macros
To save an excel file that contains macros, you have to save it as a '.xslm' file.
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?'
5 Troubleshooting
If you get an error saying #VALUE? like this
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!