Parsing the Parameters of a Function

Question

I am trying to create a UDF within VBA which go through some function syntax and treat it as Text.

The function will look like :

FunctionA( Param1 , Param2 , Param3 , Param 4 )

I am trying to develop a UDF which will pull out the value of the Param based on the position I input into my UDF function.

GetN( FunctionA , 3 ) = "Param3"

GetN FunctionA , 1 ) = "Param1"

Here's my function so far but it's off....

It's behaving like :

GetN( FunctionA , 0 ) = Param2

Here's my function:

Function GetN(sInputString As String, n As Integer) As String
     Dim sFindWhat As String
     Dim j, FindA, FindB As Integer
     Application.Volatile
     sFindWhat = ","

     FindA = 0
     For j = 0 To n
         FindA = InStr(FindA + 1, sInputString, sFindWhat)
         FindB = InStr(FindA + 1, sInputString, sFindWhat)
         If FindB = 0 Then FindB = InStr(FindA + 1, sInputString, ")")
         If FindA = 0 Then Exit For
     Next
     GetN = Trim(Mid(sInputString, FindA + 1, FindB - FindA - 1))

 End Function

Thank you for help

By the way, say there is no "," found in your string, then last commandGetN = Trim(Mid(sInputString, FindA + 1, FindB - FindA - 1)) will be GetN = Trim(Mid(sInputString, 1, - 1)) — Makketronix, Jul 04 '16 at 14:50

John Coleman · Accepted Answer · 2016-07-04T15:39:42.140

3

Split should work, though to correctly handle the case of nested functions, a preliminary hack is to first replace commas at the top level by a safe delimiter (e.g. [[,]]) and then splitting on that delimiter:

Function GetParameterN(func As String, n As Long) As String
    Dim args As Variant
    Dim safeArgs As String
    Dim c As String
    Dim i As Long, pdepth As Long

    func = Trim(func)
    i = InStr(func, "(")
    args = Mid(func, i + 1)
    args = Mid(args, 1, Len(args) - 1)

    For i = 1 To Len(args)
        c = Mid(args, i, 1)
        If c = "(" Then
            pdepth = pdepth + 1
        ElseIf c = ")" Then
            pdepth = pdepth - 1
        ElseIf c = "," And pdepth = 0 Then
            c = "[[,]]"
        End If
        safeArgs = safeArgs & c
    Next i
    args = Split(safeArgs, "[[,]]")
    GetParameterN = Trim(args(n - 1))
End Function

For example,

Sub test()
    Dim i As Long
    For i = 1 To 3
        Debug.Print GetParameterN("f(x,g(x,y,z),z)", i)
    Next i
End Sub

Produces:

x
g(x,y,z)
z

I see no good reason to make this function volatile.

edited Jul 04 '16 at 15:39

answered Jul 04 '16 at 14:56

John Coleman

51,337
7
54
119

This works perfect, I wasn't sure if there was one more change to make for it. There could be functions within functions like what happens when FunctionA( Param1 , Param2 , SubFunctionB( SubParam1 , SubParam2 ) )..... is there a was to use GetParameterN( FunctionA , 3 ) = SubFunctionB( SubParam1 , SubParam2 ).... – The_BMan Jul 04 '16 at 15:03
@The_BMan Nested functions *do* present a problem. The above code would need to be tweaked. I think I actually have a solution which I used somewhere or other, though I would have to track it down. – John Coleman Jul 04 '16 at 15:09
Thanks John, I was trying a couple versions with accomplishing this. I started to count the number "(" which would imply a function opened and use it as a running count such that a ")" would decrease it by 1 and the parsing would only work when value is set to 1 (implying only a single "(" ). but because the subfunction would have commas within it, I didn't know how to jump forward to the functions closing parenthesis – The_BMan Jul 04 '16 at 15:13
@The_BMan Yes -- parentheses depth is the way to go. See if my edit works. – John Coleman Jul 04 '16 at 15:39
Hi John thanks for all the help. One aspect of it is just kind of odd, I can try to figure it out but I thought I'd mention it. When grabbing the last parameter from "f()"... it will return the value "z)". is there a way to trim that last closing ")" behind the last parameter. Thanks for the help. – The_BMan Jul 04 '16 at 16:41
@The_BMan I can't reproduce the `z)` -- my code starts by trimming the input string and then the line `args = Mid(args, 1, Len(args) - 1)` removes the final `)`. What input are you giving it? – John Coleman Jul 04 '16 at 17:09
False alarm, once you pointed out that section of code i got it working, the functions ended with a ";" so I have to cut it back two spots. it works great. thanks. – The_BMan Jul 05 '16 at 13:11

Parsing the Parameters of a Function

1 Answers1