Étiquette : vba

Scenario

Let’s assume that we want to create a simple Excel VBA system where:

• There are different levels of users: Admin, Manager, and User.
• Each level will have different access rights (i.e., Admin has full access, Manager has limited access, and User has only viewing access).
• When a user opens the workbook, they are prompted to enter their username and password. Based on the entered credentials, the system determines their role and provides access accordingly.

Steps to Implement Multi-Level User Access Control

1. Define User Roles and Credentials

You can define a collection of users and their roles (Admin, Manager, User) and also store their credentials (username and password). A secure way to do this is to store the credentials and roles in a hidden sheet within the workbook.

For this example, we’ll assume we store the following details:

• Sheet name: UserRoles
• Columns: Username, Password, Role (Admin, Manager, User)

Username	Password	Role
admin	admin123	Admin
manager1	mng123	Manager
user1	user123	User

2. Create a Login Form

We need a login form where users can enter their username and password.

1. Go to Developer Tab > Insert > UserForm.
2. Create a simple form with:
   • A TextBox for the Username (Name it txtUsername).
   • A TextBox for the Password (Name it txtPassword and set the PasswordChar property to * to mask the password).
   • A CommandButton for login (Name it btnLogin).

3. Write the VBA Code for User Authentication

Here’s the code that checks the entered credentials against the stored data in the hidden sheet (UserRoles).

Dim UserRole As String
Sub ShowLoginForm()
    ' Show the login form
    LoginForm.Show
