UDF Looks for Named Range Not Function

[Brandt]

Strange Problem Here...

I had a cell that created a job number using some very long formulas to
check the folder path where the spreadsheet was stored and parse out a job
number. I had named the cell "JobNum". In other cells where I wanted the
job number to show I would enter "=JobNum". This worked fine but the cell
method was very cumbersome and so I changed it to use a UDF in VBA. The user
defined function is "Public Function JobNum()". For the cell to call the
function, I first have to delete the named range "JobNum" via
Insert|Name|Define menu, then I change the formula in the cell from "=JobNum"
to "=JobNum()". This has worked fine for many spreadsheets that I have
updated to use the VBA method instead of the cell method. However, today I
opened up a spreadsheet that I had not yet updated, deleted the name
(JobNum), imported the module that has the "JobNum" function (like I have
done many times) and changed the fromula to "=JobNum()". This time however I
get a “#Name?†error in the cell that calls the "JobNum()" function. I have
triple checked that I have deleted the cell name correctly. There are no
other worksheets in the file (at least that I can see from the tabs or from
VBA project window). Furthermore, when I "evaluate" formula on a "=Today()"
function that is in the spreadsheet, it initially shows the entire function
underlined including the "()" before it evaluates it. However, when I
evaluate the "=JobNum()" function only the "JobNum" part is underlined (and
not the parenthesis) which leads me to believe that the spreadsheet still
thinks "JobNum" is a named range. I have put a stop in my VBA code and
verified that the UDF is never even called. (If I change both the cell
formula and function name to “JobNum1()†it works fine, however I am
interested in solving this problem and also in keeping this method consistent
with the many other spreadsheets that use this UDF.)

I've tried everything I can think of. Does anyone have any ideas?

Thanks in Advance

 

[Dave Peterson]

First, if you're doing this stuff, stop using the same string for the name and
UDF. You'll make life lots easier for yourself.

Second, I'd try adding the name back:
Insert|Name|Define
MyFunc
refers to $a$1
(of the activesheet)

Then delete that newly created name via Insert|Name|define.

Then try entering the UDF in a test cell:
=jobnum()

If that works, you'll want to change all the "broken" calls to the UDF:
Group all the worksheet first (to get all the formulas in all the worksheets)
Select all the cells
edit|replace
what: = (equal sign)
with: =
replace all
Then ungroup the worksheets and see if it worked.

 

[Brandt]

Thanks Dave,

That worked. I had to comment out the UDF before it would let me re-name a
cell "JobNum" but after that it worked great. Thanks for the idea. I know
it would be better to not use the same string for UDF and Name, but there
were some reasons that it was necessary. I guess the order that this stuff
is done has some implications.

Thanks again