There was a comment on LinkedIn about my post about using CF to highlight 3D formulas

“Since I never use a direct reference (or, come to that, enter a formula without naming the range to which it applies) any 3D reference I might use would pass under the radar. Unless, of course, you have an array UDF which will parse the formula to yield a set of references; in which case can I put in an order?”

Initially, I replied that it was not possible. But, the challenge was irresistable. I started working on the problem and, after a number of dead-ends, I was able to come up with a solution. It required a VBA function to return an array of defined names.

Function DefinedNameArray() As Variant

Application.Volatile

Dim Arr As Variant

nCount = ActiveWorkbook.Names.Count

ReDim Arr(1 To nCount)

For N = 1 To nCount

cPos = InStr(1, ActiveWorkbook.Names(N).RefersTo, “:”)

ePos = InStr(1, ActiveWorkbook.Names(N).RefersTo, “!”)

If cPos < ePos Then

Arr(N) = ActiveWorkbook.Names(N).Name

Else

Arr(N) = “”

End If

Next

DefinedNameArray = Arr

End Function

What the VBA function does is return an array of defined names, but only places the items meeting the correct criteria for a 3D formula in the final array (which is the same concept using in the initial article).

In this case, the InStr function was used to locate the positions of the first colon and exclamation point in the RefersTo string and the values are compared. If cPos<ePos, then the name is added to the array and a null string added otherwise. This array is used in the following formula to find if a 3D defined name is part of the string returned by the FORMULATEXT function. It was defined for use as a CF formatting formula, as shown below (F5 was the active cell when defined).

Is3DDefinedName=MATCH(TRUE,IFERROR(FIND(IFERROR(DefinedNameArray(),””),FORMULATEXT(F5))>1,FALSE),0)

Both F5 and F7 contain formulas using 3D defined ranges.

Peter, thanks for the challenge!

You can download the example file here.