What is VBA and why should I use it?
VBA, or Visual Basic for Applications, is a scripting/programming language that can be used in Excel.
VBA Scripts, which are also referred to as βMacrosβ, are used for automating time-consuming operations in Excel, as well as enabling additional functionalities β Such as creating User Forms, creating User Defined Functions (UDFs), and more.
An additional benefit of VBA – All Office Applications can speak with each other using VBA β We can send an email from an Excel spreadsheet or create a Word document based on a specific Excel range, etc.
Here are some examples of VBA codes that I personally like:
- VBA code that unhides all worksheets in a workbook
- VBA code that creates a table of contents for all the worksheets in the workbook
- VBA code to remove page breaks
- VBA code that generates a brand new Excel Function that can sum and counts cells by their color!
Just because you may not have any prior knowledge in VBA, it doesn’t mean you can’t use it!
There are countless VBA code snippets which can be found on the net, and be used to solve problems in your Excel Workbook. All you have to do is to know how to find your required code, and how to run it.
Safety guidelines for using VBA
Before we start, a couple of IMPORTANT warnings – PLEASE READ CAREFULLY!!!
- VBA is a programming language. It can be very useful, but also very dangerous. Just like any programming language, it can be used for good purposes, but also bad purposes!
- VBA can be dangerous because it may contain malicious code that may intentionally harm your files and computer! You can easily use VBA to delete files from your computers, send them to other people and basically wreak total havoc π
- VBA can be also dangerous if you run a certain code without understanding the impact of a specific line of code. It can crash your Excel, disable a certain functionality in Excel, become irresponsive, or even cause severe data loss to your files – On your active workbook and even on other workbooks (Open or even closed ones!)
- There is no “CTRL Z” (Undo) after running a VBA code. This means that unless you’ve saved your documents before running your code, you might not be able to undo your actions in Excel.
- Macros can be programmed to be triggered as soon as Excel starts or a certain operation is done. This is especially relevant for XLSM files (Excel Macro-Enabled Workbook). This means that you may trigger a macro without even knowing!
Attention – Please use macros at your own risk – we are not responsible for any loss or damage caused by running any of the VBA codes that are displayed or referred to on this website!
Okay, now that I’ve got your attention – Macros are a useful and important tool in the toolbox of any Excel expert.
Here are some guidelines on how to avoid the risks of using Macros, to enjoy their benefits:
- Before you run any VBA code – even the simplest one – Make sure you’ve saved all active Excel workbooks. If things go south, you can always re-open your workbook.
- If your Excel file is important and you’re not 100% sure about the consequences of your code – Create a backup to your files. In case you accidentally changed something and found out only a day after that this change was undesired – You can still revert to the backup file.
- A good practice before running VBA codes is to close all unnecessary Excel workbooks, as some VBA codes can unintentionally affect other workbooks. Closing these workbooks will lower the risk of data loss.
- Do not run VBA codes from untrusted websites. As beginners, you should focus on Macros you find on trusted and popular websites.
- Do not open suspicious Excel workbooks, especially XLSM (Excel Macro-Enabled Workbook) ones.
- Test your code before running it on your actual data. For example, run the code on a “dummy” file/data to verify it works well.
- Quality-Check your data and files after running the VBA code. Make sure that there are no undesired results!
- If you opened an XLSM workbook, Excel will ask you if it’s OK to enable Macros. Do not enable it unless you are 100% certain it’s safe, as this may trigger unwanted Macros in the background. If not sure – Do not enable. Better safe than sorry π
Remember – Even Excel experts with many years of experience can make mistakes, especially when working with advanced features like VBA.
Being safe and careful is less painful and less time-consuming than trying to recover your valuable files.
So how do I run VBA codes?
Okay, let’s start with a simple code snippet that we’d like to run:
Sub Hello_Excel()
MsgBox (“I love Excel Practice Online!”)
End Sub
Basically, every VBA program starts with the word Sub, followed by a name for this program. Every program also ends with End Sub
Between the first and last line, you can see the MsgBox command – This will show a message box (MsgBox) with the message “I love Excel Practice Online” π
To run this code:
- Copy all three lines to your clipboard (CTRL+C)
- Open a new Excel workbook (XLSX format is fine).
- As this is the first time you run a Macro, make sure all other Excel workbooks are saved and closed. Remember – Better safe than sorry.
- Next, you’ll need to open the Visual Basic Editor (VBE). This can be done by using the ALT+F11 Shortcut. You should now see the following screen:
- Click Insert – Module, to insert a new VBA module:
- Paste the code in the blank module, place your cursor somewhere in the highlighted line, and click Run (the green triangle, or use the ALT+F5 shortcut) to run the Macro:
- And here’s the result:
That’s it, you’ve successfully run your first Macro! π
Am I the VBA Master now?
Unfortunately, not yet π
But every journey begins with a single step – And this was your first step.
Now, progression is up to you. It’s a good idea to start trying to solve your everyday Excel problems using Macros. Just use Google.
For example, asking Google “How to unhide all worksheets VBA” will return the relevant VBA codes. Add the words VBA or Macro to your Google searches to make sure you get VBA solutions for your problems.
Also, follow our VBA section for useful VBA codes as well as VBA tutorials.
Good luck! π