Use of operators in Text Boxes inconsistent?

[stranno]

Hi,
Please have a look at the following code lines in a Userform.
I know how to solve this, but can someone explain
the reason behind this? The text in the TextBoxes 1 and 2
is treated as numbers in case of subtraction, division and multiplying
, but is treated as text in case of adding. Strange or not?


Private Sub UserForm_Initialize()
TextBox1 = 6
TextBox2 = 2
TextBox3 = TextBox1 - TextBox2 'anwswer = 4
TextBox4 = TextBox1 / TextBox2 'answer = 3
TextBox5 = TextBox1 * TextBox2 'answer = 12
TextBox6 = TextBox1 + TextBox2 'answer = 62 why?
End Sub


sub StartForm ()
Userform1.show vbModeless
end sub

regards,
stranno

[stranno]

Sorry, how do i obtain the code format? I'm afraid i lost it.

[snb]

TextBox6 = 0+TextBox1 + TextBox2

You can use the # icon to add code tags.

[mancubus]

or add / before closing tag: [ /CODE ]

[stranno]

Thanks Snb. It used to be done in another way as far as i can remember. I couldn't find that "VBA" mark.

[mikerickson]

1) The value in a text box is a string. Strictly speaking all of that code should give a type mismatch error, except that Excel has auto-type conversion features. As you see, they don't work consistently.

2) The + operator when applied to strings concatenates the arguments, hence your fourth result.

Try this, which avoids the use of defaults, both default properties and default conversions.

Private Sub UserForm_Initialize()
TextBox1.Text = 6
TextBox2.Text = 2
TextBox3.Text = Val(TextBox1.Text) - Val(TextBox2.Text) 'anwswer = 4
TextBox4.Text = Val(TextBox1.Text) / Val(TextBox2.Text) 'answer = 3
TextBox5.Text = Val(TextBox1.Text) * Val(TextBox2.Text) 'answer = 12
TextBox6.Text = Val(TextBox1.Text) + Val(TextBox2.Text) 'answer = 8
End Sub

If one doesn't explicitly code for what one wants, one can't complain when the (unspecified) defaults give an undesired result.

[Aussiebear]

@stranno When we upgraded the forum we have moved to using Code /Code as apposed to vba /vba.

[stranno]

mikerickson,
Thanks for answering my question. So it's a bit like I presumed. Excel does not work consistenty in this case. But still I would rather
expect an error report in case of multiplying, division or subtraction text than an unexpected result in case of adding text. By the way,
I used CDbl(TextBox...) instead of Val(TextBox...) because i needed
more accuracy.

Aussiebear,
Does the code format also take effect in the preview mode? Because as you could have seen in my original post, I did use [code/code]
but nothing happend. I will try it again in a test.

regards,
Stranno

[snb]

You probably overlooked my answer to your VBA question:

http://www.vbaexpress.com/forum/showthread.php?48937-Use-of-operators-in-Text-Boxes-inconsistent&p=304950&viewfull=1#post304950

[mikerickson]

stranno,
I prefer Val to CDbl because Val("xyz") returns 0, while CDbl("xyz") gives an error and crashes.

Garbage In, Garbage Out shouldn't mean Garbage In, Dump Truck Breaks

My personal preference is that if a user inputs text instead of numerals I'd rather return them useless calculations than validate their entry and nag them "numeric entry required"

I wasn't aware that Val was less accurate than CDbl.

Edit: I just ran this test program and detected no difference between Val and CDbl.


Sub test()
Dim i As Long
Dim num As Double, strNum As String
For i = 1 To 10000
strNum = CStr(1 / i)
If Val(strNum) <> CDbl(strNum) Then
MsgBox strNum & vbCr & Val(strNum) & ":" & CDbl(strNum) & vbCr & (Val(strNum) = CDbl(strNum))
End If
Next i
MsgBox "done"
End Sub

[Paul_Hossler]

If one doesn't explicitly code for what one wants, one can't complain when the (unspecified) defaults give an undesired result.


Wisdom for the ages

Paul

[GTO]

Garbage In, Garbage Out shouldn't mean Garbage In, Dump Truck Breaks

:funnyasheSomeone is on a roll! Too funny Mike!

[stranno]

stranno,
I prefer Val to CDbl because Val("xyz") returns 0, while CDbl("xyz") gives an error and crashes.

Garbage In, Garbage Out shouldn't mean Garbage In, Dump Truck Breaks

My personal preference is that if a user inputs text instead of numerals I'd rather return them useless calculations than validate their entry and nag them "numeric entry required"

I wasn't aware that Val was less accurate than CDbl.

Edit: I just ran this test program and detected no difference between Val and CDbl.


Sub test()
Dim i As Long
Dim num As Double, strNum As String
For i = 1 To 10000
strNum = CStr(1 / i)
If Val(strNum) <> CDbl(strNum) Then
MsgBox strNum & vbCr & Val(strNum) & ":" & CDbl(strNum) & vbCr & (Val(strNum) = CDbl(strNum))
End If
Next i
MsgBox "done"
End Sub



But this the situation in my workbook. There are no declared variables. In that case I have to choose for CDbl instead of Val.

regards,
Stranno

[stranno]

You probably overlooked my answer to your VBA question:

http://www.vbaexpress.com/forum/showthread.php?48937-Use-of-operators-in-Text-Boxes-inconsistent&p=304950&viewfull=1#post304950

Hi snb, You're right. I overlooked you post. Sorry. Good idea using #!!!
regards,
stranno

[snb]

the *, - and / operators have no double meaning, + has: concatenation (of strings) or adding (of numericals).
So it has nothing to do with inconsistency nor with garbage.

[mikerickson]

When I run your userform, it shows that 2.3 * 3.23 = 7.429 both with Val and CDbl.

[mancubus]

for me, 2.3 * 3.23 shows 6 with Val(Me.TextBox1) * Val(Me.TextBox2)7.429 with CDbl(Me.TextBox1) * CDbl(Me.TextBox2)7.429 with (Me.TextBox1) * (Me.TextBox2)can it be stg related with int'l settings?

[GTO]

Hi mancubus,


Sub test()

MsgBox Val("2.3") * Val("3.23")
'returns 7.429 for me
End Sub

Could you post the workbook example so we could see exactly how it is returning 6 for you?

Mark

[mancubus]

hi mark. thanks. i just downloadad the file in post#13 by stranno, added another TextBox to the existing UF and inserted a final line in the UF's initialize event: TextBox5.Value = (Me.TextBox1) * (Me.TextBox2)

[snb]

Stranno's Dutch International Settings (comma as decimal separator, dot as 1000 separator) result in:


Sub M_snb()
x1 = Val("2,67") * Val("3,12") ' 6
x2 = Val("2.67") * Val("3.12") ' 8,3304

x3 = ("2,67") * ("3,12") ' 8,3304
x4 = ("2.67") * ("3.12") ' 83304

x5 = CDbl("2,67") * CDbl("3,12") ' 8,3304
x6 = CDbl("2.67") * CDbl("3.12") ' 83304
End Sub

[Aflatoon]

Per the Help file:

The Val function recognizes only the period (.) as a valid decimal separator. When different decimal separators are used, as in international applications, use CDbl instead to convert a string to a number.

[stranno]

Thanks all,
I think i know what to do. Since I have the dutch version of excel installed on my computer, I stick to CDbl.
Snb also thanks for your hint.
stranno