Étiquette : vba

Implementing a Version Control System (VCS) in Excel using VBA can be a useful technique for managing the versions of your Excel workbooks, tracking changes, and even restoring previous versions of your documents. While Excel does not natively support VCS like Git, it is possible to create a simple version control system using VBA to track changes and manage different versions of your workbook.

Key Concepts of Version Control

1. Version Tracking: A version control system allows you to save and track changes made to the workbook, and provides a way to « rollback » to previous versions if necessary.
2. Versioning by Saving Copies: Every time a change is made, we create a copy of the workbook or a snapshot with a new version number.
3. Version Descriptions: To keep track of what changes were made, we can use a description for each version.
4. Automatic Saving: The system will automatically create a new version each time certain changes are made to the workbook.

Step-by-Step Explanation

1. Saving a Workbook as a New Version:
   • You will need to store versions of the workbook in a predefined folder.
   • Each new version will be saved with a unique identifier, like a timestamp or incremental version number.
2. Creating a Version Log:
   • A log will be maintained in a hidden sheet or external file to track the versions, the date they were created, and any descriptions provided by the user.
3. Rollback to Previous Versions:
   • We will allow the user to load previous versions by opening the saved versions.

VBA Code Implementation

Below is a basic implementation of version control in Excel using VBA:

1. Setup Workbook and Version Control Folder

First, create a folder on your system where the versioned workbooks will be stored (e.g., C:\ExcelVersions\). This folder will contain all the saved versions of the workbook.

2. Create a Hidden Sheet for Version Log

You should add a hidden sheet in your Excel workbook where the version log will be stored. This log will contain information such as:

• Version Number
• Date
• Description of Changes

3. VBA Code to Implement Version Control

This is a detailed VBA script for version control:

Sub SaveNewVersion()
    Dim versionFolder As String
    Dim versionNumber As Long
    Dim versionDescription As String
    Dim versionFileName As String
    Dim versionLog As Worksheet
    Dim versionRow As Long
    Dim versionDate As String
    Dim currentWorkbook As Workbook
    ' Initialize variables
    versionFolder = "C:\ExcelVersions\" ' Path to store versioned files
    versionDate = Format(Now, "yyyy-mm-dd_hhmmss")
    Set currentWorkbook = ThisWorkbook
    versionDescription = InputBox("Enter a brief description of this version:", "Version Description")
    ' Check if the version folder exists
    If Dir(versionFolder, vbDirectory) = "" Then
        MsgBox "Version control folder does not exist. Please create it and try again."
        Exit Sub
    End If
    ' Find the next version number (based on the number of files in the version folder)
    versionNumber = GetNextVersionNumber(versionFolder)
    ' Construct the version file name (e.g., "Workbook_v1_2023-03-28_120500.xlsx")
    versionFileName = "Workbook_v" & versionNumber & "_" & versionDate & ".xlsx
    ' Save the current workbook as a new version
    currentWorkbook.SaveCopyAs versionFolder & versionFileName
    ' Log the new version in the version log sheet (create if it doesn't exist)
    On Error Resume Next
    Set versionLog = currentWorkbook.Sheets("VersionLog")
    If versionLog Is Nothing Then
        Set versionLog = currentWorkbook.Sheets.Add
        versionLog.Name = "VersionLog"
        versionLog.Visible = xlSheetVeryHidden
        versionLog.Cells(1, 1).Value = "Version Number"
        versionLog.Cells(1, 2).Value = "Date"
        versionLog.Cells(1, 3).Value = "Description"
    End If
    On Error GoTo 0
    ' Find the next empty row in the version log
    versionRow = versionLog.Cells(versionLog.Rows.Count, 1).End(xlUp).Row + 1
    ' Write the version information into the log
    versionLog.Cells(versionRow, 1).Value = versionNumber
    versionLog.Cells(versionRow, 2).Value = versionDate
    versionLog.Cells(versionRow, 3).Value = versionDescription
    MsgBox "Version " & versionNumber & " saved successfully!"
End Sub
Function GetNextVersionNumber(versionFolder As String) As Long
    Dim fileName As String
    Dim versionCounter As Long
    Dim versionNumber As Long
    Dim versionPrefix As String
    Dim versionSuffix As String
    versionCounter = 0
    versionPrefix = "Workbook_v"
    ' Loop through files in the version folder
    fileName = Dir(versionFolder & "*.xlsx")
    Do While fileName <> ""
        ' Extract the version number from the file name (e.g., "Workbook_v1_2023-03-28_120500.xlsx")
        If InStr(fileName, versionPrefix) > 0 Then
            versionNumber = GetVersionFromFileName(fileName, versionPrefix)
            If versionNumber > versionCounter Then
                versionCounter = versionNumber
            End If
        End If
        fileName = Dir
    Loop
    ' Return the next version number
    GetNextVersionNumber = versionCounter + 1
