Text if range exists Excel VBA

Question

I want to test if a range exists to be able to create the following pattern:

if not exists(r) then
   MsgBox("Range is missing")
end if

Function exists(r as range) as boolean

End function

Here is an example of a range that I would like to test if it exists or not

Call RangeExists(lob.ListColumns("Leverera utt").DataBodyRange)

How can I do this?

manu · Accepted Answer · 2016-01-28 08:52:23Z

1

You could do it this way:

Sub CheckRange()

    Dim myRange As Variant

    myRange = InputBox("Enter your name of your range")

           If RangeExists(CStr(myRange)) Then
                MsgBox "True"
           Else
                MsgBox "No"
           End If

    End Sub

And the function:

   Function RangeExists(s As String) As Boolean
        On Error GoTo No
        RangeExists = Range(s).Count > 0
    No:
    End Function

answered Jan 28, 2016 at 8:52

manu

9428 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

PankajR Over a year ago

elegant function RangeExists

Kathara Over a year ago

why don't you declare myRange as String directly? instead of entering a string in the inputbox, send it to myRange which is a variant and then convert it back to a string?! Uneccessairily complicated or do I miss something?

manu Over a year ago

@Kathara It is because i used this code personnaly with an array and not with an Input box. That is true you can directly declare the input box as string. I did not change it because the most important was the function and not the Sub.

Kathara Over a year ago

@manu aah, ok. Well then it's just nicer for the eye if you'd change it and it will probably be faster by 0.0000x seconds ;)

user4720266 · Accepted Answer · 2020-04-02 09:57:11Z

1

Avoiding the label required in ON ERROR GOTO is also possible Function RangeExists(rngName As String) As Boolean On Error Resume Next RangeExists = Range(rngName).Column And (Err.Number = 0) Debug.Print "RangeExists= " & RangeExists & " " & rngName End Function

answered Apr 2, 2020 at 9:57

user4720266

215 bronze badges

Collectives™ on Stack Overflow

Text if range exists Excel VBA

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related