Blog

In this tutorial from Iran VBA’s free tutorial series, we will create our first programme in the Excel environment and practise fundamental VBA concepts hands-on. If you are new to VBA and want to write your first real programme, this tutorial is the ideal starting point.

What You Will Learn in This Tutorial

• How to use InputBox to get user input
• Performing mathematical operations (exponentiation) in VBA
• How to display results using MsgBox
• Creating and running a macro in Excel

Prerequisites for this section:
VBA Code Editor
Module in VBA
Variable in VBA
Constant in VBA
VBA Operators
Operator Precedence
Procedure in VBA
Built-in VBA Functions
Immediate Window

In previous tutorials, we familiarised ourselves with concepts such as the VBE editor, modules, variables, constants, operators, and procedures. Now it is time to apply what we have learned by creating a real programme.

Although this programme is simple, it provides a deeper understanding of VBA’s logical structure and how commands are executed in Excel.

Note: In all projects on the site, we follow programming principles step by step to strengthen your skills progressively.

Your First VBA Programme

Programme Objective

To calculate the square of a number entered by the user and display the result as a message.

Programme Design and Implementation Steps

• Step One: Precise Problem Definition
• Step Two: Understanding Data Types & Constraints
• Step Three: Handling Edge Cases
• Step Four: Algorithm Design / Logical Planning
• Step Five: Concept of a Robust / Error-Resistant Programme
• Step Six: Implementation / Coding

Step One: Precise Problem Definition

The problem is that we want to calculate the square of a number. In the everyday world, this is done by opening a calculator or multiplying the number by itself. In programming, the issue is not just calculation; there are other considerations that must be taken into account.

Primary Considerations:

1. User: Who or what inputs the number and what do they expect from the result? How should user behaviour be managed so the programme achieves its goal and does not encounter errors?
2. Input Data: What type of data is entered and what limitations does it have?
3. Data Processing: How is the input data converted to output and what is the logic?
4. Output: How is the result displayed or where is it used?

Step Two: Understanding Data Types & Constraints

After defining the problem, we need to know what data our programme can accept and what constraints exist. Even for calculating the square, this step is essential to make the programme robust and resilient.

Primary Considerations:

1. Input Type: Does the programme accept integers, decimals, negative, or positive numbers?
2. Input Constraints: What is not allowed and how should the programme handle it? (e.g., text or empty input)
3. User Expectations: What does the user expect to happen when invalid input is entered?
4. Error Handling Consideration: How should the programme react so the user is not confused and the programme does not crash?

Even for a simple problem, programming means thinking about all possible conditions; not just calculating a number. Understanding data and constraints is a vital step towards achieving a stable and reliable programme.

Step Three: Handling Edge Cases

A programme, even for a simple task like calculating the square, must be able to handle unexpected conditions and unusual inputs correctly. This step makes the programme resilient and robust, preventing unwanted errors.

Primary Considerations:

1. Empty Input: What happens if the user does not enter any number?
2. Invalid Input: If the user enters text or a number outside a logical range, how should the programme react?
3. Special Numbers: What effect might negative numbers, zero, or very large numbers have on processing?
4. Programme Behaviour Prediction: The programme should react in all these conditions without crashing and with an appropriate message.

This step shows that programming is not just calculation; it is designing a small machine that can predict all possible scenarios and respond appropriately when faced with abnormal conditions.

Step Four: Algorithm Design / Logical Planning

Before writing even one line of code, the logical path for performing the task must be specified. This step involves thinking about how to convert input to output. Even for calculating the square of a number, having a clear algorithm is essential for the programme to be robust and understandable.

Primary Considerations:

1. Determining Processing Steps:
   • Getting input from the user
   • Validating the input
   • Calculating the square
   • Displaying the result to the user
2. Logical Flow of Operations:
   Each step must be performed in the proper sequence to produce the correct output.
3. Predicting Responses in Unusual Conditions:
   The algorithm must include how to handle empty or invalid input.
4. Simplicity & Clarity:
   Even a simple programme must have a clear and traceable algorithm, so that a human or machine can follow the steps without ambiguity.

This step shows that programming means thinking step by step; before writing code, we must know what we are doing and what logical path we must follow.

Step Five: Concept of a Robust / Error-Resistant Programme

The goal of this step is for the programme not only to calculate correctly but also to behave appropriately when faced with unexpected conditions or wrong inputs, without crashing. Even for a simple programme like calculating the square, this step is important to make the programme reliable and resilient.

Primary Considerations:

1. Programme Stability:
   The programme should not crash with unexpected or empty data input.
2. Appropriate Response to Errors:
   Instead of crashing or producing wrong output, an appropriate message should be displayed to the user, or the programme should stop in a controlled manner.
3. User Trust:
   A robust programme ensures the user is confident that each time, with valid or invalid data, they will receive a logical and predictable result.
4. Preventing Future Problems:
   Even if we later want to expand the programme, following these principles makes adding new features easier and less error-prone.

