Catégorie : Excel VBA Course

The goal is to automate the data enrichment process in Excel using VBA, which might involve adding extra information (like postal codes and cities) to an existing dataset based on certain criteria.

Objective:

Let’s assume you have a worksheet with customer data, including columns like « First Name, » « Last Name, » « Email, » « Country, » and you want to add more information (for example, « Postal Code » and « City ») from an external source or dataset.

VBA Code Structure:

1. Import data (e.g., from an external file or API).
2. Clean the data (e.g., remove duplicates, check for errors).
3. Enrich the data (e.g., add external information like postal codes or cities based on the country).
4. Export the results or update the worksheet.

Detailed VBA Code:

Sub EnrichData()
    ' Declare variables
    Dim wsSource As Worksheet
    Dim wsDestination As Worksheet
    Dim LastRow As Long
    Dim i As Long
    Dim Country As String
    Dim PostalCode As String
    Dim City As String   
    ' Set worksheets
    Set wsSource = ThisWorkbook.Sheets("Sheet1") ' Source sheet with existing data
    Set wsDestination = ThisWorkbook.Sheets("Sheet2") ' Destination sheet where enriched data will go
    ' Find the last row with data in column A of the source sheet
    LastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    ' Loop through each row of data
    For i = 2 To LastRow ' Start from row 2 to skip headers
        ' Get the country value from the current row
        Country = wsSource.Cells(i, 4).Value ' Assume the "Country" is in column 4       
        ' Enrich the data based on the country (simple examples)
        If Country <> "" Then
            ' Enrichment logic: Adding a postal code and city based on the country
            Select Case Country
                Case "France"
                    PostalCode = "75000"
                    City = "Paris"
                Case "Belgium"
                    PostalCode = "1000"
                    City = "Brussels"
                Case "Germany"
                    PostalCode = "10115"
                    City = "Berlin"
                Case Else
                    PostalCode = "N/A"
                    City = "Unknown"
            End Select           
            ' Write the enriched data to the destination sheet
            wsDestination.Cells(i, 5).Value = PostalCode ' Add Postal Code to column 5
            wsDestination.Cells(i, 6).Value = City ' Add City to column 6
        End If
    Next i
    MsgBox "Data enrichment completed successfully!"
End Sub

Code Explanation:

1. Variable Declaration:
   • wsSource and wsDestination are variables referencing the source and destination worksheets.
   • LastRow is used to find the last row with data in column « A » of the source sheet.
   • Country, PostalCode, and City are variables used to temporarily store data for enrichment.
2. Set Worksheets:
   • wsSource is the worksheet containing the original data (e.g., customer details).
   • wsDestination is the worksheet where the enriched data (e.g., postal code and city) will be placed.
3. Find the Last Row:
   • LastRow is determined by looking for the last filled cell in column A (assuming column A contains customer data like « Name » or « Email »).
4. Loop to Process Each Row:
   • A For loop processes each row in the source sheet, starting from row 2 (to skip headers).
   • For each row, the value of the « Country » column (column 4) is retrieved.
5. Enrich the Data:
   • A Select Case structure checks the value of Country. Based on the country, it assigns a postal code and city (this is a simple example).
   • If a valid country is found, the corresponding postal code and city are written into the destination sheet (columns 5 and 6).
6. Completion Message:
   • After the loop is finished, a message box (MsgBox) pops up to notify that the data enrichment is complete.

Possible Enhancements:

• Importing Data from an API: You could enrich the data by pulling external information (e.g., via an API or a database). This would involve using XMLHttpRequest to query an API and retrieve data in JSON format.
• Data Validation and Cleaning: Before enriching the data, you can add steps to clean it, such as checking for missing or invalid country names.
• Enrichment from External Files: Instead of using hardcoded values, you could pull the postal code and city data from an external file (like another Excel file or a database) to enrich the data.

Conclusion:

This code provides a basic structure to automate the data enrichment process in Excel via VBA. It can be customized based on specific needs, such as integrating with external systems, handling more complex data, or performing additional data cleaning tasks.

 

Here is a detailed VBA code to automate the process of consolidating data from multiple Excel sheets or files into one master sheet. This example assumes you are consolidating data from several files or sheets with similar structures, and your goal is to gather and organize the data into a single consolidation sheet.

Objective:

• Open multiple Excel files or sheets.
• Consolidate data from these files/sheets into one master sheet.
• Perform tasks like removing duplicates and applying formatting.

General Steps:

1. Open source files.
2. Loop through each file/sheet to collect the data.
3. Copy the data to a consolidation sheet.
4. Remove duplicates (if needed).
5. Format the consolidation sheet (adding column headers, conditional formatting, etc.).

