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.
- Place any shape by clicking on the insert menu from the illustrations group.
- Right-click on the shape, and select assign macro.
- select the shape name from the available list and click on the OK button.
- Now, go to the Developer tab.
- Design Mode should be turned off from the Controls group.
- 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.
- Open the VBA Editor window or Press Alt+F11.
- Insert the user form from the Insert menu.
- Create a design as shown in the above steps.
- Add Procedures by double-clicking on a user form.
- Run the project by hitting the F5 key from the keyboard.
- hit the numbers on the calculator and see the output on the display area.
- And also you can use keyboard keys(up, down, left, right and enter to display numbers.