End Sub
Sub btnLogin_Click()
    Dim ws As Worksheet
    Dim username As String
    Dim password As String
    Dim lastRow As Long
    Dim i As Long
    Dim userFound As Boolean
    ' Get the username and password entered by the user
    username = LoginForm.txtUsername.Value
    password = LoginForm.txtPassword.Value
    ' Reference to the 'UserRoles' sheet where credentials are stored
    Set ws = ThisWorkbook.Sheets("UserRoles"
    ' Find the last row with data
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    userFound = False
    ' Loop through the UserRoles sheet to validate username and password
    For i = 2 To lastRow
        If ws.Cells(i, 1).Value = username And ws.Cells(i, 2).Value = password Then
            ' If a match is found, store the user role and set userFound to true
            UserRole = ws.Cells(i, 3).Value
            userFound = True
            Exit For
        End If
    Next i
    ' If no match found, show error message
    If Not userFound Then
        MsgBox "Invalid username or password", vbCritical
        Exit Sub
    End If
    ' Close the login form
    LoginForm.Hide
    ' Call the function to set user access based on role
    SetUserAccess UserRole
End Sub
Sub SetUserAccess(role As String)
    ' Based on the role, define what the user can access
    Select Case role
        Case "Admin"
            ' Full access (you can unhide sheets, allow editing)
            MsgBox "Welcome, Admin. You have full access."
            ' Example of unhiding a sheet
            ThisWorkbook.Sheets("AdminSheet").Visible = xlSheetVisible
        Case "Manager"
            ' Limited access (you can unhide some sheets or disable some features)
            MsgBox "Welcome, Manager. You have limited access."
            ' Example: Only allow editing certain areas
            ThisWorkbook.Sheets("ManagerSheet").Visible = xlSheetVisible
        Case "User"
            ' View-only access (you can hide sheets, disable editing, etc.)
            MsgBox "Welcome, User. You have view-only access."
            ' Example: Lock the sheet
            ThisWorkbook.Sheets("UserSheet").Visible = xlSheetVisible
            ThisWorkbook.Sheets("UserSheet").Protect
        Case Else
            MsgBox "Invalid role", vbCritical
    End Select
End Sub

Explanation of the Code:

1. LoginForm Show: This line will display the login form when the user presses the button to start the process.
2. btnLogin_Click: This subroutine checks if the username and password entered by the user match those stored in the UserRoles sheet. If they match, the user’s role is assigned to the UserRole variable, and the SetUserAccess subroutine is called to assign access rights based on the user’s role.
3. SetUserAccess: Based on the user’s role, this subroutine grants different levels of access. It can:
   • Show or hide sheets (e.g., for Admin, Manager, or User).
   • Protect sheets for view-only access (e.g., for Users).
   • Perform other custom actions like enabling or disabling features.

4. Configure Sheet Access Control

In the SetUserAccess procedure:

• Admin: The Admin gets full access, including unprotecting and making all sheets visible.
• Manager: The Manager might only get access to certain sheets or sections.
• User: The User might only have view-only access, where the sheets are protected, and editing is disabled.

5. Protect the Workbook and Sheets

To prevent users from easily changing the workbook structure:

• Protect Sheets: After setting up user access, you can protect sheets for non-Admin users so they cannot make changes.

ThisWorkbook.Sheets("UserSheet").Protect Password:="userpass"

• Protect Workbook: Protect the entire workbook structure to prevent unauthorized users from adding or deleting sheets.

ThisWorkbook.Protect Password:="workbookpassword"

6. Customizing the System

You can further customize this system by:

• Adding an Admin interface to manage users and roles (e.g., adding, deleting, or modifying users).
• Implementing more advanced permissions, like restricting access to certain ranges or data.

7. Adding an Admin Interface

If you’re an admin, you can add a User Interface for modifying user roles or resetting passwords. This can be done using forms or simple Excel sheets protected with VBA code.

Final Notes:

• Security: This method is a simple access control mechanism and should not be used for highly secure applications. Passwords in the Excel file are not encrypted and can be extracted by someone with sufficient knowledge of Excel.
• Extensibility: You can extend this model by adding more user roles, more specific access restrictions, and features like logging and auditing of user activity.

Key Concepts in Error Handling

1. On Error Resume Next: This tells VBA to continue executing the next line of code even if an error occurs. It’s useful when you want to skip over certain errors but still run the rest of the program.
2. On Error GoTo [Label]: This directs VBA to jump to a specific part of the code (usually called an error-handling section) if an error occurs. This allows you to handle the error in a controlled manner.
3. On Error GoTo 0: This resets error handling to the default behavior, meaning VBA will stop execution and show the standard error message when an error occurs.
4. Err Object: The Err object contains information about the last error that occurred, such as the error number, description, and source.

Detailed Example of Error Handling

Sub ErrorHandlingExample()
    On Error GoTo ErrorHandler ' Set up error handling
    ' Simulate some code execution with potential errors
    Dim result As Double
    Dim number1 As Double
    Dim number2 As Double
    Dim filePath As String
    number1 = 10
    number2 = 0 ' This will cause a division by zero error
    ' Division by zero: Potential error here
    result = number1 / number2
    ' File I/O operation: Potential error if file doesn't exist
    filePath = "C:\InvalidPath\testfile.txt"
    Open filePath For Input As #1 ' This could cause an error if the file doesn't exist
    ' Code continues here if no errors
    MsgBox "Result is " & result
    ' Exit the sub before the error handler
    Exit Sub
ErrorHandler:
    ' Error handling code
    If Err.Number = 11 Then
        MsgBox "Division by zero error occurred.", vbCritical, "Error"
    ElseIf Err.Number = 53 Then
        MsgBox "File not found. Please check the file path.", vbCritical, "Error"
    Else
        MsgBox "An unknown error occurred. Error Number: " & Err.Number & " - " & Err.Description, vbCritical, "Error"
    End If
    ' Reset error handling to default
    On Error GoTo 0
    ' Clean up (e.g., closing file, resetting variables)
    On Error Resume Next ' Ignore any potential errors during clean-up
    Close #1 ' Close the file if it was opened
    On Error GoTo ErrorHandler ' Return to the error handler
End Sub

Breakdown of the Code:

1. Setting Up Error Handling (On Error GoTo ErrorHandler):
   • This line sets up the error handling by telling VBA to jump to the ErrorHandler label whenever an error occurs in the code.
2. Executing Code with Potential Errors:
   • The code does some arithmetic operations, such as dividing number1 by number2. Since number2 is 0, this will result in a « Division by Zero » error.
   • The code also tries to open a file using a potentially invalid path. If the file doesn’t exist, this will raise an error as well.
3. Error Handler:
   • If an error occurs, the program will jump to the ErrorHandler section.
   • Inside the error handler, we check the error number using Err.Number to determine the type of error and display a meaningful message to the user.
   • You can handle different types of errors based on their error numbers, such as 11 for division by zero and 53 for file not found errors.
   • If the error is not specifically handled, a generic error message is displayed, along with the error number and description.
4. Exiting the Subroutine:
   • Before jumping to the error handler, we use Exit Sub to make sure the error handler doesn’t execute if no error occurs.
5. Resetting Error Handling (On Error GoTo 0):
   • Once the error has been handled, we reset the error handling to its default behavior using On Error GoTo 0. This means that any future errors will stop execution and display a default error message.
6. Clean-up Section:
   • The clean-up section, using On Error Resume Next, ensures that even if there’s an error (like trying to close a file that wasn’t opened), the code won’t break.
   • Finally, On Error GoTo ErrorHandler re-establishes the error handler for any subsequent operations.

Other Error Handling Techniques

1. Using On Error Resume Next: This statement can be used to skip over an error and continue with the next line of code. However, it’s important to note that errors are silently ignored, and you should check for them manually afterward. Here’s an example:
2. On Error Resume Next ‘ Skip over errors
3. result = number1 / number2 ‘ Division by zero will be ignored
4. If Err.Number <> 0 Then
5.     MsgBox « An error occurred:  » & Err.Description
6. End If
7. Clearing the Error (Err.Clear): This can be used to reset the Err object. For instance, if you want to clear the current error before doing another operation, you can use Err.Clear:
8. On Error Resume Next
9. result = number1 / number2
10. If Err.Number <> 0 Then
11.     MsgBox « Error:  » & Err.Description
12.     Err.Clear ‘ Clear the error state
13. End If

Best Practices for Error Handling in VBA

1. Use Meaningful Error Messages: When an error occurs, provide the user with a clear, understandable message. Avoid cryptic system messages.
2. Don’t Overuse On Error Resume Next: While this can be useful, it can also hide real problems. Use it cautiously and only when you’re certain that the error is not critical.
3. Ensure Proper Cleanup: Always clean up resources (e.g., closing files or resetting variables) in the error handling section. This prevents memory leaks or file locks.
4. Test Error Handling Thoroughly: Simulate different errors (like invalid inputs or missing files) and test your error-handling code to ensure it works as expected.

What is Dynamic Charting?

Dynamic charting refers to the process of creating charts that automatically update when the underlying data changes. These charts can adjust to new rows or columns being added, filters being applied, or other changes made in the data source. This is particularly useful in reports or dashboards where the data source might be frequently updated.

In Excel, dynamic charts can be achieved by:

1. Using Named Ranges – A dynamic range automatically adjusts as the data is added or removed.
2. Using VBA to Update Charts – Writing code that automatically updates the chart whenever new data is available or changes.

Key Concepts:

1. Dynamic Named Range: Excel has the ability to define a named range (e.g., SalesData) whose size automatically adjusts based on the data in the range.
2. VBA for Charting: You can write VBA code to update charts, modify chart types, or change chart properties dynamically.
3. Event-Driven Updates: Using VBA to listen for changes in the data and update the charts accordingly.

Step-by-Step Guide to Implement Dynamic Charting with VBA

Let’s go through the steps and provide a detailed explanation along with the VBA code:

1. Create a Dynamic Named Range

Before implementing dynamic charts in VBA, it is useful to first set up a dynamic named range using Excel formulas. We can use the OFFSET and COUNTA functions to create a range that adjusts automatically when data is added or removed.

For example, let’s say you have sales data starting from cell A2 and going down to the last filled row. You can define a dynamic named range for your sales data.

Steps:

1. Go to the Formulas tab in Excel.
2. Select Name Manager and click New.
3. Name your range (e.g., SalesData).
4. In the Refers to box, use the following formula:

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)

