Module Precedence?
 

Hi,

I have a number of UDFs that I need to access from multiple spreadsheets so
I thought that I'd put them in an add-in rather than copying them to each
individual spreadsheet.
I started a new workbook, went to the VBE, added a module and copied the
code from an existing workbook. Save the workbook as an xla and then started
a new workbook, Tools/Add-ins and ticked the check box. In A1 I then typed in
one of my functions and it worked fine - so far so good.
I now go back to the workbook that I copied the code from and delete it from
the module. Check cells that refer to the code and I get a NAME error. If I
create a new workbook and type the name of the function it works fine, it
just won't work in my existing workbook. This workbook does have other
modules in it, could that be causing the problem finding my function? Or have
I stuffed something else up?

thanks

Module Precedence?
 

Saved from a previous post for the same kind of problem--the UDF was in a
workbook, then moved to an Addin.

After I moved the code from one workbook's project to the other and saved both
files (one as an addin), I did this in the "regular" workbook (.xls) (with the
..xla still open).

Insert|Name|define
myFunc
(refer to any cell)
Add

Then I deleted that name.
insert|name|define
myfunc
delete

Then I did edit|replace
what: = (equal sign)
with: = (equal sign)
replace all

Excel was smart enough to reevaluate this function from the xla.


========
myFunc will be the name of your UDF.

SB wrote:

Hi,

I have a number of UDFs that I need to access from multiple spreadsheets so
I thought that I'd put them in an add-in rather than copying them to each
individual spreadsheet.
I started a new workbook, went to the VBE, added a module and copied the
code from an existing workbook. Save the workbook as an xla and then started
a new workbook, Tools/Add-ins and ticked the check box. In A1 I then typed in
one of my functions and it worked fine - so far so good.
I now go back to the workbook that I copied the code from and delete it from
the module. Check cells that refer to the code and I get a NAME error. If I
create a new workbook and type the name of the function it works fine, it
just won't work in my existing workbook. This workbook does have other
modules in it, could that be causing the problem finding my function? Or have
I stuffed something else up?

thanks

--

Dave Peterson

Module Precedence?
 

Thanks Dave, worked perfectly - now can someone explain why?

Regards

scott

Module Precedence?
 

Purely unscientific...

I think that there is some overlap between where excel stores its names and
UDF/Subroutine names.

If you create a new workbook and then add a couple of routines to its project:

Option Explicit
Sub aaaaa()
MsgBox "hi"
End Sub
Function bbbbb()
MsgBox "Bye"
End Function

Then back to excel.

Edit|goto|type in
aaaaa
and watch were you end up.

Try the same with bbbbb.

Excel doesn't keep good enough track when you remove the function. But if you
create the name and then delete it, excel's memory is refreshed.

====
If you've ever used the same name for a procedure, module, or workbook Name,
you'll see lots of weird things happening.




SB wrote:

Thanks Dave, worked perfectly - now can someone explain why?

Regards

scott

--

Dave Peterson