Many thanks for your continued help with this Jonathan!
I think I am so close now - the wall is still in my path but I can see
the light!
I've slapped the code into the userform as per your instructions, but I
Userform is having a hissy-fit when trying to conver the numbers to
text as it goes down the script. Maybe its something to do with it
being VB6?
The error message reads: "Compile error: Variable not defined" and
highlights "snumbertext" within the brackets that immediately follow
"IsBounded" in the following part of the script:
'----------------------------------------
'Check/create array. If this is the first
'time using this routine, create the text
'strings that will be used.
'----------------------------------------
If Not IsBounded(sNumberText) Then
Call BuildArray(sNumberText)
End If
Here is how the script is looking in userform if it's of any further
use (PS - I've furthered my advanced beginner status you'll see
Option Explicit
Private Sub BuildArray(sNumberText() As String)
Dim sNumberText(0 To 27) As String
sNumberText(0) = "Zero"
sNumberText(1) = "One"
sNumberText(2) = "Two"
sNumberText(3) = "Three"
sNumberText(4) = "Four"
sNumberText(5) = "Five"
sNumberText(6) = "Six"
sNumberText(7) = "Seven"
sNumberText(8) = "Eight"
sNumberText(9) = "Nine"
sNumberText(10) = "Ten"
sNumberText(11) = "Eleven"
sNumberText(12) = "Twelve"
sNumberText(13) = "Thirteen"
sNumberText(14) = "Fourteen"
sNumberText(15) = "Fifteen"
sNumberText(16) = "Sixteen"
sNumberText(17) = "Seventeen"
sNumberText(18) = "Eighteen"
sNumberText(19) = "Nineteen"
sNumberText(20) = "Twenty"
sNumberText(21) = "Thirty"
sNumberText(22) = "Forty"
sNumberText(23) = "Fifty"
sNumberText(24) = "Sixty"
sNumberText(25) = "Seventy"
sNumberText(26) = "Eighty"
sNumberText(27) = "Ninety"
End Sub
Private Function IsBounded(vntArray As Variant) As Boolean
'note: the application in the IDE will stop
'at this line when first run if the IDE error
'mode is not set to "Break on Unhandled Errors"
'(Tools/Options/General/Error Trapping)
On Error Resume Next
IsBounded = IsNumeric(UBound(vntArray))
End Function
Private Function HundredsTensUnits(ByVal TestValue As Integer, _
Optional bUseAnd As Boolean) As
String
Dim CardinalNumber As Integer
If TestValue > 99 Then
CardinalNumber = TestValue \ 100
HundredsTensUnits = sNumberText(CardinalNumber) & " Hundred "
TestValue = TestValue - (CardinalNumber * 100)
End If
If bUseAnd = True Then
HundredsTensUnits = HundredsTensUnits & "and "
End If
If TestValue > 20 Then
CardinalNumber = TestValue \ 10
HundredsTensUnits = HundredsTensUnits & _
sNumberText(CardinalNumber + 18) & " "
TestValue = TestValue - (CardinalNumber * 10)
End If
If TestValue > 0 Then
HundredsTensUnits = HundredsTensUnits & sNumberText(TestValue) &
" "
End If
End Function
Private Function NumberAsText(NumberIn As Variant, _
Optional AND_or_CHECK_or_POUND As String)
As String
Dim cnt As Long
Dim DecimalPoint As Long
Dim CardinalNumber As Long
Dim CommaAdjuster As Long
Dim TestValue As Long
Dim CurrValue As Currency
Dim CentsString As String
Dim NumberSign As String
Dim WholePart As String
Dim BigWholePart As String
Dim DecimalPart As String
Dim tmp As String
Dim sStyle As String
Dim bUseAnd As Boolean
Dim bUseCheck As Boolean
Dim bUsePounds As Boolean
'----------------------------------------
'Begin setting conditions for formatting
'----------------------------------------
'Determine whether to apply special formatting.
'If nothing passed, return routine result
'converted only into its numeric equivalents,
'with no additional format text.
sStyle = LCase(AND_or_CHECK_or_POUND)
'User passed "AND": "and" will be added
'between hundredths and tens of pounds,
'ie "Three Hundred and Forty Two"
bUseAnd = sStyle = "and"
'User passed "POUND": "pound(s)" and "pence"
'appended to string,
'ie "Three Hundred and Forty Two Pounds"
bUsePounds = sStyle = "pound"
'User passed "CHECK" *or* "DOLLAR"
'If "check", cent amount returned as a fraction /100
'i.e. "Three Hundred Forty Two and 00/100"
'If "pound" was passed, "pound(s)" and "pence"
'appended instead.
bUseCheck = (sStyle = "check") Or (sStyle = "pound")
'----------------------------------------
'Check/create array. If this is the first
'time using this routine, create the text
'strings that will be used.
'----------------------------------------
If Not IsBounded(sNumberText) Then
Call BuildArray(sNumberText)
End If
'----------------------------------------
'Begin validating the number, and breaking
'into constituent parts
'----------------------------------------
'prepare to check for valid value in
NumberIn = Trim$(NumberIn)
If Not IsNumeric(NumberIn) Then
'invalid entry - abort
NumberAsText = "Error - Number improperly formed"
Exit Function
Else
'decimal check
DecimalPoint = InStr(NumberIn, ".")
If DecimalPoint > 0 Then
'split the fractional and primary numbers
DecimalPart = Mid$(NumberIn, DecimalPoint + 1)
WholePart = Left$(NumberIn, DecimalPoint - 1)
Else
'assume the decimal is the last char
DecimalPoint = Len(NumberIn) + 1
WholePart = NumberIn
End If
If InStr(NumberIn, ",,") Or _
InStr(NumberIn, ",.") Or _
InStr(NumberIn, ".,") Or _
InStr(DecimalPart, ",") Then
NumberAsText = "Error - Improper use of commas"
Exit Function
ElseIf InStr(NumberIn, ",") Then
CommaAdjuster = 0
WholePart = ""
For cnt = DecimalPoint - 1 To 1 Step -1
If Not Mid$(NumberIn, cnt, 1) Like "[,]" Then
WholePart = Mid$(NumberIn, cnt, 1) & WholePart
Else
CommaAdjuster = CommaAdjuster + 1
If (DecimalPoint - cnt - CommaAdjuster) Mod 3 Then
NumberAsText = "Error - Improper use of commas"
Exit Function
End If 'If
End If 'If Not
Next 'For cnt
End If 'If InStr
End If 'If Not
If Left$(WholePart, 1) Like "[+-]" Then
NumberSign = IIf(Left$(WholePart, 1) = "-", "Minus ", "Plus ")
WholePart = Mid$(WholePart, 2)
End If
'----------------------------------------
'Begin code to assure decimal portion of
'check value is not inadvertently rounded
'----------------------------------------
If bUseCheck = True Then
CurrValue = CCur(Val("." & DecimalPart))
DecimalPart = Mid$(Format$(CurrValue, "0.00"), 3, 2)
If CurrValue >= 0.995 Then
If WholePart = String$(Len(WholePart), "9") Then
WholePart = "1" & String$(Len(WholePart), "0")
Else
For cnt = Len(WholePart) To 1 Step -1
If Mid$(WholePart, cnt, 1) = "9" Then
Mid$(WholePart, cnt, 1) = "0"
Else
Mid$(WholePart, cnt, 1) = CStr(Val(Mid$(WholePart,
cnt, 1)) + 1)
Exit For
End If
Next
End If 'If WholePart
End If 'If CurrValue
End If 'If bUseCheck
'----------------------------------------
'Final prep step - this assures number
'within range of formatting code below
'----------------------------------------
If Len(WholePart) > 9 Then
BigWholePart = Left$(WholePart, Len(WholePart) - 9)
WholePart = Right$(WholePart, 9)
End If
If Len(BigWholePart) > 9 Then
NumberAsText = "Error - Number too large"
Exit Function
ElseIf Not WholePart Like String$(Len(WholePart), "#") Or _
(Not BigWholePart Like String$(Len(BigWholePart), "#") _
And Len(BigWholePart) > 0) Then
NumberAsText = "Error - Number improperly formed"
Exit Function
End If
'----------------------------------------
'Begin creating the output string
'----------------------------------------
'Very Large values
TestValue = Val(BigWholePart)
If TestValue > 999999 Then
CardinalNumber = TestValue \ 1000000
tmp = HundredsTensUnits(CardinalNumber) & "Quadrillion "
TestValue = TestValue - (CardinalNumber * 1000000)
End If
If TestValue > 999 Then
CardinalNumber = TestValue \ 1000
tmp = tmp & HundredsTensUnits(CardinalNumber) & "Trillion "
TestValue = TestValue - (CardinalNumber * 1000)
End If
If TestValue > 0 Then
tmp = tmp & HundredsTensUnits(TestValue) & "Billion "
End If
'Lesser values
TestValue = Val(WholePart)
If TestValue = 0 And BigWholePart = "" Then tmp = "Zero "
If TestValue > 999999 Then
CardinalNumber = TestValue \ 1000000
tmp = tmp & HundredsTensUnits(CardinalNumber) & "Million "
TestValue = TestValue - (CardinalNumber * 1000000)
End If
If TestValue > 999 Then
CardinalNumber = TestValue \ 1000
tmp = tmp & HundredsTensUnits(CardinalNumber) & "Thousand "
TestValue = TestValue - (CardinalNumber * 1000)
End If
If TestValue > 0 Then
If Val(WholePart) < 99 And BigWholePart = "" Then bUseAnd = False
tmp = tmp & HundredsTensUnits(TestValue, bUseAnd)
End If
'If in dollar mode, assure the text is the correct plurality
If bUsePounds = True Then
PenceString = HundredsTensUnits(DecimalPart)
If tmp = "One " Then
tmp = tmp & "Pound"
Else
tmp = tmp & "Pounds"
End If
If Len(PenceString) > 0 Then
tmp = tmp & " and " & PenceString
If PenceString = "One " Then
tmp = tmp & "Cent"
Else
tmp = tmp & "Cents"
End If
End If
ElseIf bUseCheck = True Then
tmp = tmp & "and " & Left$(DecimalPart & "00", 2)
tmp = tmp & "/100"
Else
If Len(DecimalPart) > 0 Then
tmp = tmp & "Point"
For cnt = 1 To Len(DecimalPart)
tmp = tmp & " " & sNumberText(Mid$(DecimalPart, cnt, 1))
Next
End If 'If DecimalPart
End If 'If bUsePounds
'done!
NumberAsText = NumberSign & tmp
End Function
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As
Integer)
If CloseMode = vbFormControlMenu Then Cancel = True
End Sub
Private Sub UserForm_Initialize()
Jonathan West wrote:
Hi,
Beginer needs help with this please.
I am not sure how to adapt. I setup Text1 Label1 Command1 and pasted
code
behind Userform.
I am not sure about setting Control to 0 what is exact name of
Control
Index? Could that cause the below error?
I get a Type Mismatch Error 13 at this point?
Private Sub Command1_Click()
Dim value As String
*** value = Text1(0).Text***
Should I be doing something different?
You've been tripped up by the fact that VB6 forms are not quite the
same as
VBA UserForms. Try this
value = Text1.Text
--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition
www.classicvb.org
