Here you are going to learn the way how you can become VBA Programmer from MIS Executive job. We are going to provide you easy ways to become VBA Programmer and Excel VBA / Macro Programmer Qualities. When you have already became expert in Excel formulas, template and dashboards. Once you learn excel formulas, reports then you are very near to become a Excel Macro or VBA programmer. There are two ways to become a VBA or Excel Macro expert. First one recording macro procedure and second one is Writing VBA coding as per VBA standards. All you need to do initially if you have support, you can start with small project and get help from senior guy. Else you can start small excel activity automation with the help of google. Initially it is very tough to do each and every line of the code. But once you have done each and every thing in that particular small project then you learn many things which repeats in every Macro or VBA program. VBA programs are very tough when you do not start. Once you start all VBA programs become easier for you. This is kind secret behind VBA Program or any other programming language. Keep this in mind to start learning VBA Programming.
Excel VBA or Macro Programmer Qualities:
When you get one activity for automation purpose, first we need to see value added things and non value added things in that particular activity. This is essential task for VBA specialist. And once we identify non value added things then we have to eliminate those things from the activity first by automating those sub processes. Once we do this, then exact process would be remained. And we have to see alternative ways to reduce steps or providing accuracy or creating control checks. Also there is no necessary to write code for every activity, because sometimes without writing code we can automate processes like creating Excel formulas or creating templates or creating some dashboards or any other way. We need to choose simplest way to automate the process always.
As a VBA developer, when we get the automation requirement from any of the team members, we need to identify the Judgmental cases and non judgmental cases in order automate. As of now we have not find any best solutions to automate the judgmental cases. Hence we have to automate all the non judgmental cases. Sometimes judgmental cases would be in between the process. At that time, we need to automate till the non judgmental case ending then we have to stop the middle judgmental case for user to do that manually. Once he completes the judgmental case manually. Rest of the process would have been automated. That’s why user can just run the macro and finish the task.
Judgmental case is nothing but thinking ability. Like there will be situations like we have to think and then we need to do that thing according to the input.
Example 1 (Judgmental case):
we have to study the comments and need to do the activity based comment sentences.
Example 2 (Judgmental case):
we have see the image and do the activity based on the image. Here VBA Macro cannot see or think as of now. Hence it cannot be automated.
Examples (Non Judgmental case):
Copy pasting data, repetitive tasks.
Once we automate the process by using Excel macro programming, we need to check for error possibilities to arrest them and keep the control in order to check by user whether the particular task done by macro correctly or not. If we can do this, your programming style would be excellent and reliable macros are being developed by you.
Modifying the Existing Macro:
Sometimes, we need to modify the existing macro which is very tedious task to VBA Developer. Then we need to check the code line by line first and understand the process which was automated by different VBA Macro specialist. Once you we understand the process, then we can respond to associate who has given the task to yourself to do modify the macro. Based on existing, you need to build the new code in case if it requires, or you just do simple changes to and deliver the project to given person. This is how you need handle the existing macros while modifying.
Here you are going to learn the way how you can use the Index & Match excel formulas combination (Reverse Vlookup) with examples in order to get the Hlookup, Vlookup outputs and more outputs in excel spreadsheet.
Index & Match combination also known as reverse Vlookup is powerful function in excel which is very powerful than the Vlookup. Because it has lot of features to prepare excel reports. We can find any column header while preparing report using this function. Vlookup will have only one feature like pulling the right side value based on left side selected value. Here Reverse Vlookup or Index & Match combination can perform the right side value based on left side selected value, left side value based on right side selected value. Hlookup and Vlookup functionalities have been included in this combination function by Microsoft team.
Left Side Value Based On Right Side Selected Value
If you have data with Case, Contact Name, Customer Name in the same order. If you need to pull the Case details with Contact Name or Customer Name, you must use the Index & Match function. Please find below the screenshot to understand the data which is there in the “Index & Match Combination” Sheet
Now in the Result sheet, you have Contact Names to pull the Case details in the next column as shown in the below screenshot and here itself you can see syntax of the formula in “Result” Sheet
Please find below the syntax to understand the formula that we have used to pull the result sheet case information using the contact name (Left side value based on right side selected value).
If you see the above, Index Formula syntax has Array, Row, Column properties. In order to find the particular row of the contact name, we have used the Match function within the index formula in the place of row property. When you say array, you need to select from Case column to Contact Name column in the “Index & Match Combination” Sheet as you can see above the Syntax Screenshot. Once you select then you need to use the Match formula in the Row property, then column number can be specified. Here in this case column number is 1 (Case). Now let’s understand Match formula. Match formula is very important excel formula which can pull the matched values based on given input. It has Lookupvalue, Array, matchtype (0,1,-1) properties. We have just used to function within the index to pull the left side value based on right side selected value which is impossible by Vlookup function. Once you arranged the formula in one cell, the same can be dragged till end row of the result sheet data. Once you drag, you can see “case” details which are available in the Array property related sheet here in this case “Index & Match Combination” Sheet. Hope you understand the first example of the Index & Match combination or Reverse Vlookup function in Excel Spreadsheet.
Right Side Value Based On Left Side Selected Value
Now you are going to do the Vlookup formula functionality with Reverse Vlookup function. In order to do that let’s use the same above example related data and get the Customer contact values using Case details. Please find below the screenshot to understand more better way.
Here we can see the function to understand the Vlookup functionality with Index & Match Combination or Reverse Vlookup. Hence this combination of formula is very strong among all excel formulas. Now let’s understand the syntax using below the screenshot.
We have just taken the array property of index formula as “Index & Match Combination” Sheet Column A to Column B as those are related Case & Customer Name. And we just reversed the requirement first Customer Name is there, hence we just need to pull the Case details in the next column. In order to do this again we have used the Match function and in that now Lookvalue property would be Case, array property would be “Case” column in the “Index & Match Combination” Sheet that is 1st column, matchtype property would be 0 (zero) as we need exact matched row. Once it is done, then column of the Index formula would be 2. Because Customer Name column is 2nd Column in the data source. Screenshot is available to understand the Syntax and match with this explanation.
Formulas of above both cases are similar but slide differences. As per first example we just took the 2nd column as base in order to get the left to right and as per second example, we just reversed our requirement 1st column as base and 2nd column as result one. First try to understand properties of the Index formula. Then understand properties of Match formula. That’s it you can play around excel spreadsheet with this formula. As of now you just saw only half part of the Reverse Vlookup. Here let’s summarize the properties meaning in Index and Match formula to get in dept knowledge of this combination formula. Array means data set which has all required columns here in our case 1st and 2nd columns of “Index & Match Combination” Sheet those are Case and Customer Name columns. Row means which is the row do we need to get into the result, columns means which is the columns that we need refer to get the value. This is all about properties of Index formula.
Now let’s know about Match formula properties. LookupValue means the value which we need to look, here in our case Result sheet related Case value. Array means again in the “Index & Match Combination” sheet in which column result is available that particular column alone. Matchtype means it is just exact match or greater than match or less than which are indicating with 0, -1, 1 values. Here in our case we have used only 0 (Zero) for exact matched values.
See Also :
Note: Stay tuned as we are going to provide you more examples on this to make you understand better.
Here you are going to learn the way how you can modify the Vlookup functionality, when there is a requirement to do in your Excel report using User defined function technique.
In order to do this, you need to add the module in the excel by pressing the Alt+F11 key or selecting the view menu then select the view macro option.
Once you get the module, you need to specify the function name and functionality to modify the Vlookup according to your requirement. This is a very simple process to excel beginners also. Any excel function can be modified using this method.
Here you are going to learn the way how you can get the last row of the range using various types techniques in Excel VBA programming. Excel vba also known as excel macro programming. Once you find the last row, you can do many things to automate the activity. Hence finding last row in the given excel range is very important concept in Excel VBA. There are many ways you can get last row in the range. 1) Using usedrow 2) using cells 3) using Range in the respective spreadsheet.
- Using UsedRow:
- Using Cells :
- Using Range :
Here I am going to teach you the way how you can use the selenium webdrivers in excel vba in order to scrape the websites information into excel spreadsheet as per user requirement.
Creation of named ranges are explained in deep as we have already seen in the vlookup tutorial.
Previously when we were using excel 2003 this named range was used frequently to automate the excel activities using Excel macros. In stead of ranges, we were using the named ranges as those fixed with particular range of cells, rows or columns. In order to create named range, we just need to select the required range of cells that you want to name, then go to cell address bar and give the desired name, once you give press enter tab. That’s it next if you select that range, you can see the given name for that range in the same cell address bar. You can check the below screen shot for your reference.
This is the simple method which we can use. we have another method for creating the named range. That option is available in the Formulas Menu -> Name Manager. You can click the name manger and select the desired sheet and range of cells in the respective text boxes which will be available in the Name Manger option, then you can give the desired name. You can check the below screen shots for your understanding purpose.
Once you get the above name manager option, you can click the new option or edit option(if you want to change any given name) to name the range of cells, rows or columns as per your requirement. Once you click the new or edit option in the name manger, you get the below option box
Hope you understand basic excel option named range. If you have suggestion, please comment in the below comment box to encourage us.
There is an option called Goal Seek to find the value of principle amount or number of years or rate of interest when tow values available among there 3 then we can find the 3rd value using this option. Usually this option very useful in Banking sector. Also when you situation to calculate the PNR/100.
This is one of excellent option in the excel to easy calculation process for interest related activities.