Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Module Precedence?
Thanks Dave, worked perfectly - now can someone explain why? Regards scott |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I unlock a module in Excel? | Excel Discussion (Misc queries) | |||
regular code module | Excel Discussion (Misc queries) | |||
code in module A to not execute a Worksheet_SelectionChange sub of another module | Excel Discussion (Misc queries) | |||
error "module not found" | Excel Discussion (Misc queries) | |||
Difference in the listing of the New module addition in 2002 version | Excel Discussion (Misc queries) |