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

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 wrote:

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

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

"Dave Peterson" wrote:

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 wrote:

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