Using a function result as a function argument in VBA?

Question

New to VBA,

can you use a function result as an argument for another function? If not, what would be the best way to do it? The following code shoud illustrate the problem: I generate data with function f_1 and want to pass the data to function f_2.

Function f_1(Arg_11, Arg_12)
    For i = 1 To 100
        Cells(i, 1).Value = Arg_11 * Arg_12
    Next i
End Function

Function f_2(Arg_21, Arg_22)
    For j = 1 To 100
        Cells(j, 1).Value Arg_21 + Arg_22
    Next j
End Function

Sub test()
k = 20
m = 10

g = 1

Debug.Print (f_1(k, m))
Debug.Print (f_2(g, f_1))

End Sub

Since you are new to VBA, you might benefit by reading up on writing Functions. — BigBen
– BigBen, Commented May 20, 2021 at 13:28
Your question needs clarification. It isn't clear what you are actually trying to do with that code. What is f_2(g, f_1)) supposed to mean? Whatever it is that you are trying to do, it wouldn't be achieved by passing one function to another, though it might be achieved by passing the return value of one function to another. — John Coleman
– John Coleman, Commented May 20, 2021 at 13:29
There is zero problem passing an array to a function which expects an array (or a variant) as one of its parameters. Not much difference between passing an array and passing an integer. — John Coleman
– John Coleman, Commented May 20, 2021 at 13:35
"Let's assume f_1 returns an array of integers" - since it currently doesn't, that seems to be your actual problem. — BigBen
– BigBen, Commented May 20, 2021 at 13:36

Kostas K. · Accepted Answer · 2021-05-20 14:17:54Z

1

Yes, but only the return value of the function is passed as an argument to another method, not the function itself.

See the example below:

Function NumberOne() As Integer
    NumberOne = 1
End Function

Function NumberTwo() As Integer
    NumberTwo = 2
End Function

Function Total(ByVal one As Integer, ByVal two As Integer) As Integer
    Total = one + two
End Function

Sub T()
    Dim t As Integer
        t = Total(NumberOne(), NumberTwo())
        
    Debug.Print t
End Sub

The return type doesn't really matter, unless the function returns an object where you need to set its reference using the Set keyword.

Therefore, the below is also valid:

Sub T()
    PrintThem ArrayValues()
End Sub

Sub PrintThem(ByVal values As Variant)

    Dim v As Variant
    
    For Each v In values
        Debug.Print v
    Next v
    
End Sub

Function ArrayValues() As Variant
    ArrayValues = Array(1, 2, 3)
End Function

edited May 20, 2021 at 14:17

answered May 20, 2021 at 13:37

Kostas K.

8,5282 gold badges26 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BJS Over a year ago

Let's assume Function NumberOne() does not create a single number, but an array of numbers. 1. How would you create such an array dynamically as a function result and pass it to the other function in order to add the constant NumberTwo?

Kostas K. Over a year ago

Change the return type to Variant and return a static array for testing, e.g. NumberOne = Array(1, 2, 3).

FaneDuru · Accepted Answer · 2021-05-20 16:20:20Z

Try the next adapted functions, please:

Function f_1(Arg_11 As Long, Arg_12 As Long) As Long
   Dim itNo As Long, i As Long
    For i = 1 To 100
        'cells(i, 1).Value = Arg_11 * Arg_12
        itNo = itNo + Arg_11 + 1
    Next i
    f_1 = itNo + Arg_12
End Function

Function f_2(Arg_21 As Long, Arg_22 As Long) As Variant
   Dim itNo As Long, j As Long
   
   itNo = 100
    For j = 1 To itNo
        'cells(j, 1).Value Arg_21 + Arg_22
    Next j
    f_2 = Array(Arg_21 + Arg_22, Arg_21 * Arg_22)
End Function

And test them in the next way:

Sub testBothFunctions()
  Dim arr, no As Long
   arr = f_2(2, 3)
   Debug.Print arr(0), arr(1)
   no = f_1(arr(0), arr(1))
   Debug.Print no
End Sub

Please, try forgetting about putting the values in the cells...

It is only a way to show you how this may be handled.

Collectives™ on Stack Overflow

Using a function result as a function argument in VBA?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related