9 Excel VBA Beginner Mistakes
1) Learning code for the sake of learning code
The truth is you need a subset of techniques. Roughly speaking, this consists of manipulating cells, manipulating variables, loops, conditional statements and position control techniques. Invest time in applying VBA code to real-world problems – to do so will progress your career much faster. Be a value creator, not a technique collector.
2) Not having a debug routine
So, what to do when you get a VBA error? Take a deep breath and then get into your debugging routine. You can see me working through mine on the channel; it roughly consists of (1) hitting debug and finding the problematic line of code (2) resetting the VBA editor (3) using breakpoints and/or ‘step into’ to go through the code, line-by-line (4) making a tweak – does the same thing happen?
3) Too much code, not enough formulae (VBA / formula balance)
The classic example is using VBA to perform calculations. Sure, this is possible in VBA, and it might be fun (and good practice) to set up the code. But, to do so loses one of Excel’s defining qualities: instant calculation. Why click a button to make Excel do what Excel normally does?! This would frustrate and this job would be better done with formulae.
4) Lack of dynamic quality – catering for changing datasets
What if the user adds data to this dataset? If more data is added, the macro no longer includes all the data. Still worse, it looks to the user as if everything is working fine, so we don’t even know that something is wrong!
The specific skill you are lacking here is dynamic position control. You see, rather than using so-called ‘hard-coded’ ranges (more on them in a second), we can using powerful VBA constructs such as .end(xldown) to dynamically define a dataset; this means that the code should still work if data is added or removed – cool 😊
5) Values embedded in code – catering for changes in a business situation
‘Hard-coded’ values are often a sign of an inexperienced programmer. A number (or another piece of information, such as a range) embedded in VBA usually takes control away from the user, since we cannot ask any user to access the VBA editor to make changes should they need to.
6) Not making assumptions clear; or not making any
So, we know dynamic coding is cool! But, is any application fully ‘dynamic’?
In reality, any Excel VBA application is breakable and we rely on the goodwill of the user (which could mean – you!) to keep things working, at least to some extent. I have found that customers are very happy to work within certain constraints if these are made clear. As far as I know, a customer has never tried to ‘break’ a file I have built for them! Once noted down, assumptions can be discussed and even removed, if required. It’s a great way to show trust in your user and to bring them into the development process.
7) Overcomplicated file design
An Excel-based tool should be ‘intuitive’ – this means it should be easy to use and the user should not have to click and scroll much to operate the file. Think of your phone, a website you use often or your car dashboard: you like using these things because the information is at your fingertips! Can you imagine having to read a manual every time you got into your car? If your Excel file requires a user manual or even instructions (though I do encourage video walkthroughs), it is too complicated.
8) Overuse of userforms
Bear in mind that Excel provides one excellent tool for data input – the spreadsheet! Yes, the best way to gather data from the user is probably via the spreadsheet.
I typically deploy userforms, if at all, towards the end of a development to gather or display data when there are already multiple sheets in the file. Think of them as an ‘additional layer’ of functionality, not necessary but powerful when used effectively with strong awareness of the implementation issues!
9) Believing that VBA has too many limitations
There are plenty of people out there are criticizing Excel and claiming other platforms are simply ‘better’. I can only agree with this if you really (I mean REALLY) understand Excel VBA. For many, this argument is an excuse for not getting to grips with Excel VBA and understanding its unique power.
So, if you are feeling like giving up VBA early, just make sure the excuse is not ‘VBA does not do the job anymore’ or ‘VBA is going out of fashion’ since, so often, it is the best tool for the job. It might be the biggest mistake of your career.
---------------------------------------------
🎫Get Your Channel Membership HERE🎫
https://tinyurl.com/yx2e685z
🎥🎁YOUR EXCEL CHEATSHEET
🎁1 Page Summary Of What You Need To Know
https://tinyurl.com/yc7jlmbs
😊 / tigerspreadsheetsolutions
🐦 / tigspreadsheets
💻http://tigerspreadsheetsolutions.co.uk
Watch video 9 Excel VBA Beginner Mistakes online without registration, duration hours minute second in high quality. This video was added by user Tiger Spreadsheet Solutions 01 January 1970, don't forget to share it with your friends and acquaintances, it has been viewed on our site 2,972 once and liked it 96 people.