How to fix this #VALUE error?

[JenniferMurphy]

In the minisheet below, I get a #VALUE error for the call to my GetFormula UDF that I have used for years. In an attempt to identify the error, I added a breakpoint.

It never got called. I wondered if in my migration of files from my old Win 10 laptop to my new Win 11 laptop, I might have made another copy of my add-in module which is where these UDFs are stored. But then I added a call to my GetFormat UDF which is right next to the GetFormula UDF in the same add-in module. That breakpoint was executed.

Why would one UDF in the same add-in module get executed and another one not? Is there a way I can find out what module the code is calling?

I ran several searches for "getformula(" in all of the active modules and got no hits.

I also found it puzzling that the call to GetFormula insisted on mixed case (that matches the declaration in the add-in module), but the call to getformat insists on all lower case, which does not match the declaration.

FormulaText.xlsx
	C	D	E
5	3	#VALUE!	General
Cell Address

Cell Formulas
Range	Formula
C5	C5	=1+2
D5	D5	=GetFormula(C5)
E5	E5	=getformat(C5)

 

[RoryA]

in your personal macro workbook (Excel15.xlb)

FYI, that is not a personal macro workbook, it's a toolbar customisation file.

 

[RoryA]

Is there a way I can find out what module the code is calling?

If you click the Workbook Links button on the Data tab, are there workbooks listed?

 

[myall_blues]

FYI, that is not a personal macro workbook, it's a toolbar customisation file.

Yes correct. Been a long day!

 

[RoryA]

After a bit of checking, it appears that Name Manager is the cause of the problem. It must have a GetFormula function in it (albeit hidden) and presumably the declaration requires different parameters to the ones for your function, hence the #VALUE error.

 

[JenniferMurphy]

I have tested hiding the name in Name Manager and the function still works, so it not showing in name manager is not conclusive. However it sounds like you haven't experimented with using the Macro4 option as an alternative to a UDF so it would seem unlikely that there is a conflicting function in name manager.

The rest of the post was just in case it piqued your or any other visitors to the post's interest in having an alternative to a UDF for these functions. I do understand that you have it installed through and add-in and there are some benefits to using it that way.

I don't understand this post. What is "the Macro4 option"?

 

[JenniferMurphy]

After a bit of checking, it appears that Name Manager is the cause of the problem. It must have a GetFormula function in it (albeit hidden) and presumably the declaration requires different parameters to the ones for your function, hence the #VALUE error.

Brilliant! That's it. I disabled Name Manager and now my GetFormula works. Thank you!

I am curious as to how you figured that out.

When you previously mentioned Name Manager, I thought you were referring to the one built into Excel not the add-in.

And now I am even more angry at MS$T. Are they too lazy or too stupid to issue a warning message if they encounter duplicate function names? D*mn! D*mn! Double D*mn!

PS: I am also a little annoyed at Name Manager (the add-in) for giving it the same name.

 

[iliace]

This problem has cost me several hours of my time today and there is no solution. Things like this are happening more and more often. When they do, I literally hate M$FT. A good friend of mine recently switched to a Mac. She loves it. It just works. She doesn't write code, but I may have to look into that.

I've been regularly disappointed in Microsoft over the last number of years. I feel the same way about Linux as your friend does about Mac.

 

[RoryA]

I am curious as to how you figured that out.

The code had to be in an add-in, since the function call didn’t include a workbook name and the workbook it was used in was not macro-enabled. Since you’d already checked your add-in and NM was the only other one loaded, I assumed it had to be that. I downloaded and installed it and could replicate the error then, which confirmed it.

When you previously mentioned Name Manager, I thought you were referring to the one built into Excel not the add-in.

That wasn’t me.

If you write code, you would not enjoy Mac Excel. And it would still be Microsoft anyway.

 

[JenniferMurphy]

The code had to be in an add-in, since the function call didn’t include a workbook name and the workbook it was used in was not macro-enabled. Since you’d already checked your add-in and NM was the only other one loaded, I assumed it had to be that. I downloaded and installed it and could replicate the error then, which confirmed it.

Excellent detective work.

That wasn’t me.

Post #14? (Re mentioning Name Manager) I meant the comment as a misunderstanding on my part, not as a criticism.

If you write code, you would not enjoy Mac Excel. And it would still be Microsoft anyway.

Yeah, that's what I figured. I am intrigued by Google Sheets, however.

Anyway, thanks very much for your many invaluable contributions to this group. This board has some amazingly knowledgeable and dedicated contributors.