Validate Method

You use the Validate method to validate data in a cell or range of cells in the Spreadsheet component. Returns Nothing.

expression .Validate

expression    Required. An expression that returns a Range object.

Remarks

The easiest way to create one or more data validation rules for cells in a Spreadsheet component is to use Microsoft Excel. You can use Excel to create a new spreadsheet, or open an existing spreadsheet, and then use the Excel data validation features to create data validation rules. You save the spreadsheet as an XML Spreadsheet file and then set the Spreadsheet component's XmlUrl property to the saved file. Validation rules created in the XML Spreadsheet file trigger validation rule error messages only when data is entered through the user interface. For example, when a user enters data into a cell that violates a validation rule, a validation rule error message is displayed to the user. However, if data is entered into a cell programmatically, or if the ImportXml method is used to import a new XML data file into the Spreadsheet component that contains invalid data, no error messages are triggered. In this circumstance, you can use the Validate method to determine whether such data is valid.

The Validate method does not return a value indicating that a cell or range of cells contains invalid data. If you are using Microsoft Visual Basic Scripting Edition (VBScript) to validate data, you must use the On Error Resume Next statement on the line immediately preceding the call to the Validate method. In the line immediately after the call to the Validate method, use the Number property of the VBScript Err object to test whether validation succeeded.

Example

The following example illustrates a function that returns True if the data in a cell is valid and False if not it is not valid.

    Dim objRange
Spreadsheet1.xmlurl = "MyXmlSpreadsheetFile.xml"
Set objRange = Spreadsheet1.Workbooks(1).ListObject("EntryID").DataBodyRange

If ValidateRange(objRange) = True Then
     ' Validation succeeded.
Else
     ' Validation failed.
End If

Function ValidateRange(objRange)
    Dim lngError
    On Error Resume Next
    objRange.Validate()
    lngError = Err.Number
    If lngError <> 0  Then
         ValidateRange = True
         Exit Function
    End If
    ValidateRange = False
End Function
  

The next example adds data to a cell and then uses the Validate method to determine whether the data satisfies the cell's validation rule:

    
Dim objRange

Set objRange = Spreadsheet1.ActiveCell

objRange.Value = 100
On Error Resume Next

objRange.Validate

If Err.Number <> 0 then
    objRange.Value = ""
End If