I cannot enter fractions in a TextBox and have them recognised as numbers.

If IsNumeric(UserForm1.TextBox2.Value) = False Then 'Quantity
MsgBox "Choose NUMERIC quantity. Transaction cancelled!", , "Company Name"
End If

I am having trouble getting this to work in Excel 2007; Vista whereas works
Excel 2003 XP.

Is there a better way or is it a machine setting problem rather than Excel
(eg Universal Date settings etc)
Thanks B

What did you mean when you said...

> I am having trouble getting this to work in Excel 2007;
> Vista whereas works Excel 2003 XP.

....that code works the same in XL2007 as it does in XL2003 (both running on
Vista) for me. A numerical value entered into a TextBox is not a number, it
is text. The only reason entries like 1, 2.3, etc. end up as numbers when
used in calculations is because VBA has native data types that hold such
values (Long, Double, etc.) and, when used in calculations, numbers that can
be converted to an appropriate native data type are done so, in the
background, automatically, in order to perform the required calculation. VBA
has no native data type for fractions, so no conversion takes place. You can
parse the fraction and convert it to a floating point value in code though.
Here is a function that I have posted in the past (in the compiled VB
newsgroups) which will convert fractions, as well as mixed numbers (whole
numbers followed by a space followed by a fraction), into a floating point
values...

Function FracToDec(ByVal Fraction As String) As Double
Dim Blank As Integer
Dim Slash As Integer
Dim CharPosition As Integer
Dim WholeNumber As Integer
Dim Numerator As Integer
Dim Denominator As Integer
'Remove leading and trailing blanks
Fraction = Trim$(Fraction)
'Collapse all multiple blanks to a single blank
CharPosition = InStr(Fraction, " ")
Do While CharPosition
Fraction = Left$(Fraction, CharPosition) & _
Mid$(Fraction, CharPosition + 2)
CharPosition = InStr(Fraction, " ")
Loop
'Remove any space character after the slash
CharPosition = InStr(Fraction, "/ ")
If CharPosition Then
Fraction = Left$(Fraction, CharPosition) & _
Mid$(Fraction, CharPosition + 2)
End If
'Remove any space character in front of the slash
CharPosition = InStr(Fraction, " /")
If CharPosition Then
Fraction = Left$(Fraction, CharPosition - 1) & _
Mid$(Fraction, CharPosition + 1)
End If
'Locate the blank and/or slash
Blank = InStr(Fraction, " ")
Slash = InStr(Fraction, "/")
'The Fraction argument can't have characters other than
'blanks, slashes, digits and it can only have one blank
'and/or one slash.
If Fraction Like "*[! /0-9]*" Or _
InStr(Blank + 1, Fraction, " ") Or _
InStr(Slash + 1, Fraction, "/") Or _
(Blank > 0 And Slash = 0) Then
MsgBox "Error -- Improperly formed expression"
'The Fraction argument is now in one of these formats
'where # stands for one or more digits: #, # #/# or #/#
Else
'There is no slash (Format: #)
If Slash = 0 Then
FracToDec = Val(Fraction)
'There is a slash, but no blank (Format: #/#)
ElseIf Blank = 0 Then
FracToDec = Val(Left$(Fraction, Slash - 1)) / _
Val(Mid$(Fraction, Slash + 1))
'There are both a slash and a blank (Format: # #/#)
Else
FracToDec = Val(Left$(Fraction, Blank - 1)) + _
Val(Mid$(Fraction, Blank + 1, _
Slash - Blank - 1)) / _
Val(Mid$(Fraction, Slash + 1))
End If
End If
End Function


Rick


"brianbanksia" <(E-Mail Removed)> wrote in message
news:EA3F1EEE-5E7D-479F-85DF-(E-Mail Removed)...
>I cannot enter fractions in a TextBox and have them recognised as numbers.
>
> If IsNumeric(UserForm1.TextBox2.Value) = False Then 'Quantity
> MsgBox "Choose NUMERIC quantity. Transaction cancelled!", , "Company Name"
> End If
>
> I am having trouble getting this to work in Excel 2007; Vista whereas
> works
> Excel 2003 XP.
>
> Is there a better way or is it a machine setting problem rather than Excel
> (eg Universal Date settings etc)
> Thanks B

