9

I have this VBA function:

Public Function testPrec(target As Range) As String
    testPrec = target.Precedents.Address(External:=False)
End Function

In cell C11, I have this formula:

=C6+C8

If I call testPrec from the immediate window, it works just fine:

?testPrec([c11])
$C$6,$C$8

EDIT: It also works fine if called from a non-UDF macro Sub. The anomaly is the UDF case.

If I call it from the worksheet as a UDF:

=testPrec(C11)

I just get back "$C$11".

Does anyone know what's going on, or even better how to get the actual precedents from within a UDF call? (I'm using Excel 2007.)

jtolle
  • 7,023
  • 2
  • 28
  • 50
  • 1
    I did find this on Charles Williams's excellent site (http://www.decisionmodels.com/calcsecretsj.htm): "You cannot make a VBA UDF which directly...uses...PRECEDENTS". However, I'm trying to get the precedents of a `Range` *other* than the one calling the UDF - i.e. get the precedents of the UDF's *argument*. That doesn't terminate the UDF call; it just returns the argument, not its precedents. – jtolle Apr 04 '11 at 18:25
  • Playing around with this a little more, I notice that this happens for *any* range, not just the argument to a UDF. I.e. a hardcoded function that returns `[c11].precedents.address` also just returns "$C$11" when called from thw worksheet. I hate calling things like this bugs since it's not really the intended use of `Precedents`, but it's so annoying that it's not documented... – jtolle Apr 04 '11 at 21:17
  • +1 great question. Dealing with precedents, especially off sheet ones, is a rather arcane process. – brettdj Oct 29 '11 at 01:00
  • @brettdj: Thanks. Very minor question, but did your vote show up in the right place? See: http://meta.stackexchange.com/questions/110600/possible-instance-of-reported-upvote-going-to-wrong-question – jtolle Oct 29 '11 at 18:46
  • You were right. I upvoted the answer from Charles and had thought I had upvoted your question too, but on revisitng this that clearly hadn't happened as intended. Mow rectified. – brettdj Oct 29 '11 at 20:59
  • Thanks, @brettdj. I promise I wasn't grubbing for a vote. I just noticed a different upvote on a different question from the same time as your comment and wondered if there was a glitch in the reporting mechanism from my profile. Did you by any chance also upvote my other question or was it just a total coincidence? – jtolle Oct 29 '11 at 22:18

3 Answers3

3

It seems the constraint lies in that any call to .Precedents in a call stack that includes a UDF gets handled differntly. So, find a way to do the call outside the call stack triggered from the UDF: One thought is to use events. Here is a overly simplistic example to demonstrate

In a module define 

Public strPrecedent As String
Public rngPrecedent As Range

Public Function testPrec(target As Range) As String
    Set rngPrecedent = target
    testPrec = strPrecedent
End Function

In a sheet define

Private Sub Worksheet_Calculate()
    If Not Module1.rngPrecedent Is Nothing Then
        Module1.strPrecedent = Module1.rngPrecedent.Precedents.Address(External:=False)
    End If
End Sub

testPrec now returns the correct range address, albeit one recal late. The idea is to have the UDF build a list of addresses to get Precedents for, and an event to do the actual GetPrecedent work, returning the address strings to the list for pickup by the udf. You might be able to build a workable solution out of this, depending on your needs.

chris neilsen
  • 52,446
  • 10
  • 84
  • 123
  • Good approach for a single instance of the UDF but gets messy for multiple calls to the UDF from the same or different worksheets/workbooks – Charles Williams Apr 04 '11 at 20:56
  • In this case, what I want really is as simple as the address of the precedents of a cell, but returned *from a UDF call*. Anything more complicated won't be worth it for me. But you're right that something like this would work in general. – jtolle Apr 04 '11 at 21:09
  • @Charles the answer is a simplistic example to demonstarte a point. A complete solution would have to manage a list (array, collection or whatever) of cells referenced by the UDF. Sure its a bit complex, but it is a workable solutin to, lets call it an undocumented feature of excel. In the end its up to the user to decide if the effort is worth it for the task at hand – chris neilsen Apr 04 '11 at 22:41
  • Thanks again for the answer. I accepted Charles's because for my purposes the answer really is just "no". – jtolle Apr 08 '11 at 15:28
  • 1
    @chrisneilsen Testing this in Excel 2007 I find that even in an event as suggested, the Dependents and Precedents do not include cross sheet cells, ever :-( – Mark Hurd Jun 05 '12 at 03:11
  • Removed my -1 because this question did not refer to cross-sheet cells. – Mark Hurd Jun 05 '12 at 03:13
  • 1
    Although not asked in the question, the fact that this doesn't handle cross-sheets formulas is very valuable information. – tbone Jul 04 '12 at 19:05
  • @tbone seeing as you didn't @ ping me, I've only just noticed your comment. Look [here](http://stackoverflow.com/q/10897958/256431) for attempts to get cross-sheet 'dents. – Mark Hurd Sep 20 '13 at 10:12
2

The only workaround I can think of is to get target.formula and parse it - not very nice.

Charles Williams
  • 23,121
  • 5
  • 38
  • 38
0

I ran into a similar problem: I had to format cells based on whether they contain a formula or a constant value. HasFormula makes it possible to determine if a cell contains a formula, however, simple calculations, like =123+45 are also detected as formulas (correctly from a technical point of view but incorrectly from a financial modelling perspective). So I wanted to use Precedents in a UDF to see whether the given cell links to any other. As mentioned above the value of Precedents during the execution of an UDF is not valid but I needed to know only if there is any precedent, not which cells they are.

So I compared the Formula and FormulaR1C1 properties because they are different only in case the Formula contains a cell reference.

There is one exception: If the Formula contains Named Ranges, then Formula and FormulaR1C1 can be equal even though the cell refers to something. (This was not relevant in my case and did not want to iterate all Names and check if they are contained in the Formula outside quotation marks.

z32a7ul
  • 3,695
  • 3
  • 21
  • 45