How to do basic math calculations on VBA(Visual Basic Application)💻

Visual Basic for Applications is a computer programming language developed and owned by Microsoft. With VBA you can create macros to automate repetitive word- and data-processing functions, and generate custom forms, graphs, and reports. Can do much more with it.

Image by Author

Subroutine procedures usually take arguments or code which are carried out to perform a specific task. Using the Sub procedure, we can set up the variable and type a variable to store a value inside the variable. After the naming, the sub procedure press enter key and “end sub” will add up automatically. So it starts with “sub” and ends with “end sub”.

Dim statement is used for declaring variables and allocating storage space. To declare a variable we use dim statement.

Range property in VBA helps to specify a particular cell or group of cells or a row or a column.

Note: can’t set up a variable and assign a value all on the same line. It shows you error.

👉Basic Math Operators

🟧 Addition operator : Adding two values with a sub procedure is created. A “Dim” statement is used for declaring the variables, so over here the variables are “Number_1” & “Number_2” and also declare their data type as “Integer” for both the variables. Assigning values for both the variables. Next, we are mentioning where the calculation result is to be seen, like on which sheet, which cell or group of cells you want to select to see the value based on argument. So we are mentioning the worksheet = 1, select cell = A12,B12, Value = Addition Answer, Number_1 + Number_2(value). Now we can see the value “Addition Answer” in “A12 cell” and “Number_1 + Number2” value in “B12 cell”.

Worksheets(1): Inside bracket you can also mention the worksheet name as “Sheet1” in double quotes. The number 1 means “worksheet 1" in the current workbook.

( ‘ ) Single quote is used for commenting in VBA.

'Adding two numbers

Sub add_numbers()
Dim Number_1 As Integer
Dim Number_2 As Integer

Number_1 = 10
Number_2 = 30

Worksheets(1).Range("A12").Value = "Addition Answer"
Worksheets(1).Range("B12").Value = Number_1 + Number_2
End Sub

🟧 Subtraction operator: Subtracting two values with a sub procedure is created. A “Dim” statement is used for declaring the variables, so over here the variables are “Number_1” & “Number_2” and also declare their data type as “Integer” for both the variables. Assigning values for both the variables. Next, we are mentioning where the calculation result is to be seen, like on which sheet, which cell or group of cells you want to select to see the value based on argument. So we are mentioning the worksheet = 1, select cell = A13,B13, Value = Subtraction Answer, Number_1 -Number_2(value). Now we can see the value “Subtraction Answer” in “A13 cell” and “Number_1-Number2” value in “B13 cell”.

'Subtracting two numbers

Sub subtr_numbers()
    Dim Number_1 As Integer
    Dim Number_2 As Integer
    
    Number_1 = 10
    Number_2 = 30
    
    Worksheets(1).Range("A13").Value = "Subtraction Answer"
    Worksheets(1).Range("B13").Value = Number_1 - Number_2
End Sub

🟧 Multiplication Operator: Multiplying two values with a sub procedure is created. A “Dim” statement is used for declaring the variables, so over here the variables are “Number_1” & “Number_2” and also declare their data type as “Integer” for both the variables. Assigning values for both the variables. Next, we are mentioning where the calculation result is to be seen, like on which sheet, which cell or group of cells you want to select to see the value based on argument. So we are mentioning the worksheet = 1, select cell = A14,B14, Value = Multiplication Answer, Number_1 * Number_2(value). Now we can see the value “Multiplication Answer” in “A14 cell” and “Number_1 * Number2” value in “B14 cell”.

 'Multiplying two numbers

Sub multiple_numbers()
    Dim Number_1 As Integer
    Dim Number_2 As Integer
    
    Number_1 = 10
    Number_2 = 30
    
    Worksheets(1).Range("A14").Value = "Multiplication Answer"
    Worksheets(1).Range("B14").Value = Number_1 * Number_2
End Sub

🟧 Division operator: Dividing two values with a sub procedure is created. A “Dim” statement is used for declaring the variables, so over here the variables are “Number_1” & “Number_2” and also declare their data type as “Integer” for both the variables. Assigning values for both the variables. Next, we are mentioning where the calculation result is to be seen, like on which sheet, which cell or group of cells you want to select to see the value based on argument. So we are mentioning the worksheet = 1, select cell = A15,B15, Value = Division Answer, Number_1 / Number_2(value). Now we can see the value “Division Answer” in “A15 cell” and “Number_1 / Number2” value in “B15 cell”.

