Post
Ask Question

Implement Excel Validations Using VBA Macro Before Uploading To SharePoint

SharePoint can store the documents of various formats, the common formats being MS Word and MS Excel documents. Data validations in an Excel sheet is a prerequisite, if we want to ensure data correctness. Human errors can creep in to the Excel sheets, if there are no validations. Manual validations are time consuming and not the best way to go when the Excel data sheet is complex and numeric computational formulas are involved. In this article, we will see how we can make use of VBA Macros to write validation logic to check for data correctness before saving Excel sheets and uploading to SharePoint.

Excel Workbook used for the demo is attached along with this article. You can download it and test it out.

Prerequisites

We have to make sure that the developer tab is available in an Excel sheet in order to get started with the development, using VBA. In order to do this, right click anywhere on the tool bar and select customize the ribbon option.


From the list box, select Developer option and click OK.


This will activate the developer tab in the ribbon menu.


Click on View code to open up Visual Basic code Window.


We will be making use of the ‘BeforeSave’ event of Excel Workbook to trigger the validation logic. Thus, click on ‘ThisWorkbook’ to open up the code Window, where we will add the ‘BeforeSave’ method. We will be writing out the entire code within this method, as it will be invoked on the Workbook Save event.


  1. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _  
  2. Cancel As Boolean)  
  3. End Sub   

Before continuing with our development, we will have to save the Workbook first. Normal Save as .xlsx will throw an error.


Since macros are involved in the Workbook, we will have to save them as macro enabled Workbook with the extension .xlsm.


Employee Details Validations

As part of this demo, we will have an Excel sheet that contains the Employee details given below like Employee ID, Employee Name, Joining Date, Department and an Address.


We will have the requirement to have the validations given below to be checked before saving an Excel sheet and uploading to SharePoint.

Validations

  • Employee ID should have a length of 7 and only numbers are allowed in the ID.
  • Employee Name should never be left blank.
  • Joining Date has to be a valid date of the format MM/DD/YYYY and between the range 1/1/2010 and 1/1/2017.
  • Department name should not contain any numbers in it.

Employee ID Validation

Since we have two validations to take care of in Employee ID, we will have to make sure that the entry in the cell should validate to ‘True’ for both the conditions. Hence, we will use ‘Or’ logical operator to throw an exception, if any one of the condition is false. We will start with the Cell number A5, which is indicated by Range("A5").Select statement .

We will then loop through the column cells until the empty cells are encountered. In order to check, if the Cell Value is of Length 7, we will make use of ‘Len’ function and for checking, if only numbers are there; we will make use of function ‘IsNumeric’.

  1. If Len(ActiveCell.Value) <> 7 Or IsNumeric(ActiveCell.Value) <> True   

If any one of the condition evaluates to false we will set the ‘exceptionCount’ variable to 1 and set the Exception String that will be shown in the message box. In case of exception we will also set the ‘ActiveCell.Interior.ColorIndex’ to ‘6’ which will highlight the exception cell with yellow color, else retains the grey color.

  1. '######################################################################################  
  2. 'Employee Number Validation Started '######################################################################################  
  3.  Activate  
  4. Range("A5").Select  
  5. exceptionCount = 0  
  6. Do Until ActiveCell.Value = vbNullString  
  7. ' Check if the Employee Number is Numeric and of Length 7  
  8. If Len(ActiveCell.Value) <> 7 Or IsNumeric(ActiveCell.Value) <> True Then  
  9. totalExceptions = 1  
  10. exceptionCount = 1  
  11. Interior.ColorIndex = 6 'Highlight with Yellow Color  
  12. Else  
  13. Interior.ColorIndex = 15 'Retain Grey Color  
  14. End If  
  15. Offset(1, 0).Select  
  16. Loop  
  17. If exceptionCount = 1 Then  
  18. exceptionString = exceptionString + vbCrLf & "- Employee Number has to be Numeric and of Length 7"  
  19. End If  
  20. '####################################################################################  
  21. 'Employee Number Validation Completed '####################################################################################  

In case of invalid data in Employee Number, we will get the error message given beloww while saving the Workbook.


Employee Name Validation

The Employee Name field has the requirement that it should never be empty. We will start with the Cell number B5, which is indicated by Range("B5").Select statement .

We will then loop through the column cells until B10 cell, the last cell, is encountered. In order to check, if the Cell Value is empty, we will make use of function ‘IsEmpty’ .If any one of the condition evaluates to false, we will set the ‘exceptionCount’ variable to 1 and set the Exception String that will be shown in the message box. In case of an exception, we will also set the ‘AcetiveCell.Interior.ColorIndex’ to ‘6’ , which will highlight the exception cell with yellow color, else it retains the grey color.

  1. '######################################################################################  
  2. 'Employee Name Validation Started  
  3. '######################################################################################  
  4. Activate  
  5. Range("B5").Select  
  6. exceptionCount = 0  
  7. Do Until ActiveCell.Address = "$B$10"  
  8. ' Check if the Employee Name is Not Null  
  9. If IsEmpty(ActiveCell.Value) Then  
  10. totalExceptions = 1  
  11. exceptionCount = 1  
  12. Interior.ColorIndex = 6 'Highlight with Yellow Color  
  13. Else  
  14. Interior.ColorIndex = 15 'Retain Grey Color  
  15. End If  
  16. Offset(1, 0).Select  
  17. Loop  
  18. If exceptionCount = 1 Then  
  19. exceptionString = exceptionString + vbCrLf & "- Employee Name Cannot be Empty"  
  20. End If  
  21. '######################################################################################  
  22. 'Employee Name Validation Completed '######################################################################################  

