#1
|
|||
|
|||
VBA issue: calculated wrong seconds
I am looking for some assistance with editing my VBA code. With the help of the expert here, I have a macro that can help me add hyperlinks to time markers in my document. Here are the steps that the macro needs to accomplish:
1.Ask for meeting ID and allow user input through an inputbox. 2.Find all six-digit numbers (time markers) using the Mid function. 3.Convert the hours, minutes, and seconds obtained from the time markers to integers using the CInt function. 4.Calculate the starting time by multiplying hours by 3600, minutes by 60, and adding them together. 5.Loop through each time marker and add a hyperlink using the ActiveDocument.Hyperlinks.Add code, with the meeting ID and starting time as parameters. However, it will calculate the wrong seconds. For example, 000234, it should be 00 = hour, 02 = mins, 34 = seconds, then I just need the seconds, the answer should be 3600*0 + 2*60+34 = 154, but the macro will calculate as "1414". Here is the code for the macro: Code:
Sub AddHyperlinksToTimeMarkers() Dim mtgID As String, aRng As Range Dim hrs As Integer, mins As Integer, secs As Integer, startTime As Long mtgID = InputBox("Enter meeting ID") ' Ask for meeting ID ' Find all six-digit numbers (time markers) Set aRng = ActiveDocument.Range With aRng.Find .ClearFormatting .Text = "[0-9]{6}" .MatchWildcards = True ' Loop through each time marker and add hyperlink Do While .Execute ' Get hours, minutes, and seconds from time marker hrs = CInt(Left(aRng.Text, 2)) mins = CInt(Mid(aRng.Text, 4, 2)) secs = CInt(Right(aRng.Text, 2)) startTime = hrs * 3600 + mins * 60 + secs ' Calculate starting time ' Add hyperlink to time marker ActiveDocument.Hyperlinks.Add Anchor:=aRng, Address:="https://ABC?meetingid=" & mtgID & "&start=" & startTime aRng.Collapse Direction:=wdCollapseEnd Loop End With End Sub |
#2
|
||||
|
||||
As per your other thread, the mins calculation is starting at the 4th digit instead of the 3rd, so it was deciding that there were 23 minutes because they were the 4th and 5th digits. Change the 4 to a 3
mins = CInt(Mid(aRng.Text, 3, 2))
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#3
|
|||
|
|||
Quote:
Thank you for pointing out the issue with the "mins" line in the provided code. I apologize for not checking it thoroughly. I appreciate your suggestion regarding debugging. Stepping through the code and examining the variable values after execution will indeed help identify and resolve such issues in the future. Once again, thank you for your keen observation and valuable input. I truly appreciate your assistance. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Point out wrong responses (A determined B-D, based on A how to highlight wrong response in B-D) | tdeogburn | Excel | 3 | 01-28-2022 01:07 AM |
How to set up a timeline with units of 0.1 seconds | gary@gathen.net | Project | 0 | 09-13-2017 10:33 PM |
Outlook 2013 Messages can sit in the Outbox anywhere from 5 seconds up to 60 seconds | BNS1968 | Outlook | 0 | 09-16-2016 08:07 PM |
Project 2013 - Issue with Resource Usage showing wrong dates | michaelwex | Project | 1 | 02-11-2016 08:41 AM |
16 seconds | Scott@Geeks | Word | 0 | 02-24-2009 01:06 PM |