'dividing two numbers

Sub div_numbers()
    Dim Number_1 As Integer
    Dim Number_2 As Integer
    
    Number_1 = 10
    Number_2 = 30
    
    Worksheets(1).Range("A15").Value = "Division Answer"
    Worksheets(1).Range("B15").Value = Number_1 / Number_2
End Sub

🟧 Mixing addition and subtraction: Assigning more variables and doing calculations on addition and subtraction. Over here assigning variable “Answer” telling VBA to do this calculation(Number_1 + Number_2-Number_3). So we are mentioning the worksheet = 1, select cell = A16,B16, Value = Mix_add_sub, Answer. Now we can see the value “Mix_add_sub” in “A16 cell” and “Answer” value in “B16 cell”.

 'adding & subtracting numbers

Sub basic_maths()
      Dim Number_1 As Integer
      Dim Number_2 As Integer
      Dim Number_3 As Integer
      Dim Answer As Integer
    
      Number_1 = 1
      Number_2 = 5
      Number_3 = 56
    
      Answer = Number_1 + Number_2 - Number_3

      Worksheets(1).Range("A16").Value = "Mix_add_sub"
      Worksheets(1).Range("B16").Value = Answer
End Sub

🟧 Mixing addition and Multiplication: Assigning more variables and doing calculations on multiplication and addition. Over here assigning variable “Answer” telling VBA to do this calculation(Number_1 * Number_2 + Number_3). So we are mentioning the worksheet = 1, select cell = A17,B17, Value = Mix_add_multi, Answer. Now we can see the value “Mix_add_multi” in “A17 cell” and “Answer” value in “B17 cell”.

'Multiplying & adding numbers

Sub mix_add_multi()
    Dim Number_1 As Integer
    Dim Number_2 As Integer
    Dim Number_3 As Integer
    Dim Answer As Integer
    
    Number_1 = 10
    Number_2 = 45
    Number_3 = 33
    
    Answer = Number_1 * Number_2 + Number_3
    
    Worksheets(1).Range("A17").Value = "Mix_add_multi"
    Worksheets(1).Range("B17").Value = Answer
    
End Sub

Mixing division and Multiplication: Instead of mentioning the variable names, we can also mention values such as in Answer = 10 * 45 / 33. So we are mentioning the worksheet = 1, select cell = A18,B18, Value = Mix_multi_div, Answer. Now we can see the value “Mix_multi_div” in “A18 cell” and “Answer” value in “B18 cell”.

'Multiplying & dividing numbers

Sub mix_multi_div()
    Dim Number_1 As Integer
    Dim Number_2 As Integer
    Dim Number_3 As Integer
    Dim Answer As Integer
    
    Number_1 = 10
    Number_2 = 45
    Number_3 = 33
    
    Answer = 10 * 45 / 33
    
    Worksheets(1).Range("A18").Value = "Mix_multi_div"
    Worksheets(1).Range("B18").Value = Answer
    
End Sub

🟧 Mixing all math operators : Now we are using all math operators at a time. So we are mentioning the worksheet = 1, select cell = A19,B19, Value = Mix_add_sub_multi_div, Answer. Now we can see the value “Mix_add_sub_multi_div” in “A19 cell” and “Answer” value in “B19 cell”. If you are using parenthesis in your calculation, VBA always follows BODMAS rule.

'Using all math operators to do calculation

Sub Mix_add_sub_multi_div()
    Dim Number_1 As Integer
    Dim Number_2 As Integer
    Dim Number_3 As Integer
    Dim Number_5 As Integer
    Dim Answer As Integer
    
    Number_1 = 55
    Number_2 = 23
    Number_3 = 3
    Number_4 = 12
    
    Answer = 23 - 3 / (55 + 12) * 3
    
    Worksheets(1).Range("A19").Value = "Mix_add_sub_multi_div"
    Worksheets(1).Range("B19").Value = Answer
End Sub

All the basic calculations in Excel

Happy learning!!!😊

🔰 If you like the article plz..do clap(click on the clap option below)😃 it would be a support 😁and also do share it 😊.

Follow me on 😎:

✅Medium

✅YouTube