Test the Existence of Name of Cells

[ramass]

Hello,

In Visual Basic, is it possible to know if a cells has a name ?

There is the object "Name" for a range but an error occurs when we try to access to it when it does not exist.

thanks for replying

Matthieu
French developper

 

[Foada]

I am making some assuptions here. If you are talking about a cell in an Excel worksheet and that you are trying to see if there is a value in a specific cell in worksheet1:

IsEmpty(Sheet1.Cells(iR, iC))

will return true if Row iR and Column iC is empty where the top leftmost cell A1 is iR = 1 iC = 1. Anything is possible, the problem is I only have one lifetime.

 

[ramass]

In fact, I try to know if there is a description of the cells in an Excel worksheet. This name is define via the Item in the 'Name' Item of the 'Edit' Menu

 

[JustinEzequiel]

using Excel 97

Option Explicit

Sub test()
  Dim oWB As Workbook
  Dim sName As String
  
  Set oWB = ActiveWorkbook ' will need to change this if to be used from VB6 instead of Excel VBA
  
  sName = "justin"
  If NameExists(sName, oWB) Then
    MsgBox oWB.Names(sName).RefersToR1C1
  Else
    MsgBox "'" & sName & "' is not a valid name"
  End If
  sName = "me"
  If NameExists(sName, oWB) Then
    MsgBox oWB.Names(sName).RefersToR1C1
  Else
    MsgBox "'" & sName & "' is not a valid name"
  End If
End Sub

Private Function NameExists(sName As String, oWB As Workbook) As Boolean
  Dim n As Long
  Dim i As Long
    
  With oWB.Names
    n = .Count
    For i = 1 To n
      If sName = .Item(i).Name Then
        NameExists = True
        Exit For
      End If
    Next i
  End With
End Function