Compile error

ajith

Board Regular
Joined
Nov 21, 2012
Messages
204
Office Version
  1. 2016
Platform
  1. Windows
hello everybody,
I am getting a compile error in the following code. pls help. I am using MS Office Professional Plus 2016. Pls help

VBA Code:
Function INRSpell(ByVal MyNumber) As String
    Dim Rupees, Paise, Temp
    Dim DecimalPlace, Count
    ReDim Place(9) As String
    Place(2) = " Thousand "
    Place(3) = " Lakh "
    Place(4) = " Crore "
    'String representation of amount.
    MyNumber = Trim(Str(MyNumber))
    'Position of decimal place 0 if none.
    DecimalPlace = InStr(MyNumber, ".")
    'Convert Paise and set MyNumber to dollar amount.
    If DecimalPlace > 0 Then
        Paise = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
        MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
    End If
    Count = 1
    Do While MyNumber <> ""
        If Count <> 1 Then
            Temp = GetHundreds(Right(MyNumber, 2))
            If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees
            If Len(MyNumber) > 2 Then
                MyNumber = Left(MyNumber, Len(MyNumber) - 2)
            Else
                MyNumber = ""
            End If
        Else
            Temp = GetHundreds(Right(MyNumber, 3))
            If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees
            If Len(MyNumber) > 3 Then
                MyNumber = Left(MyNumber, Len(MyNumber) - 3)
            Else
                MyNumber = ""
            End If
        End If
        Count = Count + 1
    Loop
    Select Case Rupees
        Case ""
            Rupees = " "
        Case "One"
            Rupees = "Rupee One "
        Case Else
            Rupees = "Rupees " & Rupees
    End Select
    Select Case Paise
        'Case ""
            'Paise = " Zero Paise "
        Case "One"
            Paise = " and Paise One"
        Case Else
            Paise = "Paise " & Paise
    End Select
    If Rupees <> " " Then Paise = " and " & Paise
    Paise = " "
    INRSpell = Application.Trim(Rupees & Paise & " Only")
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
 

ajith

Board Regular
Joined
Nov 21, 2012
Messages
204
Office Version
  1. 2016
Platform
  1. Windows
Hai,
I am getting these messages in excel sheet when I press enter from the cell linked to INRSpell code

Image 3 Excel err notif.jpg



Image 4 Excel err notif.jpg
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
well
I've read some where that you have to close an reopen it again maybe several time some times
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
untick common controls, i seem to rememebr that it was needed in the past for calender to work, it was dropped by microsoft, so any machine yo develop for will need to have it added or an alternative route found
 

ajith

Board Regular
Joined
Nov 21, 2012
Messages
204
Office Version
  1. 2016
Platform
  1. Windows
untick common controls, i seem to rememebr that it was needed in the past for calender to work, it was dropped by microsoft, so any machine yo develop for will need to have it added or an alternative route found
Hai,
On trying to untick common control I am getting an error message - "Can't remove common or reference: in use"
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
Hai,
On trying to untick common control I am getting an error message - "Can't remove common or reference: in use"
maybe just start a fresh session, see if the reference exists in the vba window, or before compiling, see if it will untick then
 

Watch MrExcel Video

Forum statistics

Threads
1,129,539
Messages
5,636,903
Members
416,948
Latest member
Jkpang

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top