Identifying cell references used in a formula

[ChristineJ]

Cell F20 contains a formula such as =D7+D8+D9, =D7+D8+2100, or SUM(D7:D9).

I want to test FORMULATEXT(F20), the actual formula in F20, to determine (TRUE or FALSE) if all cell references used are within the range D6:D14.

For example, D7+D8+D9 would return TRUE. But D7+D8+K9 or D7+D8+D50 would return FALSE.

Any way to do this? Thanks!

 

[Peter_SSs]

The RX.Pattern in your code is fascinating

I take no credit for that.
It comes from here, but as I mentioned before is not entirely fool-proof.

Is there a way to allow Sheet1 (or CapCost, which the name of my sheet) to show as TRUE?

Yes, this modification should do it BUT using the sheet name of the sheet that the formula is on is a bad practice in my book. Not only is it not needed, it can lead to errors. See here for an example of what can go wrong.

Function CheckRefs(MainRange As Range, FormulaCell As Range) As Boolean
  Dim RX As Object, M As Object
  Dim rng As Range

  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "('?[a-zA-Z0-9\s\[\]\.]{1,99})?'?!?\$?[A-Z]{1,3}\$?[0-9]{1,7}(:\$?[A-Z]{1,3}\$?[0-9]{1,7})?"
  CheckRefs = True
  For Each M In RX.Execute(FormulaCell.Formula)
    On Error Resume Next
    Set rng = Range(M)
    On Error GoTo 0
    If Not rng Is Nothing Then
      If Split(rng.Address(External:=True), "!")(0) <> Split(MainRange.Address(External:=True), "!")(0) Then
        CheckRefs = False
        Exit For
      ElseIf Intersect(MainRange, rng) Is Nothing Then
        CheckRefs = False
        Exit For
      ElseIf rng.Address <> Intersect(MainRange, rng).Address Then
        CheckRefs = False
        Exit For
      End If
    End If
  Next M
End Function

Could I have it show TRUE for an additional range as well, such as C18:G33?

Probably, but I need clarification of just what you are trying to achieve.
Do you want True for =D8+C20 or false for that and only true if all references in a particular formula come from one of the ranges.

 

[ChristineJ]

Some context might help. I'm a university professor (accounting - very quantitative and a profession that requires Excel skills) and am trying to automate the process of evaluating formulas that students enter for problems that I preformat in Excel.

One requirement is that students do not use helper cells and only reference cells in that range of data that is given. That is what you have been helping me with. In this example, I provided the data needed to solve the problem in D6:D14. The answer is a single amount, and the formula for it is required in F20.

Is there a way to allow Sheet1 (or CapCost, which the name of my sheet) to show as TRUE?
I agree that the sheet name should be omitted in a formula if it is the active sheet. However, the goal here is just to see if only cells in D6:D14 were used. (I can independently test to see if the active sheet name is included and return a separate comment that it is not necessary, or even preferable - I know how to do that.)

Do you want True for =D8+C20 or false for that and only true if all references in a particular formula come from one of the ranges.
As long as the references come from either of the two ranges, or a mix of the two, True should be returned. =D8+C20 would be True.

The context here is that D6:D14 is the data I provide, and the solution requires entries in multiple cells in C18:G33. Students would be allowed to reference D6:D14 as well as any of their own previous entries in the answer range.

THANKS!

 

[Peter_SSs]

I note that the formula cell (F20) is actually part of the additional range they are allowed to use (C18:G33) but I don't think that should cause a problem - unless they create a circular reference.

In any case, give this one a try.

Function CheckRefs(FormulaCell As Range, MainRange As Range, Optional SecondRange As Range) As Boolean
  Dim RX As Object, M As Object
  Dim rng As Range, FullRange As Range

  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "('?[a-zA-Z0-9\s\[\]\.]{1,99})?'?!?\$?[A-Z]{1,3}\$?[0-9]{1,7}(:\$?[A-Z]{1,3}\$?[0-9]{1,7})?"
  CheckRefs = True
  Set FullRange = MainRange
  If Not SecondRange Is Nothing Then Set FullRange = Union(FullRange, SecondRange)
  For Each M In RX.Execute(FormulaCell.Formula)
    On Error Resume Next
    Set rng = Range(M)
    On Error GoTo 0
    If Not rng Is Nothing Then
      If Split(rng.Address(External:=True), "!")(0) <> Split(MainRange.Address(External:=True), "!")(0) Then
        CheckRefs = False
        Exit For
      ElseIf Intersect(FullRange, rng) Is Nothing Then
        CheckRefs = False
        Exit For
      ElseIf rng.Address <> Intersect(FullRange, rng).Address Then
        CheckRefs = False
        Exit For
      End If
    End If
  Next M
End Function

I have changed the order of the function's arguments and the secondary range is optional. So you can use it like this to check if only the main range is used

=CheckRefs(F20,D6:D14)

.. or like this to include the second range

=CheckRefs(F20,D6:D14,C18:G33)

 

[ChristineJ]

Thanks - I will test this later today and post again later today to let you know it goes. I really appreciate your clear explanations of how the code works.

Regarding your comment that the formula cell (F20) is actually part of the additional range they are allowed to use (C18:G33):

There are two different problem types - one with a single answer is F20 OR one with multiple answers in range C18:G33. They are mutually exclusive.

Also, the code we are working on now will only run if the actual numerical answer is correct. So a circular reference would not be an issue.

Thanks.

 

[ChristineJ]

The VBA code in #13 works perfectly for what I need - appreciate it!

You helped me out on March 21 as well with the Get_Nums macro. That is part of this project as well.

When this is published as an ancillary to a textbook I wrote, I will acknowledge you as a contributor! C

 

[Peter_SSs]

The VBA code in #13 works perfectly for what I need - appreciate it!

You're welcome. Glad we got there in the end.

 

[ChristineJ]

A couple of days ago you helped me with converting my GETNUMS function to a macro at Extract numbers from a string

Could you also convert this CHECKREFS function to a macro in a similar way? For any cell in column G that has a cell reference typed in (otherwise cells in column G are blank), the macro should look at cell referenced. If that cell contains a formula, it should run the CHECKREFS process and return either TRUE or FALSE in column U. The ranges that should result in TRUE are D4:D20 and E5:E22.

For example, if G51 has a value of C24, and cell C24 has a formula of =D5+D6, "TRUE" returned in U51.

Thanks! I hope my explanation is clear. C

 

[ChristineJ]

Please disregard - figured it out!