Tricky one... anyone up to the challenge? ;)

C

ChrisMattock

OK I have been using the macro below to spell out a number, i.e. I pu
20 in a cell B20 and B21 has =spellnumber(B20), the result is TWENTY
The spreadsheet that does this is on our work network, and is fine fo
myself and the majority of others, but one guy in the office
evetrytime he puts a value in the cell B20 and hits enter, the debugge
comes up saying it can't compile as it can't find the project o
library, any ideas?

Function spellnumber(ByVal MyNumber)
Dim Pounds, Pence, 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 Pence and set MyNumber to dollar amount.
If DecimalPlace > 0 Then
Pence = 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 Pounds = Temp & Place(Count) & Pounds
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop

Select Case Pounds
Case ""
Pounds = "No Pounds"
Case "One"
Pounds = "One Pound"
Case Else
Pounds = Pounds & " Pounds"
End Select

Select Case Pence
Case ""
Pence = " only"
Case "One"
Pence = " and one pence"
Case Else
Pence = " and " & Pence & " Pence"
End Select

spellnumber = Pounds & Pence
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 functio
value.
If Val(Left(TensText, 1)) = 1 Then ' If value betwee
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 betwee
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 Functio
 
D

Dave Patrick

On "one guy" machine. Alt F11, Tools|References and look for something
marked as 'MISSING'

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
|
| OK I have been using the macro below to spell out a number, i.e. I put
| 20 in a cell B20 and B21 has =spellnumber(B20), the result is TWENTY.
| The spreadsheet that does this is on our work network, and is fine for
| myself and the majority of others, but one guy in the office,
| evetrytime he puts a value in the cell B20 and hits enter, the debugger
| comes up saying it can't compile as it can't find the project or
| library, any ideas?
|
| Function spellnumber(ByVal MyNumber)
| Dim Pounds, Pence, 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 Pence and set MyNumber to dollar amount.
| If DecimalPlace > 0 Then
| Pence = 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 Pounds = Temp & Place(Count) & Pounds
| If Len(MyNumber) > 3 Then
| MyNumber = Left(MyNumber, Len(MyNumber) - 3)
| Else
| MyNumber = ""
| End If
| Count = Count + 1
| Loop
|
| Select Case Pounds
| Case ""
| Pounds = "No Pounds"
| Case "One"
| Pounds = "One Pound"
| Case Else
| Pounds = Pounds & " Pounds"
| End Select
|
| Select Case Pence
| Case ""
| Pence = " only"
| Case "One"
| Pence = " and one pence"
| Case Else
| Pence = " and " & Pence & " Pence"
| End Select
|
| spellnumber = Pounds & Pence
| 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
|
|
| --
| ChrisMattock
| ------------------------------------------------------------------------
| ChrisMattock's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=33912
| View this thread: http://www.excelforum.com/showthread.php?threadid=562106
|
 
J

Jim Thomlinson

The issue is not with the code you posted (looks like the code right off the
Microsoft site that i sue). Your issue is that the computer generateing the
error is missing a library reference. Go to their system and in this
spreadsheet got to the VBE and Select Tools-> References. Look for a
reference tagged "Missing:". there is a chance that you can just delete the
reference or at the very least select a different version of the reference
that is missing.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Conversion from Number to Word!! Aki 1
Converting currency to text and spliting to two lines 14
Numaric to Inword in excel by VB 1
Auto Enable Macro 9
Display Numbers as Words 3
spell numbers into text 2
convert figures into words on Microsft Excel 4
compile error in hidden module: sheet1 4

Top