This formula creates a dynamic range starting from cell A2 and extends down based on the number of entries in column A.

2. Insert a Basic Chart

Once you have your data and dynamic named range, insert a basic chart:

1. Select the data in column A (or the named range).
2. Go to the Insert tab, choose a chart type (e.g., Line Chart).
3. Excel will automatically create a chart using your selected data.

3. VBA Code for Dynamic Charting

Now, let’s move on to implementing dynamic charts using VBA. The VBA code will monitor the dynamic named range and refresh the chart whenever the data changes.

Sample VBA Code:

Sub CreateDynamicChart()
    Dim chartObj As ChartObject
    Dim dataRange As Range
    Dim chartSheet As Worksheet
    ' Set the worksheet and data range
    Set chartSheet = ThisWorkbook.Sheets("Sheet1")
    Set dataRange = chartSheet.Range("SalesData")
    ' Delete any existing charts
    For Each chartObj In chartSheet.ChartObjects
        chartObj.Delete
    Next chartObj
    ' Create a new chart
    Set chartObj = chartSheet.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225)
    ' Set the chart data range
    chartObj.Chart.SetSourceData Source:=dataRange
    ' Set the chart type (can be changed to any other type like xlLine, xlColumn, etc.)
    chartObj.Chart.ChartType = xlLine
    ' Customize the chart title
    chartObj.Chart.HasTitle = True
    chartObj.Chart.ChartTitle.Text = "Sales Data Over Time"
    ' Customize the axis titles
    chartObj.Chart.Axes(xlCategory, xlPrimary).HasTitle = True
    chartObj.Chart.Axes(xlCategory, xlPrimary).AxisTitle.Text = "Time"
    chartObj.Chart.Axes(xlValue, xlPrimary).HasTitle = True
    chartObj.Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Sales"
    ' Set dynamic updates
    ' You can add more properties to make the chart more dynamic.
