Excel VBA - Passing Argument Parameter to Sub

Question

I have a function that has an argument I'd like to pass to a sub's variable in Excel VBA. The function's argument is a range, say A1:B1, so I'd like that range to be passed to another sub. Is there a way to do this? Everything I've read on the internet so far deals with passing the result of a function or arguments between subs, but not a function's argument passing to a sub's variable.

Thanks in advance!

Edited for more detail:

I have a function, BuildpgSQL, with the following arguments that are all range data types: FieldNames, Table, PageFilters, and GroupBy. I also have a sub that will use the same PageFilters range as a variable. Rather than define the argument twice - once for the function and again for the sub, I'd like to pass just the PageFilters argument from BuildpgSQL to the sub's variable.

What do you mean by "passing a functions argument to a subs variable"? Functions can call subs with no problem whatsoever, and the call can involve passing on the function's arguments to the sub's arguments -- but do you mean you want the values in a function to somewhat move to the sub without the sub even being called? Perhaps you can describe your problem in more detail. — John Coleman
– John Coleman, Commented Apr 15, 2016 at 2:56

Community · Accepted Answer · 2018-05-08 21:49:10Z

As @paxdiablo said, there is no problem in having a function call a sub and in so doing pass on its arguments on to the sub. But, you seem to want to bypass explicity passing the arguments from the function to the sub without having them listed explicitly as parameters to the sub. You can't do this with ordinary local variables in the sub, but you can do it with global variables.

In VBA these are called either Public or Module Level variables and are declared outside of any sub or function. For example:

Dim Table As Range
Dim FieldNames As Range 'module level variables

Function BuildpgSQL(tbl As Range, fldnames As Range)
    Set Table = tbl
    Set FieldNames = fldnames
    'do stuff
End Function

Sub AnotherSub()
    MsgBox "Table = " & Table.Address & ", & FieldNames =" & FieldNames.Address
End Sub

Sub test()
    BuildpgSQL Range("A1"), Range("A2:B2")
    AnotherSub     
End Sub

When test is run and invokes AnotherSub, the latter sub is able to correctly report the ranges that had been passed to the function.

I declared the two variables outside of any sub or function definition. Then -- any change that any sub/function in the module makes to those variables can be read by any other sub/function in the module. This makes those variables global variables in the module. These are often useful in VBA, but should be used sparingly since they tend to make your code less modular and hence more difficult to debug.

You can also use Public variables to get another type of global variable. They would also be defined outside of any module/sub but with the keyword Public rather than Dim. This would allow the variables to be accessible anywhere in the project, including other modules. Here is a nice article discussing scope in VBA.

Finally, note that I used both global variables and similarly named argument parameters to the function. This was to better match your specific question. In practice, it would make more sense to get rid of the middleman and eliminate those function parameters. Whatever code calls the function can first set the public variables (if they are not already set) and then invoke the function.

paxdiablo · Accepted Answer · 2016-04-15 02:59:51Z

1

There are no issues with passing parameters sent to a function on to a subroutine (or another function for that matter). The following code shows that in action:

Option Explicit

Sub sub2(z As Integer)
    MsgBox (CStr(z))
End Sub

Function fn1(y As Integer) As Integer
    MsgBox (CStr(y))
    sub2 (y)
    fn1 = y
End Function

Sub Macro1()
    Dim x as Integer
    x = fn1(12)
End Sub

In the above code, you can see the main macro calls the function, and the function passes that on to the subroutine. The message boxes output the variable at different points through the process (all output as 12 of course).

This works just as well for complex objects (such as ranges) as it does for simple ones.

answered Apr 15, 2016 at 2:59

paxdiablo

888k243 gold badges1.6k silver badges2k bronze badges

Collectives™ on Stack Overflow

Excel VBA - Passing Argument Parameter to Sub

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related