End Function
Function GetVersionFromFileName(fileName As String, prefix As String) As Long
    Dim versionStr As String
    Dim versionNum As Long
    versionStr = Mid(fileName, Len(prefix) + 1, InStr(Len(prefix) + 1, fileName, "_") - Len(prefix) - 1)
    versionNum = CLng(versionStr)
    GetVersionFromFileName = versionNum
End Function
Sub RollbackVersion()
    Dim versionFolder As String
    Dim versionLog As Worksheet
    Dim versionRow As Long
    Dim versionToRollback As Long
    Dim versionFileName As String
    Dim currentWorkbook As Workbook
    Dim newWorkbook As Workbook
    ' Initialize variables
    versionFolder = "C:\ExcelVersions\" ' Path to store versioned files
    Set currentWorkbook = ThisWorkbook
    Set versionLog = currentWorkbook.Sheets("VersionLog")
    ' Prompt user to select a version to roll back to
    versionToRollback = InputBox("Enter the version number to roll back to:", "Rollback Version")
    ' Find the version in the log
    versionRow = 0
    For i = 2 To versionLog.Cells(versionLog.Rows.Count, 1).End(xlUp).Row
        If versionLog.Cells(i, 1).Value = versionToRollback Then
            versionRow = i
            Exit For
        End If
    Next i
    If versionRow = 0 Then
        MsgBox "Version not found in the log."
        Exit Sub
    End If
    ' Get the file name for the rollback version
    versionFileName = versionLog.Cells(versionRow, 2).Value & "_" & versionToRollback & ".xlsx"
    ' Open the selected version
    Set newWorkbook = Workbooks.Open(versionFolder & versionFileName)
    newWorkbook.Activate
    MsgBox "You have successfully rolled back to version " & versionToRollback & "!"
End Sub

How This Code Works

1. SaveNewVersion:
   • This subroutine saves the current workbook as a new version in the specified folder.
   • It assigns a version number based on existing files and appends a timestamp to the file name.
   • The version information is then logged into a hidden sheet for tracking.
2. GetNextVersionNumber:
   • This function checks the files in the version control folder and returns the next version number based on the highest version available.
3. GetVersionFromFileName:
   • This helper function extracts the version number from the file name.
4. RollbackVersion:
   • This subroutine allows the user to rollback to a previous version by opening the saved version from the version control folder.

Conclusion

This VBA implementation offers a basic version control system for Excel. It allows users to save and track different versions of their workbooks, and even roll back to previous versions when necessary. While this is a simple version control system, it can be enhanced further with features such as automated backups, user prompts for confirming version descriptions, or better error handling.

1. Overview of User Authentication System

A typical User Authentication System consists of:

• A login form where users enter their credentials (username and password).
• A way to validate those credentials against a stored set (e.g., a database or a list in Excel).
• Restricted access to certain features or areas of the workbook based on successful authentication.

2. Preparing the Excel Workbook

Before starting the code, we will set up a simple system:

1. Sheet1 – This will contain a list of usernames and passwords.
   • In Sheet1, create a table with the following columns: Username and Password.
   • You can populate the table with a few usernames and their corresponding passwords for testing purposes.

Example:

| Username  | Password  |

|———–|———–|

| admin     | admin123  |

| user1     | password1 |

| user2     | password2 |

1. Sheet2 – This will be a protected sheet where only authenticated users can access certain information or functionality.

3. Creating the User Authentication Form

We’ll create a basic login form with two fields: Username and Password.

Steps:

1. Open the VBA editor by pressing ALT + F11.
2. In the editor, go to Insert > UserForm. A new form will appear.
3. Add the following controls to the form:
   • Two TextBox controls (TextBox1 for Username and TextBox2 for Password).
   • Two Label controls (for Username and Password).
   • A CommandButton (for logging in).
   • Optionally, a Label for displaying messages (e.g., Label3 to show errors).

The UserForm should look something like this:

• Username [TextBox1]
• Password [TextBox2] (set PasswordChar property to * to hide input)
• Login [CommandButton]

4. Writing the VBA Code for Authentication

Now that we have the form, we’ll write the code to check the username and password entered against the data in Sheet1.

Code Explanation:

