Excel with Macros

Currently I am using Excel 2010. So we are going to create simple macros here.

What is Macro ?
A very simple answer is,
Through macros you don’t need to perform same task or action on same type of data. Macros are the built-in feature of Excel that enables user to record different tasks and apply them over on some another portion of the data. It works on the back-end, recording each and every mouse click, keystrokes that you do while performing the task. Down the line if you want to done with the task in a same way, you will just have to play the recorded Macro back.


Suppose I have a worksheet in excel and I want to display product "Grade" and "Remark" according to product "Quality" column. If product quality greater than or equal to 70 then Grade should be "A" and Remark should be "Better" automatically, as smart macros fired in background.



 Exploring step by step :

 Step1 : Go to Start -> Microsoft Excel 2010 , blank worksheet will be open for you.

 Step2 : Click on View -> Micros , See below image

 


Step3 : Click on Record Macros, pop up window will be open, you can set your macro name,
shortcut key , store macro in dropdown option and description and click Ok. See below image

Step4 : Now set fx function on Grade column as =IF(B2>=70,"A","B") and set Remark column as
 =IF(C2="A","Better","Average").

Step5 : Click on IF function and click OK button to set condition for Grade column , See below Image.

Step5 : Click on IF function and click OK button to set condition for Remark column , See below Image.

   Save Excel file Type as Excel - Macro Enabled Workbook (*.xlsm).

  Step6 : Get Grade and Remark column value by giving input to Quality column through UI.
  -> Go to Start open  Microsoft Visual Studio 2010.
  -> Click on New Project you can choose Windows Or Web base application. I am exploring with windows base. See below Image :

Step7 : Go to Solution Explorer -> Right Click On References -> Add Microsoft Excel Lib reference from COM, See below Image :

Step8 : Adding Dropdown list (selecting input for Excel) , and two Text Boxes (to getting output from Grade and Remark column from Excel) See below Form1 Image :

Step8 : Adding Dropdown list collection value.
Right click on DropdownList -> Click on Properties -> Items property and add Items as,
See below Image :

Step8 : Double click on Dropdownlist. Add below code to Dropdown selected index changed event.

Code:

private void cboInputBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            ExcelApplicationClass excelApp = new ExcelApplicationClass();
            ExcelWorkbook workbook = (Excel.Workbook)excelApp.Workbooks.Add(Missing.Value);
            ExcelWorksheet worksheet;

            // Opening excel file

            //workbook = excelApp.Workbooks.Open(@"C:\Book1.xslx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            workbook = excelApp.Workbooks.Open(@"C:\testing proj\Book3.xlsm", 0, false, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            
            // Getting first Worksheet

            worksheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);

            // Setting cell values


            ((Excel.Range)worksheet.Cells["2", "B"]).Value2 = cboInputBox.SelectedItem.ToString();
            txtGrade.Text = ((Excel.Range)worksheet.Cells["2", "C"]).Value2.ToString();
            txtRemark.Text = ((Excel.Range)worksheet.Cells["2", "D"]).Value2.ToString();
            
            workbook.Save();
            workbook.Close(0, 0, 0);
            excelApp.Quit();
        }

Run The Application and select Input value from Dropdownlist you will get output at Grade and Remark column from excel. Also open an excel to see all values are getting reflect.

Copy Full Code :

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;

namespace ExcelOperation
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
           
        }

        private void cboInputBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            ExcelApplicationClass excelApp = new ExcelApplicationClass();
            ExcelWorkbook workbook = (Excel.Workbook)excelApp.Workbooks.Add(Missing.Value);
            ExcelWorksheet worksheet;

            // Opening excel file

            //workbook = excelApp.Workbooks.Open(@"C:\Book1.xslx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            workbook = excelApp.Workbooks.Open(@"C:\testing proj\Book3.xlsm", 0, false, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            
            // Getting first Worksheet

            worksheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);

            // Setting cell values


            ((Excel.Range)worksheet.Cells["2", "B"]).Value2 = cboInputBox.SelectedItem.ToString();
            txtGrade.Text = ((Excel.Range)worksheet.Cells["2", "C"]).Value2.ToString();
            txtRemark.Text = ((Excel.Range)worksheet.Cells["2", "D"]).Value2.ToString();
            
            workbook.Save();
            workbook.Close(0, 0, 0);
            excelApp.Quit();
        }


    }
}

Out keyword in C#

What is out.. ? Out keyword is like ref but here you must not initialize the variable before passing to calling method. ,

Purpose of this keyword when you want to returned one or more than one value from method.
Simply we explicitly copies the reference or address from calling method store at stack and again rewritting it to that references or addresses and return back to calling method from method called.

Here are some snaps, that will explore the work flow of Out keyword.

Look at Snap No.1 :

  Typically program will start to execute from Main method, here i have taken three variables and i will pass  these variable to calling method with out keyword, means i am passing addresses through argument to called method parameter.


Look at Snap No.2 :

Here method is going to call with out keyword ...

 Look at Snap No.3 :

 Here we explicitly copying reference or addresses from calling method to method parameter  para_One,para_Two and para_Three.

Picturized memory locations for para as :

     para_One                 para_Two                  para_Three

null	null	null
20001	20002	20003

 Look at Snap No.4 :

 I have assigned text to para_One,para_Two and para_Three.

 Look at Snap No.5 :

 Greetings !!! all values from method got returned and rewritten to address [20001], [20002], [20003] which are previously passed through calling method. 

 What we did ? Simply copied that text to address loaction.

 arg_One                       arg_Two                  arg_Three

argument 'arg_one'add = 20001 ..          value part	argument 'arg_one'add = 20002..          value part	argument 'arg_one'add = 20003 ..              value part
20001    add part	20002    add part	20003    add part

   Look at Snap No.6 :

  OutPut:

