Resources

    All about Visual Basic for Application (VBA) especially for Microsoft Excel.

Subscribe

  • Subscribe

How to sum Text Box value in Excel VBA? (+ Operator)

Posted by | October 2, 2011 .

+ Operator Example

This example uses the + operator to sum numbers. The + operator can also be used to concatenate strings. However, to eliminate ambiguity, you should use the & operator instead. If the components of an expression created with the + operator include both strings and numerics, the arithmetic result is assigned. If the components are exclusively strings, the strings are concatenated.

Dim MyNumber, Var1, Var2
MyNumber = 2 + 2 ‘ Returns 4.
MyNumber = 4257.04 + 98112 ‘ Returns 102369.04.
Var1 = “34″: Var2 = 6 ‘ Initialize mixed variables.
MyNumber = Var1 + Var2 ‘ Returns 40.
Var1 = “34″: Var2 = “6″ ‘ Initialize variables with strings.
MyNumber = Var1 + Var2 ‘ Returns “346″ (string concatenation).

The above explanation is about + Operator found in Microsoft Excel VBE help topic. When we use Userform and TextBox in VBA to sum the value for all TextBox we must use variable otherwise it will become String Concatenation meaning joint not add or plus. For example 2 + 2 will become 22 not 4 as total. Let try below example, before try we must add UserForm; add 4 TextBox and 1 button into this UserForm in VBE.

TextBox1 ~ TextBox3 for user to input, TextBox4 total value when we click the Button1

Now Double click the Button1 to insert below code:

Option Explicit
Private Sub CommandButton1_Click()
Dim iNum(1 To 3) As Double
iNum(1) = TextBox1.Value
iNum(2) = TextBox2.Value
iNum(3) = TextBox3.Value
TextBox4.Value = iNum(1) + iNum(2) + iNum(3)
End Sub

Now run this code, input any number into TextBox1 to TextBox3 then click Button1. Now you can see the result.What happen if we change your declaration Statement from Dim iNum (1 To 3) As Double to Dim iNum (1 To 3) As String. The answer will be different and this time we will get Concatenation not Addition.

Note: Why we use double instead of integer? If we declare as integer the answer is without decimal and limit to 32k, the chances we get error “Run time error ‘6’ over flow” is high. This is depending on your program needs or purpose.

If you have any question or project please e-mail to me, I will reply and create for free.
Click Here to E-mail

Thanks

Enjoy your learning.

Share Button
Share on Facebook
Bookmark this on Digg
Bookmark this on Yahoo Bookmark
Bookmark this on Google Bookmarks
Bookmark this on Delicious

Leave a Comment

If you would like to make a comment, please fill out the form below.

Name (required)

Email (required)

Website

Comments

2 Comments so far
  1. Nathaly August 8, 2012 7:59 pm

    Hello,

    After reading your article on:

    http://www.vbexcel.com/how-to-sum-text-box-value-in-excel-vba-operator/

    I used your code to in the Userform that I created with textboxes to sum up the total of textboxes by clicking a button and it appears in another textbox. Thank you very much for that!
    Is there any way to edit this code to still sum even if one of the Textboxes iNum(1-5) is empty. I am using these textboxes for users to input costs but sometimes there will be an empty textbox if an invoice has not yet been received.

    Here is my code so far that works to sum up the 5 textboxes but shows an error when I leave one of the textboxes blank:

    Private Sub CalculateTotalButton_Click()
    Dim iNum(1 To 5) As Double
    iNum(1) = TargetAPTextBox.Value
    iNum(2) = TargetPRTextBox.Value
    iNum(3) = TargetTransTextBox.Value
    iNum(4) = TargetSPTextBox.Value
    iNum(5) = TargetPCTextBox.Value

    TargetTotalTextBox.Value = iNum(1) + iNum(2) + iNum(3) + iNum(4) + iNum(5)
    End Sub

    Thank you in advance for your time and your help!!

  2. hisham hadi August 10, 2012 2:38 pm

    Here the answer you’re looking for.

    Hope you get my meaning. Thanks