Share via


Better way to check if name range exists in Excel workbook

Question

Friday, October 22, 2010 9:16 PM

While working on an excel 2003 add-in with vb.net, I need to check if a name range exists in the workbook.

Currently I have to loop through all the names and do a comparison like the following code. This becomes very slow when a workbook has over 6000 name ranges.

 

For Each nm As Excel.Name In

activeWorkbook.Names

 

  If nm.Name.equals("mysearch") Then

   do something

 

  End If

 

Next

nm

 

I am wondering if there is a better way to do that. Something like Hashtable.ContainsKey() would be nice.

Thanks

 

All replies (6)

Saturday, October 23, 2010 5:50 AM ✅Answered

Just a thought - why not try to find the name, assuming it exists, and handle the exception if it is not found?

I didn't have the patience to create 6000 named ranges, but it may be worth a shot. Sorry for the C# code, but I think it should be relatively easy to convert.

var workbook = Globals.ThisAddIn.Application.ActiveWorkbook;
var name = "Alpha";
var names = workbook.Names;
try
{
  var result = names.Item(name);
  MessageBox.Show(result.Name);
}
catch (Exception)
{
  MessageBox.Show("No such name here.");
}

Hope this helps,

Mathias


Sunday, October 24, 2010 11:41 AM ✅Answered

Peter has gotten another reply to this duplicate question in http://social.msdn.microsoft.com/Forums/en-US/exceldev/thread/d751b59e-0a46-4a79-97fe-7438cf768ee8

FWIW provoking an error is not an optimal approach in context of the .NET Framework (as opposed to VBA) and the use of Exceptions for that purpose is discouraged...

Cindy Meister, VSTO/Word MVP


Sunday, October 24, 2010 2:51 AM

Thanks for the reply. I tried the 'try-catch' approach with the following code. However in my case, I need to check over 500 names to see if they exists, this become slow as well. Is there a way to do something like the SQL select where clause because all my names starts with 'Period_"?

Try

 

End Try Return False Catch Return True

 

End Try Return False Catch Return True Dim namedRange As Name = activeWorkbook.Names.Item(strName)


Sunday, October 24, 2010 2:54 AM

sorry, I cut and paste my code in previous message, but it shows up all wrong. I am giving it a second try. Here is my code:

 

Try

 

Dim namedRange As

Name = activeWorkbook.Names.Item(strName)

 

Return True

 

Catch

 

Return False

 

End Try


Sunday, October 24, 2010 11:01 PM

Cindy,

Totally agree with the statement regarding Exceptions; in general it's a fairly bad idea to use Exceptions as a control flow mechanism. However, in this case, it didn't seem like a bad approach: if the name requested is expected to exist, then the exception shouldn't happen.


Thursday, April 19, 2018 6:40 PM

Better late to a party than never...

From what I can tell the NamedRange object is a member of the Worksheet Controls collection.

This VB in a VSTO works for me:

Sub InitNamedRange(_rngTargetToName As Range, _strName As String)

Dim wsNative AS Worksheet = _rngTargetToName.Worksheet

Dim ws As Worksheet = Globals.Factory.GetVSTOObject(wsNative)

Dim controls As ControlCollection = ws.Controls

' Check to see if the Named ranges exists

If Not controls.Contains(_strName) Then 

     ' Add it If not

     rngNamedNew = ws.Controls.AddNamedRange(_rngTargetToName, _strName))

End If

End Sub