1. Validate User Login: We’ll loop through the list of usernames and passwords stored in Sheet1 and compare them with the entered username and password. If a match is found, we’ll authenticate the user and allow access. If not, we’ll display an error message.
2. Code for User Authentication: Below is the VBA code that will go into the CommandButton click event (the login button) for authentication.

Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim username As String
    Dim password As String
    Dim i As Long
    Dim validUser As Boolean
    ' Retrieve username and password entered in the form
    username = TextBox1.Value
    password = TextBox2.Value
    ' Check if username and password fields are filled
    If username = "" Or password = "" Then
        MsgBox "Please enter both username and password.", vbExclamation
        Exit Sub
    End If
    ' Set reference to the worksheet where users are stored (Sheet1)
    Set ws = ThisWorkbook.Sheets("Sheet1")
    ' Find the last row with data in the worksheet (for user list)
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    ' Initialize the variable for validity check
    validUser = False
    ' Loop through each row in the worksheet and compare with the entered credentials
    For i = 2 To lastRow ' Starting from row 2 (assuming row 1 is header)
        If ws.Cells(i, 1).Value = username And ws.Cells(i, 2).Value = password Then
            validUser = True
            Exit For ' Exit the loop if match found
        End If
    Next i
    ' If valid user, grant access; otherwise, show error
    If validUser Then
        MsgBox "Login successful! Access granted.", vbInformation
        ' Optionally, you can hide the login form and show the protected area
        Me.Hide
        Sheets("Sheet2").Visible = True ' Show protected sheet
    Else
        MsgBox "Invalid username or password.", vbCritical
    End If
End Sub

Explanation of the Code:

1. Variables:
   • ws is used to reference Sheet1, where the list of usernames and passwords is stored.
   • lastRow is used to determine how many rows are in the user list to loop through.
   • username and password hold the input values entered by the user in the form.
   • validUser is a flag that tells whether the login was successful or not.
2. Input Validation: The code first checks if the username and password fields are empty and shows a message if they are.
3. Looping Through User Data: It loops through all rows in Sheet1 to compare the input credentials with stored ones. If a match is found, the user is authenticated.
4. Granting Access: If the user is valid, a message is shown, and the form is hidden, along with the protection of the target sheet. The protected sheet (Sheet2) is made visible.
5. Error Handling: If no match is found, an error message is displayed.

5. Adding Security to the Protected Sheet

Once the user is authenticated, we can protect Sheet2 by hiding it initially and showing it after successful authentication.

You can protect a worksheet by setting its Visible property to xlSheetVeryHidden, and once authenticated, you can make it visible again with the following code:

Sheets(« Sheet2 »).Visible = xlSheetVeryHidden ‘ Hide sheet initially

6. Running the Authentication

To run the authentication system, simply open the workbook, then use the following code to show the login form when the workbook is opened:

1. Go to the ThisWorkbook module in the VBA editor.
2. Add the following code:

Private Sub Workbook_Open()
    ' Show the login form when the workbook is opened
    UserForm1.Show
End Sub

This will automatically show the login form when the workbook is opened, ensuring that the user is prompted to authenticate.

7. Enhancements and Considerations

1. Hashing Passwords: This simple system uses plain text passwords, but in a real-world scenario, you should hash passwords to protect them.
2. Adding Timeout: You could implement a timeout or a limit on the number of failed login attempts.
3. Logging Attempts: Consider keeping a log of failed login attempts for security reasons.
4. Multi-level Security: You could extend this system to handle different user roles (e.g., Admin, User) and show different levels of access based on the role.

Conclusion

This code provides a basic yet effective approach to implementing a user authentication system in Excel using VBA. By customizing this, you can add more sophisticated features like role-based access, encryption, and more secure handling of passwords.

Real-Time Data Monitoring Tool in Excel VBA

In this example, we will create a tool that allows users to monitor real-time data updates. The idea is to automatically pull data from an external source (which can be a web service, database, or even a data file) and display it within an Excel worksheet. The key concept here is to refresh the data at regular intervals without requiring the user to press any buttons.

Step 1: Setting up the Worksheet for Data Monitoring

Let’s assume that we will create a simple monitoring system for stock prices, but this system can be adapted for any kind of data source.

1. Create a Worksheet Layout
   • Column A: Stock Symbols
   • Column B: Stock Prices
   • Column C: Last Updated Time

For simplicity, we will have a list of stock symbols in Column A. We will display the stock price in Column B and the timestamp of the last update in Column C.

Step 2: Add VBA Code to Excel Workbook

1. Open the Visual Basic for Applications (VBA) editor by pressing Alt + F11.
2. In the VBA editor, create a new Module (Right-click on any existing object in the « VBAProject » window > Insert > Module).
3. Write the VBA code in the module. This code will periodically update the stock prices and display the results.

Step 3: Create the Real-Time Data Monitoring Function

Here is an example of the VBA code:

Dim lastUpdateTime As Double
Dim updateInterval As Double
Sub StartMonitoring()
    ' Set the time interval for the updates (in seconds)
    updateInterval = 10 ' Update every 10 seconds
    ' Initialize last update time
    lastUpdateTime = Timer
    ' Start the real-time data monitoring loop
    Call UpdateStockPrices