This step shows that programming is not just writing a formula, but designing a “small, reliable machine” that can work correctly in the real world with various inputs and manage errors.

Step Six: Implementation / Coding

In this step, the algorithm and thinking designed in steps 1 to 5 are converted into actual commands in the VBA programming language or environment. Even for a simple programme like calculating the square, following the steps below makes the programme stable, understandable, and extensible.

Step 1: Creating an Executable Procedure

In VBA, no command executes independently. All code must be inside Procedures (Routines) so Excel knows where to start the programme and where it ends.

A procedure is essentially like the main framework of the programme. Inside it, we write what tasks the programme performs and in what order.

As we discussed in the tutorial Procedure in VBA | Definition and Types of Procedures in Visual Basic, there are two main types of procedures:

1. Sub → For tasks that are simply performed and display a result.
2. Function → For calculation and returning a result to another part of the programme (e.g., inside an Excel cell).

In a programme like calculating the square, our goal is only to execute a simple process and display the result to the user, not to return a value to a cell or another function.
Therefore, choosing a Sub procedure is more logical because:

• It can be executed directly,
• It does not need to return a value,

The general structure of a Sub is as follows:

Sub Procedure_Name()
    ' This is where the programme commands are written
End Sub

For our programme, we choose the name SquareNumber.
This name consists of two parts:

• Square meaning “squared”
• Number meaning “number”

This naming means that even someone looking at the code understands what this procedure does without additional explanation.

Therefore, the start of our programme will be:

Sub SquareNumber()
    ' In subsequent steps, the code to calculate and display the square will be written here
End Sub

In this step, we created the main framework of the programme. From the next step onwards, we will complete the logic within this framework — i.e., defining variables, receiving input, and calculating the result.

Step 2: Getting Input from the User

In any programme, Input is the starting point. A programme cannot perform any processing without data. In this example, we must get a number from the user to calculate its square.

Defining a Variable to Store Data

Before getting input, we need a place to store it — i.e., a Variable.
In VBA, we use the Dim command to define a variable:

Dim userInput As String

Here we defined a variable named userInput to store the user’s input.

Getting the Value from the User

There are various methods for receiving data from the user:

1. Getting data from Excel cells
   If the input is entered in the sheet, it can be read directly from a cell. For example, the following code reads the input value directly from cell A1 in Excel:

userInput = Range("A1").Value

2. Using a UserForm
   Graphical forms include text boxes, buttons, and other controls and are suitable for larger programmes.
3. Getting input with InputBox
   The InputBox function is the simplest method for getting a value from the user in VBA. This function opens a window and asks the user to enter a value. In this programme, we use this method to receive user input.
   

userInput = InputBox("Please enter your desired number:")

After confirmation, the entered value is stored in the userInput variable and is ready for processing in the next step. We defined the userInput variable as String because the return value of the InputBox function is a String value.

To learn more about data types in VBA, you can view this page.

Note that the VBE editor utilises an advanced auto-complete feature as well as displaying information and properties.

You can use the auto-complete feature while typing code to reduce typographical errors and speed up writing code, and use the information display feature about functions and objects to write correct code.

For example, while writing the InputBox function, the VBE editor will display information as shown below:

According to the information VBE displays for the InputBox function, this function has a return value of type String and has 7 arguments, only the first of which (Prompt) is mandatory.

If you mistakenly choose the data type of the userInput variable as a numeric type (for example, Integer or Double) and the user enters a non-numeric value, a compiler error for type mismatch (Error 13) will occur as shown below, and your programme will stop.

In this step, we learned:

• How to define a string variable
• What methods exist for getting input in VBA
• And how to get a value from the user with InputBox

Our programme’s code so far:

Sub SquareNumber()
    Dim userInput As String
    userInput = InputBox("Please enter your desired number:")
End Sub

Step 3: Validating User Input

When we receive a number from the user, the input might be empty, cancelled, or non-numeric. If the programme continues without checking, an error will occur. Therefore, before calculating the square, the input must be validated.

Important Note About Cancel

• When the user presses the Cancel button, InputBox returns the value empty string (“”).
• Also, if the user does not enter a value and presses Enter, the same empty string is returned.
• Therefore, checking for an empty string covers both Cancel and empty input.

Validation Steps

1. Input is initially received as a String.
2. We check that the string is not empty (including Cancel). For this purpose, we add the following code to our programme to check the value of the userInput variable. In this code, if the value of the userInput variable is equal to an empty string "", a warning message is displayed to the user, and we exit the programme using Exit Sub.

If userInput = "" Then
    MsgBox "Input is empty or user pressed Cancel.", vbExclamation
    Exit Sub
End If

3. We check that the string is numeric. To check if the user’s input is a number, we use the IsNumeric function. The IsNumeric function checks an Expression and returns True if the expression is a numeric value, otherwise it returns False. We add the following code to our programme for this purpose:

If Not IsNumeric(userInput) Then
    MsgBox "Input is not valid. Please enter a number.", vbExclamation
    Exit Sub
