[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2007-10-21
Medium Priority
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

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

Accepted Solution

GrahamSkan earned 1200 total points
ID: 20118497
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

by:Jeroen Rosink
Jeroen Rosink earned 600 total points
ID: 20118527
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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

LVL 33

Expert Comment

by:Jeroen Rosink
ID: 20118531
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

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

Assisted Solution

harfang earned 100 total points
ID: 20121457
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

EDDYKT earned 100 total points
ID: 20122169
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

ID: 20122821
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.

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

834 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