Example VBA Code:

Sub ConsolidateData()
    ' Declare variables
    Dim wsConsolidation As Worksheet
    Dim wbSource As Workbook
    Dim wsSource As Worksheet
    Dim lastRowConsolidation As Long
    Dim lastRowSource As Long
    Dim sourceFile As String
    Dim sourcePath As String
    Dim i As Integer
    Dim files As Variant    
    ' Create or activate the consolidation sheet
    On Error Resume Next
    Set wsConsolidation = ThisWorkbook.Sheets("Consolidation")
    On Error GoTo 0    
    If wsConsolidation Is Nothing Then
        Set wsConsolidation = ThisWorkbook.Sheets.Add
        wsConsolidation.Name = "Consolidation"
    End If    
    ' Clear previous data in the consolidation sheet
    wsConsolidation.Cells.Clear    
    ' Add column headers (customize based on your needs)
    wsConsolidation.Cells(1, 1).Value = "Name"
    wsConsolidation.Cells(1, 2).Value = "Surname"
    wsConsolidation.Cells(1, 3).Value = "Age"
    wsConsolidation.Cells(1, 4).Value = "Registration Date"    
    ' Define the source file path (adjust this path based on your setup)
    sourcePath = "C:\Users\YourName\Documents\Excel\Sources\"    
    ' List of files to consolidate (you can add more files here)
    files = Array("File1.xlsx", "File2.xlsx", "File3.xlsx")    
    ' Loop through each file
    For i = LBound(files) To UBound(files)        
        ' Open the source file
        sourceFile = sourcePath & files(i)
        Set wbSource = Workbooks.Open(sourceFile)        
        ' Loop through each sheet in the source workbook
        For Each wsSource In wbSource.Sheets            
            ' Find the last used row in the source sheet
            lastRowSource = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row            
            ' Find the first empty row in the consolidation sheet
            lastRowConsolidation = wsConsolidation.Cells(wsConsolidation.Rows.Count, "A").End(xlUp).Row + 1            
            ' Copy the data from the source sheet to the consolidation sheet
            wsSource.Range("A2:D" & lastRowSource).Copy wsConsolidation.Range("A" & lastRowConsolidation)
        Next wsSource        
        ' Close the source file without saving
        wbSource.Close False
    Next i    
    ' Remove duplicates in the consolidation sheet
    wsConsolidation.Range("A1:D" & wsConsolidation.Cells(wsConsolidation.Rows.Count, "A").End(xlUp).Row).RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes    
    ' Apply formatting to the consolidation sheet (optional)
    wsConsolidation.Columns.AutoFit
    wsConsolidation.Rows(1).Font.Bold = True
    wsConsolidation.Rows(1).HorizontalAlignment = xlCenter    
    ' Display a message indicating the process is complete
    MsgBox "Data consolidation is complete.", vbInformation    
End Sub

Detailed Explanation of the Code:

1. Creating and Preparing the Consolidation Sheet:
   • The code checks if a sheet named Consolidation already exists in the current workbook. If it doesn’t, it creates a new sheet with that name.
   • It then adds column headers to the first row of the consolidation sheet.
2. Defining the Source Files Path:
   • The path to the source files is specified in the sourcePath variable. You should adjust this path to where your source files are stored.
   • The list of files to be consolidated is stored in the files array. You can modify this array to include all the files you want to consolidate.
3. Looping Through Each Source File:
   • For each file in the files array, the code opens the file and loops through all the sheets in the workbook.
   • It finds the last used row in each source sheet and the next available row in the consolidation sheet.
   • The code then copies the data (from row 2 onward, to exclude headers) from the source sheet to the consolidation sheet.
4. Removing Duplicates:
   • After all data has been copied to the consolidation sheet, the code removes duplicates based on columns 1, 2, and 3 (Name, Surname, and Age).
   • You can change the columns in the RemoveDuplicates method if you want to base duplicates on other criteria.
5. Applying Formatting:
   • The code auto-fits the columns to adjust the width based on the data.
   • It applies bold formatting to the first row (headers) and centers the text in the headers.
6. Completion Message:
   • Once the consolidation is complete, a message box will appear, letting you know that the process is finished.

Customization:

• File Names and Columns: Modify the files array with the actual file names and adjust the column headers in the consolidation sheet according to your data structure.
• Duplicate Removal: If you want to remove duplicates based on other columns, modify the RemoveDuplicates line to include the relevant column indexes.
• Additional Formatting: You can add more formatting styles (like cell colors, fonts, etc.) if needed in the section for formatting.

