Skip to main content

Data Validation (Input Value Restrictions, Custom Validation Rules)

APIDescriptionExample
Range.ValidationProperty that returns the validation management object for the specified rangelet range = instance.Application.ActiveSheet.Range("A1");

let validation = range.Validation;
Validation.AlertStyleProperty that returns the alert type for validationlet range = instance.Application.ActiveSheet.Range("A1");
let validation = range.Validation;

console.log(validation.AlertStyle);
Validation.Formula1Property that returns the first formula used in validationlet range = instance.Application.ActiveSheet.Range("A1");
let validation = range.Validation;

console.log(validation.Formula1);
Validation.Formula2Property that returns the second formula used in validation
cf) Works normally when validation operator (Validation.Operator) corresponds to the following items
· instance.Enums.XlFormatConditionOperator.xlBetween
· instance.Enums.XlFormatConditionOperator.xlNotBetween		let range = instance.Application.ActiveSheet.Range("A1");
let validation = range.Validation;

console.log(validation.Formula2);
Validation.IgnoreBlankProperty that returns or changes whether to ignore blanks during validationlet range = instance.Application.ActiveSheet.Range("A1");
let validation = range.Validation;

console.log(validation.IgnoreBlank);
validation.IgnoreBlank = true;
Validation.OperatorProperty that returns the validation operatorlet range = instance.Application.ActiveSheet.Range("A1");
let validation = range.Validation;

console.log(validation.Operator);
Validation.TypeProperty that returns the validation typelet range = instance.Application.ActiveSheet.Range("A1");
let validation = range.Validation;

console.log(validation.Type);
Validation.ValueProperty that returns whether the validation condition has been passedlet range = instance.Application.ActiveSheet.Range("A1");
let validation = range.Validation;

console.log(validation.Value);
Validation.AddFunction that adds validationlet range = instance.Application.ActiveSheet.Range("A1");
let validation = range.Validation;

validation.Add({
Type: instance.Enums.XlDVType.xlValidateWholeNumber,
Operator: instance.Enums.XlFormatConditionOperator.xlBetween,
Formula1: "1",
Formula2: "10"
});
Validation.DeleteFunction that removes validationlet range = instance.Application.ActiveSheet.Range("A1");
let validation = range.Validation;

validation.Delete();
Validation.ModifyFunction that modifies validationlet range = instance.Application.ActiveSheet.Range("A1");
let validation = range.Validation;

validation.Modify(instance.Enums.XlDVType.xlValidateWholeNumber);

Data Processing , Built-in Function Libraries , Auto Complete , Generate and Edit Pivot Table , Filter and Sort , Find and Replace