Excel VBA Data Validation Custom Formula: How to Use It

VBA & macros · Updated November 21, 2026

In Excel VBA, create custom data validation with the Validation.Add method, setting Type:=xlValidateCustom and supplying a formula that evaluates to TRUE for acceptable entries. For example, to restrict B2:B100 to whole-number values from 1 to 100, apply =AND(ISNUMBER(B2),B2>=1,B2<=100); the reference B2 is relative to the top-left cell of the selected range.

What custom data validation means

Excel’s Data Validation feature controls which values users can enter in selected cells. Its built-in options cover common cases such as whole numbers, decimals, dates, times, text length, and lists. A custom rule uses a worksheet formula instead, which is useful when the rule combines conditions or depends on another cell.

A custom formula must return TRUE for a value Excel should accept and FALSE for a value it should reject. The formula is not a calculation displayed in the cell: it is a test applied when someone enters or edits a value. VBA simply adds or changes that rule. It does not make invalid existing values disappear, and it does not guarantee that pasted data will always be stopped.

Custom validation is useful for rules such as “the number must be within a range,” “this entry must not duplicate another entry,” or “this field is required only when another field has a particular value.” Choose it when the standard validation categories cannot express the rule clearly.

How the custom formula is evaluated

Excel evaluates the rule relative to the top-left cell in the range where validation is applied. If the rule is applied to B2:B100, a reference to B2 checks the current cell as the rule moves down the range: B3 is checked against B3, B4 against B4, and so on. This is why the sample formula uses B2 rather than an absolute reference such as $B$2.

Use dollar signs to lock references that should not move. For example, if entries in B2:B100 must be unique within that same column, the layout is B2:B100 with the first validated cell at B2. The custom rule is =COUNTIF($B$2:$B$100,B2)=1. The range stays fixed, while the second argument adjusts for each cell. A value appearing once passes; a duplicate makes the count greater than one and fails.

Validation formulas can also refer to other cells. Relative references move with the validated cell; absolute references remain fixed; mixed references lock only a row or column. Test the formula in the worksheet first, using the same top-left reference and range layout, if its behavior is not obvious.

Add a custom rule with VBA

The following macro replaces validation on B2:B100 in a worksheet named Entry. It accepts numeric values from 1 through 100, displays an error message for rejected entries, and shows an input prompt when a cell is selected. The formula is written as a VBA string; its leading equals sign and commas are part of the Excel formula.

Sub SetCustomValidation()
    Dim ws As Worksheet

    ' Use the worksheet that contains the entry cells.
    Set ws = ThisWorkbook.Worksheets("Entry")

    ' Save a copy before running: macros cannot be undone with Ctrl+Z.
    With ws.Range("B2:B100").Validation
        .Delete
        .Add Type:=xlValidateCustom, _
             AlertStyle:=xlValidAlertStop, _
             Formula1:="=AND(ISNUMBER(B2),B2>=1,B2<=100)"
        .IgnoreBlank = True
        .InputTitle = "Enter a number"
        .InputMessage = "Use a number from 1 through 100."
        .ErrorTitle = "Invalid entry"
        .ErrorMessage = "Enter a number from 1 through 100."
        .ShowInput = True
        .ShowError = True
    End With
End Sub

Before running it, change the sheet name, target range, and rule to match the workbook. The .Delete line removes any existing validation from those cells before adding the new rule. xlValidateCustom identifies the validation type, and xlValidAlertStop tells Excel to block an invalid entry when the validation prompt is enforced. The formula checks each cell in the range, beginning with B2.

On Windows, open the Visual Basic Editor with Alt+F11, or use Developer > Visual Basic. On Mac, use Developer > Visual Basic; Option+F11 may also open the editor, depending on the keyboard’s function-key settings. Save a macro-enabled workbook as .xlsm if it must retain VBA. Excel for the web can open a macro-enabled workbook, but it cannot run or edit VBA macros; use the desktop app for this procedure.

Adapt the rule to common entry requirements

Change the formula to reflect the actual policy, and keep the cell reference aligned with the first cell in the validated range. These examples show the worksheet layout each rule assumes:

  • Unique entries: With names entered in A2:A50 and A2 as the first validated cell, use =COUNTIF($A$2:$A$50,A2)=1. It rejects a value that would occur more than once in the specified range.
  • Required value when another field is filled: With a status in A2 and a corresponding description in B2, apply validation to B2:B100 using =OR(A2="",B2<>""). A blank status permits a blank description; a filled status requires one. Adjust the references if the first row differs.
  • Text beginning with a prefix: With codes in A2:A100, use =LEFT(A2,3)="INV" to require the first three characters to be INV. This checks the prefix, not the rest of the code’s format.