Conclusion:

This VBA script will automate the process of consolidating data from multiple Excel files or sheets into one master sheet. You can further customize it to fit your specific requirements, such as handling different types of data or applying more complex formatting.

 

Automating data comparison in Excel using VBA can be extremely helpful when working with large datasets. This process can include comparing two columns, highlighting differences, or even automating the export of the results.

Here is a detailed VBA code to automate data comparison in Excel:

Scenario

Suppose you have two columns of data (e.g., Column A and Column B), and you want to compare the values in each row to see if they match. If they don’t match, you want to highlight the cell in the respective column in red.

Steps to Create the VBA Code

1. Open the VBA Editor:
   • Open Excel.
   • Press Alt + F11 to open the VBA editor.
   • In the editor, click on Insert > Module to add a new module.
2. Write the VBA Code: Here is the VBA code to compare two columns and highlight the cells that do not match.

VBA Code for Data Comparison

Sub CompareData()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long   
    ' Set the active worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your actual sheet name   
    ' Find the last row in Column A
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row   
    ' Loop to compare each row in Columns A and B
    For i = 1 To lastRow
        ' Compare values in Columns A and B
        If ws.Cells(i, 1).Value <> ws.Cells(i, 2).Value Then
            ' If values don't match, color cells red
            ws.Cells(i, 1).Interior.Color = RGB(255, 0, 0) ' Column A in red
            ws.Cells(i, 2).Interior.Color = RGB(255, 0, 0) ' Column B in red
        Else
            ' If values match, remove any background color
            ws.Cells(i, 1).Interior.ColorIndex = xlNone
            ws.Cells(i, 2).Interior.ColorIndex = xlNone
        End If
    Next i
End Sub

Detailed Explanation of the Code

1. Declaring Variables:
   • ws: Declares a variable for the worksheet.
   • lastRow: Finds the last row used in column A (you can modify this for other columns).
   • i: Used in the loop to iterate over each row.
2. Setting the Worksheet:
   • Set ws = ThisWorkbook.Sheets(« Sheet1 »): This sets the worksheet where you want to perform the comparison. Replace « Sheet1 » with the actual name of your sheet.
3. Finding the Last Row Used in Column A:
   • lastRow = ws.Cells(ws.Rows.Count, « A »).End(xlUp).Row: This finds the last row with data in column A.
4. Loop to Compare Data:
   • For i = 1 To lastRow: This loop goes through each row in column A up to the last row with data.
   • If ws.Cells(i, 1).Value <> ws.Cells(i, 2).Value Then: This condition checks if the values in column A and column B are not equal.
     • If the values do not match, both cells in columns A and B are highlighted in red.
     • If the values match, any background color is removed from the cells.
5. Highlighting Differences:
   • ws.Cells(i, 1).Interior.Color = RGB(255, 0, 0): If the values don’t match, this line colors the cell in column A red.
   • ws.Cells(i, 2).Interior.Color = RGB(255, 0, 0): Similarly, this line colors the cell in column B red.
6. Removing Background Color for Matches:
   • ws.Cells(i, 1).Interior.ColorIndex = xlNone: If the values match, this line removes any background color from column A.
   • ws.Cells(i, 2).Interior.ColorIndex = xlNone: Similarly, this line removes the background color from column B.

How to Run the Code

1. After pasting the code in a module, you can run it in several ways:
   • Press F5 in the VBA editor to execute the code.
   • Or, go back to Excel and add a button (via Developer > Insert > Button) and assign the macro to that button.

Possible Extensions

You can also extend this code to perform other types of comparisons:

• Compare more complex ranges of data.
• Output results to a new sheet to indicate where the differences are.
• Compare date values, text values, or even numerical data based on specific thresholds.

This basic code can be an excellent starting point for many automated data comparison processes in Excel.

 

Data cleaning is a crucial step in data analysis. It involves manipulating the data to ensure consistency, reliability, and readiness for analysis. Below is a detailed VBA code example to automate some common data cleaning tasks in Excel.

Code Objectives:

1. Remove duplicates.
2. Trim excessive spaces in cells.
3. Convert data to uppercase or normal text.
4. Delete empty rows.
5. Apply basic formatting (like date or currency).

Step-by-step VBA Code

Here’s the complete code along with explanations:

Sub DataCleaning()
    ' Declare variables
    Dim ws As Worksheet
    Dim LastRow As Long
    Dim LastCol As Long
    Dim RangeData As Range
    Dim Cell As Range
    ' Set the active worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change the sheet name if necessary
    ' Find the last row and column in the active sheet to define the data range
    LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    LastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    ' Define the data range (from the first row to the last row, and from the first column to the last column)
    Set RangeData = ws.Range(ws.Cells(1, 1), ws.Cells(LastRow, LastCol))
    ' Remove duplicates
    RangeData.RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes ' Add or modify column numbers as needed
    ' Trim excessive spaces
    For Each Cell In RangeData
        If Not IsEmpty(Cell.Value) Then
            ' Trim spaces from the start and end of the cell
            Cell.Value = Trim(Cell.Value)
        End If
    Next Cell
    ' Convert data to uppercase (optional)
    For Each Cell In RangeData
        If Not IsEmpty(Cell.Value) Then
            ' Convert to uppercase
            Cell.Value = UCase(Cell.Value)
        End If
    Next Cell
    ' Delete empty rows (empty cells in the first column)
    For i = LastRow To 1 Step -1
        If IsEmpty(ws.Cells(i, 1).Value) Then
            ws.Rows(i).Delete
        End If
    Next i
    ' Format numeric data (e.g., column 2 as currency)
    For Each Cell In ws.Range(ws.Cells(2, 2), ws.Cells(LastRow, 2)) ' Change column if necessary
        If IsNumeric(Cell.Value) Then
            Cell.NumberFormat = "#,##0.00" ' Format as currency
        End If
    Next Cell
    ' Format dates (column 3 as Date)
    For Each Cell In ws.Range(ws.Cells(2, 3), ws.Cells(LastRow, 3)) ' Change column if necessary
        If IsDate(Cell.Value) Then
            Cell.NumberFormat = "mm/dd/yyyy" ' Date format
        End If
    Next Cell
    ' End message
    MsgBox "Data cleaning is complete!", vbInformation
End Sub

Detailed Explanation of the Code

1. Variable Declaration:
   • ws: This variable represents the worksheet where you will perform the cleaning.
   • LastRow and LastCol: These variables are used to find the last row and column with data, helping define the range to clean.
   • RangeData: Represents the range of data to be cleaned (from the first cell to the last).
   • Cell: Used to loop through each cell in the data range.
2. Remove Duplicates:
   • The RemoveDuplicates method is used to remove duplicates in specified columns. Modify the Columns:=Array(1, 2, 3) argument to check for duplicates in the desired columns (here, columns 1, 2, and 3 are checked).
3. Trim Excessive Spaces:
   • The code loops through each cell in the data range and uses the Trim function to remove spaces before and after the content of each cell.
4. Convert to Uppercase:
   • The UCase function is used to convert the value of each cell to uppercase.
5. Delete Empty Rows:
   • The code checks each row in column 1 (or any column you choose) and deletes the entire row if the cell in that column is empty.
6. Format Numeric Data:
   • For numeric columns (e.g., column 2), the format #,##0.00 is applied to display numbers with commas as thousand separators and two decimal places.
7. Format Dates:
   • The code checks if the cell contains a date and then applies a date format (e.g., mm/dd/yyyy).
8. End Message:
   • After the cleaning process is completed, a message box is displayed to inform the user that the data cleaning is finished.

How to Use This Code

1. Open the VBA Editor: Press Alt + F11 to open the VBA editor in Excel.
2. Insert a Module: In the VBA editor, click on Insert > Module.
3. Copy and Paste the Code: Paste the provided code into the module you just created.
4. Run the Macro: Press F5 or go to the Developer tab > Macros to run the macro.

Customization

• Columns: You can change the columns to check for duplicates, format numbers, or dates by modifying the column numbers in the code.
• Data Formatting: You can add more formatting options for other data types (e.g., percentages or integers).
• Data Range: If you have specific cell ranges to clean, you can adjust the RangeData definition accordingly.

This VBA code can be further customized to meet the specific needs of your data cleaning process.

 

Objectives:

1. Automatic Data Backup: Save a copy of the file to a specified location with a timestamp in the file name.
2. Data Retrieval: Load data from a previous backup into the current file.

Explanation and VBA Code

Step 1: Open the VBA Editor

1. Open Excel.
2. Go to the Developer tab (if you don’t have it, you can enable it in Excel settings).
3. Click on Visual Basic to open the VBA editor.
4. In the VBA editor, click on Insert and then Module to add a blank module.

Step 2: VBA Code for Backup and Retrieval

