Étiquette : vba

Automating transportation and logistics planning in Excel using VBA can significantly enhance process efficiency and reduce human errors. Here is a detailed VBA code example that can be used to automate a part of the transport and logistics planning, such as assigning deliveries to drivers or planning routes. This example scenario includes the following steps:

1. Data Entry: An Excel table contains delivery-related information, including departure address, destination, delivery date, cargo weight, etc.
2. Automation of Assignment: The VBA code will analyze the data and assign each delivery to a driver or vehicle, based on criteria (such as vehicle capacity, proximity, etc.).
3. Route Planning: The code can also plan the route based on distance or priority.

Structure of the Data in Excel

Let’s assume you have a table in Excel with the following columns:

• A: Delivery ID
• B: Departure Address
• C: Destination Address
• D: Cargo Weight
• E: Delivery Date
• F: Assigned Driver
• G: Assigned Vehicle
• H: Delivery Status (e.g., « Planned », « In Progress », « Delivered »)

Example of VBA Code

Sub TransportLogisticsPlanning()
    ' Declare variables
    Dim ws As Worksheet
    Dim row As Long
    Dim departureAddress As String
    Dim destinationAddress As String
    Dim weight As Double
    Dim deliveryDate As Date
    Dim availableDriver As String
    Dim availableVehicle As String
    Dim distance As Double
    Dim maxWeight As Double
    Dim vehicleCapacity As Double   
    ' Set reference to the planning table (Excel sheet)
    Set ws = ThisWorkbook.Sheets("Deliveries") ' Replace "Deliveries" with the name of your sheet   
    ' Initialize weight limits (e.g., max weight of a vehicle)
    vehicleCapacity = 1000 ' Example capacity in kg   
    ' Loop through all rows in the table (starting from row 2)
    row = 2
    Do While ws.Cells(row, 1).Value <> ""       
        ' Retrieve delivery data
        departureAddress = ws.Cells(row, 2).Value
        destinationAddress = ws.Cells(row, 3).Value
        weight = ws.Cells(row, 4).Value
        deliveryDate = ws.Cells(row, 5).Value       
        ' Check if the cargo weight is within the vehicle's capacity
        If weight <= vehicleCapacity Then           
            ' If the weight is within the limit, search for an available driver and vehicle
            availableDriver = FindAvailableDriver(deliveryDate)
            availableVehicle = FindAvailableVehicle(deliveryDate)           
            ' If a driver and vehicle are available, assign them to the delivery
            If availableDriver <> "" And availableVehicle <> "" Then
                ws.Cells(row, 6).Value = availableDriver ' Assign the driver
                ws.Cells(row, 7).Value = availableVehicle ' Assign the vehicle
                ws.Cells(row, 8).Value = "Planned" ' Set status to "Planned"
            Else
                ws.Cells(row, 8).Value = "No resources available"
            End If           
        Else
            ' If the weight exceeds the vehicle's capacity, display a message
            ws.Cells(row, 8).Value = "Weight too high"
        End If       
        ' Move to the next row
        row = row + 1
    Loop
End Sub

Function FindAvailableDriver(deliveryDate As Date) As String
    ' Example function to find an available driver for the given delivery date
    Dim driver As String
    driver = ""   
    ' Simplified: the example returns a fictional driver if available
    ' Add logic here to search in a table of available drivers
    If deliveryDate >= Date Then
        driver = "Driver 1" ' Replace with dynamic search
    End If  
    FindAvailableDriver = driver
End Function

Function FindAvailableVehicle(deliveryDate As Date) As String
    ' Example function to find an available vehicle for the given delivery date
    Dim vehicle As String
    vehicle = ""  
    ' Simplified: the example returns a fictional vehicle if available
    ' Add logic here to search in a table of available vehicles
    If deliveryDate >= Date Then
        vehicle = "Vehicle A" ' Replace with dynamic search
    End If
    FindAvailableVehicle = vehicle
End Function

Code Explanation

1. Variables and Data Structure:
   • The code starts by declaring variables for the worksheet (ws), the delivery data for each row (departure address, destination, weight, etc.), and auxiliary functions to determine available resources (driver and vehicle).
