Creating and formatting a chart automatically in Excel with VBA

Using Excel involves creating a lot of charts and tables. Most of the time, the operations are really simple but time consuming. Creating and formatting a chart takes time. In this post, we are exploring how we can create a chart automatically with some VBA code. (more…)

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!

Excel, VBA, and the Worksheet.Change Event - Do you have users that change a worksheet, and at each change, you are required to check if some specific action is required? Then you can use the Worksheet.Change event provided in VBA. You can find more information about the Worksheet change event at Microsoft's Dev Center. In this article, we will code a short Worksheet.Change script. (more…)