VBA Function To Validate Range Address
You Need To Check Your Cell Address Input
A common task when using VBA with Excel is to validate cell references. However, handling cell references in VBA is often viewed as challenging, especially when one needs to validate whether a given input is a valid cell reference.
In this blog post, we’re going to unravel the mystery of validating cell references in Excel VBA and present a straightforward and practical solution.
VBA Code Solution For Testing A Range Reference Input
The following code provides a VBA macro function that validates a result of TRUE if a given input is a valid cell reference within Microsoft Excel.
Function IsCellReferenceValid(CellReference As String) As Boolean
'PURPOSE: Determine if a given string is a valid cell reference
'SOURCE: www.TheSpreadsheetGuru.com
Dim sht As Worksheet
Dim rng As Range
'Use any worksheet (like the first one) for this operation
Set sht = ActiveSheet
'Try to use the cell reference
On Error GoTo InvalidReference
Set rng = sht.Range(CellReference)
On Error GoTo 0
'If there was no error, it's a valid reference
IsCellReferenceValid = True
Exit Function
'ERROR HANDLERS
InvalidReference:
IsCellReferenceValid = False
End FunctionUnderstanding How The Macro Code Works
Let’s delve into this VBA macro function that validates if a given input is a valid cell reference. This function uses the tried-and-true programming method of “trial and error”. The function tries to use the input as a cell reference, and if an error is thrown, it understands the reference is invalid.
Here’s a quick walkthrough of the function:
- The function IsCellReferenceValid accepts a string input CellReference, which is the supposed cell reference to be validated.
- The On Error Goto InvalidReference statement tells VBA to jump to the InvalidReference label if any error occurs during execution.
- We then declare a Worksheet object sht and a Range object rng.
- We assign the active worksheet of the workbook to sht.
- We try to assign the range based on cellReference from the worksheet to rng.
- If no error occurs, IsCellReferenceValid is set to TRUE, indicating a valid reference, and the function ends.
- If any error occurs, the code execution jumps to InvalidReference, setting IsCellReferenceValid to FALSE, indicating an invalid reference.
Use Case Examples With This VBA Code
Now that you’ve gotten to grips with the code, let’s illustrate its utility with some real-world examples.
Validating Userform Inputs
Consider you have a userform created where you need the user to input a cell reference into a textbox. It would be logical that you would want to ensure the user provided a valid spreadsheet reference before executing your userform’s code to ensure it doesn’t error out.
You can use this article’s validation function in your userform execution button’s code to ensure it runs as intended. Here is a simple example:
Private Sub CommandButton1_Click()
'PURPOSE: Determine if a given string is a valid cell reference
If IsCellReferenceValid(TextBox1.Text) = False Then
MsgBox "Invalid cell reference. Please try again."
TextBox1.Text = ""
Exit Sub
End If
'Execute userform actions......
End Sub
I Hope This Microsoft Excel VBA Tutorial Helped!
Excel VBA offers a powerful toolkit for enhancing the capabilities of your spreadsheets. With functions like IsCellReferenceValid, you can add data validation capabilities to your macros, helping to prevent errors and improve the user experience.
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.