# Function Reference

# _Excel_RangeValidate

Adds data validation to the specified range

#include <Excel.au3>
_Excel_RangeValidate ( $oWorkbook, $vWorksheet, $vRange, $iType, $sFormula1 [, $iOperator = Default [, $sFormula2 = Default [, $bIgnoreBlank = True [, $iAlertStyle = $xlValidAlertStop [, $sErrorMessage = Default [, $sInputMessage = Default]]]]]] )

## Parameters

$oWorkbook | Excel workbook object
$vWorksheet | Name, index or worksheet object. If set to keyword Default the active sheet will be used
$vRange | A range object, an A1 range or keyword Default to validate all cells in the specified worksheet
$iType | The validation type. Can be any of the XlDVType enumeration
$sFormula1 | The first part of the data validation equation
$iOperator | [optional] The data validation operator. Can be any of the XlFormatConditionOperator enumeration (default = keyword Default)
$sFormula2 | [optional] The second part of the data validation when $iOperator is $xlBetween or $xlNotBetween. Otherwise it is ignored (default = keyword Default)
$bIgnoreBlank | [optional] If set to True, cell data is considered valid if the cell is blank (default = True)
$iAlertStyle | [optional] The validation alert style. Can be any of the XlDVAlertStyle enumeration (default = $xlValidAlertStop)
$sErrorMessage | [optional] Message to be displayed in a MsgBox when invalid data has been entered (default = keyword Default)
$sInputMessage | [optional] Message to be displayed in a Tooltip when you begin to enter data (default = keyword Default)

## Return Value

Success: | the range object.
Failure: | 0 and sets @error.
@error: | 1 - $oWorkbook is not an object or not a workbook object 2 - $vWorksheet name or index are invalid or $vWorksheet is not a worksheet object. @extended is set to the COM error code 3 - $vRange is invalid. @extended is set to the COM error code 4 - Error returned by the Add method. @extended is set to the COM error code

## Remarks

Parameter $bDisplayAlerts for function _Excel_Open needs to be set to True to display $sErrorMessage.
If you want to validate against a list of values stored in another cell range ($iType = $xlValidateList) then
$sFormula1 has to start with a "=" (e.g. "=C:C").
Before adding a new validation rule to a range the function deletes existing validation rules of the range.

## Example

### Example 1

    #include <Excel.au3>
    #include <MsgBoxConstants.au3>

    ; Create application object and open an example workbook
    Local $oExcel = _Excel_Open(True, True)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeValidate Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    Local $oWorkbook = _Excel_BookNew($oExcel)
    If @error Then
        MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeValidate Example", "Error creating new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        _Excel_Close($oExcel)
        Exit
    EndIf

    ; Only values from a list are valid
    _Excel_RangeValidate($oWorkbook, Default, "A1:A10", $xlValidateList, "Yes;No;Don't know")
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeValidate Example 1", "Error setting range validation." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeValidate Example 1", "Validation against a list of values successfully set for range 'A1:A10'.")

### Example 2

    #include <Excel.au3>
    #include <MsgBoxConstants.au3>

    ; Create application object and open an example workbook
    Local $oExcel = _Excel_Open(True, True)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeValidate Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    Local $oWorkbook = _Excel_BookNew($oExcel)
    If @error Then
        MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeValidate Example", "Error creating new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        _Excel_Close($oExcel)
        Exit
    EndIf

    ; Only values from a list defined in a cell range (column 'D') are valid
    ; Write values to a range
    Local $aValidation[] = ["10", "20", "30", "40", "50"]
    _Excel_RangeWrite($oWorkbook, Default, $aValidation, "D1")
    ; Only values from the range are valid
    _Excel_RangeValidate($oWorkbook, Default, "C:C", $xlValidateList, "=D:D")
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeValidate Example 2", "Error setting range validation." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeValidate Example 2", "Validation against a list of values in cell range '=D:D' successfully set for colum 'C'.")

### Example 3

    #include <Excel.au3>
    #include <MsgBoxConstants.au3>

    ; Create application object and open an example workbook
    Local $oExcel = _Excel_Open(True, True)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeValidate Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    Local $oWorkbook = _Excel_BookNew($oExcel)
    If @error Then
        MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeValidate Example", "Error creating new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        _Excel_Close($oExcel)
        Exit
    EndIf

    ; Only numeric values are valid
    _Excel_RangeValidate($oWorkbook, Default, "B:B", $xlValidateDecimal, 0, $xlGreater, Default, Default, $xlValidAlertStop, "You entered a non numeric value!", "Only numeric values are valid.")
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeValidate Example 3", "Error setting range validation." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeValidate Example 3", "Only numeric values are valid in column 'B'.")

### Example 4

    #include <Excel.au3>
    #include <MsgBoxConstants.au3>

    ; Create application object and open an example workbook
    Local $oExcel = _Excel_Open(True, True)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeValidate Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    Local $oWorkbook = _Excel_BookNew($oExcel)
    If @error Then
        MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeValidate Example", "Error creating new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        _Excel_Close($oExcel)
        Exit
    EndIf

    ; Custom validation. Sum of all cells in column 'E' has to be < 100
    _Excel_RangeValidate($oWorkbook, Default, "E:E", $xlValidateCustom, "=SUM(E:E)<=100", Default, Default, Default, Default, "Sum of all cells in column 'E' > 100.")
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeValidate Example 4", "Error setting range validation." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeValidate Example 4", "Sum of all cells in column 'E' has to be < 100.")