2. Loop Through Data Rows:
   • The code loops through each row in the planning table, starting at row 2 (assuming row 1 contains headers).
   • For each delivery, it checks if the cargo weight is within the vehicle’s capacity (vehicleCapacity). If yes, it proceeds to check for available drivers and vehicles for the specified delivery date.
3. Auxiliary Functions:
   • The functions FindAvailableDriver and FindAvailableVehicle are simplified examples that return an available driver and vehicle. In a real application, these functions could perform searches in other sheets or tables that contain the schedules and availability of drivers and vehicles.
4. Assigning Resources:
   • If a driver and vehicle are available for the specified delivery date, the code assigns these resources to the delivery by updating the corresponding columns in the Excel table (driver and vehicle assigned).
   • The delivery status is updated to « Planned » if the assignment is successful, or to « No resources available » or « Weight too high » if the planning fails.
5. Delivery Status:
   • The status of the delivery is updated, either to « Planned » if successfully assigned, or to « No resources available » or « Weight too high » if there are issues with assigning resources.

Customization and Extension

• Dynamic Resource Search: You can replace the FindAvailableDriver and FindAvailableVehicle functions with dynamic searches in availability tables that contain actual schedules of drivers and vehicles.
• Distance Calculation: You can integrate APIs like Google Maps to calculate the distance between departure and destination addresses and optimize routes.
• Priority Management: You can add logic to assign priorities to urgent deliveries and allocate resources accordingly.

Conclusion

This VBA code provides a foundation for automating the planning of deliveries and logistics resources in Excel. It can be customized based on your specific needs, such as integrating advanced features for route optimization or managing a larger number of resources.

Steps:

1. Sign up for Azure Cognitive Services:
   • First, you need to sign up for the Text Analytics API from Microsoft Azure.
   • Once you sign up, you will get an API key and a endpoint URL.
2. Prepare Your Excel File:
   • Create an Excel file with text data (for example, in column A).
   • In column B, we will display the sentiment analysis result (positive, negative, or neutral).
3. VBA Code for Text Cleaning and Calling the Azure API.

Detailed VBA Code:

Sub AnalyzeSentiment()
    ' Variables
    Dim http As Object
    Dim url As String
    Dim apiKey As String
    Dim text As String
    Dim jsonRequest As String
    Dim jsonResponse As String
    Dim sentiment As String
    Dim i As Long   
    ' Insert your API Key and Endpoint URL here
    apiKey = "YOUR_API_KEY"
    url = "https://<your_endpoint>.cognitiveservices.azure.com/text/analytics/v3.0/sentiment"
    ' Create HTTP object to send the request
    Set http = CreateObject("MSXML2.XMLHTTP")   
    ' Loop through each row and analyze the sentiment
    For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
        text = Cells(i, 1).Value       
        ' Clean the text (remove unnecessary spaces and convert to lowercase)
        text = Trim(LCase(text))       
        ' Create JSON request
        jsonRequest = "{""documents"":[{""id"":""1"",""text"":""" & text & """}]}"
        ' Send the HTTP POST request
        http.Open "POST", url, False
        http.setRequestHeader "Content-Type", "application/json"
        http.setRequestHeader "Ocp-Apim-Subscription-Key", apiKey
        http.Send jsonRequest
        ' Get the response JSON
        jsonResponse = http.responseText       
        ' Extract the sentiment from the response
        sentiment = GetSentimentFromResponse(jsonResponse)
        ' Output the sentiment in column B
        Cells(i, 2).Value = sentiment
    Next i
    ' Release the memory
    Set http = Nothing
End Sub
' Function to extract sentiment from the JSON response
Function GetSentimentFromResponse(response As String) As String
    Dim startPos As Long
    Dim endPos As Long
    Dim sentiment As String
    ' Find the position of sentiment in the JSON response
    startPos = InStr(response, """sentiment"":""") + Len("""sentiment"":""")
    endPos = InStr(startPos, response, """")
    ' Extract sentiment value
    sentiment = Mid(response, startPos, endPos - startPos)
    ' Return the sentiment
    GetSentimentFromResponse = sentiment
End Function

Detailed Explanation of the Code:

