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
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
Arr(N) = “”
DefinedNameArray = Arr
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).
Both F5 and F7 contain formulas using 3D defined ranges.
Peter, thanks for the challenge!
You can download the example file here.