Hi,

Textboxes (unsurprisingly) return text so you need val

mynumber = Val(UserForm1.TextBox2.Value)

Mike

"brianbanksia" wrote:

> I cannot enter fractions in a TextBox and have them recognised as numbers.
>
> If IsNumeric(UserForm1.TextBox2.Value) = False Then 'Quantity
> MsgBox "Choose NUMERIC quantity. Transaction cancelled!", , "Company Name"
> End If
>
> I am having trouble getting this to work in Excel 2007; Vista whereas works
> Excel 2003 XP.
>
> Is there a better way or is it a machine setting problem rather than Excel
> (eg Universal Date settings etc)
> Thanks B

The Val function will not convert fractions to floating point values; it
will print the numerator only.

Rick


"Mike H" <(E-Mail Removed)> wrote in message
news:5D3401E1-071D-416C-ABB1-(E-Mail Removed)...
> Hi,
>
> Textboxes (unsurprisingly) return text so you need val
>
> mynumber = Val(UserForm1.TextBox2.Value)
>
> Mike
>
> "brianbanksia" wrote:
>
>> I cannot enter fractions in a TextBox and have them recognised as
>> numbers.
>>
>> If IsNumeric(UserForm1.TextBox2.Value) = False Then 'Quantity
>> MsgBox "Choose NUMERIC quantity. Transaction cancelled!", , "Company
>> Name"
>> End If
>>
>> I am having trouble getting this to work in Excel 2007; Vista whereas
>> works
>> Excel 2003 XP.
>>
>> Is there a better way or is it a machine setting problem rather than
>> Excel
>> (eg Universal Date settings etc)
>> Thanks B

Make a Textbox (I used the Drawing Toolbar). Enter 3/4

Run the following:

Sub Macro1()
Dim s As String
ActiveSheet.Shapes("Text Box 1").Select
s = Selection.Characters.Text
MsgBox (Evaluate(s))
End Sub

Outputs .75
--
Gary''s Student - gsnu200788


"brianbanksia" wrote:

> I cannot enter fractions in a TextBox and have them recognised as numbers.
>
> If IsNumeric(UserForm1.TextBox2.Value) = False Then 'Quantity
> MsgBox "Choose NUMERIC quantity. Transaction cancelled!", , "Company Name"
> End If
>
> I am having trouble getting this to work in Excel 2007; Vista whereas works
> Excel 2003 XP.
>
> Is there a better way or is it a machine setting problem rather than Excel
> (eg Universal Date settings etc)
> Thanks B

Rick,

VBA doesn't have native fraction value use, but Excel does, and you can
leverage that ability.

The cell that you use could be anywhere, including a hidden sheet in an
add-in.

And you can do this
> 'Remove leading and trailing blanks
> Fraction = Trim$(Fraction)
> 'Collapse all multiple blanks to a single blank

by using Appliction.Trim, which removes leading, trailing, and multiple
internal spaces.

And note the use of a Sub rather than a Function definition.

Clearly, my code isn't as robust (not looking for extra /'s, non-numeric
characters, for example),
but, hey, it's just an example. ;-)

Bernie

Option Explicit

Sub TestIt()
Dim myValue As Double
FracToDec " 1,230 5 \ 6", myValue
MsgBox myValue
End Sub