1. Variable Declarations:
   • http: An object used to send HTTP requests.
   • url: The endpoint URL of the Azure Cognitive Services Text Analytics API.
   • apiKey: Your API key to authenticate requests.
   • text: The text you want to analyze.
   • jsonRequest: The JSON structure containing the text to send in the request.
   • jsonResponse: The JSON response returned by the API after analyzing the text.
   • sentiment: The sentiment (positive, negative, or neutral) extracted from the API’s response.
2. Sending Request to Azure API:
   • The text is first cleaned by trimming spaces and converting it to lowercase.
   • A POST request is sent to the Azure API with the cleaned text in JSON format.
   • The API processes the text and returns a JSON response containing the sentiment analysis.
3. Extracting the Sentiment from the Response:
   • The function GetSentimentFromResponse extracts the sentiment value (positive, negative, or neutral) from the JSON response by searching for the « sentiment » key.
4. Displaying the Result:
   • The sentiment value is placed in column B of the Excel sheet, corresponding to the text in column A.

Prerequisites:

1. Reference to Microsoft XML Library:
   • Open the VBA editor (Alt + F11).
   • Go to Tools > References.
   • Check the box for Microsoft XML, v6.0 (or similar) to enable the use of MSXML2.XMLHTTP object.
2. Error Handling:
   • The code does not handle all potential errors. It’s a good idea to check if the API responds correctly and implement error handling for issues such as invalid API keys, API downtime, or network errors.

How to Test:

1. Put some text data in column A (e.g., customer reviews or feedback).
2. Run the AnalyzeSentiment macro to analyze the sentiment of each text. The sentiment (positive, negative, or neutral) will be displayed in column B.

Limitations:

• API Quotas: Ensure you stay within the limits of your Azure API plan (e.g., the number of requests per month).
• Language: The Azure Cognitive Services API supports multiple languages. Make sure the text you’re analyzing is supported by the API.

This process allows you to automate sentiment analysis directly within Excel using VBA and external Azure services, which can be very powerful for analyzing large volumes of textual data.

Automating the retrieval of stock market data in Excel using VBA can be extremely useful for investors and financial analysts. Below is a detailed guide and code to retrieve stock market data using an API (e.g., Alpha Vantage) and display it in an Excel spreadsheet.

Step 1: Obtain an Alpha Vantage API Key

1. Visit Alpha Vantage and create an account.
2. Obtain your free API key.

Step 2: Write VBA Code to Retrieve Stock Data

Follow these steps to set up your VBA code.

1. Open the VBA Editor

• Press Alt + F11 to open the VBA editor.
• Go to Insert > Module to add a new module.

2. Add VBA Code to Retrieve Stock Data

Here’s an example of VBA code to retrieve stock data from Alpha Vantage. This code fetches the closing price of a specific stock and displays it in an Excel sheet.

Sub GetStockData()
    ' Declare variables
    Dim Symbol As String
    Dim APIKey As String
    Dim URL As String
    Dim httpRequest As Object
    Dim JSON As Object
    Dim ClosePrice As Double
    Dim LastDate As String   
    ' Enter the stock symbol and your API key
    Symbol = "AAPL" ' Example: Apple Inc. (change as needed)
    APIKey = "your_api_key" ' Replace with your Alpha Vantage API key   
    ' Alpha Vantage API URL (Daily Time Series)
    URL = "https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol=" & Symbol & "&apikey=" & APIKey   
    ' Create an HTTP request to fetch the data
    Set httpRequest = CreateObject("MSXML2.XMLHTTP")
    httpRequest.Open "GET", URL, False
    httpRequest.Send   
    ' Check if the request was successful
    If httpRequest.Status = 200 Then
        ' Parse the JSON response
        Set JSON = JsonConverter.ParseJson(httpRequest.responseText)       
        ' Get the last date and the closing price
        LastDate = JSON("Time Series (Daily)").Keys(1)
        ClosePrice = JSON("Time Series (Daily)")(LastDate)("4. close")       
        ' Display the results in Excel cells
        Range("A1").Value = "Symbol"
        Range("B1").Value = Symbol
        Range("A2").Value = "Last Close Date"
        Range("B2").Value = LastDate
        Range("A3").Value = "Closing Price"
        Range("B3").Value = ClosePrice       
    Else
        MsgBox "Error retrieving data: " & httpRequest.Status
    End If   
End Sub

Detailed Explanation of the Code

