Check if string is valid range

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Check if string is valid range

Post by YasserKhalil »

Hello everyone
Is there a way to check if a string is valid range or not?
Example: If I have string like that "A1:D10" .. How can I check if this string is a valid range or not?

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Check if string is valid range

Post by HansV »

For example:

Code: Select all

Function ValidRange(MyString As String) As Boolean
    Dim MyRange As Range
    On Error Resume Next
    Set MyRange = Range(MyString)
    ValidRange = (Err = 0)
End Function
Example of usage:

Code: Select all

Sub Test()
    Dim MyString As String
    MyString = "A1:A10"
    Debug.Print MyString, ValidRange(MyString)
    MyString = "A1:A"
    Debug.Print MyString, ValidRange(MyString)
End Sub
Result in the Immediate window:

Code: Select all

A1:A10        True
A1:A          False
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Check if string is valid range

Post by YasserKhalil »

Thank you very much. Amazing solution.

Here's my try that I need to evaluate that approach (to make sure it is right)

Code: Select all

Sub Test()
    Dim e
    e = "A1:A10"
    If IsNumeric(Evaluate("SUM(" & e & ")")) And Not IsNumeric(e) Then
        Debug.Print "Valid Range"
    End If
End Sub

LisaGreen
5StarLounger
Posts: 964
Joined: 08 Nov 2012, 17:54

Re: Check if string is valid range

Post by LisaGreen »

Nice use of on error resume next Hans!!!

Lisa

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Check if string is valid range

Post by StuartR »

LisaGreen wrote:
26 Jul 2020, 12:30
Nice use of on error resume next Hans!!!

Lisa
I thought that was very elegant too
StuartR