Attend courses from home - All courses REMOTE from April 1 - April 30

Reading glasses in front of computer screens

4 Tips for VBA Excellence

October 30, 2017

Tips and tricks


When we encounter a new language not to mention a programming language, it is understandable to be apprehensive about it. If you are trying to automate an Excel spreadsheet using VBA programming, it can seem especially daunting if you have never programmed before. The following tips and tricks will help you become more proficient and comfortable in your coding experience.

 

1. Using Recorder

One of the best tools you can use while creating your VBA is to use the recorder button to record a Macro. This will autogenerate the VBA code for you. While you may need to make some small changes to this code to better suit your needs, it should guide you in the right direction and approach.

The best way to facilitate code generation is to record a different macro for each step and combine it into your final programme. Which brings us to the next tip …

 

2. Following a modular approach

Try to break-up your solutions into multiple steps and tackle them separately. Create separate sub routines for tasks which are repeated.

Need to open a different file every time? – make a small sub-routine which accomplishes this task.

Modular approach will do wonders to the ease of programming. This approach also reduces coding errors and makes your programme easier to understand.

 

3. Forcing declaration of Variables

Compared to some other programming languages like C, C++, Java, etc., VBA takes a lax approach to variables, in that, it allows the use of a variable without declaring them beforehand.

This approach while easy to use at first, frequently leads to coding errors. To force yourself to declare a variable before using it in a module, use the following command at top of your module:

                  Option Explicit

 

4. Disabling Excel Alerts

Some Excel actions require a confirmation on Excel in form of alerts – e.g., deleting a sheet with some data or formatting on it pops up an Excel confirmation alert. You can disable these alerts in VBA by using the following code before the coding area where you expect the alert/s:

                  Application.DisplayAlerts = False

Later, the alerts can be re-enabled with the command:

                  Application.DisplayAlerts = True

 

Hopefully, these tips will help you along your journey to VBA programming excellence. All the very best!

 

What is Remote Training?

Valued by Individuals

 

4.8/5
Over 20105 Reviews
I am excited about PowerPoint after completing this course with John. I was advised by Ella to do this course and it was good advice. Thanks again John! -PowerPoint Level 2 Sydney
Paul - PowerPoint Level 2, .
Awesome class today John. Really informative and thoroughly enjoyed the topics especially the Video and Master Slides components, I will be able to use these for upcoming projects. -PowerPoint Level 2 Sydney
Dom - PowerPoint Level 2, .
Great course. John is an awesome trainer. Had a great learning. Thank you very much. -PowerPoint Level 2 Sydney
Manisha - PowerPoint Level 2, .
Great course very in depth, covered all basis, great templates -PowerPoint Level 2 Sydney
Shin - PowerPoint Level 2, .
john was extremely informative and made the day go quickly. helped me regain a lot of forgotten knowledge -PowerPoint Level 2 Sydney
Nathan - PowerPoint Level 2, .
Jagmohan presented well and explained in detail. I found the content to be a little basic for what i was expecting. -Word Intermediate Sydney
Shanay - Word Intermediate, .
Was really informative, easy to follow and Ken did a great job given a few technical issues beyond control -Teams for Collaboration Adelaide
Melissa - Teams for Collaboration, .
Lesson went really well - had some time left so Ken went through some additional apps at our request. Very insightful. -Teams for Collaboration Adelaide
Lucia - Teams for Collaboration, .
Great Trainer. Very impressed. Learned a lot. -Power BI Beginner Melbourne
Andrew - Power BI Beginner, .
Great course, highly recommend. -Excel Intermediate Canberra
Chandre - Excel Intermediate, .
Great course, very informative! Excited to apply what I have learnt at work. -Excel Intermediate Canberra
Polly - Excel Intermediate, .
Enjoyed course just couple of issues with remote connection in that I could hear and see course, but could not ask questions for a period of time. Terry was very helpful in sorting out issue during the lunch break.. -Excel Intermediate Canberra
Mary - Excel Intermediate, .
For a session that is originally designed for face to face delivery, the online learning (aside from a few technological issues) was informative and covered a broad range of topics in detail. Jag explained everything really well and made sure that all participants were following and always stopped to help if anyone fell behind or were having issues. Thank you! -Excel Intermediate Canberra
Katie - Excel Intermediate, .
Thank you Michael for a very comprehensive introduction to Power BI. I appreciated your explanations regarding why we do certain actions. You also re-emphasised points by repeating them as there is a lot to process through in one lesson. -Power BI Beginner Melbourne
Matthew - Power BI Beginner, .
Good -Excel Macros/VBA 2 Day Sydney
Qing - Excel Macros/VBA 2 Day, .
Course was very informative. Thoroughly enjoyed. -SharePoint Site Owner Perth
Shannon - SharePoint Site Owner, .
Great course - Dominic was a engaging instructor considering the limitations of online learning -SharePoint Site Owner Perth
Alyce - SharePoint Site Owner, .
Michael was an excellent demonstrator, and engaged us well and answered all our questions. Thank you Michael! -Power BI Beginner Melbourne
Vatsal - Power BI Beginner, .
Excellent - great pace and clear instructions - really useful! Will send others from my team. Cheers Michael Sean -Power BI Beginner Melbourne
Sean - Power BI Beginner, .
Very detailed. Might be good to have a shorter higher level course, i found this course to be a little too detailed, as I am already very familiar with SharePoint. More time on Flow would have been good and less time on other functions -SharePoint Site Owner Brisbane
Jodie - SharePoint Site Owner, .

Trusted by Business

Procured by Government

Map of Australia