# Pass argument from Function to Sub

##### Board Regular
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

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
In your example, exp is returning a value back to the caller fa, that makes exp a function as well.

Code:
``````[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.

You can pass variables to subs just as you'd pass them to functions:
Code:
``````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.

Last edited:
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
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

Last edited:
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

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.

Code:
``````[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]
[color=darkblue]End[/color] [color=darkblue]If[/color]
End [color=darkblue]Function[/color]``````

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

You're welcome.

Replies
8
Views
309
Replies
0
Views
132
Replies
2
Views
281
Replies
0
Views
242
Replies
0
Views
588

1,196,116
Messages
6,013,563
Members
441,771
Latest member
clamnets

### 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?

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