Automate Data writing processes with Excel VBA

This script can be adapted for different use cases, such as inserting data into tables, updating cells based on specific conditions, or reading and writing to multiple sheets.

Scenario:

Imagine we have a table with company information that includes columns for the company name, annual revenue, region, and creation date. We want to automate the data entry into this table using VBA, filling these columns with predefined data.

1. Table Structure (Excel Sheet Example)

2. VBA Code to Automate Data Entry

Step 1: Open the VBA editor

1. Open Excel and press Alt + F11 to open the VBA editor.
2. In the « Insert » menu, choose « Module » to insert a new module.
3. Copy and paste the following code into the module window.

Sub AutomateDataEntry()
    ' Declare variables for the worksheet and data
    Dim ws As Worksheet
    Dim i As Integer
    Dim companies As Variant
    Dim revenues As Variant
    Dim regions As Variant
    Dim dates As Variant
    ' Reference the worksheet where data will be inserted
    Set ws = ThisWorkbook.Sheets("Sheet1")
    ' Define the data to be inserted
    companies = Array("Company A", "Company B", "Company C")
    revenues = Array(5000000, 12000000, 7500000)
    regions = Array("Europe", "America", "Asia")
    dates = Array("01/01/2010", "15/05/2012", "20/08/2015")
    ' Start writing data from row 2
    For i = 0 To UBound(companies)
        ' Insert company name into column A
        ws.Cells(i + 2, 1).Value = companies(i)      
        ' Insert revenue into column B
        ws.Cells(i + 2, 2).Value = revenues(i)      
        ' Insert region into column C
        ws.Cells(i + 2, 3).Value = regions(i)   
        ' Insert creation date into column D
        ws.Cells(i + 2, 4).Value = CDate(dates(i)) ' Convert to date format
    Next i  
    ' Confirmation message
    MsgBox "The data has been entered successfully!", vbInformation
End Sub

3. Explanation of the Code

Variable Declarations

Dim ws As Worksheet
Dim i As Integer
Dim companies As Variant
Dim revenues As Variant
Dim regions As Variant
Dim dates As Variant

• ws: A variable that refers to the worksheet where the data will be entered.
• i: A variable used to iterate through the loop.
• companies, revenues, regions, dates: Arrays that store the values to be inserted into the respective columns of the Excel sheet.

Referencing the Worksheet

Set ws = ThisWorkbook.Sheets("Sheet1")

This line references the specific worksheet in which we want to write the data. You can replace « Sheet1 » with the actual name of the sheet you’re working with.

Defining the Data

companies = Array("Company A", "Company B", "Company C")
revenues = Array(5000000, 12000000, 7500000)
regions = Array("Europe", "America", "Asia")
dates = Array("01/01/2010", "15/05/2012", "20/08/2015")

These arrays hold the data that will be inserted into the respective columns of the Excel sheet.

Loop to Insert Data

For i = 0 To UBound(companies)
    ws.Cells(i + 2, 1).Value = companies(i)
    ws.Cells(i + 2, 2).Value = revenues(i)
    ws.Cells(i + 2, 3).Value = regions(i)
    ws.Cells(i + 2, 4).Value = CDate(dates(i))
Next i

• For i = 0 To UBound(companies) : The loop starts at index 0 and runs until the end of the companies array (which is determined by UBound(companies)).
• ws.Cells(i + 2, 1).Value = companies(i) : For each iteration, the values from the arrays companies, revenues, regions, and dates are inserted into the corresponding cells. Since the arrays are zero-indexed but the data starts at row 2, we add i + 2 to the row number.
• CDate(dates(i)) : The CDate function is used to convert the date values into a valid date format.

Confirmation Message

MsgBox "The data has been entered successfully!", vbInformation

Once all the data has been entered, a message box appears to inform the user that the process was successful.

4. Running the Code

1. In the VBA editor, go to the « Run » menu and select « Run Sub/UserForm » or press F5 to execute the script.
2. Once you return to your Excel sheet, you should see the data automatically populated starting from row 2 in columns A to D.

5. Customization

You can easily customize this code by changing:

• The data in the arrays (companies, revenues, etc.).
• The sheet you’re working with by changing the sheet name in Set ws = ThisWorkbook.Sheets(« Sheet1 »).
• The range of cells where the data is entered.

6. Possible Applications

• Automatically filling reports: You can use this code to insert data from another source (e.g., a database, CSV file, etc.).
• Updating information: You can automate updating cell values based on certain conditions.
• Financial report generation: For example, you could calculate monthly revenue based on annual revenue.

This code provides a solid foundation for automating data entry in Excel and can be adapted to various use cases, helping save time on repetitive tasks.