I was working on several VBA macros to automate several processes and I found myself repeating certain VBA code within each sub.  I decided that it would best to turn this into a public function that I can reuse over and over again.

The code may not work for everyone, but where it is most helpful is error handling or in other words error proofing the macro so users don’t do something silly like name their worksheet with disallowed characters such as \ / ? * [ or ].  If you try naming a worksheet with those characters, then you will receive this error message:

The one thing you could add to the code below at the beginning of the function is the following:

Then add the code below at the end of the function:

Now don’t get upset that I included some code above that seems unnecessary to simply add a worksheet.  I will explain.  You really only need the DisplayAlerts for this function to work without the user being asked to confirm if a worksheet should be deleted.  I actually use the code above in separate Public Functions called prepareFile() and resetFile() and then I call those functions at the beginning and end of my sub.  I just wanted to mention that this code is helpful to turn off unnecessary features of Excel while the macro runs, which speeds up or improves the performance of the macro.

Use the code above if you want, but it is not necessary.  Just a helpful tip since it is not imbedded in the main function code displayed at the end of this post.

Now, the following code is necessary because it is called in the function at end of this post (don’t give me crap for using “On Error Resume Next“):

One note on using the newWorksheet function.  When calling the function, there is an optional added text you can provide.  This is to help differentiate the worksheet from other worksheets with a similar year and month (YYYY-MM) in their name.

Please leave any comments if you have any suggestions to improve or how you might use it in your next project.  So here is the code in all it’s glory: