Need VBA to show values after decimal place as numbers, when 'spelling' numbers

[Ochimus]

Apologies if this has been done here before, but could not find it if it was.

K5 contains values, which may or may not have decimal places.

If there are decimal places, need to retain them as "(number)" &"/100" when converting the integer into words

Adapted the attached Function suite from Stack Overflow, but can't see why it is not doing that?

Option Explicit

Function SpellNumber(ByVal MyNumber)

Dim Dollars, Cents, Temp
Dim DecimalPlace, Count
    
    ReDim Place(9) As String
    Place(2) = " Thousand "
    Place(3) = " Million "
    Place(4) = " Billion "
    Place(5) = " Trillion "
    
'String representation of amount.
    
    MyNumber = Trim(Str(MyNumber))

'Position of decimal place 0 if none.
    
    DecimalPlace = InStr(MyNumber, ".")
'Convert cents and set MyNumber to dollar amount.
    
    If DecimalPlace > 0 Then
        Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
        MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
    End If
    
    Count = 1
    
    Do While MyNumber <> ""

    Temp = GetHundreds(Right(MyNumber, 3))
        If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
        If Len(MyNumber) > 3 Then
            MyNumber = Left(MyNumber, Len(MyNumber) - 3)
        Else
            MyNumber = ""
        End If
        Count = Count + 1
    Loop
    
    Select Case Dollars
        Case ""
            Dollars = "No Dollars"
        Case "One"
            Dollars = "One Dollar"
         Case Else
            Dollars = Dollars & " Dollars"
    End Select
    
    Select Case Cents
        Case ""
            Cents = " only."
        
**********************************************
'This step should show the integer as wording, but retain the decimals as values (e.g. "One hundred ten dollars and 12/100 Cents". 
**********************************************
        Case Else
        
            Cents = " and " & Right(MyNumber, 2) & "/100 cents."
    
    End Select
    
    
    SpellNumber = Dollars & Cents

End Function

' Converts a number from 100-999 into text

Function GetHundreds(ByVal MyNumber)
    
Dim Result As String
    
    If Val(MyNumber) = 0 Then Exit Function
    MyNumber = Right("000" & MyNumber, 3)
'Convert the hundreds place.
    
    If Mid(MyNumber, 1, 1) <> "0" Then
       Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
    End If

'Convert the tens and ones place.
    If Mid(MyNumber, 2, 1) <> "0" Then
        Result = Result & GetTens(Mid(MyNumber, 2))
    Else
        Result = Result & GetDigit(Mid(MyNumber, 3))
    End If
    GetHundreds = Result

End Function

' Converts a number from 10 to 99 into text.

Function GetTens(TensText)
Dim Result As String
    
    Result = ""           ' Null out the temporary function value.
    If Val(Left(TensText, 1)) = 1 Then   ' If value between 10-19...
        Select Case Val(TensText)
            Case 10: Result = "Ten"
            Case 11: Result = "Eleven"
            Case 12: Result = "Twelve"
            Case 13: Result = "Thirteen"
            Case 14: Result = "Fourteen"
            Case 15: Result = "Fifteen"
            Case 16: Result = "Sixteen"
            Case 17: Result = "Seventeen"
            Case 18: Result = "Eighteen"
            Case 19: Result = "Nineteen"
            Case Else
        End Select
    Else                                 ' If value between 20-99...
        Select Case Val(Left(TensText, 1))
            Case 2: Result = "Twenty "
            Case 3: Result = "Thirty "
            Case 4: Result = "Forty "
            Case 5: Result = "Fifty "
            Case 6: Result = "Sixty "
            Case 7: Result = "Seventy "
            Case 8: Result = "Eighty "
            Case 9: Result = "Ninety "
            Case Else
        End Select
        Result = Result & GetDigit _
            (Right(TensText, 1))  ' Retrieve ones place.
    End If
    GetTens = Result
End Function

' Converts a number from 1 to 9 into text.
Function GetDigit(Digit)
    
    Select Case Val(Digit)
        Case 1: GetDigit = "One"
        Case 2: GetDigit = "Two"
        Case 3: GetDigit = "Three"
        Case 4: GetDigit = "Four"
        Case 5: GetDigit = "Five"
        Case 6: GetDigit = "Six"
        Case 7: GetDigit = "Seven"
        Case 8: GetDigit = "Eight"
        Case 9: GetDigit = "Nine"
        Case Else: GetDigit = ""
    
    End Select

End Function

All solutions, suggestions and alternatives welcome as ever

Ochimus

[JohnTopley]

Delete Delete

[JohnTopley]

Can you give example of expected result for 110.12?

Guessing ....

Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
        nCents = Left(Mid(MyNumber, DecimalPlace + 1), 2) * 1

Cents = " and " & nCents & "/100 cents."

so ,,, " One Hundred Ten Dollars and 12/ 100 cents." ??

[Ochimus]

John,

Many thanks for prompt response.

The example of expected result for 110.12 is in the "asterisked" comment in the first SpellNumber function in the original post:

If K5 is 110.12, C5 should show the integer as wording, but retain the decimals as values (e.g. "One hundred ten dollars and 12/100 Cents").'

But I must be misreading your suggestion, because it's throwing "#Value" in C5?

Ochimus

[JohnTopley]

Please see attached...

[Ochimus]

John,

Prompt clarification much appreciated.

Can now mark this as Solved.

Ochimus

[JohnTopley]

You're welcome and thank you for the rep.