Excel 2016 - Conny Söderholm

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…)

How do you copy an Excel 2016 formula without changing the cell references?- Have you copied a range of cells in Excel and wanted them to reference the same cells as before? Have you happily copied them, pasted them and realized that Excel dynamically updates the cell references? There are two ways around this. Either you lock the cell references with the dollar ($) sign or use a small Notepad hack which I will show you below. (more…)

You have one Excel workbook. You have several sheets in the workbook. You have one summary table on Sheet1 where you want to compile all results. The summary table references to three cells on each sheet. You fill in the formulas, cross-referencing the ranges, Sheet2!A2, Sheet2!B2, Sheet2!C4. This is all fine, it takes a while to do, but you will get there.

Now suppose you add 3 more sheets. You have to copy the formulas, and then change the sheet references to update your table. Quite cumbersome, but there is a solution to it, reference one cell instead of all with Excels ADDRESS and INDIRECT functions! (more…)