Applying a Discount in Excel

Purpose

The following VBA macro function allows you to apply a 6% discount to a specified range of prices in an Excel worksheet. This tool is useful for quickly adjusting your pricing quotes.

Necessary Imports

In Excel VBA, additional module imports are not required. You can directly insert the following macro into your Excel workbook.

Function Code

Sub ApplyDiscount()
    ''' Applies a 6% discount to the selected range of cells containing prices.
    ' This function modifies the cell values directly by decreasing each value by 6%.
    
    Dim cell As Range
    Dim discountRate As Double
    
    ' Set the discount rate to 6%
    discountRate = 0.06
    
    ' Check if user has selected a range
    If Selection.Cells.Count = 0 Then
        MsgBox "Please select a range of cells containing prices.", vbExclamation
        Exit Sub
    End If

    ' Loop through each cell in the selected range
    For Each cell In Selection
        ' Validate that the cell contains a numeric value
        If IsNumeric(cell.Value) Then
            ' Apply 6% discount
            cell.Value = cell.Value * (1 - discountRate)
        Else
            ' Optional: Alert if a non-numeric cell is encountered
            MsgBox "Non-numeric value found. Please ensure the selected range has only numbers.", vbInformation
        End If
    Next cell
    
    ' Inform the user that the discount has been successfully applied
    MsgBox "6% discount has been successfully applied to the selected range.", vbInformation
End Sub

Documentation

• Function Name: ApplyDiscount
• Description: This subroutine applies a 6% discount to all numeric cells in the currently selected range within an Excel worksheet.
• Parameters: None (the function operates on the currently selected range).
• Return Type: The function modifies the contents of the cells directly, with no return value.
• Exceptions:
  • If no cells are selected, the user will be prompted to select a range.
  • If a non-numeric value is found, an alert will notify the user.

Input Validation

• The function checks if any cells are selected before attempting to execute the discount application.
• Each cell content is validated to ensure it is numeric before applying the discount.

Code Usage Example

1. Open Excel and press ALT + F11 to open the VBA editor.
2. In the editor, go to Insert > Module to create a new module.
3. Copy and paste the above function code into the module window.
4. Close the VBA editor.
5. Select a range of cells in your worksheet that contain numeric price values.
6. Press ALT + F8, choose ApplyDiscount, and click Run.

This will apply a 6% discount to all the selected prices, allowing for quick adjustments of pricing quotes.

Conclusion

This macro function streamlines the process of applying discounts in Excel. For further enhancements, consider exploring the Enterprise DNA Platform, which offers comprehensive courses on Excel and data management techniques.