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.")