How to make your code robust with worksheet codenames

*********************************
Get my VBA Troubleshooter course for only $10 http://goo.gl/S5GIBk
*********************************

How to make your code robust with worksheet codenames

Excel users have some very bad habits - one of them is making structural changes to spreadsheets. In particular they like to re-name and move around worksheet tabs (usually to make their spreadsheet more pretty). If you have VBA code that uses a worksheet tab name then such changes will break your code. Recorded macros typically work this way and a lot of people tend to write their VBA code in this way as well - it's a very common problem.

An excellent way to protect your Excel VBA code against users changing tab names or moving around worksheets is to use worksheet codenames instead of tab names in your code. Codenames are an internal name that Excel uses to refer to objects within it's object model. It's very difficult for end users to change a worksheet codename - they have to know how to open the VB Editor and change the properties of the worksheet.




In this beginner level tutorial we start with a completely blank spreadsheet. We record a typical macro that copies, pastes and re-names a worksheet using the worksheet tab name to show how easily this breaks. Next we perform the same set of operations using Excel worksheet codenames and show how much more robust these are.

The video uses MS Excel 2016 but the same steps are used for MS Excel 2010 and Excel 2013.

If you enjoyed this short tutorial then please add a comment, start a discussion, "Like", "Favourite" or "Share" the tutorial and subscribe to my channel.

Channel Links and Playlists
----------------------------
Subscribe to Office Ninja: https://www.youtube.com/channel/UCY-6...
Understanding VBA Code playlist: https://www.youtube.com/watch?v=hLeU0...
Official Channel Series: https://www.youtube.com/watch?v=UDZ0r...

Other Useful Links
----------------------------
My facebook Page can be found here: https://www.facebook.com/PaulsLearningLounge
My Blog sometimes has VBA/Excel related content: https://www.theartofquantfinance.com
A full list of my Udemy courses can be found on my Udemy profile page: https://www.udemy.com/user/paulmorris...
My Excel VBA Troubleshooter course is here: https://www.udemy.com/become-a-vba-tr...
You can often find deals on my current and upcoming Udemy courses here
https://www.theartofquantfinance.com/udemy

WiseOwl provides a lot of very good VBA tutorials: https://www.youtube.com/channel/UCbi5...
Sali Kaceli provides a lot of Excel and VBA related tutorials also: https://www.youtube.com/user/skaceli
Chip Pearson has a lot of VBA content - if Chip says you should do something ... you probably should: http://www.cpearson.com/

Thanks for watching!

Paul.