Coding by Learning

 RPA stands for Robotics Process Automation. A leading technology in the current IT world. It will help us to automate the manual application activities of human by some tools with low code or no code. Some Examples of RPA Tools Benefits/Advantages of using RPA tools It makes automation in a fast way.  Automation projects are peak in demand for business operations teams when it comes to process improvement. It comes with analysis tools/features RPA automation can be scheduled either attended or Un-attended way very easily. Development effort will be less. So implementation cost will be less. Easy to learn and easy to start even by non IT students with the minimum technical knowledge. We have feature to call other scripts of Python, VBA macros and JavaScript. Drawbacks/Disadvantages It comes with license cost which may make project cost more when comparing open source software. It leads in reduction of logical thinking. Developers will not concentrate on programming concepts. I...

Hello Readers, Welcome to Coding by Learning !!! 😀  Take this scenario. There is an automation requirement where there is a master data file. User manually copy paste some range/cell to master file. Lets consider user needs to copy from 20 files to master file. Developer  automated this manual entry/copy paste activity using VBA code. But after the deployment user feels while running the code, it keeps changing the input file, copying  required data, going to master file, pasting data to master file, going to input file, copying required data, pasting data to master file and the activity continues until loop runs. As a user system looks like flipping pages of book which does not look good. Basically users wants to see only output after running automation. System screen should stay in the VBA macro file until getting output. How you make the code user convenience. Here VBA concept screen updating comes into picture . It is question might be asked in almost all the VBA int...

 Hello Readers, Welcome to Coding by Learning !!! 😀  In this post lets look how to copy or cut data from one range to another range by VBA code How to copy Data from one range to another range by VBA code Lets look at the example table in sheet named "source_data". I have a table which has list of vehicle names. Data is present in 'A' column. Now I want to copy entire range of ‘A’ column to ‘B’ column. We know we have excel short cut Ctrl + C and Ctrl + V. But how to copy range from one range to another range by VBA code VBA Code: Output: Lets run the program (press F5). We will get following output. Data from range A:A is copied and pasted in to range B:B How to Cut Data from one range to another range by VBA code Take the same above table as example. Data is present in entire 'A' and 'B' column. Now I want to cut the data from range 'A:A' and paste into 'C:C' column. We have excel short cut Ctrl + X and Ctrl + V to do the same. But h...

Hello Readers, Welcome to Coding by Learning !!! 😀  VBA   is widely used language for Microsoft office automation. Since it is more used in Excel automation, it will add an advantage in interviews related to Excel reporting, Excel Data analytics, MIS teams openings (Management information system) and many business enabled functions (Teams will support data, back office works of it's parent companies).  It is widely used in Automation field. Because VBA is capable of automating manual activities of Word , Excel , Access , Power Point , Outlook , SAP , IBM Mainframe , Web automation .  Also it can be used along with Selenium to automate browser activities. Now coming to the question Which course one should do with VBA to get the better job. It is based on the field which we are choosing.  If you like to go for Data analyst, then I would suggest please learn ‘ R ’ language, Python data analytics (Libraries like Scikit learn, Pandas, NumPy), Tableau and Micr...

Hello Readers, Welcome to Coding by Learning !!! 😀  Lets see interview questions on VBA array concepts. 1. What is array? Array is a group of elements of similar data types 2. How to declare a array variable? Syntax : Dim arrayVariableName(size) As Datatype Ex : Dim fruits(5) As String 3. What is ReDim keyword in VBA? Using ReDim keyword developer can redefine the size of an array 4. How to use ReDim in VBA? Syntax : ReDim arrayVariableName(size) As Datatype   Ex : ReDim fruits(10) As String 5. I have already stored some elements in the array, what will happen if I redefine the size? When you redefine the size of an array without preserve keyword, then already stored elements will be erased from the array 6. What is preserve keyword in VBA? Preserve keyword in VBA helps to retain already stored elements in the array even though it is redefined. Example :  ReDim Preserve fruits(10) As String 7. What is option base in VBA? Option base is related with array concepts....

Hello Readers, Welcome to Coding by Learning !!! 😀  6. What is data type? As name says it defines type of data the value holds that can be stored in to variable 7. What are different data types available in VBA? VBA has multiple data types in order to handle data for the programming. Some of the major data types which often be used are: integer, single, long, double, range, date, variant, object 8. What is Variant data type? Variant is the special data type which can accept any type of data. Variable which is declared as variant will get data type based on the value it is assigned 9. How to declare a variable in VBA? Using Dim keyword variable will be declared in VBA.  Syntax is :   Dim VariableName As Datatype 10. Is it possible to declare a variable without data type in VBA? If yes, What data type it holds? It is possible. If developer declares a variable without mentioned any data type then it will be considered as variant data type.  Happy learning & ...

Hello Readers, Welcome to Coding by Learning !!! 😀  1. What is VBA? VBA stands for Visual basic for application. 2. Who developed VBA?  VBA is created my Microsoft developers team.  3. Where developer can write VBA code? All office based application has in-built with VBA editor. Developer needs to write their code in the editor. How to access VBA code editor/IDE Way1 We can access it using keyboard short cut Alt+F11. Way2 Navigate File -> Options -> Customize Ribbon -> click on the check box next “Developer” under Main tabs - > Ok Go to Developer Menu and click on Visual Basic 4. What is basic object model of excel application? Application -> Workbook -> Worksheet -> Range/Charts 5. What is option explicit? Option explicit making declaration of variables mandatory. For more details refer my blog link :  Option Explicit Happy learning & Happy coding!!! Comment and share this post to your friends and colleagues 😀

Hello Readers, Welcome to Coding by Learning !!! 😀  This is one of the important formulae in excel application Vlookup function is used for the purpose of getting corresponding data for the given lookup value Syntax :      =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) Explanation : Lookup value is root value for which we are going to find corresponding data value, Table Array is the column range between lookup value column and corresponding data column Col index num is the count of total columns of table array finally range lookup, it is optional value which accepts TRUE (approximate match) or FALSE (Exact match) Let see with an example : Data sheet in the example work book has the detail of Indian states and its capital cities. Now look at the sheet named "Example". I have listed some of the Indian states here. Now I want to get the capital cities of respective states in the sheet. Lets use VLOOKUP formula as per syntax. Happy learning & Ha...

Hello Readers, Welcome to Coding by Learning !!! 😀  In our last post we have seen what is Visible, Hidden and Very Hidden modes and how to do that using excel option. Please read post:  https://codingbylearning.blogspot.com/2022/05/excel-sheet-visible-hidden-and-very.html Here in this post we will discuss more on this topic. You can get a question both Hidden and Very Hidden are helping user to hide the excel sheets. Then what is Difference between Hidden and VeryHidden and why we need this 2 options. Lets look at following Pictures. Here sheet 1 is in Visible mode. Sheet 2 is in hidden and Sheet3 in Very hidden mode. Now go to excel work book and do right click - > Unhide You can see only Sheet2 is visible and we cannot see Sheet3 in this list. Because sheet3 is on Very Hidden mode. So the concept here is if sheet is hidden user can view the sheet and can unhide it. But if a sheet is in very hidden user cannot even see the sheet is available on excel workbook. Also we can...

Hello Readers, Welcome to Coding by Learning !!! 😀  This is some interesting topic. We are going to learn the three visible modes of excel sheet. Visible, Hidden and Very Hidden Visible Mode User can view all the sheets at visible mode.  Hidden Mode When user hide sheet it will be changed to hidden mode.  There are 2 ways to make an excel sheet to hidden mode Way1   Do right click on the top of the sheet name on tab and select hide option Way 2 Go to VBA editor (Alt+F11). Select the sheet in the project explorer Now see the last property called Visible and select the option 0- xlSheetHidden Very hidden Mode Go to VBA editor (Alt+F11). Select the sheet in the project explorer. Now see the last property called Visible and select the option 2- xlSheetVeryHidden Let's see with some more points on this topic in the next post. Visit :  https://codingbylearning.blogspot.com/2022/05/more-regarding-excel-sheet-visible.html Happy learning & Happy coding!!! Comment an...