Sub BackupWorkbook()
    ' Variables for the backup path and file name
    Dim backupPath As String
    Dim fileName As String
    Dim dateTime As String   
    ' Define the backup path - Customize as needed
    backupPath = "C:\Backup\"  ' Specify the backup folder path   
    ' Create a unique file name for the backup based on the current date and time
    dateTime = Format(Now(), "yyyy-mm-dd_hh-mm-ss")
    fileName = "Backup_" & dateTime & ".xlsm"  ' Change the extension if needed (e.g., .xlsx for non-macro files)   
    ' Save a copy of the workbook
    ThisWorkbook.SaveCopyAs backupPath & fileName   
    ' Display a confirmation message
    MsgBox "Backup completed successfully! The file has been saved as: " & backupPath & fileName, vbInformation
End Sub
Sub RestoreBackup()
    ' Variables for the backup path and file name
    Dim backupPath As String
    Dim fileName As String
    Dim selectedFile As Variant   
    ' Define the backup path - Customize as needed
    backupPath = "C:\Backup\"  ' Specify the backup folder path   
    ' Ask the user to select a backup file to restore
    selectedFile = Application.GetOpenFilename("Excel Files (*.xlsm), *.xlsm", , "Select Backup File")   
    ' Check if a file was selected
    If selectedFile <> "False" Then
        ' Open the selected backup file and copy its data into the active workbook
        Workbooks.Open selectedFile       
        ' For example, copy all data from the first sheet
        ' You can adjust this line to copy specific data you need
        ThisWorkbook.Sheets(1).Cells.Clear ' Clear old data
        ActiveSheet.UsedRange.Copy Destination:=ThisWorkbook.Sheets(1).Range("A1")
        ' Close the backup file without saving changes
        Workbooks(Dir(selectedFile)).Close SaveChanges:=False      
        ' Display a confirmation message
        MsgBox "Data has been successfully restored!", vbInformation
    Else
        ' If the user cancels the file selection
        MsgBox "No backup file selected.", vbExclamation
    End If
End Sub

Detailed Explanation

1. Backup Function (BackupWorkbook)

• Variables:
  • backupPath defines the directory where backup files will be stored.
  • fileName contains the name of the backup file, based on the current date and time to ensure uniqueness.
• Date Format: Format(Now(), « yyyy-mm-dd_hh-mm-ss ») adds the current date and time to the file name.
• Backup Process: ThisWorkbook.SaveCopyAs backupPath & fileName creates a copy of the active workbook and saves it to the specified location.

2. Restore Function (RestoreBackup)

• File Selection:
  • Application.GetOpenFilename opens a dialog box for the user to select a backup file to restore.
• Copy Data:
  • If a file is selected, the code opens the backup file and copies its content into the active workbook. Here, ActiveSheet.UsedRange.Copy Destination:=ThisWorkbook.Sheets(1).Range(« A1 ») copies all data from the first sheet of the backup file into the first sheet of the active workbook.
• Closing the Backup: After copying the data, the backup file is closed without saving any changes.

Step 3: Testing the Code

1. For Backup: Go to the VBA editor, place the cursor inside the BackupWorkbook function, and press F5 to execute the code and create a backup.
2. For Restore: Similarly, place the cursor inside the RestoreBackup function and press F5 to restore data from a backup.

Customization

• You can customize the backupPath variable to specify a different backup location.
• The data copy process can be adjusted to only copy certain specific data instead of the entire content of the sheet.

This code provides a simple solution to automate regular backups and data recovery in Excel using VBA.

Goal of the VBA Code

Let’s assume we have a dataset in Excel, and we want to automate the process of increasing or augmenting this data. For example, we want to:

1. Add new rows of data with randomly generated values.
2. Modify existing values.
3. Apply some transformations to certain columns (like increasing numeric values or modifying text).

Example VBA Code to Automate Data Augmentation

Here is an example of a VBA code that adds new rows with randomly generated data, modifies existing values, and applies transformations.

Steps to Insert and Run the VBA Code in Excel:

1. Open the VBA Editor: Press ALT + F11 to open the VBA editor.
2. Insert a New Module: In the VBA editor, go to Insert > Module.
3. Copy and Paste the Code below into the module.

