Checking that Excel Range Exists in VB.NET

I am new to writing VB.NET code for Excel and needed to have a function that checked to see if a certain named range exists in a worksheet. I need this for a VB.NET Add-in that I'm writing for Excel 2003. I found some example Excel VBA code that would work nice, but I can seem to get it written correctly for VB.NET. My VB.NET translated code that I have is:

Private Function RangeNameExists(ByVal nname As String) As Boolean
' Returns TRUE if the range name exists
Dim n As Excel.Name
RangeNameExists = False

For Each n In ThisWorkbook.Names <-- ERROR OCCURS HERE
If UCase(n.Name) = UCase(nname) Then
RangeNameExists = True
Exit Function
End If
Next n
End Function

The error that I get is "System.NullReferenceException: Object reference not set to an instance of an object"

Major Tom

Welcome to the forum MajorTom, you could check this link Automate Office with .NET (http://www.xtremevbtalk.com/showthread.php?t=160433) you may find it very useful...

What you need is have something like this
Dim xlApp As Excel.Application

Dim WB As Excel.Workbook
Dim WS As Excel.Worksheet


Good luck :)

I should have mentioned that I have the following objects declared at the start of my class..

Dim WithEvents applicationObject As Excel.Application
Dim addInInstance As Object
Dim ThisWorkBook As Excel.Workbook
Dim ThisWorkSheet As Excel.Worksheet

The error message seems to say that I don't have something initialized properly, but I'm not sure what I'm doing wrong.

Major Tom

Tom, that code is perfectly valid for VBA. However, in VB 6.0 or in VB.Net, the concept of 'ThisWorkbook' has no meaning.

In VBA, 'ThisWorkbook' means "the Workbook in which this code resides". But for a DLL (a "managed COM DLL" in this case) the code is compiled. There is no "ThisWorkbook".

So I think you have to think about "Which Workbook" you really mean? Do you mean the ActiveWorkbook? In that case I would utilize applicationObject.ActiveWorkbook. Or, better yet, have your RangeNameExists() function take a 'TheWorkbook As Excel.Workbook' arguement so that the Caller can pass in the RangeName and the Workbook it wishes to test.

Make sense?

Mike,
Thanks for you help. I managed to get this to work. This might not be the the best way to code this but this is what I have...

At the beginning of my Add-In Class

Dim WithEvents applicationObject As Excel.Application
Dim addInInstance As Object
Dim ThisWorkBook As Excel.Workbook

The RangeNameExists Function

Private Function RangeNameExists(ByVal pstrRangeName) As Boolean
' Returns TRUE if the range name exists

Dim lngMaxNames As Long
Dim lngCount As Long

RangeNameExists = False

ThisWorkBook = applicationObject.Workbooks(1)
lngMaxNames = ThisWorkBook.Names.Count

For lngCount = 1 To lngMaxNames
If UCase(ThisWorkBook.Names.Item(lngCount).Name) = UCase(pstrRangeName) Then
RangeNameExists = True
Exit For
End If
Next

End Function

Major Tom

Looks good to me. I will say that "ThisWorkbook" might look confusing to some because it has a specific meaning in VBA, but if it's clear to you, then that's fine.

Also, using 'Workbooks(1)' is not very well specified. Maybe it would be better to access the Workbook by Name (string)? If you do not know the name, then I think you probabably mean the 'applicationObject.ActiveWorkbook' in this case?

Lastly, you are using some VERY long names. 'applicationObject', for example, is pretty long and you will need to use it a lot. I use 'xlApp' myself. Others use 'oExcel' or sometimes even 'oXL'. Up to you of course, but it's just an idea. I use 'xlApp' because it's easy to be consistent with 'xlWB', 'xlWS', or 'xlRng' and the like...