My "GetFormula" now a reserved word?

[JenniferMurphy]

I wrote a GetFormula UDF many years ago before Excel had the FormulaText function. Sometime in the past several months, it started getting a #VALUE error before it is even called. If I change the name, it works. Here's the code. Is "getformula" now a reserved word?

Function GetFormula(pCell As Range, Optional pAddrSw As Boolean = True) As String

GetFormula = pCell(1).FormulaArray      'Get the formula in the cell
If pCell(1).HasArray Then               'If it's an array formula,
  GetFormula = "{" & GetFormula & "}"     '.add the {}s
End If

If pAddrSw Then   'If switch is on, add the cell address and leading quote, if any
  GetFormula = pCell(1).Address(0, 0) & ": " & pCell(1).PrefixCharacter & GetFormula
End If

End Function

 

[6StringJazzer]

GetFormula is not a reserved word, or built-in function, or built-in method for any object.

Your code above works fine for me as is.

it started getting a #VALUE error before it is even called

I can't make sense of that statement. You can't get a #VALUE error unless the function is called. When this happens with a UDF I write a Sub to call the UDF so I can control the execution, and step through it.

 

[Alex Blakenburg]

I can only get the #VALUE, by providing the parameter "pAddrSw" as a Cell reference with a value in it that can not be interpreted as being Boolean eg a text value other than TRUE & FALSE

20220410 VBA UDF Failing JenniferMurphy.xlsm
	A	B	C	D	E	F
2	Value1	Value2	Formula	Parameter pAddrSw	GetFormula Text	FormulaTxt
3	10	100	110		C3: =A3+B3	=GetFormula(C3)
4	10	100	110	TRUE	C4: =A4+B4	=GetFormula(C4,D4)
5	10	100	110	FALSE	=A5+B5	=GetFormula(C5,D5)
6	10	100	110	1	C6: =A6+B6	=GetFormula(C6,D6)
7	10	100	110	0	=A7+B7	=GetFormula(C7,D7)
8	10	100	110	X	#VALUE!	=GetFormula(C8,D8)
Sheet1

Cell Formulas
Range	Formula
E3	E3	=GetFormula(C3)
F3:F8	F3	=FORMULATEXT(E3)
E4:E8	E4	=GetFormula(C4,D4)
C3:C8	C3	=A3+B3

 

[JenniferMurphy]

GetFormula is not a reserved word, or built-in function, or built-in method for any object.

Your code above works fine for me as is.

Curious

I can't make sense of that statement. You can't get a #VALUE error unless the function is called. When this happens with a UDF I write a Sub to call the UDF so I can control the execution, and step through it.

What I mean is that I set a breakpoint on the function definition (line 1). When I execute the cell that calls it, the breakpoint never happens, so I assume that the function is never called.

I made a copy of the function in the same add-in module and changed the name. That version works. Here's that code and a mini-sheet:

Function GetFormulaX(pCell As Range, Optional pAddrSw As Boolean = True) As String

GetFormulaX = pCell(1).FormulaArray      'Get the formula in the cell
If pCell(1).HasArray Then               'If it's an array formula,
  GetFormulaX = "{" & GetFormulaX & "}"     '.add the {}s
End If

If pAddrSw Then   'If switch is on, add the cell address and leading quote, if any
  GetFormulaX = pCell(1).Address(0, 0) & ": " & pCell(1).PrefixCharacter & GetFormulaX
End If

End Function

Book1
	B	C	D
3	9	#VALUE!	B3: =4+5
Sheet1

Cell Formulas
Range	Formula
B3	B3	=4+5
C3	C3	=getformula(B3)
D3	D3	=getformulaX(B3)

 

[JenniferMurphy]

I can only get the #VALUE, by providing the parameter "pAddrSw" as a Cell reference with a value in it that cannot be interpreted as being Boolean eg a text value other than TRUE & FALSE

As you can see from the mini-sheet above, I am not passing that parameter.

What's odd is that it was working until a few months ago. ??

 

[Alex Blakenburg]

Does it only happen in the one workbook ?
Do have a named range using that name ?
Do have any addins loaded, can you try disabling them ?

 

[6StringJazzer]

Here is what I get in the exact same setup.

$scratch.xlsm
	B	C	D
3	9	B3: =4+5	B3: =4+5
Sheet5

Cell Formulas
Range	Formula
B3	B3	=4+5
C3	C3	=GetFormula(B3)
D3	D3	=getformulaX(B3)

 

[JenniferMurphy]

Does it only happen in the one workbook ?
Do have a named range using that name ?
Do have any addins loaded, can you try disabling them ?

I tried it in several workbooks -- same result.

The mini-sheet I posted above was in a brand new workbook with nothing but that example, so no named ranges.

The only add-ins are Name Manager, XL2BB, and my add-in.

I remember trying Name Manager a long time ago, but I had some problems. I thought I unloaded it. I unchecked that box and the problem was solved. I tried to open Name Manager.xlam, but it's locked. I then tried reinstalling it. My GetFormula still works. But since I am not using it, I unchecked it.

It's still puzzling why is started failing just recently. I don't recall checking that box. Perhaps by accident.

In any case, problem solved. thanks.

 

[Alex Blakenburg]

Thank you for documenting your findings. Glad it is working now.