Sub AugmentData()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim newRow As Long
    Dim randomValue As Double
    Dim randomText As String   
    ' Set the active worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change sheet name as necessary   
    ' Find the last row of data in column A
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row   
    ' Add 10 new rows of data
    For i = 1 To 10 ' We are adding 10 rows here, you can adjust this number
        newRow = lastRow + i       
        ' Example of generating random data for columns A, B, and C
        ' Column A: Incrementing numerical value
        ws.Cells(newRow, 1).Value = ws.Cells(newRow - 1, 1).Value + Int(Rnd() * 10) + 1 ' Increment previous value with a random number       
        ' Column B: Random numerical value
        ws.Cells(newRow, 2).Value = Rnd() * 100 ' Random number between 0 and 100       
        ' Column C: Randomly generated text
        randomValue = Int(Rnd() * 4) ' Create a random number between 0 and 3
        Select Case randomValue
            Case 0
                randomText = "Product A"
            Case 1
                randomText = "Product B"
            Case 2
                randomText = "Product C"
            Case 3
                randomText = "Product D"
            Case Else
                randomText = "Unknown Product"
        End Select
        ws.Cells(newRow, 3).Value = randomText ' Add random text to column C       
        ' You can add more columns and transformations here if needed
    Next i
    MsgBox "Data augmentation complete!", vbInformation
End Sub

Explanation of the Code

Set the Worksheet:

Set ws = ThisWorkbook.Sheets("Sheet1")

This line sets the active worksheet where you want to add data. Be sure to adjust the sheet name (Sheet1) to match your actual sheet.

Find the Last Used Row:

lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

This line finds the last used row in column A. It’s useful for determining where new rows will be added.

Loop to Add New Rows:

For i = 1 To 10
    newRow = lastRow + i

This loop adds 10 new rows. You can change the To 10 to add more or fewer rows.

Generate Random Values for Each Column:

• • Column A: A value in column A is incremented by a random number.

ws.Cells(newRow, 1).Value = ws.Cells(newRow - 1, 1).Value + Int(Rnd() * 10) + 1

1. • Column B: A random value between 0 and 100 is generated for column B.

ws.Cells(newRow, 2).Value = Rnd() * 100

1. • Column C: Random text is generated for column C using a Select Case statement.

randomValue = Int(Rnd() * 4)
Select Case randomValue
    Case 0
        randomText = "Product A"
    Case 1
        randomText = "Product B"
    Case 2
        randomText = "Product C"
    Case 3
        randomText = "Product D"
End Select

Confirmation Message:

MsgBox "Data augmentation complete!", vbInformation

This line displays a message box when the process of adding new rows is complete.

Customizing the Code

• You can modify the random data generators to suit your needs. For instance, if you have specific columns with formulas or need to apply complex transformations, you can modify those sections of the code.
• You can extend the process to apply more complex transformations to the existing data, such as doubling certain values, applying conditions, or concatenating strings.

Conclusion

This VBA code helps automate the process of augmenting data in Excel by adding new rows with randomly generated values. You can customize this code based on your specific needs, whether you’re working with numeric data, text, or other types of data transformations.

Here’s a detailed explanation and VBA code to automate data audit trails in Excel. This code will help track changes made in a spreadsheet by recording information such as the user who made the change, the cell modified, the value before and after the change, and the date and time of the modification.

VBA Code to Automate Data Audit Trail

1. Create an Audit Sheet: First, create a new worksheet named « Audit » where all changes will be logged. The « Audit » sheet should contain the following columns:
   • Date & Time: to log when the change was made.
   • User: to log who made the change.
   • Modified Cell: to log the address of the modified cell.
   • Old Value: to log the value before the modification.
   • New Value: to log the value after the modification.

For example, the « Audit » sheet might look like this:

• Column A: Date & Time
• Column B: User
• Column C: Modified Cell
• Column D: Old Value
• Column E: New Value

Step 1: Writing the VBA Code

1. Open the VBA Editor (Press Alt + F11).
2. In the VBAProject pane, double-click the worksheet where you want to track changes (e.g., Sheet1).
3. Paste the following code into the sheet’s code window.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim AuditSheet As Worksheet
    Dim LastRow As Long
    Dim OldValue As Variant
    Dim NewValue As Variant
    Dim UserName As String
    Dim CellAddress As String
    Dim CurrentTime As String
    ' Exit if multiple cells are modified (optional)
    If Target.Count > 1 Then Exit Sub   
    ' Set reference to the Audit sheet
    Set AuditSheet = ThisWorkbook.Sheets("Audit")
    ' Get the value before the modification (if available)
    OldValue = Target.Value
    ' Get the new value after modification
    NewValue = Target.Value
    ' Get the username of the person making the change (using computer's username)
    UserName = Application.UserName
    ' Get the address of the modified cell
    CellAddress = Target.Address
    ' Get the current date and time
    CurrentTime = Now()
    ' Find the last empty row in the Audit sheet
    LastRow = AuditSheet.Cells(AuditSheet.Rows.Count, "A").End(xlUp).Row + 1   
    ' Log the information into the Audit sheet
    With AuditSheet
        .Cells(LastRow, 1).Value = CurrentTime        ' Date & Time
        .Cells(LastRow, 2).Value = UserName          ' User
        .Cells(LastRow, 3).Value = CellAddress       ' Modified Cell
        .Cells(LastRow, 4).Value = OldValue          ' Old Value
        .Cells(LastRow, 5).Value = NewValue          ' New Value
    End With
