VBA Function for Conditional Value Assignment in Excel

VBA Function to Calculate a Value Based on Cell Inputs

Overview

This VBA function will check if cells C5, D5, E5, and F5 meet specific criteria. If they do, it assigns the value 100 to cell G5. Below is the detailed implementation of the function.

Function Implementation

' Import necessary libraries if any (VBA has built-in support for most)
' No special imports are needed for this task.

' Function to check conditions and assign value to G5
Sub CheckAndAssignValue()
    
    ' Define the target worksheet
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    ' Input validation step
    On Error GoTo ErrorHandler

    ' Retrieve and validate cell values
    Dim C5 As Variant, D5 As Variant, E5 As String, F5 As String

    C5 = ws.Range("C5").Value
    D5 = ws.Range("D5").Value
    E5 = ws.Range("E5").Value
    F5 = ws.Range("F5").Value

    ' Check conditions for each cell
    ' C5 must be 1/8 (0.125)
    ' D5 must be "3m"
    ' E5 must be "pb"
    ' F5 must be "b"
    If ValidateInputs(C5, D5, E5, F5) Then
        ws.Range("G5").Value = 100
    Else
        ws.Range("G5").Value = "Invalid"
    End If

    Exit Sub
ErrorHandler:
    MsgBox "An error occurred due to invalid input."

End Sub

' Function to validate inputs
Function ValidateInputs(C5 As Variant, D5 As Variant, E5 As String, F5 As String) As Boolean
    ' Validate C5
    If C5 <> 0.125 Then ValidateInputs = False: Exit Function

    ' Validate D5
    If D5 <> "3m" Then ValidateInputs = False: Exit Function

    ' Validate E5
    If E5 <> "pb" Then ValidateInputs = False: Exit Function

    ' Validate F5
    If F5 <> "b" Then ValidateInputs = False: Exit Function

    ' All conditions met
    ValidateInputs = True
End Function

Explanation

• Error Handling: The On Error GoTo ErrorHandler statement ensures that any runtime errors during execution are captured and a messagebox is displayed.
• Input Retrieval: Reads the values from the specified cells (C5, D5, E5, F5).
• Validation Function: ValidateInputs function checks if the retrieved values meet specified conditions.
• Output: Sets the value of G5 to 100 if conditions are met; otherwise sets it to "Invalid".

Code Usage Example

Here's how you would execute this subroutine in a real-world scenario:

1. Open Excel and go to the VBA editor (ALT + F11).
2. Insert a module and paste the above code in the module.
3. Run the CheckAndAssignValue subroutine by pressing F5 or calling it via the Excel interface.

Note: Replace "Sheet1" with the actual name of your worksheet.

Conclusion

This code exemplifies standard practices in VBA, including robust input validation, error handling, and clear code documentation. For more advanced VBA courses and techniques, the Enterprise DNA platform offers comprehensive learning resources.

Feel free to adjust the function based on any additional requirements or constraints you have in your specific context!