1. Variable Declarations:
   • Symbol: The stock symbol (e.g., « AAPL » for Apple).
   • APIKey: Your Alpha Vantage API key.
   • URL: The API URL to fetch daily time series data for the specified stock symbol.
   • httpRequest: An object to send an HTTP request to the API.
   • JSON: An object to parse the JSON response returned by the API.
   • ClosePrice and LastDate: Variables to store the closing price and the date of the last stock closing.
2. Fetching Data from the API:
   • The code sends an HTTP GET request to the Alpha Vantage API to retrieve daily stock data.
   • If the request is successful (status = 200), it parses the JSON response and extracts the date and closing price of the most recent stock data.
3. Displaying Data in Excel:
   • The stock symbol, last close date, and closing price are then displayed in cells A1, B1, A2, B2, etc.

Step 3: Add a JSON Parser for VBA

Alpha Vantage returns data in JSON format, but Excel VBA doesn’t natively understand JSON. To parse the response, you need a JSON parser like VBA-JSON.

Here’s how to add it:

1. Download the VBA-JSON library from GitHub.
2. Copy the JsonConverter.bas file and import it into your VBA editor (via File > Import File).
3. The code above uses this module to parse the JSON data.

Step 4: Test and Run the Code

1. Close the VBA editor.
2. In Excel, press Alt + F8, select GetStockData, and click Run.
3. You will see the stock symbol, last close date, and closing price displayed in the specified cells of your Excel sheet.

Step 5: Customization

• You can customize the stock symbol (e.g., « GOOGL » for Google) or even automate retrieving multiple stock symbols listed in your Excel sheet.
• You can also extend the code to retrieve other financial data like open price, high, low, or volume.

Note

The free Alpha Vantage API allows a limited number of requests per minute (5 requests per minute for free accounts). If you need more frequent data retrieval, you can either upgrade to a paid plan or consider using another service or API that suits your needs.

Troubleshooting

• Ensure that your API key is valid and hasn’t expired.
• Ensure that the API URL is correct and the symbol you’re using is supported.

Automating statistical analysis in Excel using VBA can help perform complex calculations quickly and generate reports repetitively without having to manually enter formulas each time. Below is a detailed VBA code that performs basic statistical analysis such as mean, standard deviation, variance, quartiles, and generates an automated report. The code is structured to work with data in a specific range of cells.

1. Objective

The goal of this code is to automate the statistical analysis of the following metrics:

• Mean
• Standard Deviation
• Variance
• Median
• Quartiles (Q1, Q2, Q3)
• Min / Max

The report will be generated in a new worksheet.

2. VBA Code: Automate Statistical Analysis

Step 1: Create a VBA Module

1. Open Excel and press Alt + F11 to access the VBA editor.
2. In the « Insert » menu, select « Module » to create a new module.
3. Paste the following code into this module.

Sub AutomateStatisticalAnalysis()
    ' Declare variables
    Dim wsData As Worksheet
    Dim wsReport As Worksheet
    Dim DataRange As Range
    Dim LastRow As Long   
    ' Assign the data sheet and create a new sheet for the report
    Set wsData = ThisWorkbook.Sheets("Data") ' Replace "Data" with the name of your data sheet
    Set wsReport = ThisWorkbook.Sheets.Add
    wsReport.Name = "Statistical_Report"   
    ' Determine the last row with data in column A
    LastRow = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row   
    ' Define the data range for analysis (example: from A2 to A100)
    Set DataRange = wsData.Range("A2:A" & LastRow) ' Adjust range as needed   
    ' Add a header to the report
    wsReport.Cells(1, 1).Value = "Statistical Analysis Report"
    wsReport.Cells(2, 1).Value = "Metric"
    wsReport.Cells(2, 2).Value = "Value"   
    ' Perform the statistical calculations and insert them into the report
    wsReport.Cells(3, 1).Value = "Mean"
    wsReport.Cells(3, 2).Value = WorksheetFunction.Average(DataRange)   
    wsReport.Cells(4, 1).Value = "Standard Deviation"
    wsReport.Cells(4, 2).Value = WorksheetFunction.StDev(DataRange)   
    wsReport.Cells(5, 1).Value = "Variance"
    wsReport.Cells(5, 2).Value = WorksheetFunction.Var(DataRange)   
    wsReport.Cells(6, 1).Value = "Median"
    wsReport.Cells(6, 2).Value = WorksheetFunction.Median(DataRange)   
    wsReport.Cells(7, 1).Value = "Quartile 1 (Q1)"
    wsReport.Cells(7, 2).Value = WorksheetFunction.Quartile(DataRange, 1)   
    wsReport.Cells(8, 1).Value = "Quartile 2 (Q2)"
    wsReport.Cells(8, 2).Value = WorksheetFunction.Quartile(DataRange, 2)   
    wsReport.Cells(9, 1).Value = "Quartile 3 (Q3)"
    wsReport.Cells(9, 2).Value = WorksheetFunction.Quartile(DataRange, 3)   
    wsReport.Cells(10, 1).Value = "Min Value"
    wsReport.Cells(10, 2).Value = WorksheetFunction.Min(DataRange)   
    wsReport.Cells(11, 1).Value = "Max Value"
    wsReport.Cells(11, 2).Value = WorksheetFunction.Max(DataRange)   
    ' Format the report
    wsReport.Rows(1).Font.Bold = True
    wsReport.Rows(2).Font.Bold = True
    wsReport.Columns("A:B").AutoFit
    wsReport.Range("A1:B11").Borders(xlEdgeBottom).LineStyle = xlContinuous   
    ' Display a message
    MsgBox "Statistical analysis has been successfully generated in the sheet " & wsReport.Name, vbInformation
