Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Beginner Variable Problem
First Day Learning VBA for Excel. I've written the following su
routine, Option Explicit Sub SunnyDay() Dim Input1 Dim Input2 Dim Total Input1 = InputBox("Enter the first amount!") Input2 = InputBox("Enter the second amount!") Total = Input1 + Input2 MsgBox "The total amount is " & Total End Sub Why does the Total Amount of 2+2 display as 22? If I change th operators to * , / , or - , I get the correct arithmetic answer. Wit the + I get concatenation instead of addition. Curious. Cheers Dodu -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Beginner Variable Problem
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Beginner Variable Problem
Doug,
It's treating the input as strings and the + operator is concatenating the strings, not adding the value of those strings. You could try Total = CDbl(Input1) + CDbl(Input2) or declare the variables as double Dim Input1 As Double Dim Input2 As Double Either way you should add some error handling to ensure you don't get bad input. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "doc_in_bc " wrote in message ... First Day Learning VBA for Excel. I've written the following sub routine, Option Explicit Sub SunnyDay() Dim Input1 Dim Input2 Dim Total Input1 = InputBox("Enter the first amount!") Input2 = InputBox("Enter the second amount!") Total = Input1 + Input2 MsgBox "The total amount is " & Total End Sub Why does the Total Amount of 2+2 display as 22? If I change the operators to * , / , or - , I get the correct arithmetic answer. With the + I get concatenation instead of addition. Curious. Cheers Dodug --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Beginner Variable Problem
Because you have not specified the type of input variable, input box
defaults to a string so Input1 + Input2 is concatentating "2" + "2" Change the Dim lines to Dim Input1 as Double Dim Input2 as Double This tells VBA to expect a numerical value. Caution do not rely on this method, if the user enters anything other than a numerical value you will get an error. Cheers Nigel "doc_in_bc " wrote in message ... First Day Learning VBA for Excel. I've written the following sub routine, Option Explicit Sub SunnyDay() Dim Input1 Dim Input2 Dim Total Input1 = InputBox("Enter the first amount!") Input2 = InputBox("Enter the second amount!") Total = Input1 + Input2 MsgBox "The total amount is " & Total End Sub Why does the Total Amount of 2+2 display as 22? If I change the operators to * , / , or - , I get the correct arithmetic answer. With the + I get concatenation instead of addition. Curious. Cheers Dodug --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Beginner Variable Problem
Doug
InputBox returns a string, and when using "+" VBA apparently uses concatenation on strings instead of summing as the default operation. Since "-", "*" and "/" can't be used for string manipulation, Excel try to convert the strings before carrying out the calculation in these instances. Two ways to overcome it: Dimensioning the variables explicitly. Dim Input1, dimensions Input1 as the variable type "Variant", which will hold, what's put into it :-) Sub SunnyDay() Dim Input1 As Double 'Or any other numeric type Dim Input2 As Double Dim Total Input1 = InputBox("Enter the first amount!") Input2 = InputBox("Enter the second amount!") Total = Input1 + Input2 MsgBox "The total amount is " & Total End Sub Converting the string to a numeric type: Sub SunnyDay() Dim Input1 Dim Input2 Dim Total Input1 = InputBox("Enter the first amount!") Input2 = InputBox("Enter the second amount!") Total = CDbl(Input1) + CDbl(Input2) MsgBox "The total amount is " & Total End Sub Actually it's only necessary to convert one of the values. Excel will get the drift :-) Sub SunnyDay() Dim Input1 Dim Input2 Dim Total Input1 = InputBox("Enter the first amount!") Input2 = InputBox("Enter the second amount!") Total = CDbl(Input1) + Input2 MsgBox "The total amount is " & Total End Sub -- Best Regards Leo Heuser Followup to newsgroup only please. "doc_in_bc " skrev i en meddelelse ... First Day Learning VBA for Excel. I've written the following sub routine, Option Explicit Sub SunnyDay() Dim Input1 Dim Input2 Dim Total Input1 = InputBox("Enter the first amount!") Input2 = InputBox("Enter the second amount!") Total = Input1 + Input2 MsgBox "The total amount is " & Total End Sub Why does the Total Amount of 2+2 display as 22? If I change the operators to * , / , or - , I get the correct arithmetic answer. With the + I get concatenation instead of addition. Curious. Cheers Dodug --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Beginner Variable Problem
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Beginner Variable Problem
You're welcome and thanks for the feedback :-)
LeoH "doc_in_bc " skrev i en meddelelse ... Thanx bunches ... makes sense! Cheers Doug |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with a defined variable | Excel Programming | |||
Object variable problem | Excel Programming | |||
vba variable problem | Excel Programming | |||
Mixed variable problem | Excel Programming | |||
Problem trying to us a range variable as an array variable | Excel Programming |