Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
accept fractions from textbox
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
accept fractions from textbox
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
accept fractions from textbox
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
accept fractions from textbox
The Val function will not convert fractions to floating point values; it
will print the numerator only. Rick "Mike H" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
accept fractions from textbox
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
accept fractions from textbox
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)" wrote in message ... 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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
accept fractions from textbox
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" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
accept fractions from textbox
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" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
accept fractions from textbox
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How2 accept only numbers in userform textbox | Excel Programming | |||
HELP! I Lost The Ability To Advance From TextBox To TextBox With the ENTER Or The TAB Keys | Excel Programming | |||
Textbox Bug? Missing/delayed update of textbox filled via VBA | Excel Programming | |||
Textbox Bug? Missing/delayed update of textbox filled via VBA | Excel Programming | |||
How to move cursor from one textbox control to another textbox con | Excel Programming |