There are several ways you can future proof your code in Visual Basic. By using the Macro Dialogue Box in Excel (as well as Word) you can make sure your colleagues can understand the purpose and general use of the Macro you record or write. To use the Macro Dialogue Box to is advantage. Here the best ways on how to do so:
Give your macro a user friendly name.
Always give your macros a user-friendly name, or agree a naming procedure for your Macros so that everyone knows what they are for. This may seem a bit like stating the obvious, but if your Macro is there to format a table, then give it a name that would summarize what it formats and keep it memorable and concise. Naming a Macro No.14 isn’t really going yourself or others any favors when debugging any problems.
Add the shift button to any key short cuts.
Always add the Shift button to any key shortcuts. A CTRL+Key shortcut will disable its MS predefined purpose, so If you add CTRL+C shortcut to your macro and run it, you will not be able to use that short key to copy text or cells.
Use the description box to explain the Macro.
Always use the description box to add as much info about the Macro as possible and dates when it was edited. This way you can always know what went wrong and when and by who, not to point blame but to understand what happened and minimize time spent on investigating the problem.
Use comments when you write a code.
Commenting can also be very handy when writing new code, recycling code or adding more details to your existing code to help others understand the purpose of your code.
Adding a comment is fairly easy. Go to your line of code and add an apostrophe (‘) in front of it. Anything on that line will turn green indicating that VBA is not recognizing it as a line of code, but seeing it a comment or note.
Always use commenting creatively to lines of code you are unsure of, as well as adding notes to your code for people to see what that block of code is meant to do. You could also use the apostrophe to debug the code if it is not working correctly or clashing with another line of coding. Or instead of deleting code, you can use commenting to leave the code where it is and come back to it at a later stage.