I’ve come across a lot of Excel users who are terrified of the term VBA in Excel. What I’ll say is, it’s not scary to begin with, and after some familiarization, you can master your work and cut down on clock-time by a large margin!
My article today aims at breaking down the VBA and its procedures and explaining the codes for users who are still beginners at it.
Also, it’ll highlight who will benefit from VBA and in what ways. I’ll also be giving you some idea about the Macros that come hand in hand with the VBA.
To aid your comprehension, I’ll be adding visuals so that you can connect it to your own work and Excel sheet.
All in all, going through this article will be educational and I hope it’ll be enough to induce your interest to delve deeper in the VBA field.
Contents
What is VBA In Excel?
VBA is the acronym of Visual Basic for Applications. It’s basically a programming language that Microsoft developed that allows users to write codes that can automate tasks in Excel.
For instance, there are limited numbers of formulas, functions or other features in Excel. What VBA does is, it lets you create unique and specific codes that perform distinct tasks that are necessary for your workflow.
In a way, you can interpret it as a means to customize Excel’s abilities and enhance it to some extent to match your requirements. Once you get acquainted with VBA, you’ll find many doors opening for you to sort out and analyze your data beyond the confinement of original Excel features.
Excel VBA Interface Breakdown
Excel VBA opens up in a new window with an old-fashioned interface that dates back many years. You’ll find some resemblances with the older versions of Excel because Microsoft never truly updated the way it looks.
You need to have a good understanding of this interface to be fully functional without strolling through like a blind person. I’ll help you pick out the separate locations so that your workflow paces up drastically!
- Starting off, you’ll find a ribbon at the top where you’ll find all the most basic options like File, Edit, Windows etc. options. Underneath them, there are some shortcut buttons that you can access from the top part or using specific shortcuts.
- At the left hand side, you’ll find the VBA project tab and the properties right underneath. In the project tab, you can locate the Excel objects like your workbook or sheet. Selecting either will show all the details in the properties box.
- What’s important is that, at the top of your project tab, you’ll find the first button that says ‘View Code’. Clicking this will open up the code window in the open space where you’ll input all of your coded language to run upon completion.
How To Access VBA In Excel?
It might come to you as a surprise, but you won’t find the VBA options in your Excel software until you tweak the organization a little.
To access VBA in Excel, you have to:
- Click File and select Options (if you can’t see Options, click More and it’ll show the Options button)
- At the left, track down the Customize Ribbon option and open it.
- Under the Choose commands from: tab, select the Main tabs functions.
- There, you’ll notice the Developer button. Highlight the Developer parent without opening it and click on Add at the right hand side.
- For further adjustments, use the Up or Down slider at the very right to place your Developer tab anywhere you like on your ribbon!
From this Developer tab, you’ll find all the options to access your Visual Basic or Macros buttons. Visual Basics will open up your VBA window directly. You can also use the Alt+F11 keyboard shortcut to access VBA without going through any of these steps!
How To Run VBA Commands?
Basically, all of your VBA commands work using the Visual Basic window. Bring it up and create a new module at the left tab. Upon opening the module, you’ll find a text window at the right hand side.
In this window, you’ll have to manually type in all of your VBA commands and codes. When you’re satisfied with your work, press F5 on the keyboard or the Play button at the top. If you record this with a Macro, then you can run it from the Macro tab as well!
Why Should You Use VBA?
You might know how VBA can aid your work and that lack of knowledge can deprive you of so many opportunities. This portion will break down the reasons you should use VBA and learn it properly.
To Save Time
You might not know, VBA in Excel is guaranteed to save you time if you’re planning on working with large sets of data over a prolonged period of time.
Most of the time in VBA will be invested in writing the code according to your expected outcome. It might require you to trim, filter and sort your data so that VBA codes can take immediate effect.
Once you’ve prepared your code, you can simply use it for all of your similar data-set within the same project. It cuts down on your time so much that you’ll actually enjoy working with large spreadsheets.
Automate The Workflow
Imagine you have to sort a large chunk of data manually by implementing filters and applying Excel formulas. If your workflow repeats a few patterns, then you’re in luck!
Excel VBA lets you create codes that reflect your workflow and allow you to include functions, formulas, filterings and every other Excel feature.
Once you’ve prepared the code, you can simply go to your worksheet and run the code wherever you want it to, and it’ll automatically perform the task for which you’d have to go through six or seven steps each time!
Be An Efficient Worker
An efficient worker is the one who works the quickest without error and generates the most refined outcomes. Excel VBA gives you the opportunity to become the most efficient employee in your office.
Since it’s automated, there’s little room for error. And as it cuts down on your time, you’ll be able to get a lot of manual labor done in a very short amount of time.
VBA in Excel seems more interesting now, doesn’t it?
Utilize Excel To The Fullest Extent
You might appreciate all the features and functions in Excel, but if you don’t include VBA to your workflow, you’ll actually be missing out on a lot of opportunities.
So, if you want to call yourself an Excel specialist and act accordingly, you must pick up VBA skills to be regarded by everyone as one. Only then you’ll understand the extent of Excel and how it has unbound room for implementing creative approaches with data.
Who Is VBA For?
If you’re not sure if VBA is the right choice of path for you, let me describe who are the ones who would be greatly benefited if this was introduced to their Excel workflow.
For Employees Who Work With Datasets Regularly
In corporate offices or business firms, working with huge datasets is a daily practice. It requires a lot of repetitive, boring tasks that can easily be sorted with VBA, thus it’s a handy tool for these individuals.
For Teachers Who Create Result Sheets
If you are a teacher, accumulating course-wise number sheets and developing result sheets for each of the students isn’t an easy task and it requires much attention as well as patience. Using VBA in this regard will automate these procedures and generate outcomes a lot faster than usual!
For Students Who Assess Numeric Data
There are many students who have to work with numeric data and apply mathematical formulas that aren’t directly available in Excel. You can use VBA to combine Excel functions that’ll produce the same outcome swiftly!
For Social Workers Who Analyze Field Data
Social workers gather a lot of quantitative data that needs further time & effort to sort and analyze. Applying VBA in these tasks can speed up the entire process and deliver an error-free result!
Some Handy VBA Shortcuts
There are shortcuts for each of the tasks in VBA. Let me walk you through these shortcuts so that you don’t have to manually move & click your mouse every time you want to access a feature.
| Shortcut | Description |
| Alt + F11 | It opens the VBA Editor |
| Alt + F8 | Show all the macros |
| Alt + F4 | Close the VBA window |
| F7 | Enter code editor |
| F1 | Show help |
| Alt + F11 | Alternate between VBA Editor & Excel |
| Alt + F6 | Toggle between last open windows |
| Alt + F5 | Start the error handler |
| Home | Go to the start of the line |
| F10 | Turn on the menu bar |
| Ctrl + Space | Automatically complete the space |
Macros: An Alternate To Learning Programming Language
Macros can often cut-down on your coding time or even spare you the pain of learning to code in VBA. Macros record your flow of work and save it under Macro names.
For instance, if you are on your data set, you can use Macro recording to apply your filtering, functions, formulas or however you usually work. Make sure not to make any unnecessary adjustments.
Going to a separate spreadsheet carrying that macro and clicking on it will make Excel apply the same workflow you just recorded into your new sheet and save you a bunch of time!
In the following section, I’ll be showing you how you can use Macro very briefly for your convenience!
How To Use Macros? Basic Guideline
I’ll be showing you the most basic example of using macros in your Excel sheet. You can achieve the same outcome using VBA in Excel but you’ll need to type everything out manually.
Let’s say, you’ll be generating the top row for each of the columns that you’ll have to use in a large number of worksheets. Instead of creating each of them manually, you can record a macro and do it once, and the macro activation will apply the same production in all the other sheets.
Let me show you!
Firstly, you’ll have to click on the cell where you’ll start your work from. Once highlighted, go to the Developer tab and click Record Macro.
Once you click it, every action you do is going to be recorded, so make sure to keep your workflow neat & distinct and avoid performing any unnecessary tasks.
What I do is, I type in the details that I need as column headers, like the ID, Name, Date of Birth etc. at the very top. Then, I select all of it and change the formatting, like Bold, Font color, Center alignment, Outside border etc. Once I’m satisfied, I go back to the Developer tab and hit Stop Recording.
What Excel does is, it records every bit of adjustment I just performed in-between the starting and stopping of recording. Excel initially asks me to name that macro, which I did so that I can distinguish one from the other.
Whenever I need to add this top header in other sheets, I go to the Developer tab, select Macros and highlight the macro I just created. Hitting the Run button on the right hand side will produce the same column header in any of the spreadsheets of this document!
Note: Macro recorder doesn’t include any alteration with cell height, width or similar adjustment settings. This is something you’ll have to do manually.
VBA & Command Button Tutorial
Suppose you have a large data sheet that you need to sort using the filter from the Data tab. But you want to create this for someone who doesn’t know how to use the Excel filters. What to do then? VBA is going to rescue you!
There’s an option in Excel that allows you to place a button within your spreadsheet. I’ll shortly get to the point about what this button does. But first, let me show you how to insert a VBA code to make your filters work automatically with one click!
Open your spreadsheet containing the data that needs to be sorted first. Then go to the Developer tab, under the Control ribbon, click Insert>Button. Excel will let you create a rectangle with the shape of your choice. It’s going to be the button you’ll be interacting with later on.
Let’s say, you want to filter out only the sales record of Samin. So make sure to name the button while inserting to ‘Samin’ so that you can identify it later on without any confusion.
When you hover over the button, you’ll find the option to type in. Click there and delete the existing text to replace it with ‘Samin’. It’s not mandatory for the button text and name to be the same, but it’ll remove confusion and make your life easier.
Then, you’ll have to insert the filter code. Open up the Visual Basic window and locate the project window at the very left of your screen.
At the blank space, right click and go to Insert>Module. It’ll create an option called Module 1, double-clicking on it will open the code sheet. There, type out the following code:
Sub Samin()
‘
‘ Samin Macro
‘
ActiveSheet.Range(“$A$4:$G$704”).AutoFilter Field:=1, Criteria1:=”Samin”End Sub
Let me break it down for you. These codes begin with ‘Sub’ and end with ‘End Sub’. In-between, after the Sub part, write down the name for your code: ‘Samin’ followed by two brackets ().
Use apostrophes to break between lines. Using the ‘ActiveSheet.Range command, select the sheet range that you want your VBA code to work on. Apply the AutoFilter Field command set to one, and the Criteria set to Samin (the filter name in your spreadsheet).
After this, go to your spreadsheet and right click on the button you had created. Select Assign Macro, you’ll find a new macro that you just coded in your VBA named Samin. Highlight that and hit OK.
When you click that filter button, you’ll see your data set filter out only Samin’s sales records. Similarly, create a Filter button and use the following code to clear out the filtering to get your original data back:
Sub Clear()
‘
‘ Clear Macro
‘
ActiveSheet.ShowAllDataEnd Sub
Keep on repeating these steps to create unique filter buttons in your Excel using VBA!
VBA Examples With Codes
Let me show you a couple of examples with VBA codes that’ll help you automate your workflow. Using these codes that generate a macro, you’ll be able to apply these on your Excel sheet at any time without having to write down formulas at each instance.
Result-Sheet Generation
Suppose you have a result sheet to generate and classify whether your students passed or failed. You can easily create a VBA code using the IF formula to speed up your workflow!
The code should resemble this:
Sub Result()
‘
‘ Result Macro
‘
Range(“C2”).SelectActiveCell.FormulaR1C1 = “=IF(RC[-1]>70, “”Pass””, “”Fail””)”Selection.AutoFill Destination:=Range(“C2:C6”), Type:=xlFillDefault
End Sub
Here, I need the result to start appearing from the C2 cell, thus I need to provide a Select command. Then, I’ll implement the formula on the active cell, thus the ActiveCell command.
With the FormulaR1C1 =, I’ll dictate the IF formula using my condition that’ll dictate based on the achieved marks whether that course was a pass or a fail.
Then I’ll use the Selection.AutoFill command to fill in the same formula within the destination range between C2:C6. I’ll include the End Sub command to dictate that the code is completed.
Once done, open the Macros tab and select the ‘Result’ macro. At the right hand side, hit the Run button, and Excel will automatically generate the Pass/Fail result!
Minimum-Maximum Identification
If you have large data sheets from which you’ll need to figure out the minimum and maximum numbers, you can easily create a macro using VBA codes and apply it to your every worksheet with a single click!
You can use the code below to draw ideas into your own workflow:
Sub MinMax()
‘
‘ MinMax Macro
‘
Range(“C54”).SelectActiveCell.FormulaR1C1 = “=MIN(R[-52]C[1]:R[-2]C[1])”Range(“C55”).Select
ActiveCell.FormulaR1C1 = “=MAX(R[-53]C[1]:R[-3]C[1])”
Range(“C56”).Select
End Sub
Like the previous code, this one too applies the formulas of MIN and MAX separately within one code. After getting through with the code and range, simply hit Run in the Macro tab with selection on the MinMax bar.
These are only minor examples of what you can do with VBA in Excel. You’ll be able to reach advanced degrees with practice and in-depth knowledge about Excel Macros and VBA Codes.
Thoughts
I’ll encourage you to keep on trying to include VBA in Excel whenever you feel the need of automating your work and giving you some free-time after swift execution.
You won’t get it right straight away, so don’t pressurize yourself too much. If you feel that learning VBA language will assist your workflow, then go for it.
VBA specialists are always valued highly in their field of work and it’s a skill that sells more than a lot of other soft skills that you won’t be able to highlight as much as you can with this.
Lastly, you should keep on practicing VBA to perfect your work. In Excel, nothing beats keeping on repeating a task, especially the ones that require the mixture of creativity with finesse.
In due time, you’ll see yourself coding in VBA as effortlessly as typing out a letter to your friend on your keyboard!
Further Reading: