Function rather than macro to return cell references

ChristineJ

Well-known Member
Joined
May 18, 2009
Messages
768
Office Version
  1. 365
Platform
  1. Windows
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!

Code:
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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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.
 
Upvote 0
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!".
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,015
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top