Find Parameters in a User Defined Function

ptownbro985

New Member
Joined
Apr 3, 2012
Messages
17
This is a tricky one. Let's say I created a user defined function named "MyFunction" with three parameters "param1", "param2", and "param3". What I want to do is find the values passed to each parameter BUT... from another procedure!

Function:
Code:
Function MyFunction(param1, param2, param3)
   MyFunction = param1 & param2 & param3
End Function

Now, let's say I use the function in cell A1 as (used a weird result to illustrate problem):
Code:
=MyFunction(B1, C1, D1)

I started writing my other procedure as:
Code:
Sub MyProcedure()
   Dim MyBeg, MyEnd
   MyBeg = Instr(1, Range("A1").Formula, "MyFuction") + Len(Range("A1").Formula)
   MyEnd = Instr(MyBeg, Range("A1").Formula, ")")
   '... stopped when I realized this won't work (see below)
End Sub

The problem is that each paramater could be referring another functions making it difficult to find the last ")". As in:
Code:
=MyFunction(B1, Upper(C1 & lower(D1)), sum(E1, F1, G1))


Extra info for those interested:
What's the real reason why I want to do this? Well... long story, but in brief, what I'm ultimately trying to do is let the user overwrite "MyFunction" with a new value. Then using SheetSelectionChange and SheetChanged events at the application level, I want to save the new value to a back-end database using the parameters passed in the function they overwrote to determine which record to update. Go it all working (not as complicated as I may have made it sound), but just can't figure out how to get the parameters passed.
 
Last edited:

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,112
Office Version
  1. 2016
Platform
  1. Windows
If the parameters are cells and not values you could try using the Range.Precedents Property to retrieve the parameters
 

ptownbro985

New Member
Joined
Apr 3, 2012
Messages
17
Thanks for your response and suggestion. Interesting, but that won't work either. This wouldn't work for my second example for instance (it's nonsensical, but demonstrates the diffculty). I've altered it a bit to demonstrate what I mean.

Code:
=MyFunction("text", Upper(C1 & lower(D1)), sum(E1, F1, G1))

If the user entered the above, I want it to return:

param1 should return "text"
param2 should return the result of Upper(C1 & Lower(D1))
param3 should return the result of Sum(E1, F1, G1)

If I follow you, using Range.Precendents won't work here. I can get it to return the address of C1, D1, E1, F1, G1. But, it won't tell the result of "Upper(C1 & Lower(D1))" and "Sum(F1, G1)" which is what I need.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,679
Office Version
  1. 2010
Platform
  1. Windows
Something like this maybe...
Code:
Sub MyProcedure()
  Dim Frmla As String, Params() As String
  Frmla = Range("A1").Formula
  If Left(Frmla, 12) = "=MyFunction(" Then
    Frmla = Mid(Left(Frmla, Len(Frmla) - 1), InStr(Frmla, "(") + 1)
    Params = Split(Frmla, ",")
    '
    '  Params is a zero-based array constaining the parameters
    '  passed into MyFunctionso do whatever you want with them
    '
    '  Note: You may want to apply VB's Trim function to each
    '        array element before using it since the space you
    '        included after each comma in the function call in
    '        cell A1 will have been retained after the Split.
    '
  End If
End Sub
 

ptownbro985

New Member
Joined
Apr 3, 2012
Messages
17

ADVERTISEMENT

Very close! =) But still not quite there. Thanks by the way!!

If you apply it to my made up example and add the code below to what you provided, it returns:

0 - "text"
1 - UPPER(C1&LOWER(D1))
2 - SUM(E1
3 - F1
4 - G1)

It should return:

0 - "text"
1 - UPPER(C1&LOWER(D1))
2 - SUM(E1, F1, G1)

Really though... I want it to return the return value of the Upper() and Sum() functions, but if I can get it at least that close I can take it from there.



Code added:
Code:
For i = 0 To UBound(Params)
   Debug.Print i & " - " & Params(i)
Next
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,881
Hi

Nice problem. Not as easy as it seems. Just splitting at the commas is not enough, ex.:

=MyFunction("text1,text2,text3", Upper(C1 & lower(D1)), sum(E1, '2015,Jan'!A1))

Unfortunately I'm leaving.
I'll sleep on it and I'll check this thread tomorrow.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,679
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Very close! =) But still not quite there. Thanks by the way!!

If you apply it to my made up example and add the code below to what you provided, it returns:

0 - "text"
1 - UPPER(C1&LOWER(D1))
2 - SUM(E1
3 - F1
4 - G1)

It should return:

