Microsoft Office Forums - View Single Post - [Solved] VBA issue: calculated wrong seconds

yanyan9896 · 10-08-2023, 02:39 AM

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

I would greatly appreciate your assistance in editing the macro to ensure accurate calculation of seconds. I am currently facing an issue with the existing macro, as it does not provide correct calculations for seconds. Could you kindly help me in rectifying this issue? Your expertise in macro editing would be immensely valuable in resolving this problem. Thank you in advance for your support.