End Sub

Explanation of the Code:

1. Setting the Worksheet and Range:
   • We define the worksheet and the range (the dynamic named range SalesData) that we want to chart.
   • Set chartSheet = ThisWorkbook.Sheets(« Sheet1 »): Sets the worksheet where the chart will be placed.
   • Set dataRange = chartSheet.Range(« SalesData »): Sets the data range based on the dynamic named range.
2. Deleting Existing Charts:
   • We loop through all existing charts on the sheet and delete them, ensuring that only one chart is displayed at a time.
3. Creating a New Chart:
   • Set chartObj = chartSheet.ChartObjects.Add(…): Adds a new chart to the worksheet at a specified position and size.
   • The chartObj.Chart.SetSourceData Source:=dataRange line sets the chart data based on the dynamic range.
4. Chart Customization:
   • We define the chart type (xlLine for line chart in this case) and set the chart’s title and axis titles.
5. Dynamic Updates:
   • If the data in the SalesData range changes (rows are added or removed), the chart will update automatically based on the dynamic range.

4. Running the VBA Code Automatically

To ensure the chart updates automatically whenever the data changes, you can link the VBA code to a specific event in Excel, such as:

• Worksheet Change Event: Runs the code whenever data in the worksheet changes.

Private Sub Worksheet_Change(ByVal Target As Range)
    ' If data in the SalesData range changes, update the chart
    If Not Intersect(Target, Me.Range("SalesData")) Is Nothing Then
        Call CreateDynamicChart
    End If
End Sub

This code will run the CreateDynamicChart subroutine whenever there is a change in the SalesData range.

5. Testing the Dynamic Charting

To test the dynamic chart:

1. Add new data to the SalesData range.
2. Run the macro CreateDynamicChart or make changes to the data and let the event trigger the update.
3. The chart should automatically update based on the new data, adjusting the chart range and content.

Conclusion

With this approach, you’ve successfully implemented dynamic charting techniques in Excel using VBA. By combining dynamic named ranges with VBA scripting, you can create interactive, auto-updating charts that are perfect for dashboards, reports, and any other application where the underlying data changes frequently.

Additional Tips:

• More Advanced Charts: You can use VBA to create multiple types of charts (like bar charts, pie charts, etc.) based on different data ranges.

Advanced Formatting: Use VBA to add more advanced formatting, like conditional formatting or custom chart colors

1. Introduction to Data Encryption

Encryption is a process of converting data (plaintext) into an unreadable format (ciphertext) using an algorithm and a secret key. The goal is to ensure confidentiality—only those with the key can decrypt and access the original data.

For this example, we’ll use a basic method that can be easily implemented in VBA using Microsoft CryptoAPI, which allows access to cryptographic functions. VBA itself does not have built-in encryption algorithms, so we will rely on Windows API and external libraries like Microsoft CryptoAPI or use simple encryption algorithms like XOR for demonstration.

However, keep in mind that simple algorithms like XOR are not recommended for production use and should only be used for basic educational purposes.