These rules are examples, not universal templates. The conditional-required example permits a description when the status is blank; if that is not allowed, add a condition that explicitly prohibits it. Likewise, a uniqueness check against the entire input range may treat a cell containing its own value as one occurrence, which is why a count equal to one is accepted.

Choose custom validation or a built-in option

For a simple restriction, a built-in validation type is usually easier to maintain. On Windows in Microsoft 365, select the cells and open Data > Data Validation. In the Settings tab, choose a value under Allow, such as Whole number, Date, or List. Excel for Mac has a Data Validation command on the Data tab; labels and dialog layout can vary slightly by release. The same basic validation choices are available in the desktop apps.

Use a custom formula when the condition needs logical operators, references to related cells, or a test not represented by the built-in choices. A list rule is often preferable for fixed choices because it provides a drop-down; custom validation generally checks whether the entry meets a condition rather than presenting choices. You can also use a list alongside an input message to explain the expected format.

VBA is worthwhile when the rule must be installed consistently across many cells, worksheets, or generated workbooks. For a one-time change, using the Data Validation dialog may be simpler. VBA adds automation, not a stronger enforcement boundary: users can change validation, and imported or pasted content may not be checked in the same way as a normal typed entry.

Common problems and how to fix them

  • The formula checks the wrong cell. Make its relative reference match the top-left cell of the applied range. For B2:B100, use B2, not B1 or a fully fixed $B$2 reference.
  • The rule rejects an expected value. Custom formulas must return TRUE for allowed entries. Test the formula against an example that should pass and one that should fail. Also check whether values that look numeric are actually stored as text; ISNUMBER returns FALSE for text.
  • The macro reports an error. Confirm the worksheet name and range exist, and that the target sheet is not protected against changes to validation. A workbook opened in a restricted or read-only state may also prevent edits. Review the exact formula string for balanced parentheses and quotation marks.
  • Existing bad values remain in the cells. Adding validation normally governs later entry; it is not a cleanup command. Review existing records separately, for example with a helper formula or a filter, before relying on the rule.
  • Pasted values seem to bypass the rule. Data validation is an entry aid, not a security feature. Paste operations, external imports, macros, and other editing routes can leave invalid data. Add a separate audit or checking step when the workbook’s accuracy matters.

Another frequent issue is a direct reference to a different worksheet in a validation formula. Excel’s validation rules can restrict direct cross-sheet references. A common workaround is to define a workbook name for the source range and refer to that name, or to use a helper cell on the same sheet. Keep helper logic visible or documented so a future editor can understand why the rule works.

Messages, blank cells, and practical limits

The input message and error message serve different purposes. An input message explains what to enter when a user selects a cell; it is optional guidance. An error alert appears after an entry fails the rule. In the VBA example, xlValidAlertStop makes the alert a stop-style warning, while .ShowError = True enables the error message. If the macro sets .ShowError = False, the rule may still exist but users will not see the configured error alert.

.IgnoreBlank = True allows blanks where Excel’s validation behavior permits them. It does not mean that every formula containing a blank will behave as a required-field rule; the formula itself still determines whether the input is valid. If a cell must never be blank, express that condition in the custom formula and test deletion as well as typing. Other workbook processes can still introduce blanks or invalid values.

Data validation formulas have practical constraints, and complex rules become difficult to maintain. Keep the formula readable, use named ranges or helper cells for complicated logic, and test the rule after copying or extending the validated range. If the workbook uses regional settings, enter the formula through VBA in standard VBA formula syntax and verify the resulting rule in Excel’s Data Validation dialog.

Frequently Asked Questions

What is the VBA constant for custom data validation?
Use xlValidateCustom as the Type argument to Validation.Add. Supply the rule through Formula1, and make sure the formula returns TRUE for permitted entries.

Does a custom validation formula start with an equals sign?
Yes. The formula string normally begins with =, for example "=A2>0" in VBA when the validated range starts at A2. The quotation marks delimit the VBA string; the equals sign belongs to the Excel formula.

How do I apply one custom rule to a whole range?
Call Validation.Add on the range, such as B2:B100, and write the formula using B2 as the relative reference when B2 is the range’s top-left cell. Excel adjusts that relative reference for other cells.

Can custom validation stop users from pasting invalid data?
Do not rely on it as a complete paste or data-import safeguard. Validation is designed to guide and check cell entry, but other ways of changing a workbook can bypass or replace the rule. Use a separate audit if invalid records must be detected.

Can I undo a VBA validation macro with Ctrl+Z?
No. Running a VBA macro clears Excel’s normal undo history, so Ctrl+Z cannot reverse its changes. Save a separate copy of the workbook before running a macro that changes validation.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.