In this article, I’ll show you how you can create a simple database using VBA in Excel.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
An Overview of a Database in Excel VBA
Our objective today is to create a database with a 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 covert 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.
How to Create the Database Using 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
- 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
- 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.[Click here to learn how to unhide the Developer tab in Excel in case it’s not shown].
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.
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.
So we’re done today. We’ve created our simple database with Excel VBA. If you have any questions, feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.