Nate_
04-27-2021, 12:13 PM
Hi,
So I wrote/copied a function to convert decimal hours to hours minutes and second and I get my result as a string looking like this: "10h30m30s". How do I superscript the letters in the function?
I managed to do it in a normal sub macro with the code: [ActiveCell.Characters(Start:=4, Length:=1).Font.Superscript = True] but in my function it doesn't work.
Here is my Function:
Function Convert_Into_HMS(Decimal_Deg) As String
Dim hours
Dim minutes
Dim seconds
If Decimal_Deg >= 0 Then
With Application
hours = Int(Decimal_Deg)
minutes = (Decimal_Deg - hours) * 60
seconds = Format(((minutes - Int(minutes)) * 60), "00.0")
Convert_Into_HMS = " " & hours & "h" & Format(Int(minutes), "00") & "m" & seconds & "s"
End With
ElseIf Decimal_Deg > -1 Then
With Application
hours = Left(Decimal_Deg, 2)
minutes = (Decimal_Deg + hours) * -60
seconds = Format(((minutes - Int(minutes)) * 60), "00.0")
Convert_Into_HMS = " " & hours & "h" & Format(Int(minutes), "00") & "m" & seconds & "s"
End With
Else
With Application
hours = Left(Decimal_Deg, 3)
minutes = (Decimal_Deg - Int(hours)) * -60
seconds = Format(((minutes - Int(minutes)) * 60), "00.0")
Convert_Into_HMS = " " & hours & "h" & Format(Int(minutes), "00") & "m" & seconds & "s"
End With
End If
Dim Colin As String
Colin = Convert_Into_HMS
Dim this As Integer
this = InStr(1, Colin, "h")
Dim should As Integer
should = InStr(1, Colin, "m")
Dim work As Integer
work = InStr(1, Colin, "s")
ActiveCell.Characters(Start:=this, Length:=1).Font.Superscript = True
ActiveCell.Characters(Start:=should, Length:=1).Font.Superscript = True
ActiveCell.Characters(Start:=work, Length:=1).Font.Superscript = True
End Function
As you can see I'm trying to superscript at the end of the function, is there a way to do it right away in the code that defines the function? ( Convert_Into_HMS = " " & hours & "h" & Format(Int(minutes), "00") & "m" & seconds & "s")
And also I Apologize if my coding is a bit awkward, this is my first encounter with VBA.
Thanks and Cheers.
So I wrote/copied a function to convert decimal hours to hours minutes and second and I get my result as a string looking like this: "10h30m30s". How do I superscript the letters in the function?
I managed to do it in a normal sub macro with the code: [ActiveCell.Characters(Start:=4, Length:=1).Font.Superscript = True] but in my function it doesn't work.
Here is my Function:
Function Convert_Into_HMS(Decimal_Deg) As String
Dim hours
Dim minutes
Dim seconds
If Decimal_Deg >= 0 Then
With Application
hours = Int(Decimal_Deg)
minutes = (Decimal_Deg - hours) * 60
seconds = Format(((minutes - Int(minutes)) * 60), "00.0")
Convert_Into_HMS = " " & hours & "h" & Format(Int(minutes), "00") & "m" & seconds & "s"
End With
ElseIf Decimal_Deg > -1 Then
With Application
hours = Left(Decimal_Deg, 2)
minutes = (Decimal_Deg + hours) * -60
seconds = Format(((minutes - Int(minutes)) * 60), "00.0")
Convert_Into_HMS = " " & hours & "h" & Format(Int(minutes), "00") & "m" & seconds & "s"
End With
Else
With Application
hours = Left(Decimal_Deg, 3)
minutes = (Decimal_Deg - Int(hours)) * -60
seconds = Format(((minutes - Int(minutes)) * 60), "00.0")
Convert_Into_HMS = " " & hours & "h" & Format(Int(minutes), "00") & "m" & seconds & "s"
End With
End If
Dim Colin As String
Colin = Convert_Into_HMS
Dim this As Integer
this = InStr(1, Colin, "h")
Dim should As Integer
should = InStr(1, Colin, "m")
Dim work As Integer
work = InStr(1, Colin, "s")
ActiveCell.Characters(Start:=this, Length:=1).Font.Superscript = True
ActiveCell.Characters(Start:=should, Length:=1).Font.Superscript = True
ActiveCell.Characters(Start:=work, Length:=1).Font.Superscript = True
End Function
As you can see I'm trying to superscript at the end of the function, is there a way to do it right away in the code that defines the function? ( Convert_Into_HMS = " " & hours & "h" & Format(Int(minutes), "00") & "m" & seconds & "s")
And also I Apologize if my coding is a bit awkward, this is my first encounter with VBA.
Thanks and Cheers.