...

MS Excel VBA

Calculator Excel using VBA User Form Macros

Calculator Excel using VBA User Form Macros:Project Objective

Calculator using MS Excel VBA User Form. Following is the step-by-step detailed explanation to automate this scheme using VBA. How we are doing calculations, clearing the text, etc.

How we are proceeding to develop this project module (The KEY steps) :

To create a calculator, you can see the structure of the user form in the section. Let me describe the key steps to creating a calculator project. We are going to write multiple procedures for multiple controls with the down approach.

  • Step 1: Create User Form: we have to first create the User form from the Insert Toolbar in VBA Editor.
  • Step 2: Place toolbox controls on the made user form: Place the required controls on the user form. You can drag the controls from the toolbox and drop controls on the user form.
  • Step 3: Set properties and alignment of all controls: Properties of each control has set. It is shown in the design section.
  • Step 4: Clear Button: It is used to clear the display area.
  • Step 5: Back Button: It is used to go back to one digit.
  • Step 6: Divide (/) Button: It is used for division.
  • Step 7: Multiplication (*) Button: It is used for multiplication.
  • Step 8: Minus (-) Button: It is used for subtraction.
  • Step 9: Add (+) Button: It is used for addition.
  • Step 10: Dot (.) Button: It is used to add the dot to the number.
  • Step 11: One (1) Button: It is used to display the number 1.
  • Step 12: Two (2) Button: It is used to display the number 2.
  • Step 13: Three (3) Button: It is used to display the number 3.
  • Step 14: Four (4) Button: It is used to display the number 4.
  • Step 15: Five (5) Button: It is used to display the number 5.
  • Step 16: six (6) Button: It is used to display the number 6.
  • Step 17: Seven (7) Button: It is used to display the number 7.
  • Step 18: Eight (8) Button: It is used to display the number 8.
  • Step 19: Nine (9) Button: It is used to display the number 9.
  • Step 20: Zero (0) Button: It is used to display number .0
  • Step 21: Equal To (=) Button: It is used to perform all calculations like addition, subtraction, multiplication, etc.
  • Step 22: Text-box: It is used to display results.

Code explanation

Variable Declaration
  Public tVar As String
Public Cval As String  
To display a message when exceeding more than 10 numbers on the To display area.
  Private Sub txtResults_Change()
     If txtResults.TextLength > 10 Then
        MsgBox "Its Too long to calculate value.", vbInformation
        txtResults.Text = Left(txtResults.Text, 10)
        Exit Sub
    End If
End Sub  
To clearing data in the To display section.
  Private Sub Btnclr_Click()
    txtResults = 0: txtDisplay = Empty
End Sub  
To go back one step.
  Private Sub BtnBak_Click()
    If txtResults <> 0 And txtResults <> "" Then txtResults = Left(txtResults, Len(txtResults) - 1)
End Sub  
For divide button symbol.
  Private Sub BtnDvd_Click()
    If txtResults <> 0 Then
        txtDisplay = txtResults
        txtResults = 0
        Cval = "Divide"
    End If
End Sub  
For divide button multiplication symbol.
  Private Sub BtnMult_Click()
    If txtResults <> 0 Then
        txtDisplay = txtResults
        txtResults = 0
        Cval = "Multiplication"
    End If
End Sub  
For minus button symbol.
  Private Sub BtnMns_Click()
    If txtResults <> 0 Then
        txtDisplay = txtResults
        txtResults = 0
        Cval = "Minus"
    End If
End Sub  
For addition button symbol.
  Private Sub BtnAdd_Click()
    If txtResults <> 0 Then
        txtDisplay = txtResults
        txtResults = 0
        Cval = "Add"
    End If
End Sub  
For Dot button symbol.
  Private Sub BtnDot_Click()
    If txtResults <> 0 Then txtResults = txtResults + "."
End Sub  
Display Number 1
  Private Sub Btn1_Click()
    If txtResults = 0 Then
        txtResults = cmdBtn1.Caption
    Else
        txtResults = txtResults + Btn1.Caption
    End If
End Sub  
Display Number 2
  Private Sub Btn2_Click()
    If txtResults = 0 Then
        txtResults = Btn2.Caption
    Else
        txtResults = txtResults + Btn2.Caption
    End If
End Sub  
Display Number 3
  Private Sub Btn3_Click()
    If txtResults = 0 Then
        txtResults = Btn3.Caption
    Else
        txtResults = txtResults + Btn3.Caption
    End If
End Sub  
Display Number 4
  Private Sub Btn4_Click()
    If txtResults = 0 Then
        txtResults = Btn4.Caption
    Else
        txtResults = txtResults + Btn4.Caption
    End If
End Sub  
Display Number 5
  Private Sub Btn5_Click()
    If txtResults = 0 Then
        txtResults = Btn5.Caption
    Else
        txtResults = txtResults + Btn5.Caption
    End If
End Sub  
Display Number 6
  Private Sub Btn6_Click()
    If txtResults = 0 Then
        txtResults = Btn6.Caption
    Else
        txtResults = txtResults + Btn6.Caption
    End If
End Sub  
Display Number 7
  Private Sub Btn7_Click()
    If txtResults = 0 Then
        txtResults = Btn7.Caption
    Else
        txtResults = txtResults + Btn7.Caption
    End If
End Sub  
Display Number 8
  Private Sub Btn8_Click()
    If txtResults = 0 Then
        txtResults = Btn8.Caption
    Else
        txtResults = txtResults + Btn8.Caption
    End If
End Sub  
Display Number 9
  Private Sub Btn9_Click()
    If txtResults = 0 Then
        txtResults = Btn9.Caption
    Else
        txtResults = txtResults + Btn9.Caption
    End If
End Sub  
Display Number 0
  
Private Sub Btn0_Click()
    txtResults = txtResults + Btn0.Caption
End Sub  
Final Calculationser
  Private Sub BtnEql_Click()
On Error GoTo ErrOcccered
 
    
    If txtDisplay = "Cannot divide by Zero" Then txtDisplay = Empty
    
    If txtResults <> "" And Cval <> "" Then
        FNum = Val(txtDisplay): SNum = Val(txtResults)
        Select Case Cval
            Case "Add"
                txtResults = FNum + SNum
            Case "Minus"
                txtResults = FNum - SNum
            Case "Multiplication"
                txtResults = FNum * SNum
            Case "Divide"
                If SNum = 0 Then
                    txtResults = "Cannot divide by Zero"
                Else
                    txtResults = FNum / SNum
                End If
            Case Else
                
        End Select
        txtDisplay = Empty
    End If
ErrOcccered:

End Sub  

Display Calculator on the WorkSheet:

Here are steps to display the calculator on the user form.

  1. Place any shape by clicking on the insert menu from the illustrations group.
  2. Right-click on the shape, and select assign macro.
  3. select the shape name from the available list and click on the OK button.
  4. Now, go to the Developer tab.
  5. Design Mode should be turned off from the Controls group.
  6. Now, go back to the shape and click on the created shape to see the calculator on the user form.

Instructions to Execute the Procedure:

You can download the below file and see the code and execute it.

  1. Open the VBA Editor window or Press Alt+F11.
  2. Insert the user form from the Insert menu.
  3. Create a design as shown in the above steps.
  4. Add Procedures by double-clicking on a user form.
  5. Run the project by hitting the F5 key from the keyboard.
  6. hit the numbers on the calculator and see the output on the display area.
  7. And also you can use keyboard keys(up, down, left, right and enter to display numbers.

Leave a Comment

Your email address will not be published. Required fields are marked *


Scroll to Top