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 (please read my post on How to add Command Button in Excel ? )on your worksheet and add the following lines of code:
‘declare a variable with name x and variable data type as Integer
Dim x As Integer
‘Assign the value of range A1 in excel sheet to x variable
x = Range(“A1”).Value
‘Create a formula for our calculation test. here we are adding 1 to variable x value
x = x + 1
‘Update the new value of variable x in range A1
Range(“A1”).Value = x
To test this code exit the Visual Basic Editor and enter a value into cell A1. In our below example we have entered 2.
Click on CommandButton1 to see how the value of cell A1 is incremented each time you click on CommandButton1.
Result:
First Click
Second Click
Third Click
We have just created a counter in Excel VBA. The number will increment by 1, till we keep clicking on command button.
Thank you for reading. Happy Learning !