Function rather than macro to return cell references

[ChristineJ]

I use the macro below to look at the formula in a cell and return all cell references in the string that are "not allowed" in another cell

In the code below, allowed = "G5,G6,G7,G8,E15,E16,E17,E21,E24,E25,G18,G26," --- so a formula of =G8+L15-E25*A1 in one cell would return L15, A1 in another cell. (They are then wrapped in a SUM function.)

Is there a way to convert this from a macro to a function, such as =HelperCells(B15), where B15 contains the formula? Thanks!

Sub HelperCells(fCell As String, rCell As String)                 '
  Dim xRegEx As Object, xMatch As Object, d As Object
  Dim allowed As String, Sheetname As String, s As String
  
Dim addresses As String
Dim c As Integer
Dim r As Integer
Dim n As Integer
Dim addaray As Variant

allowed = "G5,G6,G7,G8,E15,E16,E17,E21,E24,E25,G18,G26,"

  Sheetname = ActiveSheet.Name & "!"
  Set d = CreateObject("Scripting.Dictionary")
  Set xRegEx = CreateObject("VBSCRIPT.REGEXP")
  With xRegEx
    .Pattern = "('?[a-zA-Z0-9\s\[\]\.]{1,99})?'?!?\$?[A-Z]{1,3}\$?[0-9]{1,7}(:\$?[A-Z]{1,3}\$?[0-9]{1,7})?"
    .Global = True
    For Each xMatch In .Execute(Replace(Replace(Replace(Replace(Range(fCell).Formula, ":", ","), "$", ""), " ", ""), Sheetname, ""))
      If InStr(1, "," & allowed & ",", "," & xMatch & ",") = 0 And Not d.exists(CStr(xMatch)) Then
          s = s & ", " & xMatch
          d(CStr(xMatch)) = 1
      End If
    Next xMatch
  End With
  
  addresses = Mid(s, 3)
  
  If addresses = "" Then
    addresses = addresses
Else
addresses = "=SUM(" & Mid(s, 2) & ")"
End If
Range(rCell).Value = addresses
addaray = Split(addresses, ",")
End Sub

 

[Micron]

The only difference between a function and a sub (they are both called procedures in Access vba, which is how I prefer to view things) is that a function can return a value whereas a sub cannot. It should be enough to just replace Sub with Function wherever Sub is used in your code. IMO, it is best to declare the data type that the function will return. So maybe like
Function HelperCells(fCell As String, rCell As String) As String

You would change As String to whatever type you want the function to return.

 

[ChristineJ]

The only difference between a function and a sub (they are both called procedures in Access vba, which is how I prefer to view things) is that a function can return a value whereas a sub cannot. It should be enough to just replace Sub with Function wherever Sub is used in your code. IMO, it is best to declare the data type that the function will return. So maybe like
Function HelperCells(fCell As String, rCell As String) As String

You would change As String to whatever type you want the function to return.

Yes, I've tried this, but the function returns "#VALUE!".

 

[Micron]

You should show what you tried? I can't see how you could expect the code you posted to work by passing just B15 when your code needs 2 parameters, not 1. I guess I missed that in my first reply. So #Value error has many causes, but I think you have to address the parameters issue first, then see if that link provides any clues when you examine you sheet data against those causes. Would not surprise me if your issue isn't listed there, but it's a start.

If that fails, then maybe explain what needs to be done at my/our end to replicate the problem. Perhaps use XL2BB to post data for that?
Also, could you please use vba code tags so that keywords are formatted correctly and indentation is maintained? It would make your posted code easier to follow.