Sub FracToDec(ByVal Fraction As String, ByRef myDV As Double)
Dim myC As Range
Fraction = Application.Trim(Fraction)
Fraction = Replace(Fraction, "\", "/")
Fraction = Replace(Fraction, " /", "/")
Fraction = Replace(Fraction, "/ ", "/")
Set myC = Cells(Rows.Count, 1).End(xlUp)(2)
myC.NumberFormat = "G"
myC.Value = Fraction
myDV = myC.Value
myC.Clear
End Sub



"Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
> What did you mean when you said...
>
>> I am having trouble getting this to work in Excel 2007;
>> Vista whereas works Excel 2003 XP.
>
> ...that code works the same in XL2007 as it does in XL2003 (both running
> on Vista) for me. A numerical value entered into a TextBox is not a
> number, it is text. The only reason entries like 1, 2.3, etc. end up as
> numbers when used in calculations is because VBA has native data types
> that hold such values (Long, Double, etc.) and, when used in calculations,
> numbers that can be converted to an appropriate native data type are done
> so, in the background, automatically, in order to perform the required
> calculation. VBA has no native data type for fractions, so no conversion
> takes place. You can parse the fraction and convert it to a floating point
> value in code though. Here is a function that I have posted in the past
> (in the compiled VB newsgroups) which will convert fractions, as well as
> mixed numbers (whole numbers followed by a space followed by a fraction),
> into a floating point values...
>
> Function FracToDec(ByVal Fraction As String) As Double
> Dim Blank As Integer
> Dim Slash As Integer
> Dim CharPosition As Integer
> Dim WholeNumber As Integer
> Dim Numerator As Integer
> Dim Denominator As Integer
> 'Remove leading and trailing blanks
> Fraction = Trim$(Fraction)
> 'Collapse all multiple blanks to a single blank
> CharPosition = InStr(Fraction, " ")
> Do While CharPosition
> Fraction = Left$(Fraction, CharPosition) & _
> Mid$(Fraction, CharPosition + 2)
> CharPosition = InStr(Fraction, " ")
> Loop
> 'Remove any space character after the slash
> CharPosition = InStr(Fraction, "/ ")
> If CharPosition Then
> Fraction = Left$(Fraction, CharPosition) & _
> Mid$(Fraction, CharPosition + 2)
> End If
> 'Remove any space character in front of the slash
> CharPosition = InStr(Fraction, " /")
> If CharPosition Then
> Fraction = Left$(Fraction, CharPosition - 1) & _
> Mid$(Fraction, CharPosition + 1)
> End If
> 'Locate the blank and/or slash
> Blank = InStr(Fraction, " ")
> Slash = InStr(Fraction, "/")
> 'The Fraction argument can't have characters other than
> 'blanks, slashes, digits and it can only have one blank
> 'and/or one slash.
> If Fraction Like "*[! /0-9]*" Or _
> InStr(Blank + 1, Fraction, " ") Or _
> InStr(Slash + 1, Fraction, "/") Or _
> (Blank > 0 And Slash = 0) Then
> MsgBox "Error -- Improperly formed expression"
> 'The Fraction argument is now in one of these formats
> 'where # stands for one or more digits: #, # #/# or #/#
> Else
> 'There is no slash (Format: #)
> If Slash = 0 Then
> FracToDec = Val(Fraction)
> 'There is a slash, but no blank (Format: #/#)
> ElseIf Blank = 0 Then
> FracToDec = Val(Left$(Fraction, Slash - 1)) / _
> Val(Mid$(Fraction, Slash + 1))
> 'There are both a slash and a blank (Format: # #/#)
> Else
> FracToDec = Val(Left$(Fraction, Blank - 1)) + _
> Val(Mid$(Fraction, Blank + 1, _
> Slash - Blank - 1)) / _
> Val(Mid$(Fraction, Slash + 1))
> End If
> End If
> End Function
>
>
> Rick
>
>
> "brianbanksia" <(E-Mail Removed)> wrote in message
> news:EA3F1EEE-5E7D-479F-85DF-(E-Mail Removed)...
>>I cannot enter fractions in a TextBox and have them recognised as numbers.
>>
>> If IsNumeric(UserForm1.TextBox2.Value) = False Then 'Quantity
>> MsgBox "Choose NUMERIC quantity. Transaction cancelled!", , "Company
>> Name"
>> End If
>>
>> I am having trouble getting this to work in Excel 2007; Vista whereas
>> works
>> Excel 2003 XP.
>>
>> Is there a better way or is it a machine setting problem rather than
>> Excel
>> (eg Universal Date settings etc)
>> Thanks B
>

Gary''s Student,

Evaluate is an excellent idea - much better than my using a range object -
but with a little extra code to clean things up, since evaluate doesn't like
commas...

Bernie

Sub TestIt()
Dim myValue As Double
myValue = FracToDec(" 1,230 5 \ 6")
MsgBox myValue
End Sub

Function FracToDec(ByVal Fraction As String) As Double
Fraction = Application.Trim(Fraction)
Fraction = Replace(Fraction, "\", "/")
Fraction = Replace(Fraction, " /", "/")
Fraction = Replace(Fraction, "/ ", "/")
Fraction = Replace(Fraction, ",", "")
FracToDec = Evaluate(Fraction)
End Function

"Gary''s Student" <(E-Mail Removed)> wrote in message
news:4CFE0D1E-3B29-4A6D-AF13-(E-Mail Removed)...
> Make a Textbox (I used the Drawing Toolbar). Enter 3/4
>
> Run the following:
>
> Sub Macro1()
> Dim s As String
> ActiveSheet.Shapes("Text Box 1").Select
> s = Selection.Characters.Text
> MsgBox (Evaluate(s))
> End Sub
>
> Outputs .75
> --
> Gary''s Student - gsnu200788
>
>
> "brianbanksia" wrote:
>
>> I cannot enter fractions in a TextBox and have them recognised as
>> numbers.
>>
>> If IsNumeric(UserForm1.TextBox2.Value) = False Then 'Quantity
>> MsgBox "Choose NUMERIC quantity. Transaction cancelled!", , "Company
>> Name"
>> End If
>>
>> I am having trouble getting this to work in Excel 2007; Vista whereas
>> works
>> Excel 2003 XP.
>>
>> Is there a better way or is it a machine setting problem rather than
>> Excel
>> (eg Universal Date settings etc)
>> Thanks B

Hi,

Thanks for the correction, I'm aware of that but misread to post I thought
the problem was simply getting numbers from a textbox.

Mike

"Rick Rothstein (MVP - VB)" wrote:

> The Val function will not convert fractions to floating point values; it
> will print the numerator only.
>
> Rick
>
>
> "Mike H" <(E-Mail Removed)> wrote in message
> news:5D3401E1-071D-416C-ABB1-(E-Mail Removed)...
> > Hi,
> >
> > Textboxes (unsurprisingly) return text so you need val
> >
> > mynumber = Val(UserForm1.TextBox2.Value)
> >
> > Mike
> >
> > "brianbanksia" wrote:
> >
> >> I cannot enter fractions in a TextBox and have them recognised as
> >> numbers.
> >>
> >> If IsNumeric(UserForm1.TextBox2.Value) = False Then 'Quantity
> >> MsgBox "Choose NUMERIC quantity. Transaction cancelled!", , "Company
> >> Name"
> >> End If
> >>
> >> I am having trouble getting this to work in Excel 2007; Vista whereas
> >> works
> >> Excel 2003 XP.
> >>
> >> Is there a better way or is it a machine setting problem rather than
> >> Excel
> >> (eg Universal Date settings etc)
> >> Thanks B
>
>

Thanks for the tip...
--
Gary''s Student - gsnu200788


"Bernie Deitrick" wrote:

> Gary''s Student,
>
> Evaluate is an excellent idea - much better than my using a range object -
> but with a little extra code to clean things up, since evaluate doesn't like
> commas...
>
> Bernie
>
> Sub TestIt()
> Dim myValue As Double
> myValue = FracToDec(" 1,230 5 \ 6")
> MsgBox myValue
> End Sub
>
> Function FracToDec(ByVal Fraction As String) As Double
> Fraction = Application.Trim(Fraction)
> Fraction = Replace(Fraction, "\", "/")
> Fraction = Replace(Fraction, " /", "/")
> Fraction = Replace(Fraction, "/ ", "/")
> Fraction = Replace(Fraction, ",", "")
> FracToDec = Evaluate(Fraction)
> End Function
>
> "Gary''s Student" <(E-Mail Removed)> wrote in message
> news:4CFE0D1E-3B29-4A6D-AF13-(E-Mail Removed)...
> > Make a Textbox (I used the Drawing Toolbar). Enter 3/4
> >
> > Run the following:
> >
> > Sub Macro1()
> > Dim s As String
> > ActiveSheet.Shapes("Text Box 1").Select
> > s = Selection.Characters.Text
> > MsgBox (Evaluate(s))
> > End Sub
> >
> > Outputs .75
> > --
> > Gary''s Student - gsnu200788
> >
> >
> > "brianbanksia" wrote:
> >
> >> I cannot enter fractions in a TextBox and have them recognised as
> >> numbers.
> >>
> >> If IsNumeric(UserForm1.TextBox2.Value) = False Then 'Quantity
> >> MsgBox "Choose NUMERIC quantity. Transaction cancelled!", , "Company
> >> Name"
> >> End If
> >>
> >> I am having trouble getting this to work in Excel 2007; Vista whereas
> >> works
> >> Excel 2003 XP.
> >>
> >> Is there a better way or is it a machine setting problem rather than
> >> Excel
> >> (eg Universal Date settings etc)
> >> Thanks B
>
>
>

This would be my attempt at making it fully robust...

Function FracToDec(ByVal Fraction As String) As Double
Dim myC As Range
If Fraction Like "*\*#* *#*" Or Fraction Like "*/*/*" Or _
Fraction Like "*#* *#* *#*/*" Or Fraction Like "*[! /0-9-]*" Then
Err.Raise Number:=1001, Description:="Improperly formed fraction"
Exit Function
ElseIf Fraction Like "*#* *#*/*" Then
Fraction = Replace(Fraction, " ", Chr$(0), , 1)
End If
Fraction = Replace(Replace(Fraction, " ", ""), Chr$(0), " ")
FracToDec = Evaluate(Replace(Fraction, ",", ""))
End Function

Note that I implemented an "error number" (1001) that can be trapped for in
code.

Rick


"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:(E-Mail Removed)...
> Gary''s Student,
>
> Evaluate is an excellent idea - much better than my using a range object -
> but with a little extra code to clean things up, since evaluate doesn't
> like commas...
>
> Bernie
>
> Sub TestIt()
> Dim myValue As Double
> myValue = FracToDec(" 1,230 5 \ 6")
> MsgBox myValue
> End Sub
>
> Function FracToDec(ByVal Fraction As String) As Double
> Fraction = Application.Trim(Fraction)
> Fraction = Replace(Fraction, "\", "/")
> Fraction = Replace(Fraction, " /", "/")
> Fraction = Replace(Fraction, "/ ", "/")
> Fraction = Replace(Fraction, ",", "")
> FracToDec = Evaluate(Fraction)
> End Function
>
> "Gary''s Student" <(E-Mail Removed)> wrote in
> message news:4CFE0D1E-3B29-4A6D-AF13-(E-Mail Removed)...
>> Make a Textbox (I used the Drawing Toolbar). Enter 3/4
>>
>> Run the following:
>>
>> Sub Macro1()
>> Dim s As String
>> ActiveSheet.Shapes("Text Box 1").Select
>> s = Selection.Characters.Text
>> MsgBox (Evaluate(s))
>> End Sub
>>
>> Outputs .75
>> --
>> Gary''s Student - gsnu200788
>>
>>
>> "brianbanksia" wrote:
>>
>>> I cannot enter fractions in a TextBox and have them recognised as
>>> numbers.
>>>
>>> If IsNumeric(UserForm1.TextBox2.Value) = False Then 'Quantity
>>> MsgBox "Choose NUMERIC quantity. Transaction cancelled!", , "Company
>>> Name"
>>> End If
>>>
>>> I am having trouble getting this to work in Excel 2007; Vista whereas
>>> works
>>> Excel 2003 XP.
>>>
>>> Is there a better way or is it a machine setting problem rather than
>>> Excel
>>> (eg Universal Date settings etc)
>>> Thanks B
>
>