0 - "text"
1 - UPPER(C1&LOWER(D1))
2 - SUM(E1, F1, G1)
Okay, I see the problem now. Give this macro a try...
Code:
Sub MyProcedure()
  Dim X As Long, Frmla As String
  Dim Params() As String, QuoteDelimited() As String
  Frmla = Range("A1").Formula
  If Left(Frmla, 12) = "=MyFunction(" Then
    Frmla = Mid(Left(Frmla, Len(Frmla) - 1), InStr(Frmla, "(") + 1)
    QuoteDelimited = Split(Replace(Replace(Frmla, "(", Chr$(2)), "(", "|"), Chr$(2))
    For X = 0 To UBound(QuoteDelimited) Step 2
      QuoteDelimited(X) = Replace$(QuoteDelimited(X), ",", Chr$(1), , , Compare)
    Next
    TextToSplit = Join(QuoteDelimited, Chr$(2))
    Params = Split(TextToSplit, Chr$(1))
    For X = 0 To UBound(Params)
      Params(X) = Replace(Trim(Params(X)), Chr$(2), "(")
    Next
    '
    '  Params is a zero-based array constaining the parameters
    '  passed into MyFunctionso do whatever you want with them
    '  Here, I will simply print them out to the Immediate Window
    '
    For X = 0 To UBound(Params)
        Debug.Print Trim(Params(X))
    Next
  End If
End Sub
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,904
This will parse the sub-formulas that make up a formulas paramaters.


Code:
Sub Test()
    MsgBox Join(Arguments(Range("A1").Formula), vbCr)
End Sub

Function Arguments(formulaString As String) As Variant
    Dim i As Long
    Dim startArg As Long, stopArg As Long
    Dim isQuoted As Boolean, parenCount As Long
    Dim Result() As String
    Dim outputIndex As Long
    
    ReDim Result(1 To Len(formulaString))
    startArg = 1 + InStr(1, formulaString, "(")
    For i = 1 + InStr(1, formulaString, "(") To Len(formulaString) - 1
        If Mid(formulaString, i, 1) = Chr(32) Then
            isQuoted = Not isQuoted
        End If
        If Not isQuoted Then
            If Mid(formulaString, i, 1) = "(" Then parenCount = parenCount + 1
            If Mid(formulaString, i, 1) = ")" Then parenCount = parenCount - 1
            If Mid(formulaString, i, 1) = "," Then
                If parenCount = 0 Then
                    GoSub RecordArgument
                End If
            End If
        End If
    Next i
    'Exit Function
    GoSub RecordArgument
    ReDim Preserve Result(1 To outputIndex)
    Arguments = Result
    Exit Function
RecordArgument:
    outputIndex = outputIndex + 1
    Result(outputIndex) = Mid(formulaString, startArg, i - startArg)
    startArg = i + 1
    Return
End Function
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,679
Office Version
  1. 2010
Platform
  1. Windows
This will parse the sub-formulas that make up a formulas paramaters.
When I run you code with this formula in cell A...

=MyFunction(B1, UPPER(C1 & LOWER(D1)), SUM(E1, F1, G1))

the printout in the MsgBox looks like this...

Code:
B1
 UPPER(C1 & LOWER(D1)), SUM(E1, F1, G1)

The code I posted in Message #7 prints it out like this (which is what I think the OP wants)...

B1
UPPER(C1 & LOWER(D1))
SUM(E1, F1, G1)

If it helps any, here is my code with the ending modified to print out to a MessageBox instead of the Immediate Window...
Code:
Sub MyProcedure()
  Dim X As Long, Frmla As String
  Dim Params() As String, QuoteDelimited() As String
  Frmla = Range("A1").Formula
  If Left(Frmla, 12) = "=MyFunction(" Then
    Frmla = Mid(Left(Frmla, Len(Frmla) - 1), InStr(Frmla, "(") + 1)
    QuoteDelimited = Split(Replace(Replace(Frmla, "(", Chr$(2)), "(", "|"), Chr$(2))
    For X = 0 To UBound(QuoteDelimited) Step 2
      QuoteDelimited(X) = Replace$(QuoteDelimited(X), ",", Chr$(1), , , Compare)
    Next
    TextToSplit = Join(QuoteDelimited, Chr$(2))
    Params = Split(TextToSplit, Chr$(1))
    For X = 0 To UBound(Params)
      Params(X) = Replace(Trim(Params(X)), Chr$(2), "(")
    Next
    '
    '  Params is a zero-based array constaining the parameters
    '  passed into MyFunctionso do whatever you want with them
    '
    MsgBox Join(Params, vbLf)
  End If
End Sub
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,316
Members
414,053
Latest member
Dual Showman

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
Top