Excel VBA: Validate text is range

What VBA would I need to validate a string can also be a range?

Assume I have a sheet where the user enters text in A1. If the text can be a range like "B20" then show a message box that says, "This is a range!!"

"A1" = True: It is a range
"2-2A" = False: is not a range
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wayne Taylor (webtubbs)Commented:
You'll probably need to capture an error using the EVALUATE method. Something like this...

Function ValidateRange(str As String) As Boolean
    Dim x As String
    On Error Resume Next
    x = Evaluate(str)
    ValidateRange = (Err.Number = 0)
    On Error GoTo 0
End Function

Open in new window

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may declare a range variable and set it with the text entered in A1 and if the text entered is a valid address of a cell/range, the range variable will be set without an error else it will not be set and will be nothing and then you can test if the range variable is set correctly or not to prompt the user.
e.g. place the following change event code on Sheet Module and test it by entering a test string in cell A1.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim rng As Range
If Target.Address(0, 0) = "A1" Then
    On Error Resume Next
    Set rng = Range(Target)
    On Error GoTo 0
    If Not rng Is Nothing Then
        MsgBox Target & " is a range."
        MsgBox Target & " is not a range."
    End If
End If
End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Both the answers resolved the original question.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.