Passing multiple values as function in a single function-CodePudding

In Python, we normally pass arguements in the function when we have to call them multiple times as below:

def function_add(a,b):
print(a b)

function_add(4,5)
function_add(5,7)
function_add(10,4)
function_add(4,6)

Do we have a similar way to implement it in VBA too? I tried to implement it but I couldn't make it. Below is my code.

Private Sub SearchAndInsertRows()
Dim rng As Range
Dim cell As Range
Dim search As String
Dim kk As String

Set rng = ActiveSheet.Columns("A:A")
search = ""
Call searchGetKey(search)
Set cell = rng.Find(What:=search, LookIn:=xlFormulas, LookAt:=xlWhole, MatchCase:=False)

If cell Is Nothing Then
    MsgBox "Not Found"
Else
    kk = ""
    Call searchSetKey(kk)
    cell.Value = kk   
End If
End Sub

Sub searchGetKey(ByRef getKey As String)
    getKey = "a"
End Sub  

Sub searchSetKey(ByRef setKey As String)
    setKey = "b"       
End Sub

Sub searchGetKey and searchSetKey modifies one cell but I need to do the same for number of cells. Is there any other ways to do it?

Please fell free to optimize the code wherever necessary.

Thank you very much and much appreciated. :)

CodePudding user response：

A function in VBA must return something. Otherwise, you should use a Sub:

Function function_add(a As Long, b As Long) As Long
   function_add = a   b
End Function

Sub TestFunction()
   MsgBox function_add(3, 5)
End Sub

You can use a function without arguments, just returning according to a specific calculation algorithm. For instance:

Function tomorrow_Date() As Date
     tomorrow_Date = Date   1
End Function

It can be called as:

Sub testTommorrow_Date()
   MsgBox tomorrow_Date
End Sub

Or a Sub which by default takes arguments ByRef, if not specified ByVal:

Sub Sub_add(a As Long, b As Long, c As Long)
    c = a   b
End Sub

And test it as:

Sub TestSub_Add()
   Dim c As Long
   Sub_add 3, 2, c
   MsgBox c
End Sub

Of course, a and b may be declared in the testing Sub and used like arguments, but I wanted saying that they are not relevant against c which was updated after the call...