2. Preparing VBA Environment

To begin, we need to ensure that you have the necessary references set up in VBA:

1. Open Excel and press Alt + F11 to open the VBA editor.
2. Click on Tools > References and check Microsoft Scripting Runtime for working with dictionaries (optional) or Microsoft XML, v6.0 if working with advanced APIs.

3. Using Simple XOR Encryption (For Educational Purposes)

XOR encryption is a very simple encryption technique. It is symmetric, meaning the same key is used for both encryption and decryption. Here is the detailed explanation and the code to implement it.

XOR Encryption Algorithm Explanation

XOR encryption works by applying the XOR operation between the plaintext and a key. The XOR operation outputs 1 if the bits are different, and 0 if they are the same. This makes it a very simple encryption method, though not very secure.

For example:

• Plaintext: A (ASCII: 65)
• Key: K (ASCII: 75)
• XOR operation result: 65 XOR 75 = 10 (which corresponds to a non-readable character in ASCII)

To decrypt, we apply the XOR operation again with the same key, which will give us back the original value.

Step-by-Step Code Implementation

Sub EncryptDecryptData()
    ' Example: Encrypt and Decrypt data using XOR encryption
    Dim inputString As String
    Dim encryptedString As String
    Dim decryptedString As String
    Dim key As Integer
    Dim i As Integer
    ' Sample input data
    inputString = "SensitiveData"
    ' Encryption Key (must be the same for both encryption and decryption)
    key = 75  ' This is the ASCII value for 'K' (you can use any number as key)
    ' Encrypting the input data
    encryptedString = XOREncryptDecrypt(inputString, key)
    Debug.Print "Encrypted Data: " & encryptedString
    ' Decrypting the data (same key used for XOR)
    decryptedString = XOREncryptDecrypt(encryptedString, key)
    Debug.Print "Decrypted Data: " & decryptedString
End Sub
' Function to perform XOR encryption or decryption
Function XOREncryptDecrypt(ByVal data As String, ByVal key As Integer) As String
    Dim result As String
    Dim i As Integer
    Dim currentChar As String
    Dim encryptedChar As Integer
    ' Loop through each character in the string
    result = ""
    For i = 1 To Len(data)
        currentChar = Mid(data, i, 1)
        encryptedChar = Asc(currentChar) Xor key
        result = result & Chr(encryptedChar)
    Next i
    XOREncryptDecrypt = result
End Function

Explanation of the Code:

1. Input String: « SensitiveData » is the data we want to encrypt.
2. Encryption Key: We use the integer value 75 (ASCII for ‘K’) as a key. This key is used for both encryption and decryption.
3. XOREncryptDecrypt Function: This function loops through each character in the input data, applies the XOR operation between the character’s ASCII value and the key, and appends the result to a string.
4. Encryption Process: When we run XOREncryptDecrypt with the input string and key, we obtain an encrypted string.
5. Decryption Process: By running XOREncryptDecrypt again on the encrypted data with the same key, we get the original data back because the XOR operation is reversible.

Results:

• When you run this code, you will see the encrypted data (which may look like unreadable symbols) and the decrypted data printed in the Immediate Window in the VBA editor.

4. Limitations of XOR Encryption

• Weak Security: XOR is a very weak encryption method. It is susceptible to frequency analysis, which means attackers can deduce the key if they have enough ciphertext and know something about the structure of the plaintext.
• Reversible: XOR is reversible, so if an attacker knows the encryption algorithm and the key, they can easily decrypt the data.

5. Using More Secure Algorithms like AES (Advanced Encryption Standard)

If you want to use more robust encryption techniques like AES (Advanced Encryption Standard), you can leverage libraries or tools that are integrated into Windows, such as Microsoft CryptoAPI or use third-party libraries such as Bouncy Castle for VBA. AES encryption is much more secure than XOR.

However, integrating AES directly into Excel VBA involves more complex APIs, and you might have to call external DLLs or use Windows scripting objects. A common approach is to use Windows Script Host (WSH) or PowerShell to call encryption functions in a more secure way.

6. Conclusion

In this tutorial, we implemented a simple XOR encryption technique using Excel VBA. This example is useful for educational purposes, as it demonstrates how encryption can be done with VBA. However, for real-world applications, especially involving sensitive or valuable data, it’s important to use established and secure encryption algorithms like AES.

