Create & Use a UDF Without a Workbook Reference

**xlnitwit** · 02-15-2017, 03:22 PM

Originally Posted by sfrehling

4) I closed Excel and opened one of the workbooks that uses MyUDF. Excel asked if I wanted to Update the links to other sources. I clicked Update. It said I had one or more links that couldn't be updated. I clicked Continue. I looked at one of the references to MyUDF. It was fully qualified with the name of the original xlam file. I edited the formula by deleting the file qualification. It still showed a #Name? error. I tried to fully qualify the UDF with the path to my xlam file. Excel accepted the new formula, dropped the path info and left only the name of the xlam file

Hi,

First, I suggest that you ensure the UDF is not stored in a module with the same name (the name of the workbook is not relevant).

If that is already the case, then I suggest at this stage you replace all the calls to your UDF with some static text. In other words replace
='c:\the path to your add-in\add-in name.xlam'!function_Name(...)
with for example
||function_Name(...)
Doing this with the Find/Replace dialog shold be simple. Then save and close the file.

Now reopen the file and replace the double pipe characters with an = symbol. Hopefully that will rectify your problem.

**sfrehling** · 02-15-2017, 05:18 PM

Again, thanks for your quick and thorough help. My module name is different than my function name, so no problem there.

I appreciate your suggestion for getting past the qualified function references. With more than 50 workbooks containing references, that's a bit of a pain, but then this whole thing has been a pain. Since last looking at your response, I have discovered that I can open each workbook, click Edit Links when prompted, select the UDF workbook, click Change Source, browse to and select my xlam file, click Update Values, and save the workbook. That too eliminates the qualified function references. Fingers crossed this problem doesn't come back again.

There seems to be no doubt that Excel or some other part of Windows is storing some value that keeps it from automatically picking up the most recent version each time. Who knows where that might be or how to overcome it (if that's even possible).

Create & Use a UDF Without a Workbook Reference

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Re: Create & Use a UDF Without a Workbook Reference

Re: Create & Use a UDF Without a Workbook Reference

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Locked formula changes reference when rows added to reference sheet in same workbook

Trying to create a cross reference sheet within a workbook

[SOLVED] Create new workbook, then REFERENCE it, Excel 2013

Create an external reference (link) to a cell range in another workbook WITH relative path

[SOLVED] copy active sheet, create new workbook, name new workbook based on cell reference

Create a Reference To a Different Closed Workbook With Multiple Variables

Create a workbook reference

[SOLVED] How to create a reference using VBA to a source list in different Workbook?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions