How to know a nammed cell exist in Excel from Access in VBA

Posted on 2007-10-21
Last Modified: 2012-05-05
Hi experts,

this is quite urgent :
i need to insert data from my access VBA code into an excel sheet.
I am using nammed cells.
So i do this :
xlsapp.Sheets("My sheet").Select
xlsapp.Names("TheCellName").RefersToRange.Value = myValue

THis seems to work but in the case the cell nammed "TheCellName" does not exist I've got an error message.
I need to be able to ensure the nammed cell exist before trying to insert value in it.

How to do that easily because I have sometinh like 150 nammed cell to test each time ..????
because I need to handle several files that contains several different nammed cells...

Big thanks in advance !
Question by:Madeso
    LVL 29

    Expert Comment

    <$.02>Perhaps using named cells is a bad idea?</$.02>
    LVL 76

    Accepted Solution

    You could use a function like this:

    Function IsCellName(strName As String, wbk As Workbook) As Boolean
        Dim nm As Name
        Dim rng As Range
        For Each nm In wbk.Names
            If UCase$(nm.Name) = UCase$(strName) Then
                Set rng = nm.RefersToRange
                If rng.Cells.Count = 1 Then
                    IsCellName = True
                End If
            End If
        Next nm
    End Function

    LVL 33

    Assisted Solution

    Hello GrahamSkan,
    perhaps to add to your function when IsCellName  is found tp be TRUE to exit the function as it doesn't need to search further.


    add this line to the function after the TRUE element:
    Exit Function

    LVL 33

    Expert Comment

    Using the function of GrahamSkan,

    xlsapp.Sheets("My sheet").Select
    If IsCellName = TRUE Then
    xlsapp.Names("TheCellName").RefersToRange.Value = myValue
    xlsapp.Names.Add name:="TheCellName", RefersTo:=Range(myValue).RefersTo
    end if
    LVL 76

    Expert Comment

    Thanks Jeroen. The Exit function would be more elegant.
    LVL 58

    Assisted Solution

    What about error handling?

    On Error Goto Oops

        ' do stuff, including:
        xlapp.Evaluate("TheCellName") = myValue
        ' etc...
        Exit Sub/Function

        ' use Err.Number and Err.Description to take action

    You can use "Resume Next" to skip that name entirely, or "Resume" to retry after having fixed the problem, e.g. by creating the missing name.

    LVL 26

    Assisted Solution

    Instead of looping

    Public Function IsCellName(strName As String) As Boolean
    Dim x As Name

    On Error Resume Next
    Set x = xlsapp.Names("TheCellName")
    If (Err.Number = 0) Then IsCellName = True
    end function

    Author Comment

    Hi all,

    Big thanks it works perfectly !
    I used the the IsCellName function.

    Thanks u all for you rparticipation and discussion about this !
    hav ea nice day.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now