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)

 

[iliace]

I haven't succumbed to Windows 11 yet, but one possible way to troubleshoot: in an empty cell, click the F(x) button to bring up the function selection dialog box, and in the Category dropdown select User Defined. That should tell you all UDFs that you have available, for example if there is a version in PERSONAL.XLSB or something.

Also, if you're getting past the function call, try debugging it by changing it to

GetFormula = "dummy value"

to see if that part will go through.

Another thing to try maybe open Excel in safemode (no addins, customizations, etc) then open your module and try on a blank file.

 

[JenniferMurphy]

I haven't succumbed to Windows 11 yet, but one possible way to troubleshoot: in an empty cell, click the F(x) button to bring up the function selection dialog box, and in the Category dropdown select User Defined. That should tell you all UDFs that you have available, for example if there is a version in PERSONAL.XLSB or something.

Here's what that gets. What does that mean?

Also, if you're getting past the function call, try debugging it by changing it to

GetFormula = "dummy value"

to see if that part will go through.

I changed it to this. Same result.

Another thing to try maybe open Excel in safemode (no addins, customizations, etc) then open your module and try on a blank file.

I'll do that next...

 

[JenniferMurphy]

I closed all Excel workbooks, then opened Excel in safe mode. I did that by clicking on the Excel icon while holding down the Ctrl key and then clicking "Yes" when asked if I wanted to open it in safe mode. I then typed "=getformula(C4)" in an empty cell. I was not prompted with the UDF name as I had been and I got a #NAME error. I cannot attach a minisheet because xl2bb is also not active. This is curious, because when I open the Add-ins dialog, it shows both as checked.

Now what?

 

[JenniferMurphy]

I changed the name of the UDF to GetFormulax and it works. And the breakpoint worked, so it was executing that UDF. I changed it back and it got the #VALUE error.

Next, I opened a new workbook, added a code module, and copied the UDF intact to that code module. It worked.

 

[JenniferMurphy]

I paused Carbonite (which sometimes grabs ahold of files and causes problems), closed everything, rebooted, and tried again. Same problem.

I give up. I renamed my GetFormula UDF to MyFormulaText. It works. If anyone has any other ideas, I can try them.

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.

 

[Alex Blakenburg]

Just grasping at straws here. You don't have anything in Name Manager do you ?

Book1
	A	B	C	D
1
2	Input	Formula	=GetFormula(B3)	=GetFormat(B3)
3	50	60	=10+A3	General
Sheet1

Cell Formulas
Range	Formula
C2:D2	C2	=FORMULATEXT(C3)
B3	B3	=10+A3
C3	C3	=GetFormula(B3)
D3	D3	=GetFormat(B3)

Lambda Functions
Name	Formula
GetFormat	=LAMBDA(rcell,GET.CELL(7,rcell))
GetFormula	=LAMBDA(rcell,GET.CELL(6,rcell))

 

[JenniferMurphy]

Just grasping at straws here. You don't have anything in Name Manager do you ?

If I open Name Manager in that workbook, I see:

I don't know what I should do with the rest of your post:

Book1
	A	B	C	D
1
2	Input	Formula	=GetFormula(B3)	=GetFormat(B3)
3	50	60	=10+A3	General
Sheet1

Cell Formulas
Range	Formula
C2:D2	C2	=FORMULATEXT(C3)
B3	B3	=10+A3
C3	C3	=GetFormula(B3)
D3	D3	=GetFormat(B3)

Lambda Functions
Name	Formula
GetFormat	=LAMBDA(rcell,GET.CELL(7,rcell))
GetFormula	=LAMBDA(rcell,GET.CELL(6,rcell))

 

[Alex Blakenburg]

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.

 

[myall_blues]

I changed the name of the UDF to GetFormulax and it works. And the breakpoint worked, so it was executing that UDF. I changed it back and it got the #VALUE error.

Next, I opened a new workbook, added a code module, and copied the UDF intact to that code module. It worked.

I tried both of your UDFs and they both worked (365).

As a longshot, is it possible that you have a version of GetFormula() saved in your personal macro workbook (Excel15.xlb)?

Book1
	B	C	D	E	F
4
5		3	C5: =1+2	General
6
Sheet2

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