How to pass variables to Subroutine?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, everyone,

I am writing a subroutine that may received one or more variables passed in
from the calling Subs. I use array in my subsroutine, my problem is how to
pass the variables (strItem1, strItem2….) to strAry(i) one at a time. Thank
you for your help.

Private Sub Call1_Click()
Dim n As Integer
n = 2
strItem1 = "Apple" REM strItem1 is Public variable
strItem2 = "Orange" REM strItem2 is Public variable

Call sRoutine_Click(n)
End Sub

Private Sub sRoutine_Click(n As Integer)
Dim strAry() As String
Dim i As Integer
Dim SQL As String
ReDim strAry(n - 1) As String
For i = 0 To n - 1
strAry(i) = strItem??
SQL = "SELECT * FROM Product WHERE Item = '" & strAry(i) & "'
………………..
Next
End Sub
 
Jeff, take a look at help on ParamArray

This lets you declare a procedure and pass any number of values as a
delimited list. The receiving function treats them as an array.

Here's a useful example:
MinOfList() and MaxOfList() functions
at:
http://allenbrowne.com/func-09.html
Demonstrates how to receive a unknown number of values, and loop through
them to find the least/greatest. You don't need to pass the number of
elements, since the receiving function can get that from the upper bound of
the array.

If you are attempting to concatente the values into the WHERE clause of a
query string, the IN operator could be useful. The target string will be:
"SELECT * FROM Product WHERE Item IN (""Apple"", ""Orange"",
""Banana"");"
 
Back
Top