Referencing a Range from within an Excel Add-in with a UDF

[colin42]

I have created an excel add-in that contains a series of User Defined functions.

The add-in consists of several worksheets that contain tables and single values, each of these has been referenced by a unique name i.e. "Table1-2" or "Value1-4".

My UDF perform lookup's on these tables. All this wrks great while the workbook is saved in .xlsm format but as soon as i save to .xlam the functions stop working.

How can i refer to ranges that are part of the excel add-in file? I've tried refering directly to the range within the file i.e.

Val1-4 = Range(Workbooks("My Add-in.xlam").Names("Value1-4")).Value

but all i get is
"Run-time error '1004': Method "Range" of object '_Global' Failed

I've also tried

set Tab1-2= range("table1-2") as range

but this doesn't work either.

Please

 

[GlennUK]

Wouldn't it be more like:

myval = ThisWorkbook.Sheets("whateversheet").Range("Value1_4").Value

... except your names don't look right .... I didn't think that embedded minus signs were valid in defined names or variable names.

 

[colin42]

Glenn - Thanks that worked..

so it appears that names that are global in an excel file become local when converted to an add-in.

You're correct about the names - those aren't the names I'm using i just typed them as an example

 

[GlennUK]

I'm glad that worked!