Full Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

            namespace ConsoleApplication1
             {
              class out_keyword
               {
                public static void method(out string para_One,out string para_Two,out string para_Three)
                 {
para_One = "argument 'arg_One' address = 20001 from calling method and" + Environment.NewLine + "copied into called method parameter 'para_One' and rewritten to arg_One";
para_Two = "argument 'arg_Two' address = 20002 from calling method and" + Environment.NewLine + "copied into called method parameter 'para_Two' and rewritten to arg_Two";
para_Three = "argument 'arg_Three' address = 20003 from calling method" + Environment.NewLine + "copied into called method parametere 'para_Three' and rewritten to arg_Three";   
                 }
        public static void Main(string[] args)
        {
            string arg_One, arg_Two, arg_Three;

            method(out arg_One, out arg_Two, out arg_Three);
            Console.WriteLine("{0}\n\n{1}\n\n{2}",arg_One,arg_Two,arg_Three);
            Console.ReadKey();
        }

    }
}

ref keyword in C#

By using 'ref' keyword we keeps the same address of two different variable.

without ref Scenario : When you does at normal functioning means without use of ref keyword, pass argument value from calling method to method defination at that time there is different addresses for both argument and parameter.

ref Scenario : ref parameter in method defination refers the same address of argument at calling method and parameter at method defination.

Below are some images that will explore ref keyword program with flow :

Look at Snap no. 1: Program will start from Main (..) method , here I am assigning a text to string variable.

Look at Snap no. 2: From previous snap, there is wrong statement so, i want to correct that statement by using ref keyword.

Look at Snap no. 3: calling method

method(ref femaleVoiceIsLikeMale);

here, argumnet name femaleVoiceIsLikeMale followed by ref keyword, means that argument value goes into perticuler memory loaction picturized as below :

Female voice is exactly equal to Male voice : Wrong statement	- value
200000	- address

argument value and address passing to method body parameter at class ref_keyword.

Look at Snap no. 4: argument value and address passed to method body parameter, but here I am going to change the argument value that was passed from calling method as :

 "Female voice is exactly different than Male voice : Correct statement"

 Look at Snap no. 5:

 Now memory location value part will reflect as below [take refrence here from Snap no 3]:
  

Female voice is exactly different than Male voice : Correct statement	- value
200000	- address

Greetings !!! address [20000] remain same but value got changed.


 Look at Snap no. 6:

 returning changed value at same address ..

 Look at Snap no. 7: Here we proved smart use of ref keyword
 Output :

Copy Full Code:

namespace ConsoleApplication1
{
    class ref_keyword
    {
        public static void method(ref string femaleVoiceIsNotLikeMale)
        {
            femaleVoiceIsNotLikeMale = "Female voice is exactly different than Male voice : Correct statement";
        }
        public static void Main(string[] args)
        {
            string femaleVoiceIsLikeMale = "Female voice is exactly equal to Male voice : Wrong statement";

            method(ref femaleVoiceIsLikeMale);

            Console.WriteLine(femaleVoiceIsLikeMale);

            Console.ReadKey();
        }
    }
}

Indexer in C#

By means of indexer you can index to your class object.I would say it nothing but an array.More flexible to accessing the class simple array syntax.

Look at snap No.1 : Here indexer Demo object is created as ' i ' of class Indexer.

Look at snap No.2 : Here I am trying to assign value 5 at perticuler index [3] of Object ' i ' of class Indexer.

Look at snap No.3 : How value is going to set at set {..} accessor of  ' this ' indxer place, at class Indexer.

Look at snap No.4 : Value 5 assingned at perticuler index [3] of Object ' i ' of class Indexer.

Look at snap No. 5 : Here I am trying to assign value 6 at perticuler index [5] of Object ' i ' of class Indexer.

Look at snap No. 6 : How value is going to set at set {..} accessor of  ' this ' indxer place, at class Indexer.

Look at snap No. 7 : Value 6 assingned at perticuler index [5] of Object ' i ' of class Indexer.

Look at snap No. 8 : for loop rotation to reach at last index position at intArray[..] decleared at Indexer class.That will print 11 number of time from 0 - 10 at commond prompt.

Look at snap No. 9 : Here set values are returning from get {..} accessor of  indexer ' this ' place at Indexer class.

 

Look at snap No. 10 : Values are set at prefered index location of Object ' i ' of class indexer.

  Look at snap No. 10 : Indexer Output at Command prompt.



Copy Full Code:

namespace ConsoleApplication1
{
    class Indexer
    {

        int[] intArray = new int[11];

        public int this[int index]
        {

            get
            {

                return intArray[index];

            }

            set
            {

                intArray[index] = value;

            }

        }


        public class Demo
        {

            static void Main(string[] args)
            {

                Indexer i = new Indexer();

                i[3] = 5;

                i[5] = 6;



                for (int j = 0; j <= 10; j++)
                {

                    Console.WriteLine("Value at Index of [{0}]={1}", j, i[j]);

                }

                Console.ReadKey();

            }

        }

    }


}

Virtual function in C# .

Virtual function is useful in C# where you have feasibility to modify the function by means of override the same function at derived class place.Look into provided images step by step.

                                        
                                          Here is output at command prompt.!!!



                                         

Copy Full Code:

namespace ConsoleApplication1
{
    class Program
    {
        string n = string.Empty;
        virtual public string myData(string s)
        {
            n = s + "Kant";
            return n;
        }

        class Program1 : Program
        {
            public override string myData(string s)
            {
                return base.myData(s) + " Patil";
            }
        }

        static void Main(string[] args)
        {
            Program1 p = new Program1();
            Console.WriteLine(p.myData("Shashi"));
            Console.ReadKey();
        }
    }
}