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.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
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’.

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.

'######################################################################################
'Employee Number Validation Started '######################################################################################
 Activate
Range("A5").Select
exceptionCount = 0
Do Until ActiveCell.Value = vbNullString
' Check if the Employee Number is Numeric and of Length 7
If Len(ActiveCell.Value) <> 7 Or IsNumeric(ActiveCell.Value) <> True Then
totalExceptions = 1
exceptionCount = 1
Interior.ColorIndex = 6 'Highlight with Yellow Color
Else
Interior.ColorIndex = 15 'Retain Grey Color
End If
Offset(1, 0).Select
Loop
If exceptionCount = 1 Then
exceptionString = exceptionString + vbCrLf & "- Employee Number has to be Numeric and of Length 7"
End If
'####################################################################################
'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.

'######################################################################################
'Employee Name Validation Started
'######################################################################################
Activate
Range("B5").Select
exceptionCount = 0
Do Until ActiveCell.Address = "$B$10"
' Check if the Employee Name is Not Null
If IsEmpty(ActiveCell.Value) Then
totalExceptions = 1
exceptionCount = 1
Interior.ColorIndex = 6 'Highlight with Yellow Color
Else
Interior.ColorIndex = 15 'Retain Grey Color
End If
Offset(1, 0).Select
Loop
If exceptionCount = 1 Then
exceptionString = exceptionString + vbCrLf & "- Employee Name Cannot be Empty"
End If
'######################################################################################
'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.

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.

'#################################################################################### 'Joining Date Validation Started '####################################################################################
Activate
Range("C5").Select
exceptionCount = 0
recordCount = 0
Dim dtStart As Date
Dim dtEnd As Date
dtStart = #1/1/2000#
dtEnd = #1/1/2017#
Do Until ActiveCell.Value = vbNullString
'Validate Joining Date
If IsDate(ActiveCell.Value) <> True Or (ActiveCell.Value < dtStart Or ActiveCell.Value > dtEnd) Then
totalExceptions = 1
exceptionCount = 1
Interior.ColorIndex = 6 'Highlight with Yellow Color
Else
Interior.ColorIndex = 15 'Retain Grey Color
End If
recordCount = recordCount + 1
Offset(1, 0).Select
Loop
If exceptionCount = 1 Then
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"
End If
'#################################################################################### '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.

'#################################################################################### 'Employee Department Validation Started '####################################################################################
Activate
Range("D5").Select
exceptionCount = 0
Do Until ActiveCell.Value = vbNullString
' Check if the Employee Department has number
If HasNumber(ActiveCell.Value) Then
totalExceptions = 1
exceptionCount = 1
Interior.ColorIndex = 6 'Highlight with Yellow Color
Else
Interior.ColorIndex = 15 'Retain Grey Color
End If
Offset(1, 0).Select
Loop
If exceptionCount = 1 Then
exceptionString = exceptionString + vbCrLf & "- Employee Department cannot conain numbers"
End If
'#################################################################################### 'Employee Department Validation Completed '####################################################################################
'#################################################################################### Begin - Function to Check if string has a number '####################################################################################
Function HasNumber(strData As String) As Boolean
Dim iCnt As Integer
For iCnt = 1 To Len(strData)
If IsNumeric(Mid(strData, iCnt, 1)) Then
HasNumber = True
Exit Function
End If
Next iCnt
End Function
'#################################################################################### '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.