If you want to explore more advanced encryption techniques, you can look into integrating external libraries or even calling .NET methods (e.g., AES) from VBA using a COM interface or through PowerShell scripts.

Exponential Smoothing Forecasting Technique

Exponential Smoothing is a simple and widely-used method for forecasting time series data. It assigns exponentially decreasing weights over time, meaning more recent observations have more influence on the forecast than older ones.

In its simplest form, the formula for Exponential Smoothing is:

St=α×Yt+(1−α)×St−1S_t = \alpha \times Y_t + (1 – \alpha) \times S_{t-1}

Where:

• StS_t is the smoothed value for time period tt
• α\alpha is the smoothing constant (0 < α\alpha < 1)
• YtY_t is the actual value at time tt
• St−1S_{t-1} is the previous smoothed value

The forecasting technique can be expanded with more advanced methods, like Holt’s Linear Trend or Holt-Winters Seasonal Method. However, for simplicity, let’s implement Single Exponential Smoothing in Excel VBA.

Steps:

1. Prepare Data: Have your time series data in Excel. Let’s assume your data is in column A from A2 to A100.
2. Insert VBA Code: Press Alt + F11 to open the VBA editor and insert a new module.

Here’s a detailed VBA implementation for Single Exponential Smoothing.

VBA Code:

Sub ExponentialSmoothingForecast()
    ' Declare variables
    Dim alpha As Double
    Dim lastRow As Long
    Dim i As Long
    Dim smoothedValue As Double
    Dim actualValue As Double 
    ' Set the smoothing constant (alpha) - This is between 0 and 1.
    alpha = 0.3 ' Adjust as needed
    ' Get the last row with data in column A (where the time series data is located)
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    ' Initialize the first smoothed value with the first actual value (or use a different initialization method)
    smoothedValue = Cells(2, 1).Value ' Assuming the first value is the starting point of the smoothing
    ' Output the smoothed value in column B starting from B2
    Cells(2, 2).Value = smoothedValue
    ' Loop through the time series data and apply exponential smoothing
    For i = 3 To lastRow
        ' Get the actual value from column A
        actualValue = Cells(i, 1).Value
        ' Apply the Exponential Smoothing formula
        smoothedValue = alpha * actualValue + (1 - alpha) * smoothedValue
        ' Output the smoothed value in column B
        Cells(i, 2).Value = smoothedValue
    Next i  
    MsgBox "Exponential Smoothing Forecasting Complete!"
End Sub

Explanation of the Code:

1. alpha: This is the smoothing constant. The value of α\alpha lies between 0 and 1, where:
   • A higher value of α\alpha gives more weight to recent data.
   • A lower value gives more weight to older data.
2. lastRow: This determines the number of data points available in the time series (i.e., how many rows have data in column A).
3. smoothedValue: The smoothed value is calculated iteratively using the Exponential Smoothing formula. The first smoothed value is set equal to the first actual value (or can be initialized differently based on the application).
4. For Loop: This loop goes through each row of data and calculates the smoothed value for each time period, storing it in column B.
5. Output: The smoothed values are placed in column B next to the original data in column A. This allows you to compare the smoothed forecasted values against the original series.

Running the Code:

1. Open Excel, and press Alt + F11 to open the VBA editor.
2. Insert a new module by clicking Insert > Module.
3. Copy and paste the code into the module.
4. Close the VBA editor, and go back to Excel.
5. Press Alt + F8, select ExponentialSmoothingForecast, and click Run.

This will calculate the exponentially smoothed values and display them in column B. You can change the alpha value to adjust the sensitivity of the smoothing.

Advanced Forecasting with Excel VBA

While Single Exponential Smoothing is a relatively simple forecasting technique, more advanced methods like ARIMA or Holt-Winters Seasonal methods would require more sophisticated implementations or external libraries. In Excel, these techniques are not natively supported, but you could potentially implement them with VBA or rely on external tools like R or Python for advanced forecasting.

However, if you’re interested in adding a seasonal component or trend-following behavior, you could adapt this method by extending it with Holt’s Linear Method or Holt-Winters Seasonal Method. These methods would involve more complex formulas and might need more than VBA to compute efficiently.