Returning several variables from function

[Leen]

Hi!

I'm using a separate procedure in which I enter 1 variable. This procedure should return 3 strings. I know how a function procedure can return 1 argument, but I can't find how to let it return several arguments. I tried via an array "parts()" but this doesn't work:

My code:

Function regexpparts(inputfield As String, ParamArray parts()) 
    Dim parts
    Dim regEx As Object
    Dim number
    Set regEx = CreateObject("vbscript.regexp")
    
    With regEx
        .Pattern = "(\b\w+?\b)"
        .Global = True 'find all matches
    End With
    
    Set matches = regEx.Execute(inputfield)
    number = matches.Count
    parts(1) = matches(0)
    parts(2) = matches(1)
    parts(3) = matces(2)
    
    Set matches = Nothing
    Set regEx = Nothing
End Function

However, when I want to assign these values to a public variable, for example "reftablename" in another sub procedure, it returns an error saying the function or sub procedure "parts()" is not defined.

reftablename = reggexpparts(parts(1))

Can anyone help how to return the 3 values from the procedure?

Thanks a lot on forehand!
Leen

 

[modest]

First, your function isn't returning any value. The correct syntax for function definition would be something like:
Function regexpparts(inputfield As String) As SomeVariableType
End Function

However, if you are trying to return multiple values that are needed, pass the parameters by reference rather than value.

Function <FunctionName>(ByRef <VariableName> As <VariableType>)
End Function


After looking at your code, what datatype is matches, Recordset or an array? You could just return matches, instead of three variables separately.

 

[RuralGuy]

Maybe this link will help a bit.

 

[Leen]

Hi,

I tried the method using Byref, although I get an error when I call the function on line "regexpparts(presence)": "argument not optional"

My code for the function:

Function regexpparts(inputfield As String, ByRef part1 As String, ByRef part2 As String, ByRef part3 As String)  

    Dim part1, part2, part3
    Dim regEx As Object
    Dim number
    Set regEx = CreateObject("vbscript.regexp")
    
    With regEx
        .Pattern = "(\b\w+?\b)"
        .Global = True 'find all matches
    End With
    
    Set matches = regEx.Execute(inputfield)
    number = matches.Count
    
    part1 = (matches(0)) '=name of the reference table (LU_ATTRIB or LANDMARKS_ATTRIB)= reftablename
    part2 = (matches(1)) '=reffield (reference field -CATNAM or LU_TYPE- on which the value of the field depends) = reffield
    part3 = (matches(2)) '=field (the field which has to be checked: A1_PRESENCE etc.) = field
    
    Set matches = Nothing
    Set regEx = Nothing
End Function

And when I call the function, I want to use the 3 values returned (part1, part2, part3):

regexpparts (presence)
 reftablename = part1
 reffield = part2
 field = part3

Actually, matches(0), matches(1) and matches (3) are all strings. Using regular expression it is possbible to find some combinations into an inputvalue. The output (matches) are all the "matches" itself. (the words found back)

Do you have any idea why I get the error " argument not optional"?
So for the function, the inputvariable = inputfield (is a string),
the outputvariables = part1/part2/part3 (are strings)

Thanks!!

(Using the link of "RuralGuy" it seems a more complicated way in defining a new datatype, that is why I prefer the Byref method)

 

[boblarson]

When you build a function, the parts inside the parentheses are INPUTS, NOT OUTPUTS

Function regexpparts(inputfield As String, ByRef part1 As String, ByRef part2 As String, ByRef part3 As String)

Is incorrect.

This would be the input side:

Function regexpparts(inputfield As String)

I'm not that familiar with returning multiple values for a function, but I think your original array idea is one way to go or to just set public variables which you can assign values and then reference.

A single return would be

Function regexpparts(inputfield As String) As String

but I'm not sure about how you do an array.

 

[RuralGuy]

Function regexpparts(inputfield As String, [COLOR="SeaGreen"]ByRef part1 As String, ByRef part2 As String, ByRef part3 As String[/COLOR])  

    [B][COLOR="Red"]Dim part1, part2, part3[/COLOR][/B]
    Dim regEx As Object
    Dim number
    Set regEx = CreateObject("vbscript.regexp")

You do NOT want to define these variables here! Their references are passed in the call so just go ahead and use them.

Dim part1 As String, part2 As String, part3 As String
'Pass the string references in the call!
regexpparts (presence, part2, part2, part3)
'The strings will be completed in the called function and can now be used!
 reftablename = part1
 reffield = part2
 field = part3

