a defined name not working in Excel 2007

[Guest]

I have a third party workbook containing VBA scripts that works in Excel 2003
but not in Excel 2007. The VBA script contains this line :

directoryName = Range("directory").Value

where directory is a name that references a cell. Excel 2007 gives me "Can't
find project or library" compile error.

How do I fix this ?
Thanks.

 

[Arvi Laanemets]

Hi

I myself use the syntax (Excel2000) like

directoryName = [directory]

Maybe this will work


Arvi Laanemets

 

[Jim Rech]

Look at Tools, References in the VBE with this project active. Likely one
at least is marked 'missing'.

 

[Guest]

A couple of conditions which I am aware of that can cause an error with named
ranges.

Condition 1:
The named range was originally created on a worksheet in the interactive
mode and the macro has been copied into a new workbook and hence the named
range is not in the new workbook. To fix select the cell in the required
worksheet and then select Formulas ribbon and in the Defined Names block
select Name Manager to confirm it does not exist. If it does not exist then
select New and create it for the range it should represent. If it does exist,
then note the range it represents and delete it and recreate it just in case
you have a glitch in xl.

Condition 2
The named range is in another workbook and the other workbook is not the
active workbook.

Regards,

OssieMac