• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 976
  • Last Modified:

Excel Addins

I have written some functions that retrieve data from an Access Database. These work OK.

Because I need to use these same functions in other Excel workbooks I created an XLA addin and opened XLA addin in my file. Everything still works OK, even though there are 2 functions with same name.

I then deleted the code from my workbook leaving only the code in the XLA for the workbook to use. Now it doesn't work. The workbook doesn't recognise the XLA.

I then created a new empty workbook and opening the addin the functions work OK.

Why would the addin work OK in one file and not in the other?

I can get around this by having duplicate code in many workbooks but that is too difficult to maintain.

Any help appreciated.

2 Solutions
glennwaldronAuthor Commented:
I have copied all the sheets from the current workbook to a new workbook and they now recognise the addin.

I would still like to know why if there is a reason so I'll leave the question open.
Did you compile/save the workbook after deleting the code ?

Problem with Excel is that the VBA accumulates junk in the editing process. There is even a first rate tool available for free: See CodeCleaner in http://www.bmsltd.co.uk/MVP/MVPPage.asp

If you want I can give some links which makes developing of add-ins easier.
I bet your function calls lost their links to the VBA code. If you don't have very many function calls, then select each function call that doesn't work and preface it with the fully qualified reference to the .xla

As an alternative (when you have lots of VBA function calls), you can start with a complete workbook (worksheets & underlying VBA code on module sheets). Then Edit...Move the worksheets to a new workbook. That will link all the VBA function calls to the old workbook. Save the worksheet-free workbook as a .xla and save the VBA-free new workbook as your working copy. Quit Excel. Restart Excel and make the .xla an add-in. Then open the working copy and use the Edit...Links command to send the VBA function calls to the .xla
No comment has been added lately (306 days), so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area for this question:

RECOMMENDATION: split points between cri http:#8047310 and byundt http:#8133866

Please leave any comments here within 7 days.

-- Please DO NOT accept this comment as an answer ! --


EE Cleanup Volunteer

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now