Let us know how it works.

 

[boblarson]

Function regexpparts(inputfield As String, [COLOR="SeaGreen"]ByRef part1 As String, ByRef part2 As String, ByRef part3 As String[/COLOR])

RG: From my reading of the problem he is trying to split the input field into those three parts so why would you pass part1, part2, and part 3 to the function that is trying to come up with them. My understanding is that anything within that first () is INPUTS and not OUTPUTS. Am I incorrect in that?

 

[Leen]

Hi!

I tried exactly what you wrote, however, I get the error in the code on line

regexpparts (presence, part2, part2, part3)

saying " byref argument type mismatch"

However, I tried something similar, which does works!

The code of my function:

Function regexpparts(inputfield As String, ByRef part1 As String, ByRef part2 As String, ByRef part3 As String)
    Dim regEx As Object
    Dim number
    Set regEx = CreateObject("vbscript.regexp")
    
    With regEx
        .Pattern = "(\b\w+?\b)"
        .Global = True 'find all matches
    End With
    
    Set matches = regEx.Execute(inputfield)
    number = matches.Count
    
    part1 = (matches(0)) '=name of the reference table (LU_ATTRIB or LANDMARKS_ATTRIB)= reftablename
    part2 = (matches(1)) '=reffield (reference field -CATNAM or LU_TYPE- on which the value of the field depends) = reffield
    part3 = (matches(2)) '=field (the field which has to be checked: A1_PRESENCE etc.) = field
    
    Set matches = Nothing
    Set regEx = Nothing
End Function

And the code when I call it: (reftablename/reffield and field are defined as public string variables):

 result = regexpparts(presence, reftablename, reffield, field)

Actually, the code works wel: the values are correct each time. One strange aspect: I do not know what "result" exactly is. In my code I just put
"Dim result" but when I add a watch it stays empty all the time, although the function runs wel!

 

[Leen]

RG: From my reading of the problem he is trying to split the input field into those three parts so why would you pass part1, part2, and part 3 to the function that is trying to come up with them. My understanding is that anything within that first () is INPUTS and not OUTPUTS. Am I incorrect in that?

This is correct. The "inputfield" is an input, and I want to use the values of the parameters called part1, part2 and part3 wich result from the function.

 

[RuralGuy]

RG: From my reading of the problem he is trying to split the input field into those three parts so why would you pass part1, part2, and part 3 to the function that is trying to come up with them. My understanding is that anything within that first () is INPUTS and not OUTPUTS. Am I incorrect in that?

You are correct Bob but in this case the "inputs" we're looking for are the references to the variables and not trhe values so when we reference them in the function we are actually placing the answers where the calling procedure can get to them.

 

[boblarson]

But, when putting them in the input parameter locations (which you don't need as they can be declared publicly so they are available) this is why he gets the parameter not optional and you have to supply a value for each when calling the function.

 

[RuralGuy]

But, when putting them in the input parameter locations (which you don't need as they can be declared publicly so they are available) this is why he gets the parameter not optional and you have to supply a value for each when calling the function.

True, but many of us dislike public variables and this provides a means of altering a calling procedure's variables without making them public or global.

 

[boblarson]

Then wouldn't you need to declare them as OPTIONAL so you didn't need to include an input for them?

 

[RuralGuy]

By default, arguments are passed ByRef and the called procedure needs the reference to the variable. If you do not include the argument then the called procedure can not reference the calling procedure's variables.

 

[RuralGuy]

Plus, only variant datatypes are allowed to be Optional which is not efficient.

 

[boblarson]

Plus, only variant datatypes are allowed to be Optional which is not efficient.

That's not true - I've never used variants but use strings and booleans, etc. as optional.

 

[boblarson]

I just ran another test to make sure and it works fine. Try this out:

Public Function stTest(str1 As String, Optional strTest2 As String, Optional blnTest As Boolean) As Boolean
    
    If str1 = "test" Then
        stTest = True
    Else
        stTest = False
    End If
    MsgBox str1 & vbCrLf & strTest2 & vbCrLf & blnTest & vbCrLf & stTest
End Function

 

[RuralGuy]

I don't know where I saw that Bob but I can not find it now. I stand corrected.

Edit: I just found this in VBA help:

Optional arguments can have default values and types other than Variant.

Boy do I stand corrected. It must have been one of my dreams.