End Sub
Sub UpdateStockPrices()
    ' This subroutine will update stock prices at regular intervals.
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Ensure the correct sheet name
    Dim currentRow As Long
    currentRow = 2 ' Starting from row 2 assuming the first row is headers
    ' Loop through the stock symbols and update their prices
    Do While ws.Cells(currentRow, 1).Value <> ""
        Dim stockSymbol As String
        Dim stockPrice As Double
        Dim lastUpdated As Date
        stockSymbol = ws.Cells(currentRow, 1).Value
        stockPrice = GetStockPrice(stockSymbol) ' Function to get stock price
        lastUpdated = Now ' Current timestamp for the last update
        ' Update the stock price and last update time in the worksheet
        ws.Cells(currentRow, 2).Value = stockPrice
        ws.Cells(currentRow, 3).Value = lastUpdated
        currentRow = currentRow + 1
    Loop
    ' After updating, wait for the defined interval, then update again
    If Timer - lastUpdateTime >= updateInterval Then
        lastUpdateTime = Timer
        Application.OnTime Now + TimeValue("00:00:10"), "UpdateStockPrices"
    End If
End Sub
Function GetStockPrice(symbol As String) As Double
    ' This function simulates retrieving stock prices from a web service or an API.
    ' In practice, you would replace this with actual data retrieval logic (e.g., from an API).
    ' For demo purposes, returning a random stock price
    GetStockPrice = Round(Rnd() * 100 + 50, 2) ' Random stock price between 50 and 150
End Function

Explanation of Code

1. Sub StartMonitoring()

• This subroutine initializes the monitoring system. It sets the updateInterval to 10 seconds (this is the time delay between each update) and calls the UpdateStockPrices subroutine to begin updating the data.

2. Sub UpdateStockPrices()

• This is the core subroutine responsible for updating the stock prices. It loops through all the stock symbols in Column A, retrieves the stock price (using the GetStockPrice function), and updates the corresponding cells in Columns B and C with the new price and the current timestamp.
• The loop continues until it reaches a blank cell in Column A.
• After updating the stock prices, the subroutine waits for the defined updateInterval before executing again. The Application.OnTime method is used to schedule the next execution of UpdateStockPrices.

3. Function GetStockPrice()

• This function simulates retrieving stock prices. In a real application, you could replace this with code that queries a live data source, such as a financial API (e.g., Alpha Vantage, Yahoo Finance API, etc.).
• The current function just generates a random stock price to demonstrate the real-time updating process. It generates a random price between 50 and 150.

Step 4: Testing the Tool

1. To start monitoring the data, you would need to call the StartMonitoring subroutine. You can do this by adding a button to the Excel sheet:
   • Go to the Excel worksheet, click on Insert, and select a button from the form controls.
   • Right-click on the button and select Assign Macro…. Choose StartMonitoring from the list of available macros.
   • Click on the button, and it will begin the real-time monitoring of the stock prices.
2. You will notice that the stock prices in Column B and the last update time in Column C will update every 10 seconds.

Step 5: Modifications for Real-World Use

1. Use Real Data Sources:
   • To fetch actual stock prices, you will need to modify the GetStockPrice function to retrieve live data from a web API. Here’s an example of how you might do that using XMLHttpRequest for an API call:
2. Function GetStockPrice(symbol As String) As Double
3.     Dim xhr As Object
4.     Set xhr = CreateObject(« MSXML2.XMLHTTP »)
5.     Dim url As String
6.     url = « https://api.example.com/stock?symbol= » & symbol
7.    
8.     xhr.Open « GET », url, False
9.     xhr.Send
10.    
11.     ‘ Parse JSON response (assuming the API returns a JSON object with the stock price)
12.     Dim json As Object
13.     Set json = JsonConverter.ParseJson(xhr.responseText)
14.    
15.     ‘ Assuming the price is in the « price » field
16.     GetStockPrice = json(« price »)
17. End Function

You would need to implement proper error handling in case of network issues, invalid symbols, or unavailable data.

1. Performance Considerations:
   • Continuously refreshing data can be demanding on system resources. For real-world usage, consider the impact of frequent updates, especially when working with larger datasets.
   • You could add additional features such as logging the previous values for comparison, adding alerts when prices cross certain thresholds, or even sending emails or messages when a specific event occurs.
2. Advanced Features:
   • Charts: You could add charts to visually track the changes in stock prices over time.
   • Historical Data: Consider storing historical data in another worksheet to analyze the performance of the monitored data over days or weeks.

Conclusion

This is a basic example of implementing a real-time data monitoring tool in Excel VBA. It uses periodic updates with the Application.OnTime method to refresh the data at regular intervals. In a real-world application, you would connect to a live data source (such as a web API) to pull real-time data. This approach is scalable and adaptable for many types of real-time data monitoring tasks.