Beginner Variable Problem

[doc_in_bc]

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

 

[doc_in_bc]

Interesting that I can't spell my own name!
Dou

 

[Bob Phillips]

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)

 

[Nigel]

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

 

[Leo Heuser]

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

 

[doc_in_bc]

Thanx bunches ... makes sense!
Cheers
Dou

 

[Leo Heuser]

You're welcome and thanks for the feedback

LeoH