Data Validation with User Feedback in VBA

With VBA, it’s possible to develop user input forms with data validation features and responses similar to those found on professional websites and desktop applications. In doing so, you can present the user with an familiar interface and user experience.

Difficulty Advanced
Time Est. 60-90 Minutes
VBA/Macros Yes
Template Files 2016-10-21-data-validation-with-vba-template
Solution Files 2016-10-21-data-validation-with-vba-solution

Spreadsheet Design Exercise 7

This exercise of the Spreadsheet Design series, continues the focus on Data Management. The steps below contain a large number of code snippets. If you would like to jump to the final consolidated code, click here.

Our previous post on Adding New Data to Datasets in Excel provided an overview of a VBA script that could be used to add data to a dataset. While the final product did what was expected, it did not contain any data validation features. With that, users could enter improper data (e.g. alpha characters in the price field) which would cause a debug error. It’s important to include data validation scripts which check the data before data is added to a spreadsheet.

It’s worth revisiting two prior posts focused on validation techniques with Excel’s built in validation tools, and various formulas. These methods provided the user with feedback through error messages and formatting. Today’s post will provide the third and final overview of data validation—validation using VBA scripts. While this is the most complicated approach, it is also the most flexible. This post will guide you through how to set up basic data validation techniques in VBA.

Step 1: Create a template and data entry tool.

This post will use the solution for the Adding New Data to Datasets in Excel post. It’s advisable to go through that exercise to understand the context of the form. You could also download the full solution here: 2016-10-21-data-validation-with-vba-template.

Step 2: Planning data validation feedback.

While focusing on how users interact with spreadsheets, it’s important to consider the feedback provided to users as part of the data validation process. Think about validation feedback when you enter data into a website form. If incorrect data is entered or values are missing, websites can either highlight the missing field or provide a list of one or more issues. Likewise, the stage at which the data validation occurs can vary, with some advanced forms validating data and providing the user with feedback as the user types the data in (think of many password resetting fields for banking and social media sites).

Some data validation systems leave something to be desired. Large forms that only provide one-line of vague feedback can frustrate users. Likewise, validation that is too strict—such as very a very specific date format demanded from the user—can be equally frustrating and may be better off handled on the back-end.

For the most part, all data validation features you encounter on websites and desktop applications can be implemented in Excel. The only one which may need to wait on the sideline is the feedback-as-user-types-in-data form, but please leave a comment if you have a technique for this. In this exercise, we will provide a validation feature which lists all errors below the form. This may not be the most elegant approach, but it’s a good starting point..

Step 3: Planning VBA scripts.

Here’s a narrative of all the processes the script will step through. For continuity, I’m including process which were completed in the last previous exercise (currently including in this exercise’s template).

  1. Done: All variables are declared.
  2. Done: All variables are defined by user inputs and predefined values.
  3. All user inputs are passed through a single data validation function, Data_Validation().
  4. The invoked Data_Validation() function verifies that the values are:
    1. Entered if they are specified as required;
    2. Integers or whole numbers;
    3. Dates;
    4. Alpha strings;
  5. If validation passes, the Data_Validation() function returns TRUE. If validation fails, the Data_Validation() function returns a string explaining what the issue is.
  6. Back in the subroutine, an array is built containing all the data validation errors.
  7. A for loop prints the errors on individual lines.
  8. A conditional exits the subroutine if errors exist. If no errors exist, a “Success!” confirmation is provided to the user.
  9. Done: Necessary calculations are done with validated data
  10. Done: Data is entered into the dataset.

Step 4: Implementing VBA.

This still will provide an overview on how to implement procedures 3 through 7 listed above. Snippets of code will be provided.

Process 1: All user inputs are passed through a singled validation function, Data_Validation().

This step starts by invoking the function that will be created in procedure 4. Directly below the data definitions, please the following script:

This accomplishes two tasks. First, it invokes the function. Second, it assigns a value to the first array item of outputValidation. The rest of the array will be built in procedure 5.

Processes 2 and 3: The invoked Data_Validation() function verifies that the values meet specified criteria.

We’ll start by displaying the script. This is a separate function which can be placed below the subroutine, or in a different module.

The function contains 4 parameters: (1) userPassValue passes the input value entered by the user, (2) expectedFormat passes a string value to assign a specific format for userPassValue as listed in the functions conditionals, (3) returnLabel passes a user friendly version of expectedFormat that will be embedded within the error message, and (4) requiredValue is an optional parameter passing a the Boolean TRUE if Pass_Value cannot be empty.

Now let’s break the script down:

If requiredValue is true AND userPassValue is empty, then the function returns returnLabel & ” is a required field” and exits. This part of the function is unique as it does not request a format.

This is the first part of the function that requests a format, allowing it to sort where each call should be tested. This specific snippet is looking for currency, which can be any numeric value. I userPassValue passes, TRUE is returned, otherwise, an error message is returned.

This snippet is looking for whole numbers, so it puts it through a conditional testing if it’s a number, then a nested conditional testing if it’s a whole number, by subtracting userPassValue from a rounded userPassValue. If the remainder is 0, then this passes and returns true. If it fails, an error message is returned.

This snippet tests if the value is a date. Any acceptable Excel date format will pass this test – when stripped down, Excel dates are just number ranges. A passing value returns TRUE, and failing value will return an error message.

The final test checks if the value userPassValue is not a number. This is done by nesting the IsNumeric() function within a NOT operator. A pass returns TRUE while a fail returns an error message.

The function then closes with a TRUE return if no formats were called.

Process 5: Back in the subroutine, an array is built containing all the validation errors.

Built an array that collects the values returned by the Data_Validation() function.

The for loop cycles through each value in the outputValidation array, bringing the value to a conditional test. If you recall from the previous snippet, the array contains a Variant value from the function, which is either the Boolean TRUE or an error message. In this condition, if the value is NOT TRUE, it prints the error message on a line of text. Additionally, if any of the single values in the array is NOT TRUE, the value of Boolean outputValidationStatus is changed to false, which will trigger an exit from the script before data input, as explained in the next process.
The final part of this process contains a Do Until loop which checks the following cells and clears them if errors still remain from a previous attempt.

Process 6. A conditional exits the subroutine if errors exist. If no errors exist, a “Success!” confirmation is provided to the user.

The Boolean outputValidationStatus returns TRUE if every value in the outputValidation array (validation controls) is TRUE. A TRUE value will print a success message for the user. A false value will exit the subroutine. Explanation of failure was previously provided to the user in process 7.

Full VBA Script

Download Solution: 2016-10-21-data-validation-with-vba-solution

Screen Captures

When data are entered into the form:

The exercise solution with data validation errors.
The exercise solution with data validation errors.

And without errors:

The exercise solution without any data validation errors.
The exercise solution without any data validation errors.

Other Points:

  • As mentioned before, VBA is almost limitless and very versatile. This was a very basic example of how to leverage VBA for validation. With the right approach, you can implement a validation system with a similar feel of popular websites and ecommerce platforms.
  • In the previous post where this template was created, all user inputs were declared as variants. This way, one function could be used for all inputs (the argument type must match the parameter type when being passed through a function). It would be possible to declare inputs in their more efficient forms if separate functions existed for each type, but that would open users up to debug errors when non-compatible values are assigned in the definition process.
  • The template used in this exercise include the Excel built in drop-down validation feature.


  • VBA can be complicated, and the most comprehensive your validation process, the more elaborate the script. This can make things difficult to change should you need to make modifications to the front end.

Leave a Reply