×

VBA Function To Validate Range Address

By Chris Newman •  Updated: 09/01/23 •  3 min read
How to create multiline text in ribbon tooltips with Excel.

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 Function

Understanding 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:

  1. The function IsCellReferenceValid accepts a string input CellReference, which is the supposed cell reference to be validated.
  2. The On Error Goto InvalidReference statement tells VBA to jump to the InvalidReference label if any error occurs during execution.
  3. We then declare a Worksheet object sht and a Range object rng.
  4. We assign the active worksheet of the workbook to sht.
  5. We try to assign the range based on cellReference from the worksheet to rng.
  6. If no error occurs, IsCellReferenceValid is set to TRUE, indicating a valid reference, and the function ends.
  7. 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.

Range Validation Function Example Excel Userform

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.

Keep Learning

Chris Newman

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.