Faysal Easy Excel

Excel VBA is a Programming language, and though it is a language, it should support Array Programming. Yes, it does. In Excel we can use Array Formula. We can also use the power of Array in Excel VBA. What is an Array? In a simple definition, the variable, which contains two or more values under the same variable, is called an Array. In programming language sometime you need to use lookup function in a specific list. To do this use an Array. An Array is a way to store more than one value under the same variable name. An array looks like this when it is declared: Dim yourName( 5 ) as Integer In the above code, Dim is short for the word Dimension and it allows you to declare variable names and their type. Then we used a custom name yourName to store values. But how many values can store in yourName ? We declared (5) that means 0 to 5 = 6 values. If we declared (1 to 5) then it will not count the 0 (zero), so that you can store 5 values under the same yourName variabl...

Loops are great tool in Excel VBA to repeat your actions. If I tell you a simple example about loop then it would be better for you to understand. I believe that you have already worked with some WorksheetFunction like SUMIFS, INDEX-MATCH, COUNTIFS, SUM etc. And few times you did not locked the Lookup_Value or Criteria due to you wish to use the same function for multiple times but different lookup_values and criteria. In Excel VBA Loops are great tool to repeat your actions how many times you have set. While you always need the same command on different cells with a specific cell difference, then you can use Loop. It will reduce your work and time and give the result faster. Types of Loops There are different types of Loops in Excel VBA. Based on your task you have to decide which Loop will match better for your task. In Excel VBA There are 5 different types of Loops are available. These are: 1. For ... Next 2. For ... Each ... Next 3. Do ... While 4. Do ... Until 5. Wh...

For best understanding you need to practice with real data problem. From this point of view, I'm going to solve a String problem from real life experience. Problem: The problem is a raw data after downloading from data server, the SKU (Stock Keeping Unit) code wrongly encoded as "SKU-US-90/10/45". To make a SKU wise Stock Report you need to convert the SKU code to correct format like below image: Image 1: RawData SKU and Corrected SKU Solution: The problems are: Converting US to USA "/" to "" "-" to "" Use the below code to convert all the wrong SKU to correct SKU: Sub CorSKU () Dim x As String Dim i As Integer For i = 2 To 8 x = Cells(i, 1 ).Value Cells(i, 3 ).Value = Replace(Replace(Replace(x, "US" , "USA" ), "/" , "" ), "-" , "" ) Next i End Sub Here is the result: Image 2: Result got from VBA ...

String is an important variable type. I will try to discuss here about it. As the 'String' name suggest, it is used to hold strings of text. You have to work with Strings of text in Excel VBA a lot in next. So, it's time to learn in depth about it. I know now you will ask your mind, wait a minute, what things should you know about simple String variable? Don't it just store TEXT values? So, why "depth"? Well, my answer is Yes. You are absolutely right. But in "depth" I mean some others option related to String, must know for better work. Simply use a Dim command to store a String Variable like below: Dim x as String And now while you are going to store a variable against the String variable simply use Double Quotes ("") mark like below: Dim x as string x = "This is String text" If you set a Date or Number or any others variable within Double Quotes ("") against a String variable, it definitely NOT s...

WITH is another useful statement in Excel VBA. It usually used to ignore using the same object name for changing various aspects. For example if you would like to change the (1) font color (2) font size (3) font italic etc. then you simply write the codes like below: Cells( 4 , 5 ).Font.Color = RGB( 254 , 00 , 00 ) Cells( 4 , 5 ).Font.Size = 12 Cells( 4 , 5 ).Font.Italic = True But this would be more easier if you use WITH statement like below, where Cells(4,5).Font used only once. You then type the property you need after a dot: With Cells( 4 , 5 ).Font    .Color = RGB( 254 , 00 , 00 )    .Size = 12    .Italic = True End With With Statements are quite intuitive, so we don't really need to say too much about them. But just remember: if you're typing the same object over and over, you might do better to use a With ... End With statement. Writing with WITH statement the VBA code will run faster due to it will not read the same o...

SELECT CASE is another statement for decision making while you have 3 or more condition. Definitely you can use IF statement instead of SELECT CASE statement. The convenient thing is that of applying the SELECT CASE is, it makes the macro reading capability faster than IF statement. Rather than using IF statements, SELECT CASE statement is very clear in logical operation. It supports nested condition same as IF statement. In others programming language SELECT CASE statement is also known as SWITCH Statement. Example 1: This is simple example of SELECT Case statement. The working process is as below: Image 1: Simple Select Case Statement Now write the below codes in your module and run it: Sub selcase1 ()        Dim x As String        Dim y As String          x = "Microsoft"        y = "Excel"        Select ...

In this example you will learn about the cell alignment in Excel VBA. The Problem: Assume that, We have a table that shows, Mobile Brand Name, and selling quantity as below: Image 1: Sample data What you need to do is, create a Grade column and show the grade using IF statement and put a VBA Button. Also add the cell alignment and full row background color based on grade. The Grade scale is: If above 90000, A+, 70000, A, 50000, B+, 30000, B, 10000, C+, F. The Solution: Design the table like below: Image 2: Data Table Now in Module write the below codes: Option Explicit    Sub grade_analysis ()       Dim x As Integer       Dim y As Long       Dim z As String       For x = 2 To 9          y = Cells(x, 2 ).Value          If y >= 90000 Then  ...