Calling a Sub from a Sub in VBA - When you are creating macros you sometimes want to run other functions or subs from your VBA code. One clarification about what a subroutine is: It is a function that does not return anything, or in other words a macro. In this post, we are going to cover how to call a subroutine from another.
In the last post about the Worksheet.Change event, we covered how to catch a change event. We showed a MsgBox and did some changing of values. We could easily have called a subroutine as well. Let's call one from a button.
Sub Our_sub_button() I_like_to_be_called End Sub Sub I_like_to_be_called() MsgBox "Hi, nice to be called!" End Sub
First, we attach the "Our_sub_button" to a Form button. We then call the "I_like_to_be_called" subroutine from our button. Note that you do not need the Call keyword, but you can use it if you like as below:
Sub Our_sub_button() Call I_like_to_be_called End Sub Sub I_like_to_be_called() MsgBox "Hi, nice to be called!" End Sub
This is all good Conny, but I want to pass variables also. How do I do that? See below. You just put the variables after the call, omitting the parentheses since we are not using the Call keyword. You also need to create the variables in your subroutine that you are calling.
Sub Our_sub_button() I_like_to_be_called "this is ", "my message" End Sub Sub I_like_to_be_called(MSG As String, MSG2 As String) MsgBox (MSG + MSG2) End Sub
Now you know how to call a subroutine. Happy coding!