VBA sub with inputs

Question

How do I feed parameters into a sub? When I run a Macro, the sub just runs without asking for any input.

Sub do_something(input as integer)
    xxxxxx
end sub

I know I can use the sub in another sub/function where I can give it a input. Like the following,

sub caller_function()
    call do_something(1)
end sub

Is there another way to use do_something? Thanks in advance!

According to how and where from you are calling the macro, there is different possibilities. See https://stackoverflow.com/questions/29858176/how-to-call-a-macro-from-a-button-and-pass-arguments and https://support.microsoft.com/en-gb/help/153307/how-to-call-microsoft-excel-macros-that-take-parameters for example — Vincent G, Sep 26 '18 at 16:23

score 3 · Answer 1 · answered Sep 26 '18 at 15:55

Set up your sub something like this:

Sub do_something(param As Integer)
    MsgBox "The parameter passed to this Sub is " & param
End Sub

To call it:

do_something 1

If you want more than one parameter..

Sub do_something(param1 As Integer, param2 As String)
    MsgBox "The parameters passed to this Sub is " & param1 & " and " & param2
End Sub

To call it:

do_something 4,"Hello"

Or for a bit or reading chaos:

do_something param2:="Hello", param1:=4

Valon Miller · Answer 2 · 2018-09-26T16:00:44.497

The code that you provided does not compile. Maybe you were trying to provide a simple example...?

It does not compile because Input is a reserved word, used to read data from a file object. You cannot use it as a variable name. (I have assumed that "xxxxx" is indicating where code should go, i.e. 'Do something here.)

The following code works, but you cannot run it without passing a value:

Sub do_something(i As Integer)
    MsgBox i
End Sub

If you want to make the variable optional, you can add the Optional keyword. Then it will run even if a value is not passed:

Sub do_something(Optional i As Integer)
    MsgBox i
End Sub

You could also use a globally scoped variable, which would allow the sub to run without directly supplying a value:

Option Explicit

Public i As Integer

Sub do_something()
    MsgBox i
End Sub

Regarding Input: https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/inputstatement

score -1 · Answer 3 · answered Sep 26 '18 at 15:41

-1

With a single argument, you don't use parenthesis

do_something 1

answered Sep 26 '18 at 15:41

djv

15,168
7
48
72

VBA sub with inputs

3 Answers3