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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
as posted in 2016 I don't get any compile issue

where are you putting the code
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
I think it's posted in Two places
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Who said opposite of this?
 

ajith

Board Regular
Joined
Nov 21, 2012
Messages
204
Office Version
  1. 2016
Platform
  1. Windows
Hai all,
Well it is or could be true that you could not find any error. Because I didn't do anything in that as I don't know to write these codes. It was working fine earlier. Later when I changed the laptop and upgraded to the MS office Prof Plus 2016, all these started, i think. There was a date picker in that sheet that also stopped working. Just now I deleted the code for date picker. The issue still remains even after saving and closing the excel sheet.

The issue is always shown whenever I select a data from the pull down list which I prepared using data validation. When it happens, if the developer page is open (password protected), the 3rd line of the INRSpell code gets highlighted and the error is shown as in 1st image (attached) and if the developer is not open the other error is shown in the excel page itself. The value in the INRSpell linked cell shows #VALUE! error. At the same time this INRSpell code is working without any issues in another worksheet. I almost got used to it now. If you have any idea about why this issue occurs and how to correct it, pls inform.

Image 1-Dev err notif1.jpg



Excel err notif1.jpg
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Ok, can you try to replace the line in blue with
VBA Code:
ReDim Place(0 To 9) As String
 

ajith

Board Regular
Joined
Nov 21, 2012
Messages
204
Office Version
  1. 2016
Platform
  1. Windows
Hai,
Did it. Issue remains

Image 2-Dev err notif.jpg
 

ajith

Board Regular
Joined
Nov 21, 2012
Messages
204
Office Version
  1. 2016
Platform
  1. Windows
Hai,
I got rid of the issue. I found that the same code INRSpell is there in sheet 2 also. So i deleted that in module 1. Now that error notification is not appearing. But the code INRSpell linked to a cell in Sheet 2 is not working (earlier it was showing #VALUE! error). It is now showing #NAME? error in that cell.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,328
Messages
5,635,624
Members
416,869
Latest member
JeffK26

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