passing an argument to a sub

[lezawang]

Hi
I need you help understanding how to pass an argument to a sub. In this article the author said

"VBA procedures can be passed data via arguments, which are declared in the procedure definition. For example, you could have a VBA Sub procedure that adds an Integer to every cell in the current selected range on a worksheet. You could supply the value of the integer to the Sub via an argument, as follows:"

my question, how can I pass an argument to a sub. I mean lets talk about the example above. I highlight these cells that I want to add an integer to. I insert a control button and assign it to that Macro. Now I click the button to run the macro. How and when I can pass the argument?

Thank you very much.

source: VBA Function and Sub Procedures

 

[Norie]

Not really a good example, you can't directly pass values to code via a button.

If you did want to do something with the selected cells though you could do something like this.

Sub Button1_Click()
    Call ProcessRange(Selection)
End Sub

Sub ProcessRange(ByVal rng As Range)
    MsgBox "These are the selected cells: " & rng.Address
End Sub

 

[DanteAmor]

The procedure can be called only by writing the name and argument:

Private Sub CommandButton1_Click()
  Process_selected_cells 5
End Sub

Or with Call statement

Private Sub CommandButton1_Click()
  Call Process_selected_cells(5)
End Sub

This could be an example for the procedure

Sub Process_selected_cells(i As Integer)
  Dim c As Range
  For Each c In Selection
    c = c + i
  Next
End Sub

 

[lezawang]

Thank you all very much for the help

 

[DanteAmor]

Youre welcome!