End Sub

3. Explanation of the Code

Declaring Variables:

• • wsData: Refers to the worksheet that contains the data to be analyzed.
  • wsReport: Refers to the worksheet where the statistical results will be displayed.
  • DataRange: The range of cells that contains the data for statistical analysis.
  • LastRow: Used to determine the last row with data in column A (adjust if necessary).

Assigning Worksheets:

• • The code refers to the « Data » sheet for the source data and creates a new « Statistical_Report » sheet to display the results.

Determining the Data Range:

• • DataRange is defined as the range of cells containing the data (from A2 to the last row in column A).

Performing Statistical Calculations:

• • The code uses the WorksheetFunction object to calculate the following statistics:
    • Mean with Average
    • Standard Deviation with StDev
    • Variance with Var
    • Median with Median
    • Quartiles with Quartile
    • Min and Max with Min and Max
• Inserting Results into the Report:
  • Each statistic is inserted into the corresponding cells in the new sheet.
• Formatting the Report:
  • The report is formatted with bold headers and auto-fitting columns for better readability.
  • Borders are added to the range for better structure.
• Information Message:
  • Once the analysis is complete, a message box will appear to inform the user that the report has been generated.

4. Using the Code

Make sure your data is in the « Data » sheet, with the values you want to analyze in column A (or adjust the range in the code if needed).

Go to the VBA editor (Alt + F11), create a module, and paste the code.

Run the code by pressing F5 or using the « Run » button in the VBA editor.

A new sheet will be created with the statistical analysis report.

5. Conclusion

This VBA code simplifies statistical analysis by automating calculations and quickly generating a clean report in a new worksheet. You can expand this code to include more complex analyses, charts, or additional statistical measures based on your needs.

The code allocates resources to tasks based on their availability and capacity.

Scenario

Let’s assume you have a worksheet containing:

• Resources: employees, machines, etc., with information about their capacity (e.g., work hours per day) and available hours.
• Tasks: each task has a required number of hours that need to be allocated from available resources.

Goal

We want to automate the allocation of resources to tasks based on resource capacity and task requirements.

Excel Sheet Structure

Resources Sheet:

Tasks Sheet:

VBA Code

