Excel Standalone Calculator

Creating a calculator app is a great example to practice UI design and logic programming. In this post, we see how to do that in Excel using VBA macros. The calculator is a Userform with a text box for the display and several buttons for numbers, operators, and other functions. Excel Calculator is standalone and appears independently with Excel running in the background.

 

Why Creating a Calculator?

We all know how a calculator looks like and how it works, and therefore, we do not need to spend much time thinking about the design and functionality. For that reason, creating a calculator is a great project to learn and practice any programming language. We can benefit from learning two key components: user interface (UI) design, and programing logic.

Every calculator has a display and buttons with numbers, operators, and other functions. As an example, see below the calculator in Windows 10.

 

Calculator Userform

This Excel calculator is a Userform with a text box as a display that also allows manual user input, and several buttons to enter numbers, perform operations, delete digits and clear the display. This version is a simple calculator with basic operators, but once we understand how to do it, we can more add buttons to include more operators or other functionality.

 

Userform elements get a general name by default (e.g. TextBox1, Button1, Button2), but is good practice to change it to a meaningful name. This version of Excel calculator uses the name “Display” for the display or screen text box, and btn followed by the number for input numbers (btn0, btn1, btn2, …). The name for other buttons could follow that same pattern (btnClear, btnDelete, etc). The version of Excel Calculator available here may have named some buttons using a relevant expression without the “btn”.

 

Calculator Numbers

Excel Calculator buttons associated with a number trigger the same macro when clicked (AddNumber). Nevertheless, we need to add the Click event procedure for each button. Here’s the example for number 1.

 
  Private Sub btn1_Click()
      Call AddNumber
  End Sub
 

 

That macro (AddNumber) gets the number of the button and inserts the corresponding digit into the display text box. We can know which button was clicked with the following general expression:

 
  UserForm1.ActiveControl.Name
 

 

Alternatively, we could pass an attribute with the number when calling the macro.

  
  Call AddNumber(1)
 

 

The sub-routine AddNumber (and AddDot) needs to check certain conditions before actually inserting the number. Among these, we need to check the following:

• Replace 0 when adding the first number
• Replace the value if any of the operators was clicked (see later)
• Check for a dot when adding decimals (do not allow more than 1 dot)

 

Note that entering a number with the keyboard requires further conditions such as filtering out non-numerical values, capturing other possible keystrokes (return, delete, …), etc. For now, let’s assume we only use the buttons in the Excel Userform calculator.

 

Calculator Functions

Excel Calculator includes the four basic operators (addition, subtraction, multiplication, and division), the equal function, backspace to delete numbers (<<), clear value (CE), and clear all (C). Any of the operators (including the equal sign) trigger the sub-routine AddOperator, which performs the corresponding operation. The click event procedure of the button passes an attribute with the operator. See below an example for button “+”.

  
  Private Sub btnPlus_Click()
      Call AddOperator("+")
  End Sub
 

 

AddOperator sets the public Boolean newNumber to true to indicate an operator is used. That ensures the display is replace with the next number added. At the same time, another two public variables (operator and result) store the character for the operation and the value in the display, respectively, in order to compute the operation later when equal is clicked.

The backspace button (<<) deletes the last digit in the display. It also checks whether there is only one number left and replaces with zero. The clear buttons replace any value in the display with zero. When using replace all (C), the public variables “operator” and “result” are reset.

 

Keyboard Functionality

Generally, a calculator also allows entering numbers and performing operations via the keyboard or pad. For that reason, we need to anticipate certain events to account for any input via the keyboard. However, this version of Excel Calculator only accepts keyboard input when the display text box is selected (has focus). Thus, we use the following key-related events of the Userform text box:

• KeyDown
• KeyPress
• KeyUp

 

KeyDown accepts a KeyCode attribute that can capture the event of pressing RETURN or BACKSPACE. Here’s an example that calls the AddOperator procedure when the keyboard RETURN is pressed.

  
  Private Sub Display_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
      If KeyCode = vbKeyReturn Then
          Call AddOperator("=")
      End If
  End Sub
 

 

KeyPress accepts a KeyAscii attribute that is used to block the input of characters other than just numbers. If the key pressed is not a number (ascii codes between 48 and 57) or a dot (ascii code 46), we can block inserting the character by setting the Locked property of the display text box to true. Find the full list of Ascii characters in this other page: ASCII Characters Code Set

   
  Private Sub Display_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
      If KeyAscii < 48 Or KeyAscii   > 57 Then
          Display.Locked = True
      End If
  End Sub
 

Then we need to unlock the display text box afterwards. That happens in the KeyUp event procedure, which is triggered after the other two events and the Change event of the text box have occurred.

KeyPress is also used to identify any operators pressed via the keyboard. That allows calling the sub-routine AddOperator for the corresponding operator as per KeyAscii.

 

Standalone Excel Application

As we have seen in some previous articles, we can make a Userform stand alone by setting the Visible property of the Application object to false when the workbook opens. The following code needs to be added to the module ThisWorkbook. 

 
  Private Sub Workbook_Open()
      Application.Visible = False
      CalcForm.Show
  End Sub
 

We can make the calculator fully standalone adding the following code to the UserForm code to close Excel when clicking the close X button at the top right corner of the form.

  
  Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
      ThisWorkbook.Close savechanges:=False
  End Sub
 

  

Download Excel Standalone Calculator