How to identify if a named range exists

[Ken Puls]

Hello,

I'm writing code to insert a new required worksheet in many of my current workbooks. A couple of the cells have formulas in them that refer to a named formula. I need to make sure that the formula exists in the current workbook, but am unsure of how to code it.

What I need is to have the following:

If activeworkbook.range.name("date") exists Then
Else msgbox("Your workbook does not contain a named date range!")
Exit sub
End if

Can anyone help?

Thanks,

 

[NateO]

Hello Ken, try the following:

<font face=Courier New><SPAN style="color:darkblue">Sub</SPAN> sdjhfjhsfjdshfjdshjfkhdsjkhfsjkhfjkshdfkjfdhjkshfjkhfjkhsdjh()
<SPAN style="color:darkblue">Dim</SPAN> rng <SPAN style="color:darkblue">As</SPAN> Range
<SPAN style="color:darkblue">On</SPAN> <SPAN style="color:darkblue">Error</SPAN> <SPAN style="color:darkblue">GoTo</SPAN> 1
<SPAN style="color:darkblue">Set</SPAN> rng = Range("date")
<SPAN style="color:darkblue">On</SPAN> <SPAN style="color:darkblue">Error</SPAN> <SPAN style="color:darkblue">GoTo</SPAN> 0

<SPAN style="color:darkblue">Set</SPAN> rng = <SPAN style="color:darkblue">Nothing</SPAN>

<SPAN style="color:darkblue">Exit</SPAN> <SPAN style="color:darkblue">Sub</SPAN>

1:
MsgBox "Your workbook does not contain a named date range!"

<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>

 

[Ken Puls]

Beautiful, thanks!

Where did you learn this stuff, anyway? I'm trying to find a course on VBA, specifically on the Excel object model, but can't find anything out there.

Cheers,

 

[NateO]

You're welcome.

I loiter on Excel message boards. Some truth to that, and I've done a lot of reading on the internet, help files. Took a beginner class at one point. Don't know where in your neighborhood you go for a more advanced one...

You pick it up as you go, at some point it became apparent to me that if you try to set an object, and that target object doesn't exist, vb's gonna crap out, and you can use this to your advantage when handled correctly.

Bon chance eh.

 

[Smitty]

Ken,

John Walkenbach's Excel 2000 Power Programming with VBA is excellent.

You can find it on Amazon.com for less than $30.

And what I've picked up here has helped immensly too!

Smitty

 

[Ken Puls]

Nate, you must be older than your picture looks to have learned that much from help files

Is that picture from the Rosedale yearbook? (just kiddin!)

Thanks again...

 

[NateO]

Nate, you must be older than your picture looks to have learned that much from help files

Is that picture from the Rosedale yearbook? (just kiddin!)

Thanks again...

Not too far off, 1 year after Rosedale.

Yeah, a book might not be a bad idea, I have seen Juan Pablo G. recommend:

http://www.amazon.com/exec/obidos/t..._books_1/103-8856093-0654239?v=glance&s=books

I have not had a chance to review it myself.

 

[jaja]

Combined ... Better?

Public Function xExists(Optional wbName As String, Optional shName As String, Optional rngName As String) As Boolean
    'returns TRUE if the range/sheet/book exists,
    'returns FALSE if the range/sheet/book is invalid
    
    If wbName = "" Then wbName = ActiveWorkbook.Name
    If shName = "" Then shName = ActiveSheet.Name
    If rngName = "" Then rngName = "A1"

    On Error GoTo Invalid
    xExists = False
    Test_Exist = Workbooks(wbName).Sheets(shName).Range(rngName).Value
    xExists = True
    
Invalid:
    Err.Clear
    
End Function