Sub AllocateResources()
    ' Variables
    Dim wsResources As Worksheet
    Dim wsTasks As Worksheet
    Dim i As Long, j As Long
    Dim resName As String
    Dim resCapacity As Double
    Dim resAvailable As Double
    Dim taskRequiredHours As Double
    Dim totalAllocated As Double
    Dim taskName As String
    Dim remainingHours As Double
    Dim allocation As Double   
    ' References to the sheets
    Set wsResources = ThisWorkbook.Sheets("Resources")
    Set wsTasks = ThisWorkbook.Sheets("Tasks")   
    ' Loop through each task
    For i = 2 To wsTasks.Cells(Rows.Count, 1).End(xlUp).Row       
        ' Get task name and required hours
        taskName = wsTasks.Cells(i, 1).Value
        taskRequiredHours = wsTasks.Cells(i, 2).Value
        remainingHours = taskRequiredHours       
        ' Allocate resources to the task
        For j = 2 To wsResources.Cells(Rows.Count, 1).End(xlUp).Row           
            ' Get resource info
            resName = wsResources.Cells(j, 1).Value
            resCapacity = wsResources.Cells(j, 2).Value
            resAvailable = wsResources.Cells(j, 3).Value           
            ' Check if resource is available
            If resAvailable > 0 Then
                ' Calculate the possible allocation for this resource
                allocation = WorksheetFunction.Min(remainingHours, resAvailable)
                allocation = WorksheetFunction.Min(allocation, resCapacity * resAvailable)               
                ' Update allocation in task sheet
                wsTasks.Cells(i, 3).Value = wsTasks.Cells(i, 3).Value & resName & " (" & allocation & " hours) ; "
                remainingHours = remainingHours - allocation
                wsResources.Cells(j, 3).Value = resAvailable - allocation               
                ' If task hours are fully allocated, move to the next task
                If remainingHours <= 0 Then Exit For
            End If
        Next j
    Next i
    MsgBox "Resource allocation completed!", vbInformation
End Sub

Code Explanation

1. Variable Definitions:
   • wsResources and wsTasks represent the worksheets for resources and tasks, respectively.
   • The other variables are used to keep track of resource and task information during the allocation process.
2. Main Loop for Tasks:
   • The outer loop goes through each task in the « Tasks » sheet, retrieves the required hours, and tracks the remaining hours needed for allocation.
3. Resource Allocation:
   • The inner loop iterates over each resource in the « Resources » sheet. For each resource, it checks if it is available (has remaining hours).
   • The allocation is calculated by considering the remaining required hours for the task and the available capacity of the resource.
   • The allocated resource and the hours assigned are updated in the « Tasks » sheet, and the available hours for that resource are updated.
4. Updating the Sheets:
   • The allocated resources are displayed in the « Assigned Resource » column of the « Tasks » sheet.
   • The available hours for each resource are updated in the « Available Hours » column of the « Resources » sheet.
5. End of Process:
   • After the process completes, a message box pops up indicating that the resource allocation is finished.

Conclusion

This VBA macro automates the process of allocating resources to tasks based on the resource capacities and the task requirements. You can modify the code to accommodate different scenarios, such as adding task priorities, resource types, or additional constraints for more complex allocations.

Automating Monte Carlo simulation reports in Excel with VBA allows you to quickly generate multiple simulation results and produce analytical reports based on those results. Below is a detailed guide on how to create a Monte Carlo simulation for a project or investment, generate random results, and create a report using VBA in Excel.

Steps in a Monte Carlo Simulation

1. Define input variables (model parameters):
   • For example, if you are simulating a project, you might have variables such as initial cost, expected revenue, operational costs, growth rates, etc.
2. Generate random numbers for the inputs:
   • Use appropriate statistical distributions to generate random values (e.g., normal distribution for returns).
3. Run the simulation over many iterations:
   • For example, running 10,000 iterations to obtain a distribution of results.
4. Analyze the results:
   • Calculate metrics such as the mean, median, percentiles, etc.
5. Automate report generation:
   • Create a report in a new Excel sheet with charts, tables, and analytical metrics.

VBA Code to Automate Monte Carlo Simulation

Below is a detailed example of VBA code for a Monte Carlo simulation:

1. Setup Inputs

This code assumes a simple model with three variables:

• Initial Cost (which follows a normal distribution),
• Annual Revenue (which follows a uniform distribution),
• Project Lifetime (in years).

2. VBA Code for Simulation