End If

• In the code above, if the value of the userInput variable is not numeric, the message “Input is not valid. Please enter a number.” is displayed to the user and the programme stops.

4. Converting user input to a numeric value. In the previous step, we stored the user’s input in a String variable named userInput. To be able to perform mathematical operations on the user’s input value, we must convert it from String to a numeric type like Double. For this purpose, we define a new variable named num as Double and use the CDbl function to convert the data type of the userInput variable to Double and store it in the num variable.

Dim num As Double
num = CDbl(userInput)

To learn more about built-in VBA functions, you can view this page.

Our programme’s code so far:

Sub SquareNumber()
    Dim userInput As String  ' Input is initially received as a string
    Dim num As Double        ' Variable to hold the number after validation
    
    ' Getting input from the user
    userInput = InputBox("Please enter your desired number:")
    
    ' Validating input
    If userInput = "" Then
        MsgBox "Input is empty or user pressed Cancel.", vbExclamation
        Exit Sub
    End If
    
    If Not IsNumeric(userInput) Then
        MsgBox "Input is not valid. Please enter a number.", vbExclamation
        Exit Sub
    End If
    
    ' Converting string to number
    num = CDbl(userInput)
    
    ' The square calculation will be performed in the next step
End Sub

Step 4: Performing Calculation and Displaying Result

Calculating the Square

When the input is correctly received, we calculate its square. This is the data processing part. In this step, we define the variable result to store the square of the user’s input and of type Double.

Dim result As Double     ' Variable to hold the square of the number

The square of a number means multiplying the number by itself. In VBA, you can use two methods to calculate the square:

1. Simple multiplication:

result = num * num

2. Built-in function ^ (exponentiation):

result = num ^ 2

Both methods give the same result. In this example, for simplicity, we choose the simple multiplication method.

Displaying the Result

To display the result to the user, we use the MsgBox function.

MsgBox "The square of " & num & " is " & result

This line shows the message clearly, and the user understands the result.

Final Programme Code:

Sub SquareNumber()
    Dim userInput As String  ' Input is initially received as a string
    Dim num As Double        ' Variable to hold the number after validation
    Dim result As Double     ' Variable to hold the square of the number
    
    ' Getting input from the user
    userInput = InputBox("Please enter your desired number:")
    
    ' Validating input
    If userInput = "" Then
        MsgBox "Input is empty or user pressed Cancel.", vbExclamation
        Exit Sub
    End If
    
    If Not IsNumeric(userInput) Then
        MsgBox "Input is not valid. Please enter a number.", vbExclamation
        Exit Sub
    End If
    
    ' Converting string to number
    num = CDbl(userInput)
    
    ' Calculating the square
    result = num * num
    
    ' Displaying the result to the user
    MsgBox "The square of " & num & " is " & result
End Sub

Step 6: Testing the Programme

Our final programme code was prepared in the previous stage. Now, to test the programme, we can use the Immediate window or the Run menu in the VBE editor. In this tutorial, we will save the programme code as an Excel macro and run it.

To learn more about the Immediate window and how to call procedures through this window, you can view this page.

Preparing the Programme for Execution

In the previous steps, we wrote the programme code in full. Now it is time to prepare our programme for execution.

Choosing the Host Application

VBA codes cannot run independently and must be executed through a host application. The host application can be one of the Office suite programmes that supports VBA. To run this VBA programme, we have chosen Excel as the host application.

Before continuing with creating a module to store the codes for the first VBA programme, be sure to read about how to run a macro in Excel via this link.

Creating a Module to Store the Codes

Now we must choose a module to store our codes. In our first VBA programme, we have chosen the Excel object ThisWorkbook.

By double-clicking on the ThisWorkbook object, the code window for this object will be activated as shown below. Copy the programme code into the code window of this module.

Running the Programme as an Excel Macro

To run the macro, close the VBE editor and as shown below, click on the Macros button from the Developer tab.

In the Macro window, select the ThisWorkbook.SquareNumber macro and click the Run button to execute the programme.

Congratulations! 🎉 You have run your first VBA programme. Enter a number to see its square displayed in the message window.

🔍 Debugging Tips and Potential Errors

• If the macro does not run: Make sure you have set the macro security settings in Excel to “Enable all macros”. (These settings can be accessed via File > Options > Trust Center > Trust Center Settings > Macro Settings)

🧩 Exercise for You

• Modify the programme to calculate the cube of the input number instead of the square.
• Ask the user to enter their name and in the output message, display a sentence like “Hello [user name], the calculation result is [number]”.

Summary:

In this tutorial, you successfully built your first VBA programme in the Excel environment. By running this simple programme, you practically experienced key programming concepts including receiving input, processing logic, and displaying output. This exercise showed that the power of VBA programming goes beyond merely performing calculations and gives you the ability to automate tasks.

If you are ready to expand your VBA and macro programming skills and automate more complex tasks in your Excel, be sure to join us in the next tutorials!

Range("A1").Value = result