Creating a scientific calculator in VBA for Excel

Can be quite complex due to the wide range of mathematical functions and operations that a scientific calculator typically offers. In this example, I’ll provide a simplified version that includes basic arithmetic operations, square root, exponentiation, and trigonometric functions (sine, cosine, tangent). Click here for Simple Calculator.

Here are the steps to create a basic scientific calculator in Excel using VBA:

1. Open your Excel workbook and press ALT + F11 to open the Visual Basic for Applications (VBA) editor.
2. Insert a UserForm by clicking Insert > UserForm.
3. Add the following controls to the UserForm:
   • Textbox for input and display (e.g., TextBoxInput).
   • Labels for displaying the current operation or mode (e.g., LabelMode).
   • Buttons for numbers (0-9), arithmetic operations (+, -, *, /), clear (C), equal (=), square root (√), exponentiation (^), sine (sin), cosine (cos), and tangent (tan).
4. Design the UserForm to your liking, adjusting the size and layout of the controls as needed.
5. In the code window of the UserForm, add the following code:

Option Explicit

Dim CurrentValue As Double
Dim CurrentOperation As String
Dim MemoryValue As Double

Private Sub UserForm_Initialize()
    TextBoxInput.Value = ""
    LabelMode.Caption = ""
End Sub

Private Sub NumericButton_Click()
    TextBoxInput.Value = TextBoxInput.Value & Me.ActiveControl.Caption
End Sub

Private Sub OperatorButton_Click()
    CurrentValue = Val(TextBoxInput.Value)
    TextBoxInput.Value = ""
    CurrentOperation = Me.ActiveControl.Caption
    LabelMode.Caption = CurrentOperation
End Sub

Private Sub ClearButton_Click()
    TextBoxInput.Value = ""
    CurrentValue = 0
    CurrentOperation = ""
    LabelMode.Caption = ""
End Sub

Private Sub EqualButton_Click()
    Dim Result As Double
    Dim SecondValue As Double

    SecondValue = Val(TextBoxInput.Value)

    Select Case CurrentOperation
        Case "+"
            Result = CurrentValue + SecondValue
        Case "-"
            Result = CurrentValue - SecondValue
        Case "*"
            Result = CurrentValue * SecondValue
        Case "/"
            If SecondValue = 0 Then
                TextBoxInput.Value = "Error"
                Exit Sub
            Else
                Result = CurrentValue / SecondValue
            End If
        Case "^"
            Result = CurrentValue ^ SecondValue
    End Select

    TextBoxInput.Value = Result
    CurrentValue = Result
    CurrentOperation = ""
    LabelMode.Caption = ""
End Sub

Private Sub SqrtButton_Click()
    CurrentValue = Val(TextBoxInput.Value)
    If CurrentValue < 0 Then
        TextBoxInput.Value = "Error"
    Else
        TextBoxInput.Value = Sqr(CurrentValue)
    End If
End Sub

Private Sub TrigButton_Click()
    CurrentValue = Val(TextBoxInput.Value)
    Select Case Me.ActiveControl.Caption
        Case "sin"
            TextBoxInput.Value = Sin(CurrentValue)
        Case "cos"
            TextBoxInput.Value = Cos(CurrentValue)
        Case "tan"
            TextBoxInput.Value = Tan(CurrentValue)
    End Select
End Sub

Private Sub MemoryButton_Click()
    MemoryValue = Val(TextBoxInput.Value)
End Sub

Private Sub RecallButton_Click()
    TextBoxInput.Value = MemoryValue
End Sub

This code sets up event handlers for the various buttons on the calculator UserForm. It handles numeric input, basic arithmetic operations, clearing the input, performing calculations, and calculating square roots and trigonometric functions. Click here for simple calculator.

6. Add code to display the UserForm in your workbook, either through a button click or a worksheet event.

That’s a simplified example of creating a scientific calculator in VBA for Excel. You can enhance it by adding more functions, error handling, and a better user interface. Additionally, consider implementing keyboard input for better usability.