Dear reader, A warm Welcome to you! Thank you for visiting this page. I welcome you in this our very first chapter which is Introduction to Macros/VBA. If you are a beginner you are on the right page. I am Ajay Parmar and I am teaching VBA for than a decade now. I love VBA because it has given me so much fame, promotions when I was working.
One important thing, you will see my VBA code images which are shown in this article in a bit different background color because I have customized it for my sake. I like a dark background and thick font so please don’t think much on that. It has nothing to do with learning.
Let us begin and be a Rock Star!
What are we Going to Cover?
1. What is a VBA and How it can help you in Excel Tasks?
2. Introduction To a VBA Editor
3. What is a Project Explorer?
4. What are the Modules, Sheets and ThisWorkbook Tabs?
5. Record a Macro
6. How to Run a Macro
7. How to Stop a Macro
8. How to Edit a Macro with some interesting scenarios
9. Assign the shortcut keys to your Macro
10. Adding Comments in a Macro
11. What is a difference between a Macro and VBA?
12. How to Select Cells using VBA
13. How can we select Sheets
14. Discuss Subscript Out of Range error in VBA
15. A Project – How to compile data from multiple sheets and paste into one sheet?
16. Limitations of Macro recording in the above Project and how to solve those limitations?
Are you ready for a fun ride?
1. What is a VBA and How it can help you in Excel Tasks?
VBA is a Visual Basic for Application – A programming language which we write inside Excel. We must know even other products of microsoft like MS Word,MS Powerpoint, outlook, Access Database also uses VBA however Excel VBA and Access VBA are in demand due to their data analytics and automation concept. We generally dont learn other applications VBA. Just for your information.
So, where ever i use VBA word in this article it will mean only excel VBA. Please note.
If we know VBA, we can fully automate every excel task. You name it and you find the code for the same.We will learn everything.
For eg. you can use Vlookup function in hundreds of files and lookup any information in just few seconds which otherwise can take hours of your shift.You can transfer the data from one sheet to other sheet, you can copy the data from 100s excel files including 100s sheets lets say each file has and put that in one excel file.You can automate your outlook emails, connect the databases , create software’s, make powerpoint slides in few seconds, connect ms word as well . There is so much to learn in VBA.
So, that is why i said, we can fully automate excel data the way we want. By the way, you can control websites as well . You can extract or download the information from any website provided it should be allowed. VBA allows the Selenium library which in itself is a web extraction tool . We call it VBA Selenium. Did I surprise you here?
2. Introduction To a VBA Editor
VBA Editor is a place where we are going to write a code. Even, if you record a macro it will show up in the vba editor. So, Let us explore this editor and make ourselves comfortable with it.
On excel sheet, press ALT+F11 or simply click on the Developer tab and under the tab ,see the button Visual basic(Refer to Image1 below) . Click on Visual basic button. You will be automatically directed to VBA Editor. Now, let us study the options which are available in this editor.
(Image1)
(Image2)
3.What is a Project Explorer?
Under Project Explorer, we see icons of Sheets and ThisWorkbook (Pls refer to image,above).
Depending on how many sheets we have in our workbook, we see the icons of sheets. Suppose, my sheet name is Asia Data so how this is going to show up in VBA Editor in project explorer. You shall see Sheet1(Asia Data). Sheet1 because it is the first sheet in my excel file and within brackets i see the real name of a sheet. if i change the sheet name, it will also change automatically. Sheet1 name will not change , That is the index of a sheet and we do not need to go into that at this stage.
Second icon that you see there is ThisWorkbook. Now, this is not a sheet name. This is a standard name and it always appers in vba editor like this and it is not going to be duplicated or repeated. if you open any excel workbook, you find that there is alwasya a Thisworkbook tab and it does not get repeated.
Now, first question which comes in our mind is so where do i write the code. Under sheet name or Thisworkbook window?
Surprisingly, you will not write code in any of these windows. You will write code under Modules.
You write more than 90% codes under Modules but it does not mean that we do not write code under sheet and Thisworkbook window/tabs.
Before, I show you where is the module(see image3) , I want to clear this confusion for VBA Beginners that when and why do we use Modules and Sheets and Thisworkbook windows.
4.What are the Modules/Sheets/ThisWorkbook tabs?
i) Modules: All program which we want to run manually are written inside modules. It means, if i want to run a code or program , i can do it. Point is, a program will not run by itself.
ii) ThisWorkbook: Here , we write conditional based program which runs by itself on meeting the criteria under which it is written, At this stage, we need not to understand this but still I am giving you one example. Suppose, I want to run a code whenever user delete a sheet in that workbook. So, it should run automatically if user delete the sheet. This is called event or self triggered macros. We cannot write them inside modules because as i said in modules code does not run automatically. it is a user who if want to run it , only then it will run. Now, last thing, why it is called thisworkbook tab because the code we write if sheet gets delted applies to entire workbook. Means, this will be applied to any sheet if user deletes it and not just one specific sheet. And yes, this self triggered code will be avialable only that excel file inside which it is written and not in any other workbook.
iii) Sheet Level: This is again a place where self triggered code is written. Means, they run automatically on meeting the specified criteria. Now , you may wonder what is the difference then between ThisWorkbook and Sheet level. Actually, what you write inside sheet will apply to that sheet only and not entire workbook unlike in Thisworkbook. So, let’s say you have 5 sheets and one sheet has a name Country Data. Now, if you write the code inside this sheet , the same condition , if sheet gets deleted, then it will be going to run only when this sheeet Country data will be deleted. It wont run if any other sheet gets deleted.
So, Events/Self Triggered macros are written inside ThisWorkbook or Sheets ,depending on our requirement and they are very powerful and need a great level of knowledge. Hope, it has made some sense to you, Frankly speaking, even if it did not make any sense, just don’t worry because these you are not going to learn till you know VBA very well. So, let us come back to Modules now.
If you are curious to visualize , pls see the below image3. I can really understand your feelings here. I also wanted to know more about it when i was learning. But ,please don’t spend much time. Not needed at this stage , as i mentioned earlier.
(Image3)
5. Record a Macro:
Click on the Developer tab and then click a Record Macro button. You can also use a shortcut key which is ALT+T+M+R . A pop up will show up and you need to write the macro name, enter a shortcut key and a description in the description box, and then click OK to start recording. Please see below image. You can allocate a shortcut key as well as i have explained in the video too.
(Image4)
Do you see one more option here “Store macro in:” This Workbook?
This is a by default option so please do not change it from dropdown,I will come to this point in another chapter. Just to get the right understanding , ThisWorkbook here is totally different from that VBA Editor which you saw previously, Here it only means that whatever macro we are recording or vba code writing should only be available in this workbook only and not outside this excel workbook,
We are not going to change this by default option.
In our video we recorded a small macro, you can see the video as well. That macro was simple to go to different cells and write few words and then jump from one sheet to another. Below is the same code that we recorded in a video.
(Image5)
Let us discuss few important things about this macro which we have recorded, first time.
Line1:
Sub MYFIRSTMACRO()
This is the beginning of a macro, Every time we write a code or record a macro , process is same. It starts with a word called Sub followed by a name which we have given at the time of a recording. So, if i write Sub Testing() – This means I have given it a name called Testing. There are few rules while giving a name to your macro but that we will discuss in Variables.
So, with sub if you see the last line (image5) you see End Sub word. This means it is the end of that macro. And, this automatically gets inserted whether you record a macro or write it by yourself. In simple words, every thing which starts it ends too. Very emotional statement 🙂
Line2:
Range(“A1”).Select
This line takes us to the A1 cell of a active sheet. This is beautiful thing about VBA that its syntaxes are understandable and it is like an english language. Syntax clearly says there is cell called A1 which is selected. I think we don’t need any further explanation on this.So, a question for you. How do you write if you have to select B10 Cell of an active sheet?Answer is Range(“B10”).SelectVBA is a case insensitive language so as long as there are no spelling mistakes we are fine.Range(“b10”).Select is also a right syntax. B letter here is written in small letters.Now , before you ask me what happens if i write Range word in capital or small like RANGE ?Very smart! You are really going to be a great coder, I can tell you.So, Answer is these are VBA standard keywords and they are always going to be re-adjusted in the way they are supposed to be. So, even if you write all letters in capital like RANGE or in small , the moment you finish the syntax it will change as Range word because its a VBA property and it always turns these keywords in the standard format which is that a first letter is capital followed by small letters.Same is with another keyword Select. If i write SELECT or select , the moment i press enter to go to the next line S will be a capital followed by small letters. Infact, every programming language is like that, They have fixed way of showing the syntaxes.
Line3: ActiveCell.FormulaR1C1 = “january”When you write a value in a cell , this is the line which macro generates. Don’t be confused or scared by this word FormulaR1C1. it has only one meaning here that the value which is written inside the active cell is january, When we will write the code , we will simply write ActiveCell.Value = “january”Lot of things we will prefer writing in our way, By the way R refers to row and C refers to column in R1C1. I really don’t thing you need to know more here about R1C1. Let us move further.Just to revise , if you write ACTIVECELL.VALUE = “HELLO” again it will be changed toActiveCell.Value = “HELLO”. Were you thinking HELLO word would also change to something like Hello, Nope. it is not . Because it is our constant and VBA has nothing to do with it, It is not VBA Keyword.Hope, you enjoying the show till now. :)😊😊😊
Line4:Sheets(“INTRO”).SelectNow, this is not line4 but just to keep a sequence here i have written it as line4 . And, this is our last line which now will understand. As you can see clearly it is selecting a sheet which has a name called INTRO here. This had happened in our recording when we selected this sheet. Now, if let us say you want to edit the line and write INTRO as intro , it is not going to affect the code in any manner, Case sensitivity does not matter in VBA.Question for you: How do you select Sheet which has a name called “Apple”Answer is we will just eat it and enjoy the fruit. Hahaha,,,just kidding. So, it is going to be Sheets(“Apple”).Select
6.How to Run a MacroRunning a Macro can be done in several waysUser who needs to run a macro to get the output:1. Using a shortcut key ,if we have assigned any2. By going to developer tab and hitting Macro button. Select the right macro and hit on Run button3. On a button , if we have inserted a shape from excel main menu “Insert” and have assigned the macro to that shape by right clicking on same. Pls See below image. Last option in the image is Assign Macro. On clicking on same,we see a window which displays all Macros and then you got to select the correct one that you want to run on clicking the button and OK.(Image6)
Choices for Developers who create the code – How they can run the code apart from above methods.1. In VBA Editor just click anywhere between Sub and End Sub and press F5. This shortcut key will run the code where it finds the cursor.2, In standard Toolbar we can click on Run button (Image7) and again your cursor should be anywhere between Sub and End Sub of the one that you wish to run.3. Run the code line by line using F8 key. This helps the coder to see what is happening in excel when each line executes. This is called Debugging method. You see, we run the code line by line to make it free from any bug.(Image7)
7.Stop the Macro:We can stop the macro by clicking on Stop button which was a Record macro before we started the recording. So, once recording begins , name changed to Stop and one can click when he has recorded everything.(Image8)
8. How to Edit a Macro with some interesting scenariosOnce again refer to image5 which has a small code. Imagine, after you finished the recording you are informed by your boss that months need to be given a abbreviation like Jan, Feb instead of full words.So, how to edit the code. First thing is we should know which module has that macro. Correct? Because you may have 10 modules and each module may have 10 macros each. So, we should go inside the right module and then we should look for our targeted macro and you will simply edit the changes. Just change the spellings like you do in excel. It is so easy. No need to do anything else, Just save the file and bingo! Your code now next time when you will run it will have a new outputs. So, it means we just need to know the macro name and we can edit it, Nothing simpler than that.Just for your clarity, new macro is shown in the below Image9 (see arrows). I want to make sure, you understand it fully with ease.
(Image9)
9.Assign the shortcut keys to your Macro
While recording a macro , we get an option and that is where we go and give the shortcut key.(Image10)
10.Adding Comments in a Macro
Comments are used in every programming language and it has different ways, In VBA we can simply put a single quote(‘) before the line and VBA treats it as a comment. As a good practice we can always write something important or useful about program/code for ourselves and for a person who may be running those codes and may do modifications as your back up.You can write comments above the code or just next to it. See the two lines in the below image. Hope, you got my point.(Image11)
One important thing, you cannot write before the code because if you do your whole line will be treated as a comment and eventually code will be treated as a comment too which is not the motive.What if we have 100 lines to comment? Think about it…very practical question. Isn’t it? 😊Doing it one by one manually makes no sense, You will then leave the VBA. Aren’t you? 🤣🤣🤣 We have a solution.Use your Debug toolbar as it has a comment icon too. Just select all 100 lines that you want to comment and then just click on the comment button from a Debug bar. Pls see image12.And you want to uncomment later same 100 lines just click again on the same button. That’s it.
(Image12)
Pls see Uncomment icon/button in the below image too, Remember, you see them in Debug Toolbar.
(Image13)
11.What is a difference between a Macro and VBA?
People have different opinions about this question but it really does not matter. In my view, they are same. A macro is a one which you record and VBA is a language which we write instead of recording. But, Ulitmately both are same because a macro also generates the code only. It is true that it has its own style and it generates a lot more code than one writes but at the end of the day it is written inside modules only and it can be run on f5 or f8 mode. Nothing changes.Do you remember there was a line which a macro has generated ActiveCell.Formula = “Month”. I told you we generally write ActiveCell.Value = “Month” which is more understandable.But here in this case both will print a month value only in the active cell of a sheet.When i said A macro generates more lines and in its own style , please see the below image. In this macro I have only selected a A1 Cell and gave it a yellow color and made it bold. Nothing more than. But you can see many extra lines are added. If i want i can delete them. Basically, macro like to put everything related to the activity you did. Here , every possible thing which falls around formatting is being added here. And last thing ,did you see With and End With words? That is a style a macro prefer. As a programmer I can write it too and may be not. It is on me. I will discuss on this later. I promise.
(Image14)
12.How to Select Cells using VBA
We have discussed this above too. We just use Range syntax followed by a Cell address.Range(“A1”).Select is a line which selects the A1 Cell of a sheet.I will teach you more ways to select the cell which a macro does not use and trust me ,its quite worth knowing that.
13.How can we select Sheets?
Selecting a sheet is same as selecting a cell. Just that naming terms get changed. You go and write sheet name within the parenthesis and prefixed sheets word.Sheets(“Apple”).SelectApple is a name of a sheet and sheets is a standard vba word which we have to write.Note: Sheet name always comes within double quotesAnother way of selecting a sheet is by its index. For example in your workbook there are 5 sheets and apple sheet is the third one so you can just go and give the index as well.Sheets(3).SelectNote: While giving the index to a sheet we never go and put the double quotes and index is always a number, not a text. CorrectCaution: If some moves the apple sheet somewhere else , it means it is no more a third index sheet now. So,be careful and wise while using this type of an approach. I will teach you in the loops section where and why we should use index or other with pros and cons. But, till then please wait.
14.Discuss Subscript Out of Range error in VBAIn this lecture we have seen an error “Subscript out of a Range”. Why this comes and how to solve it?if i am asking VBA to go and select Apple sheet but later on let us say a name of a sheet is changed to something else or it is removed from excel file then VBA throws this error.Sheets(“Apple”).Select ‘ this line will throw an error if this sheet name does not exist.How to Solve? See,we’re discussing so much in this intro section. I know,how to take care of you. Fundamentals are so important Dear Reader.😊Immediately, you need to either correct the name of a sheet in Vba window or in excel so both of them can talk to each other effectively and a love can be prevailed. 🙂
15.Let us do a Project – How to compile data from multiple sheets and paste into one sheet?
This project if you like to see practical is discussed in our video. So, when you have time just go through it however I am explaining all of it line by line.Task: How to bring the data from multiple sheets into a one sheet. Data Compile is what we are going to do here with Macro recording. Yes, you heard me right. Till now, we are not doing coding, Just simple recording a macro, But, how we have to record the steps , we need to be careful, I am going to explain each line here. Also, let me paste the image here of this entire project that how did we create it and then let us understand line by line too.
Now, Let us dive into the code line by line.
Sub COMPILEDATA()
‘ Macro starts here’ First of all let us open the Blank Excel file from our location – here it is my desktop – file name is dummy.xlsx. This is the file where we need to copy and paste the data one by one from sheets
Workbooks.Open Filename:=”C:\Users\acer\Desktop\DUMMY.xlsx”
‘Let us put the focus back on this macro file so we can now copy the data from its sheets.’This is how macro recorded when i used Alt+Tab key to bring back the focus from Dummy.xlsx excel workbook,.
Windows(“Introduction to VBA.xlsm”).Activate
‘Now, Go to the INTRO Sheet..select this sheet which is in VBA.xlsm workbook.
Sheets(“INTRO”).Select‘Go to the top of the range from where we would like to copy the data- A1 cell of intro sheet
Range(“A1”).Select
‘This line will select the entire data in downward direction – even if you increase or decrease the range’ This is a shortcut key CTRL+Shift+Down ArrowRange(Selection, Selection.End(xlDown)).Select
‘This is now from that selected range(for e.g. A1:A20), going to copy the data to the right side .’This is a shortcut key CTRL+Shift+Right Arrow
Range(Selection, Selection.End(xlToRight)).Select ‘Up till now, Using shortcut keys while recording a macro we have selected the entire table’from top to bottom and from left to right. Correct?
‘Now Let us Copy the data using Ctrl+C
Selection.Copy
‘ Using ALT + Tab key you know we can select the right workbook.
So, let us go to Dummy.xlsx file
Windows(“DUMMY.xlsx”).Activate
‘It was a new workbook so it has its first sheet selected by default and first cell.
We just need to say Ctrl+V to paste
ActiveSheet.Paste
‘ V important step now…pls pay attention – After pasting our first copied data we need to ensure next time a new data should be pasted right below the first data- the very next blank row coming after last row of a data.
‘Go to A1 Cell of sheet and then use ctrl+Down arrow key, You know this shortcut key. This takes us to the last non blank cell of our data
Range(“A1”).SelectSelection.End(xlDown).Select ‘ CTRL+Down Arrow key
‘ Now , we know the last non blank cell but we need to go one cell down because that is where from our new copied data should go and paste, So, we are using offset command to move one row down in same column.This line we have written ourselves. Our macro failed to generate this line.
ActiveCell.Offset(1, 0).Select
‘ Come back to VBA excel workbook again and select second sheet
Windows(“Introduction to VBA.xlsM”).Activate
Sheets(“MACRO REC”).Select
‘ Do the same process again…from A1 Cell, select the data in downward and right direction
Selection.Copy‘Go to Dummy excel file and paste the data. Remember! we have already selected the right blank cell so we just need to paste the data.’We have already used ctrl+down arrow key to be on the right cell
Windows(“DUMMY.xlsx”).ActivateActiveSheet.Paste
‘Now again , we need to select the blank cell which comes just after the last non blank cell.
ActiveCell.Offset(1, 0).Select‘ Now activating the VBA.xlsm workbook to select the new data
Windows(“Introduction to VBA.xlsm”).Activate
Sheets(“DATA”).Select‘Range(“A1:C10”).Select ‘ this line we never use because this is a static line,if data rows increases or decreases this line will not be able to copy the modified data. Range(“A1”).Select ‘ Go to the top cell of a data and select the entire data
Application.CutCopyMode = FalseSelection.Copy ‘ Now copy the data which is selected
Windows(“DUMMY.xlsx”).Activate ‘ go to the dummy file where we need to paste
ActiveSheet.Paste ‘ this is simple a paste
Windows(“Introduction to VBA.xlsm”).Activate ‘Go back to intro file for user ease & say its done
Range(“A1”).Select
Sheets(“INTRO”)
End Sub
I hope, this article has made you happy and excited about VBA – How it works in excel. VBA can automate Outlook , Powerpoint, Sql server,Access , SAP , Websites and god knows how many other application /databases. If someone say it is not a benefical langauge then remember, I am working on VBA from 20 years and I have been hearing this all time. You can see my other articles too here. Have fun and consider learning as a hobby and not a target. Let us learn till we are alive.Learning is a journey and not a destination.❤️❤️❤️
