How to Create a Simple Database in Excel VBA

In this article, I’ll show you how you can create a simple database using VBA in Excel.

An Overview of a Database in Excel VBA

Our objective today is to create a database with given data in Excel using VBA. But before proceeding to the main discussion, let’s see what we are going to do first.

Let’s have a data set with the names, joining dates, and salaries of some employees of a company in an Excel worksheet.

Now we’ll convert this data set to a database. We’ll develop a process that’ll add or delete any row from the data set automatically.

To go for more illustrations, look at the image below. Here we’ve added 2 buttons along with 3 highlighted empty cells (G4, G5, and G6).

To add a new row to the database, one has to fill up the empty cells and click on the button Add Data.

For example, we’ve filled the Employee Name as Marcus North, the Joining Date as of 11/11/2011, and the Salary as 40,000$.

Now if you click on the button Add Data, you’ll find the data automatically added to a new row in the database.

Now if one clicks on the button Add Data keeping one or more cells empty, an input box will appear asking you to enter the data.

For example, if you keep the Joining Date empty, an input box will appear asking you to enter the Joining Date.

If you keep more than one cell empty, more than one input box will appear.

On the other hand, if you fill up one or more empty cells between G3, G4, and G5 and click on the button Delete Data, the row containing that data will be deleted from the database.

For example, we’ve filled Employee Name as Frank Orwell, and Salary as $36,000 (Highlighted in the database).

Now if you press Delete Data, the rows containing these data will be deleted.

Now, if you press Delete Data containing any data that doesn’t exist, a message box will appear informing you of this.

For example, if you put 50,000 as the Salary and press Delete Data, you’ll get a message like this.

Because there is no row with a salary of 50,000.

Read More: How to Create a Database with Form in Excel

How to Create a Simple Database in Excel VBA

Hope we’ve understood what we are going to achieve today. Now I’ll show you how we can accomplish this using VBA. For your convenience, I’m showing you the step-by-step process to execute this.

⧪ Step 1: Opening the Visual Basic Window

Press ALT + F8 on your keyboard. The Visual Basic window will open.

⧪ Step 2: Inserting New Modules

Go to the Insert > Module option in the toolbar. Click on Module. A new module named Module1 (Or anything else depending on your past history) will be inserted.

Repeat the procedure again. Another new module named Module2 will be inserted.

⧪ Step 3: Entering the First VBA Code

Copy and paste the following VBA code into Module1.

⧭ VBA Code:

Sub Add_Data()

Set Database = Range("B2:D12")
Set New_Input = Range("G3:G5")

Last_Row = Database.Rows.Count + 1
While Database.Cells(Last_Row, 1) <> ""
    Last_Row = Last_Row + 1
Wend

For i = 1 To New_Input.Rows.Count
    If New_Input.Cells(i, 1) <> "" Then
        New_Data = New_Input.Cells(i, 1)
        Database.Cells(Last_Row, i) = New_Data
    Else
        New_Input.Cells(i, 1).Select
        New_Data = InputBox("Enter the " + New_Input.Cells(i, 0) + ": ")
        Database.Cells(Last_Row, i) = New_Data
    End If
Next i

End Sub

⧭ Notes:

• This code creates a Macro called Add_Data.
• The first 2 lines of the code set the range of the database and the new input. Our database consists of the range B2:B12 and new input consists of the range G3:G5. You change it according to your needs.

⧪ Step 4: Entering the Second VBA Code

Similarly, enter this VBA code in Module2.

⧭ VBA Code:

Sub Delete_Data()

Set Database = Range("B2:D12")
Set New_Input = Range("G3:G5")

Deleted = 0

For i = 1 To New_Input.Rows.Count
   If New_Input.Cells(i, 1) <> "" Then
       For j = Database.Rows.Count To 1 Step -1
           If New_Input.Cells(i, 1) = Database.Cells(j, i) Then
               For k = 1 To Database.Columns.Count
                   Database.Cells(j, k).Delete
               Next k
               Deleted = Deleted + 1
           End If
       Next j
       If Deleted = 0 Then
           Heading = New_Input.Cells(i, 0)
           Data = New_Input.Cells(i, 1)
           If VarType(Data) <> 8 Then
               Data = Str(Data)
           End If
           MsgBox "There is No Row with " + Heading + " " + Data + "."
       End If
       Deleted = 0
    End If
Next i

End Sub

⧭ Notes:

• This code creates a Macro called Delete_Data.
• Here also, the first 2 lines of the code set the range of the database and the new input. Our database consists of the range B2:B12 and new input consists of the range G3:G5. You change it according to your needs.

⧪ Step 5: Adding Buttons to the Worksheet

Come back to the worksheet and go to the Developer > Insert option in the Excel toolbar. Click on Insert. Then choose Button from Form Controls.

Then drag the Button to your desired location in the worksheet and place it there. Double-click on it and rename it to Add Data (or anything you like).

Similarly, drag another Button and rename it to Delete Data (or anything).

⧪ Step 6: Assigning Macros to the Buttons

Right-click on the button Add Data. Then choose Assign Macro.

A dialogue box called Assign Macro will open. In the Macro Name field, enter Add_Data.

Similarly, assign the Macro Delete_Data to the button Delete Data.

⧪ Step 7: Observing the Output

The buttons are now ready to use. Fill up the empty cells and click on the buttons. You’ll get the desired output as mentioned in the overview section.

Read More: How to Maintain Customer Database in Excel

Things to Remember

Here, I’ve just added 2 buttons to the database to add or delete one or more rows from the database only. Obviously, there are far more operations that can be required to do to a database. But as it’s a simple database, I think it’s enough for today. We’ll add more buttons to the database when we dive deep into this.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

So we’re done today. We’ve created our simple database with Excel VBA. If you have any questions, feel free to ask us.

Related Articles

• How to Create a Searchable Database in Excel
• How to Create a Database That Updates Automatically in Excel
• How to Create a Membership Database in Excel
• How to Create a Database in Excel with Pictures

<< Go Back To Database in Excel | Learn Excel