End Sub

Detailed Explanation of the Code

1. Worksheet_Change Event:
   This event triggers whenever a change is made in the worksheet. The Target argument refers to the range (or cell) that was modified.
2. Setting up the Audit Sheet:
   The code defines a reference to the « Audit » sheet where the audit trail will be recorded. If you are using a different sheet for audit, change the sheet name accordingly:
   Set AuditSheet = ThisWorkbook.Sheets(« Audit »).
3. Checking for Multiple Cell Changes:
   If multiple cells are modified at once, the code exits to avoid logging unnecessary multiple entries. You can remove this condition if you want to log changes for multiple cells.
4. Capturing Information:
   • Old Value: The value before the change. This is captured from the cell before it is modified.
   • New Value: The value after the change.
   • User: The username of the person making the change, retrieved via Application.UserName, which is the current computer user.
   • Cell Address: The address of the modified cell, captured by Target.Address.
   • Date & Time: The date and time when the change was made, captured using Now().
5. Logging in the Audit Sheet:
   The information is logged into the « Audit » sheet starting from the first empty row. The code determines the next available row by finding the last used row in column A and then adding 1 to it.
6. Storing the Data:
   The following values are stored in the audit trail:
   • Column A: Date & Time of the change.
   • Column B: User who made the change.
   • Column C: Cell Address of the modified cell.
   • Column D: Old Value before the change.
   • Column E: New Value after the change.

Conclusion

This VBA code automates the creation of an audit trail for changes made in an Excel worksheet. The audit trail is saved in a separate sheet, allowing you to keep track of who modified what, when, and how. You can modify the code to fit your specific requirements, such as tracking only specific cells or including additional details in the audit. This is especially useful for environments where data integrity and traceability are critical, such as financial reports, project management, or regulatory compliance.

Here’s an example of a VBA code to automate the process of assigning grades based on scores in an Excel spreadsheet. This example assumes you have a table with names in column A, scores in column B, and you want to automatically assign grades in column C based on the scores in column B.

Scenario:

Suppose you have a table with names in column A and scores in column B. You want to automatically assign grades in column C based on the following conditions:

• If the score is greater than or equal to 90, the grade should be « A ».
• If the score is between 75 and 89, the grade should be « B ».
• If the score is between 60 and 74, the grade should be « C ».
• If the score is below 60, the grade should be « D ».

VBA Code:

Sub AssignGrades()
    Dim ws As Worksheet
    Dim i As Long
    Dim score As Double
    Dim grade As String   
    ' Reference to the active sheet
    Set ws = ThisWorkbook.Sheets("Sheet1")   
    ' Loop through the rows of the table (starting from row 2, assuming row 1 has headers)
    For i = 2 To ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
        ' Get the score from column B
        score = ws.Cells(i, 2).Value     
        ' Assign grades based on the score
        If score >= 90 Then
            grade = "A"
        ElseIf score >= 75 Then
            grade = "B"
        ElseIf score >= 60 Then
            grade = "C"
        Else
            grade = "D"
        End If       
        ' Assign the grade in column C
        ws.Cells(i, 3).Value = grade
    Next i
End Sub

Explanation of the Code:

1. Variable Declarations:
   • ws: a variable to reference the worksheet where the data is stored.
   • i: a variable for looping through the rows in the table.
   • score: a variable to hold the score value for each row.
   • grade: a variable to store the grade to be assigned.
2. Referencing the Worksheet:
   • Set ws = ThisWorkbook.Sheets(« Sheet1 »): This line defines the worksheet we’re working on. You can change « Sheet1 » to the actual name of your sheet.
3. Looping through the Rows:
   • The loop starts at row 2 (assuming the first row contains headers) and goes through all the rows in column B, stopping at the last non-empty row (ws.Cells(ws.Rows.Count, « B »).End(xlUp).Row).
4. Condition to Assign Grades:
   • The If statement checks the score in column B and assigns a grade to the grade variable based on the value of the score.
   • For example, if the score is 90 or higher, the grade will be « A », and so on.
5. Assigning the Grade:
   • After determining the grade, the grade is assigned to column C of the same row (ws.Cells(i, 3).Value = grade).

How to Run the Code:

