In many procedures from the Excel VBA Add-ins or other macros that I create, 2 common needs are to calculate the last used row, last column of a worksheet and to check if the user selected a Range or other object.
- The last row and column are needed because in most of the procedures we “put the code” to “do something” with all the data, until it reaches the last row and/or column.
- The TypeName of selections in needed because if he selected something else than a Range, the procedure must give a notification and exit. If he selected a Range the procedure can continue. This need appears because the macros are designed to “do something” with the values from the selected range and we don’t want to get an uncontrolled error in the situation when the user selected for example a chart axis and runs our macro.
It’s not a good practice to copy the same code block in different procedures because if in the future you need to modify something at that code you will have to search and modify in all the places where you copied it. In most cases like this, the best approach is to create in a separate module some functions which will check what you need. Then, you can call those functions in all other procedures where you need them. This way, if someone else or even you will need to modify the logic of that code, you will have to modify it only in one function, not in all other procedures which calls it. Also, this way your code will have consistency (you are always using the same “old and tested” method to get the same result !)
Enough with the theory, let’s see the facts.
- Here it is my solution for calculating the Last Row, Last Column, First Row and First Column for the used range in a given worksheet:
- The Last Row is a mandatory parameter because in 99% percent of my macros I need it. It is defined as Long, because it can have greater values than the other parameters for which the set of Integers is enough.
- You can call this function in every procedure where you need it, like in the below example:
- The Result:
- The solution for TypeName of the Selection is the following:
- We can use the function in our procedures like below (notice that if we call it in 50 other procedures for example, and at a given moment we decide to change the message received by user in case the selection is not a Range, we have to change the message only once, in our function and the update will be available immediately in all 50 procedures):
- The Result:
– If you select an Axis of a Chart:
– If you select a TextBox:
– If you select a Range:
You can also download the file with the model of this working procedures and all the used code here: