Beginner Variable Problem

  • Thread starter Thread starter doc_in_bc
  • Start date Start date
D

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
 
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)
 
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
 
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
Click to expand...


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
Click to expand...

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
Click to expand...
 
Back
Top