Sub MonteCarloSimulation()
    ' Define simulation paraeters
    Dim numIterations As Long
    numIterations = 10000 ' Number of iterations   
    Dim i As Lo
    Dim initialCost As Double
    Dim annualRevenue As Double
    Dim projectLifetime As Long
    Dim cashFlow As Double
    Dim finalResult As Double
    Dim totalResult As Double   
    ' Create an array to store results
    Dim results() As Double
    ReDim results(1 To numIterations)   
    ' Setup result sheet
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Monte Carlo Report"
    With Sheets("Monte Carlo Report")
        .Cells(1, 1).Value = "Iteration"
        .Cells(1, 2).Value = "Cash Flow"
    End With   
    ' Run Monte Carlo simulation
    For i = 1 To numIterations
        ' Generate random variables for each iteration
        initialCost = Application.WorksheetFunction.NormInv(Rnd(), 100000, 20000) ' Initial cost with normal distribution
        annualRevenue = Application.WorksheetFunction.RandBetween(5000, 20000) ' Annual revenue with uniform distribution
        projectLifetime = Application.WorksheetFunction.RandBetween(5, 15) ' Project lifetime between 5 and 15 years       
        ' Calculate cash flow (revenue - costs over the lifetime)
        cashFlow = annualRevenue * projectLifetime - initialCost       
        ' Store the result in the array
        results(i) = cashFlow       
        ' Record the results in the Excel sheet
        Sheets("Monte Carlo Report").Cells(i + 1, 1).Value = i
        Sheets("Monte Carlo Report").Cells(i + 1, 2).Value = cashFlow       
        ' Add to total result for calculating average later
        totalResult = totalResult + cashFlow
    Next i
    ' Calculate statistics for the results
    Dim mean As Double
    Dim stdDev As Double
    Dim minResult As Double
    Dim maxResult As Double   
    mean = totalResult / numIterations
    stdDev = Application.WorksheetFunction.StDev(results)
    minResult = Application.WorksheetFunction.Min(results)
    maxResult = Application.WorksheetFunction.Max(results)   
    ' Add simulation statistics to the report
    With Sheets("Monte Carlo Report")
        .Cells(numIterations + 3, 1).Value = "Mean"
        .Cells(numIterations + 3, 2).Value = mean
        .Cells(numIterations + 4, 1).Value = "Standard Deviation"
        .Cells(numIterations + 4, 2).Value = stdDev
        .Cells(numIterations + 5, 1).Value = "Min"
        .Cells(numIterations + 5, 2).Value = minResult
        .Cells(numIterations + 6, 1).Value = "Max"
        .Cells(numIterations + 6, 2).Value = maxResult
    End With   
    ' Create a chart to visualize the distribution of results
    Dim chartObj As ChartObject
    Set chartObj = Sheets("Monte Carlo Report").ChartObjects.Add(Left:=200, Width:=400, Top:=50, Height:=300)
    chartObj.Chart.ChartType = xlColumnClustered
    chartObj.Chart.SetSourceData Source:=Sheets("Monte Carlo Report").Range("B2:B" & numIterations + 1)
    chartObj.Chart.HasTitle = True
    chartObj.Chart.ChartTitle.Text = "Monte Carlo Simulation Results Distribution"  
    MsgBox "Monte Carlo Simulation Complete!", vbInformation
End Sub

Explanation of the Code

1. Defining Parameters:
   • numIterations: Number of iterations for the simulation (default is 10,000).
   • initialCost: The initial cost generated using a normal distribution.
   • annualRevenue: The annual revenue generated using a uniform distribution.
   • projectLifetime: The project lifetime generated between 5 and 15 years with a uniform distribution.
2. Simulation Loop:
   • The loop For i = 1 To numIterations generates random variables for each iteration, performs the cash flow calculation, and stores the results in an array.
3. Calculating Statistics:
   • After all iterations are completed, the code calculates the mean, standard deviation, minimum, and maximum of the results.
4. Report Generation:
   • The results of each iteration are recorded in a new sheet called « Monte Carlo Report ».
   • Statistical metrics are calculated and added to the sheet below the results.
5. Chart Creation:
   • A bar chart is created to visualize the distribution of results.

Expected Output in Excel

• A new sheet named « Monte Carlo Report » is created, showing the simulation results.
• Each iteration’s cash flow is listed.
• At the bottom of the sheet, statistics such as the mean, standard deviation, minimum, and maximum are displayed.
• A chart showing the distribution of results is added to the sheet.

Customization

• Variable Distributions: You can adjust the distribution types for each parameter as needed (e.g., using triangular, log-normal distributions, etc.).
• Model: You can replace the simple cash flow model with more complex calculations depending on your project.
• Report: The report format can be customized further (e.g., adding colors, annotations, or additional metrics).