Monday, February 16, 2015

Write a simple calculation Macro in Excel VBA

Calculate with Excel VBA and add, subtract, multiply and divide values just like you are used to doing in Excel.

Let's develop a small macro which involves a simple calculation (adding a value to a variable) and a very important programming technique.

Place a command button on your worksheet and add the following code lines:

Dim x As Integer
x = Range("A1").Value

x = x + 1
Range("A1").Value = x

1. The first code line declares a variable with name x of type Integer.

2. Next, we initialize this variable with the value of cell A1.

3. We want to add 1 to the variable x. we can do this by adding the line 'x = x +1'.
In Excel Visual Basic (and in other programming languages), the symbol '=' means becomes. It does not mean equal.
So x = x + 1 means x becomes x + 1.
In other words: take the present value of x and add 1 to it. Example: If x = 6, x becomes 6 + 1 = 7.

4. Finally, place the variable with the new value into cell A1.
Exit the Visual Basic Editor and enter a value into cell A1. Click on CommandButton1 to see how the value of cell A1 is incremented each time you click on CommandButton1.

Result:






We have just created a counter in Excel VBA.

No comments:

Post a Comment