1. Open your Excel file.
2. Press Alt + F11 to open the VBA editor.
3. In the VBA editor, click Insert > Module to add a new module.
4. Copy and paste the code into the new module.
5. Close the VBA editor.
6. To run the script, press Alt + F8, select AssignGrades, and click Run.

Expected Result:

After running this script, the grades will be automatically filled in column C based on the scores in column B.

Possible Enhancements:

• You can add error handling to manage invalid values or empty cells.
• You can set the script to run automatically when the workbook is opened or when specific cells are modified (e.g., by using Workbook_Open or Worksheet_Change events).

Here’s an example of VBA code to automate the process of archiving data in Excel, with an explanation in English.

Objective:

This VBA code will copy rows from a source worksheet to an archive workbook based on a specific condition — in this case, if the date in column A is older than 30 days from the current date.

Example VBA Code:

Sub ArchiveData()
    Dim wsSource As Worksheet
    Dim wsArchive As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim currentDate As Date
    Dim filePath As String
    Dim archiveWB As Workbook
    Dim rowRange As Range   
    ' Define the source worksheet (where the data to be archived is stored)
    Set wsSource = ThisWorkbook.Sheets("Data")   
    ' Define the current date
    currentDate = Date    
    ' Define the file path for the archive workbook
    filePath = "C:\Path\To\Your\Folder\Archive.xlsx"   
    ' Open the archive workbook
    Set archiveWB = Workbooks.Open(filePath)   
    ' Define the archive worksheet within the archive workbook
    Set wsArchive = archiveWB.Sheets("Archive")   
    ' Find the last row in the source worksheet
    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row   
    ' Loop through the rows starting from row 2 (assuming row 1 is a header)
    For i = 2 To lastRow 
        ' Check if the date in column A is older than 30 days
        If wsSource.Cells(i, 1).Value < currentDate - 30 Then           
            ' Set the range for the row to be archived
            Set rowRange = wsSource.Rows(i)           
            ' Copy the row to the archive worksheet
            rowRange.Copy           
            ' Find the first empty row in the archive worksheet
            Dim lastRowArchive As Long
            lastRowArchive = wsArchive.Cells(wsArchive.Rows.Count, "A").End(xlUp).Row + 1           
            ' Paste the row into the archive worksheet
            wsArchive.Rows(lastRowArchive).PasteSpecial Paste:=xlPasteValues           
            ' Optionally, delete the row from the source worksheet after archiving
            rowRange.Delete           
            ' Adjust the loop to compensate for the deleted row
            i = i - 1
            lastRow = lastRow - 1
        End If       
    Next i
    ' Save and close the archive workbook
    archiveWB.Save
    archiveWB.Close  
    ' Inform the user that the archiving is complete
    MsgBox "Archiving completed successfully!", vbInformation
End Sub

Explanation of the Code:

1. Variable Definitions:
   • wsSource: Refers to the source worksheet (the one with the data to be archived).
   • wsArchive: Refers to the archive worksheet where the data will be copied to.
   • filePath: Specifies the path to the archive workbook (ensure you modify this with your actual file path).
   • currentDate: Stores the current date, used to compare against dates in the source worksheet.
2. Opening the Archive Workbook:
   • The archive workbook (Archive.xlsx) is opened using Workbooks.Open.
   • The wsArchive worksheet within the archive file is selected for storing the archived data.
3. Looping Through the Source Data:
   • The code loops through all the rows in the source worksheet starting from row 2 (assuming the first row contains headers).
   • It checks if the date in column A is older than 30 days. If the condition is met, the row is copied to the archive worksheet.
4. Copying and Deleting Data:
   • The row is copied and pasted into the first available row in the archive worksheet.
   • Optionally, after the data is archived, the row is deleted from the source worksheet.
   • To compensate for the row deletion, the loop index (i) and lastRow are adjusted.
5. Saving and Closing the Archive Workbook:
   • After the archiving process, the archive workbook is saved and closed.
6. User Notification:
   • A message box appears to notify the user that the archiving process is complete.

Customization of the Code:

• Date Criteria: You can adjust the condition currentDate – 30 to use a different date threshold.
• Specific Columns: If the data to be archived is in columns other than column A, modify wsSource.Cells(i, 1) to point to the correct column.
• Archive File Path: Make sure to update the filePath variable with the actual path to your archive workbook.

How to Use the Code:

1. Open the VBA editor (Alt + F11) in Excel.
2. Create a new module and paste the code into the module.
3. Run the ArchiveData macro to archive the old data based on your criteria.

This VBA script helps automate the process of archiving old data, making it easier to manage and keep your data organized without manual intervention.