Coding by Learning

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...

 Hello Readers, Welcome to Coding by Learning !!! 😀  This is one of the important part of VBA programming We know VBA editor is available by default in all MS-office applications. Lets take excel as an example. Every excel workbook has VBA editor. We can view this by navigating Developer -> Visual basic Or by short cut Alt + F11 Now this VBA editor is from excel so we can write any VBA code for automating excel activities.  But when you want to automate any other office applications or any external applications (Like Webpage, SAP, Mainframe..etc) from excel VBA, then this “References” feature comes into picture In this example I am going to connect Ms-Word with my excel VBA code Navigate Tools -> References Select Microsoft Word 16.0 Object library Now you can see I am getting intellisense for MS-Word Like wise you can connect any MS-office or External applications to VBA code using References libraries. Note : To add references to code, there are 2 things imp...

Hello Readers, Welcome to Coding by Learning !!! 😀  VBA has the feature of locking the code using password. Since MS-Office applications come with VBA coding editor by default, It is always a good practice to lock the code before deploying it. It will help the code from getting deleted/modified by users

Hello Readers, Welcome to Coding by Learning !!! 😀  Happy learning & Happy coding!!! Comment and share this post to your friends and colleagues 😀

Hello Readers, Welcome to Coding by Learning !!! 😀  Here we are going to learn about finding last row and last column in a excel sheet. Let's see the following example. There is a table in the sheet having details of fruits bill There are 6 rows and 5 columns in the table. Lets find this by VBA code Output Best Practice : Always use "Application.rows.count" or "Application.Columns.Count". This will work for any excel version. Happy leading & Happy coding!!! Comment and share this post to your friends and colleagues 😀

Hello Readers, Welcome to Coding by Learning !!! 😀  what is use of Option Explicit Option explicit is making declaration of variables mandatory. Developer cannot use a variable without declaring it. Also duplication variable names will be treated as error. How to write Option Explicit Way 1 Use the Option explicit text at the top of the module Way 2 Go to VBA editor Navigate  Tools -> Options. You can see following dialog box. Check the dialog box Require Variable Declaration Happy learning & Happy coding!!! Comment and share this post to your friends and colleagues 😀

 Hello Readers, Welcome to Coding by Learning!!! 😀  Here see the VBA program for showing "Hello World" message displayed in message box. Steps to be followed 1. Open Excel 2. Press Alt+F11 to launch VBA editor 3. Insert a module 4. Write the following code 5. Save and press F5 Output Happy learning & Happy coding!!! Comment and share this post to your friends and colleagues 😀

Hello Readers, Welcome to Coding by Learning !!! 😀  Most of the programming languages has the concept of passing parameters to functions in different ways. Python, C, C++ and Java programming languages have the concept in the name of Call by value and Call by reference. In VBA , It is arrived using keywords ByVal and ByRef. Difference between ByVal and ByRef Now lets see the concepts with an example Here in the example, I have written a sub procedure for calculating sum of 2 numbers. I have created 2 functions "add2Numbers_byval" and "add2Numbers_byref" for differentiating the concepts. Output as follows :- Look at the output. The value of iNum2 is same using ByVal and It is modified using By Ref. Happy learning & Happy Coding !!! Comment and share this post to your friends and colleagues 😀