In case of invalid data in Employee Name, we will get the error message while saving the Workbook:


Joining Date Validation

The Joining Date field has the requirement that it has to be a valid date of the format MM/DD/YYYY and between the range 1/1/2010 and 1/1/2017. We will start with the Cell number C5, which is indicated by Range("C5").Select statement .

We will then loop through the column cells until empty cells are encountered. In order to check, if the date is a valid date, we will make use of the ‘IsDate’ function. We also have to make sure that the date is between a specified date range, which is achieved using the condition given below.

  1. If IsDate(ActiveCell.Value) <> True Or (ActiveCell.Value < dtStart Or ActiveCell.Value > dtEnd) 

Since we have two validations to take care of in Joining Date, we will have to make sure that the entry in the cell should validate to ‘True’ for both the conditions. Hence we will use ‘Or’ logical operator to throw an exception, if any one of the conditiosn is false. If any one of the conditions evaluates to false, we will set the ‘exceptionCount’ variable to 1 and set the Exception String, which will be shown in the message box. In case of an exception, we will also set the ‘ActiveCell.Interior.ColorIndex’ to ‘6’, which will highlight the exception cell with yellow color, else it retains grey color.

  1. '#################################################################################### 'Joining Date Validation Started '####################################################################################  
  2. Activate  
  3. Range("C5").Select  
  4. exceptionCount = 0  
  5. recordCount = 0  
  6. Dim dtStart As Date  
  7. Dim dtEnd As Date  
  8. dtStart = #1/1/2000#  
  9. dtEnd = #1/1/2017#  
  10. Do Until ActiveCell.Value = vbNullString  
  11. 'Validate Joining Date  
  12. If IsDate(ActiveCell.Value) <> True Or (ActiveCell.Value < dtStart Or ActiveCell.Value > dtEnd) Then  
  13. totalExceptions = 1  
  14. exceptionCount = 1  
  15. Interior.ColorIndex = 6 'Highlight with Yellow Color  
  16. Else  
  17. Interior.ColorIndex = 15 'Retain Grey Color  
  18. End If  
  19. recordCount = recordCount + 1  
  20. Offset(1, 0).Select  
  21. Loop  
  22. If exceptionCount = 1 Then  
  23. exceptionString = exceptionString + vbCrLf & "- Joining Date has to be a valid date of the format MM/DD/YYYY and Between 1/1/2000 and 1/1/2017"  
  24. End If  
  25. '#################################################################################### 'Date Validation Completed '####################################################################################  

In case of an invalid data in Joining Date, we will get the error message given below while saving the Workbook.


Department Name Validation

For Department Name validation, we have to make sure that it does not contain any numbers. We will start with the Cell number D5, which is indicated by Range("D5").Select statement .

We will then loop through the column cells until empty cell is encountered. In order to check, if the Cell Value has a number, we will create a custom function called ‘HasNumber’ and pass the current cell value to it. The function will return True, if there is a number in the string and we will set the ‘exceptionCount’ variable to 1 and set the Exception String that will be shown in the message box. In case of an exception, we will also set the ‘ActiveCell.Interior.ColorIndex’ to ‘6’, which will highlight the exception cell with Yellow color, else it retains a grey color.

  1. '#################################################################################### 'Employee Department Validation Started '####################################################################################  
  2. Activate  
  3. Range("D5").Select  
  4. exceptionCount = 0  
  5. Do Until ActiveCell.Value = vbNullString  
  6. ' Check if the Employee Department has number  
  7. If HasNumber(ActiveCell.Value) Then  
  8. totalExceptions = 1  
  9. exceptionCount = 1  
  10. Interior.ColorIndex = 6 'Highlight with Yellow Color  
  11. Else  
  12. Interior.ColorIndex = 15 'Retain Grey Color  
  13. End If  
  14. Offset(1, 0).Select  
  15. Loop  
  16. If exceptionCount = 1 Then  
  17. exceptionString = exceptionString + vbCrLf & "- Employee Department cannot conain numbers"  
  18. End If  
  19. '#################################################################################### 'Employee Department Validation Completed '####################################################################################  
  20. '#################################################################################### Begin - Function to Check if string has a number '####################################################################################  
  21. Function HasNumber(strData As String) As Boolean  
  22. Dim iCnt As Integer  
  23. For iCnt = 1 To Len(strData)  
  24. If IsNumeric(Mid(strData, iCnt, 1)) Then  
  25. HasNumber = True  
  26. Exit Function  
  27. End If  
  28. Next iCnt  
  29. End Function  
  30. '#################################################################################### 'End - Function to Check if string has a number '####################################################################################  

In case of invalid data in Department Name, we will get the error message given below, while saving the Workbook.


If there were multiple data validation errors, all the validation checks will be fired, the error message will be combined and shown as a single message box, as shown below.


Only after correcting the entire Yellow highlighted errors, will we be able to save the Excel Workbook.

Summary

Thus, we saw how we can make use of VBA Macros in an Excel sheet to perform the data validations before saving the data sheet and uploading it to SharePoint.


Similar Articles