Pass argument from Function to Sub

[fahadakbar]

Hi ,

is there any way to pass arguments from function to a sub , such as

Function fa(a, b)


Call exp
fa = p

End Function


Sub exp()

p = a+b +9

End Sub

 

[AlphaFrog]

In your example, exp is returning a value back to the caller fa, that makes exp a function as well.

[COLOR=darkblue]Function[/COLOR] fa(a, b)
    
    fa = exp(a, b)
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Function[/COLOR]

[COLOR=darkblue]Function[/COLOR] exp(a, b)
    
    exp = a + b + 9
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Function[/COLOR]

Even if exp was a Sub, you pass values to it the same way as you pass values to a function. The question is if you want exp to return a value back to the caller.

 

[Misca]

You can pass variables to subs just as you'd pass them to functions:

Dim p As Integer

Sub TestFa()


MsgBox fa(5, 19)


End Sub


Function fa(a As Integer, b As Integer)




Call exp(a, b)
fa = p


End Function




Sub exp(a As Integer, b As Integer)


p = a + b + 9


End Sub

Since your exp is a sub instead of function I had to use external variable p to get the value stated in the sub back to the function.

 

[fahadakbar]

this is interesting, but here is what I am actually trying to figure out

I want to create a function which has arguments (VTBF & Rng) . I then want to run a sub using same arguments that I defined in the function. Once the sub is run, I want the function to operate on the basis of initial arguments and value obtained from the sub ... below is the complete code

Function fa(VTBF, rng)

Call vol
fa = x

End Function




Sub vlo(VTBF, rng)

Set rng1 = Range(rng).Find(What:=VTBF)
If Not rng1 Is Nothing Then
Application.Goto rng1
x = ActiveCell.Offset(0, -1)
vol = x
Else
vol = "Not Found"
End If


End Sub

the problem I noticed in this was that the values for VTBF and rng were not being passed to the sub. That's where I get stuck

 

[fahadakbar]

Hi Misca,

you solution works. However, I want this function to be run from the formula bar , and that's where I want to define variable A and B
in your solution, A & B are defined inside VBA code , this is what I do not want.

for me Fa function should work normally, i.e I should provide the values of A & B in the inputbox when function is selected from the formula bar under 'userdefiend' category

 

[AlphaFrog]

See my signature block below about the use of code tags. It makes reading your code much easier.

You call vol but your sub is called vlo.

You didn't pass any arguments to vol
Call vol VTBF, rng

If vol is to return a value back to fa, vol it's a function, not a sub.

[color=darkblue]Function[/color] fa(VTBF, rng)
    fa = vol(VTBF, rng)
[color=darkblue]End[/color] [color=darkblue]Function[/color]

[color=darkblue]Function[/color] vol(VTBF, rng)
    [color=darkblue]Dim[/color] rng1 [color=darkblue]As[/color] Range
    [color=darkblue]Set[/color] rng1 = Range(rng).Find(What:=VTBF)
    [color=darkblue]If[/color] [color=darkblue]Not[/color] rng1 [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
        vol = rng1.Offset(0, -1)
    [color=darkblue]Else[/color]
        vol = "Not Found"
    [color=darkblue]End[/color] [color=darkblue]If[/color]
End [color=darkblue]Function[/color]

 

[fahadakbar]

"If vol is to return a value back to fa, vol it's a function, not a sub."
you nailed it...
thx a lot

 

[AlphaFrog]

You're welcome.