How to extract unique value from Filter in excel vba macro

Hello folks,

 

Welcome back...today will teach you "how to get unique values from set of values in excel using macro coding"

Open your excel file and create some duplicate data … (Don’t wait open and create now)

 

Here is my  duplicate data…



Now don’t wait for me, open VBA and do coding … (I can’t explain again the vba part so just visit How to write VBA coding  if you have any doubts)

If you are in developer mode then insert new module to do coding.

Paste the below coding’s on module (If you are a good Developer then you should do Copy Paste)

Sub UniqueValue()

Dim d As Object, c As Variant, i As Long, lr As Long

Set d = CreateObject("Scripting.Dictionary")

lr = ThisWorkbook.Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row

c = Range("A2:A" & lr)

For i = 1 To UBound(c, 1)

d(c(i, 1)) = 1

Next i

With ThisWorkbook.Worksheets(1)

.Activate

.Range("G1").Resize(d.Count) = Application.Transpose(d.Keys)

.Range("h1") = d.Count

End With

End Sub

Press F5 and Run

Gotcha! You got the final result

 

Now time to the coding part explanation …..If it is feel bore than I can’t do anything 

What is Sub? 
 
A Sub called procedure or a block of code, sub is executed in response to an event (Don’t ask what is Procedure? and what is Event? If you need explanation please comment)

Hint: Sub does not return any value.Sub UniqueValue()
End Sub



Variable Declaration:



Dim d As Object, c As Variant, i As Long, lr As Long

You can declare also like 

Dim d As Object
Dim c As variant 

Here I am going to set d object as a scripting dictionary ….Eeeeeeee don’t ask again what Script Dictionary ? check here my old post… (what is Script Dictionary?)



 d = CreateObject("Scripting.Dictionary") 

 Now, find last row of the particular column. (any one of these method)


lr = ThisWorkbook.Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row

 lr = ThisWorkbook.Worksheets("Sheet1").Cells(ThisWorkbook.Worksheets("Sheet1").Rows.Count, "A").End(xlUp).Row

lr =ThisWorkbook.Worksheets("Sheet1").UsedRange.Rows(ThisWorkbook.Worksheets("Sheet1").UsedRange.Rows.Count).Row

lr = ThisWorkbook.Worksheets("Sheet1").Range("A1").CurrentRegion.Rows.Count

okay, now some tricks…

We have a variable C as a variant data type. I am going to assign range of duplicate values in C

c = Range("A2:A" & lr)

Now use the below loop to assign unique values in dictionary object d . If you understand Scripting Dictionary then you will understand this loop.

For i = 1 To UBound(c, 1)

  d(c(i, 1)) = 1

Next i

 Reassign the last row and paste the value using transpose method

.Range("G2").Resize(d.Count) = Application.Transpose(d.Keys)

 


If you think above program is totally waste and then go with this

 
Use this coding ............

Public Sub Test()

ActiveSheet.Range("A2:A65536").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ActiveSheet.Range("B2"), Unique:=True

End Sub

 

One line code will solve your problems. But it will work only in filter

What is Scripting.Dictionary? What is Array? in VBA excel macro

Hello all,

What is Scripting.Dictionary?

Nice question … don’t worry am here to answer. Before that What is Array?

Array is a data structure (Don’t ask what is data Structure) that contain group of element with the same data type.

Example:

Dim ArrayExample

ArrayExample=array(“Fail” ,”Mark”)


Or


ArrayExample(0)=”Fail”
ArrayExample(0)=”Mark”


(If you need to know about array and dimension comment below)


Now Dictionary :


Dictionary object is similar to PERL associative array…(Google it What is PERL array?)
Dictionary object can stored any value in the array and each stored items associated with a
unique key. We can retrieve data using key.( see the example then you will understand more)


Example:

Dim DictionaryExample   (Don’t write Dim DictionaryExample() )

Set  DictionaryExample = CreateObject(“Scripting.Dictionary”)

DictionaryExample.Add  “key”, “Fail”

DictionaryExample.Add  “keeey”, “Mark”


See the example now and read the explanation again.


(I know about your laziness read here) The value “Fail” and “Mark” stored in array, the stored items “Fail” and “Mark” associated with a unique key “key” and “keeey”. We can retrieve data “Fail” and “Mark” using key “key and Keeey”.


Program for array: (Do copy past like a programmer)

Sub ArrayExampleProgram()

Dim ArrayExample

ArrayExample = Array("Fail", "Mark")

Debug.Print ArrayExample(0)

Debug.Print ArrayExample(1)

End Sub

Sub ArrayExampleProgram1()

Dim ArrayExample(1)

ArrayExample(0) = "Fail"

ArrayExample(1) = "Mark"

Debug.Print ArrayExample(0)

Debug.Print ArrayExample(1)

End Sub 

Program for Data Dictionary:

Sub DictionaryExampleProgram()
Dim DictionaryExample
Set DictionaryExample = CreateObject("Scripting.Dictionary")


DictionaryExample.Add "key", "Fail"
DictionaryExample.Add "keeey", "Mark"


Debug.Print DictionaryExample.Item("key")
Debug.Print DictionaryExample.Item("keeey")
End Sub




(bye byeeeeeeeeee......)

 

How to write Macro coding? How to run macro coding ? How to see macro coding? How to Enable Developer Mode?

How to open VBA and run macro program in Excel

How to See Macro Coding?

Open New excel File …. And Press ALT + F11 it will redirect you to the coding window.

Else open Excel and find Developer mode and press Visual Basic.

Step: 1

How to enable Developer mode on excel?

OMG… if you can’t able to find Developer mode then go to File - > Options on your Excel And find Customize Ribbon and check Developer. Now do the above process to view the coding window.

Again Step 1:

Step 2:

How to Write Macro Coding? How to Run Macro Coding?

Click “ThisWorkbook” on project window (Left side )

And write your coding like below one (Don’t copy paste just 3 line you can type) ,Now press F5 to run macro coding.

Coding:

Sub Test()

Msgbox “Hello World”

End sub

hello world program in java servlet (Dynamic web application)

                                               Java hello world program....using servlet

Before that ask yourself, What is servlet?

WHAT IS SERVLET  for this question I have many answers .

Servlet is Application Programming Interface what we called API (kind of interfaces )
                                                        Or
Servlet is a technology
                                                       oR
Servlet is a Programming language used to create web application
                                                        OR
Servlet is a Class it works like a server (Giving response to the incoming request)

oK today we learn hello world program in java servlet later we learn something  in-depth.


Before going to see hello world in servlet just navigate java hello world in eclipse that would easy to know the difference between java and java servlet

Step 1:

Open your eclipse click file -> New -> and select Project
(don't scold me .... I know you know thizzzz.....)


Step 2:-

Now You can see the above window  Click web -> Dynamic web Project
(finally .... :-) )

Step 3:-

Give your project name....if you don't like your project name just give mine

Step 4:-

You can create, Edit, and Remove the folder in the above window...
(just try it yaar....)


Step 5:-

Now you can see your Context root and directory... just check the below check box.
(This will automatically create web.xml in the project)


Step 6:-

Now you can see your project in your project explorer....


Step 7:-

In your application navigate to the src folder and right click
Click New->Class


Step 8:-

Source folder name is the name of your folder which contain all the source

package  (what is package?)  name  you can create more package (folders) by using dot (.)


Step 9:-

 

 

Go to your work space you can see the folder structure and Your Hello java file ....



Step 10:-

You can see your program in notepad



Step 11:-


You can copy from here....

package com.servletHelloWorld.hello;

import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

 Yup, now what is import ? and why we use import?

import is a keyword, class libraries can be imported by using import keyword

example :-      

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse

or

import javax.servlet.*;
import javax.servlet.http.*;

Both are same ... * will take everything form the library otherwise just mention the libraries like above.

Step 12:-

 

 

here we go...

what is ....?

public class Hello extends HttpServlet {

}

I think you know public  (if ur not click and read)

Hello is the class name ... in Step 8  you gave Hello in the Name field and modifiers public.

what is  extends ?

Extends is a keyword used for extending .... we create a class Hello (consider this is a Sub class) and we extends HttpServlet  (this is super class)

we are extending the sub class form the super class ,it maybe difficult to understand don't  worry we ill discuss this with IMPLEMENTS..


ok now what? let's see inside the class

public void init()
{
}

This is the method called init() called only once ...this is for one time initialization
this method called when the first time of servlet created.


public void doPost()
{
}

doPost has no limitations on parameter ,faster and secured

public void doGet()
{
}

doGet only limited data ,request shown in address bar.

public void destroy()
{
}

we destroy the objects using this destroy method

Step 13:

This is the coding you can copy the coading from here..

package com.servletHelloWorld.hello;

import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class Hello extends HttpServlet {

public void init() throws ServletException
{
 super.init();
}

public void doGet(HttpServletRequest req,HttpServletResponse res) throws ServletException, IOException
{
 res.setContentType("text/html");

 PrintWriter output = res.getWriter();

 output.println("<h1>Hellooo</h1>");
 output.println("<h2>Helloo</h2>");
 output.println("<h3>Hello</h3>");
}
public void doPost(HttpServletRequest req,HttpServletResponse res) throws ServletException, IOException
{

}
public void destroy()
{
super.destroy();
}
}

Step 14 :

Yep here we are ... don't ask where...


GO BACK TO STEP 5 and read inside the red box...


yes that will generate the web.xml file now we have to open the xml fileeee.....and copy paste the below coding or do type manually (copy and paste I won't tell to any one)



<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xmlns="http://java.sun.com/xml/ns/javaee"
 xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
 id="WebApp_ID" version="3.0">

  <display-name>servletHelloWorld</display-name>

 <servlet>
<servlet-name>Sample</servlet-name>
<servlet-class>com.servletHelloWorld.hello.Hello</servlet-class>
 </servlet>


 <servlet-mapping>
 <servlet-name>Sample</servlet-name>
 <url-pattern>/*</url-pattern>
 </servlet-mapping>
</web-app>


Okay ...  <?xml version="1.0" encoding="UTF-8"?>   this is xml version
you have to create xml name space and schema location with id and version...


  <display-name>servletHelloWorld</display-name>
display  the name of ur project

 <servlet> </servlet>

this is the method to create servlet name and class ..inside this  <servlet> tag write servlet name and class

<servlet-class>com.servletHelloWorld.hello.Hello</servlet-class>
goto step 8 and u can fine the class name ...u have give the proper package name with class name.

Mapping 

 <servlet-mapping> <servlet-name>Sample</servlet-name>
 <url-pattern>/*</url-pattern>
 </servlet-mapping>
servlet mapping name and servlet name should same....

url pattern is the pattern to display the page

Step 15:

start the server

Step 16

Click finish

Step 18:

yeahhh you run the java servlet program....












 

what is public? what is static? what is void? what is main? what is string args[]? what is system.out.println()? in java

                                                           Hiiiiii... am back to tell what is?

ok see the code once again (not the C O D E ):

C O D E:
          package com;
          public class HelloJavaClassName
          {
          public static void main(String []args)
          {
          System.out.println("Hello World");
          }
          }

what is public?

public class HelloJavaClassName

we called this (Public, Private, Protected) access modifiers ...and also this is a keyword so we can not use this as variable. I hope you know what is  variables if you are not I do not care ha ha ha...

Public is like Public, a variable or method is public means that can access by any class

Ex:--
                  public int a;

 this is a public variable this variable can access from anywhere form your entire application so we called this as global variable

oK dude! what is 

public class HelloJavaClassName
{

}

This is called class , it can be defined as template it describes the object behaviours
 

ok then what is object ?

Object is an instance of a class it  has its own states and behaviours.

ok yaaarrr now I can understand what is class but inside the class something is there what is that?

public class HelloJavaClassName
{
public static void main(String []args)
{
System.out.println("Hello World");
}
}

This is what we called method,  a class can contain many method  u can c d keyword  main so this is main method. It is always public because it has to be accessed by the runtime.

yaa u can create your own method inside the class ... but listen you method name should start with smaller case (naming conversion).

for example:

public class HelloJavaClassName
{
public void shouldStartWithSmallCase()
}

this is called method name shouldStartWithSmallCase()

What is static?

Static is also keyword, if You use static keyword to the variable that variable value is static to the entire application.

public class HelloJavaClassName
{
static int pi=3.14159265358979323846264338327950288419716939937510582097494459230781640
}

The pi value is, Oh! sorry you can use 3.14 this value never going to change so we can declare this pi variable as static, for your entire program the pi value is 3.14 only this is we called static variable

static variable gets memory once at the time of class loading...


oKeY! why we use public static void main(String []args) ?

This is the main function, any doubt on that if any read from first. Main function should run first , the static keyword trigger the main function for first execution. so every program started to run from main class. That means main class always run first.

what is void ?

This method just print helloworld so we are not going to return any value here, void means that the method has no return value.

what is string args[]?

string and args is keyword ,java takes every values as string so we use the data type string.args[] is argument array of console line values stored in the [] array argument.
I know it is little bit difficult to understand study about array and data type then u can understand little bit more...

what is system.out.println()?

what is class? what is package(check on previous post)? what is method?

 

If you know the answer for the previous questions then you can understand system.out.println

 

System  is a class in the package of  java.lang

 

out  is the instance of java.io.PrintStream

 

println is the methosd of  java.io.PrintStream

 

This will help to print the output......

 

ok session is end today c u sooon...

  

Java hello world in eclipse

Here, i am going to  discuss about very basic java learning blah blah blah...k lets start the hello wold program in java using eclipse.

This is the first step :

Open your eclipse click file -> New -> and select Project

Step 2:

select the java project...

step 3:

now u can see the window ... just give the project name and click finish

step 4:

click your project  name ..Oooops right click your project name and again new -> Class

Step 5:

Package: yah that is package , I can see that but what is package?

package is  similar to different folders on your computer ...( i can not explain about  folders and computers if you want to know mail me...) 
you can create packages (folders) to write your class , i gave [] com [] in the package name

Name: it's your class name ...give it and click finish

Step 6:

your package and class created ,now type the code... or copy paste it

package com;
public class HelloJavaClassName {
 public static void main(String []args) {
        System.out.println("Hello World");
     }
}


Now press the play button or press  Ctrl + F11 you can see the result in the console window...



Yah ! you run your first java program but
what is public?
what is static?
what is void? 
what is main?
what is String args and what is []?
what is System.out.println();?

try to search on web else wait for the next post.....  

 

 

Login Application in VBA Excel Macro

                                                                  
Hi am karthikeyan k, Software Developer here i post  How to create Login page in vba  with more effective way..

Design  the login page...

coding...

Public Username As String  

Public Password As String  

Public i As Integer  

Public j As Integer  

Public u As String  

Public p As String   

  

Private Sub CommandButton1_Click ()  

    Application.ScreenUpdating = False  

    If Trim (TextBox1.Text) = "" And Trim (TextBox2.Text) = "" Then  

        MsgBox "Enter username and password.", vbOKOnly  

        Else If Trim (TextBox1.Text) = "" Then  

        MsgBox "Enter the username ", vbOKOnly  

        Else If Trim(TextBox2.Text) = "" Then  

        MsgBox "Enter the Password ", vbOKOnly  

    Else  

        Username = Trim (TextBox1.Text)  

        Password = Trim (TextBox2.Text)  

        i = 1  

        Do While Cells (1, 1).Value <> ""  

            j = 1  

            u = Cells (i, j).Value  

            j = j + 1  

            p = Cells (i, j).Value  

            If Username = u And Password = p And Cells (i, 3).Value = "fail" Then  

                MsgBox "Your Account temporarily locked", vbCritical  

                Exit Do  

                Else If Username = u And Password = p Then  

                Call clear  

                UserForm1.Hide  

                UserForm2.Label1.Caption = u  

                UserForm2.Label1.ForeColor = &H8000000D  

                UserForm2.Show  

                Exit Do  

                Else If Username <> u And Password = p Then  

                MsgBox "Username not matched", vbCritical + vbOKCancel  

                Exit Do  

                Else If Username = u And Password <> p Then  

                If Cells (i, 3).Value = "fail" Then  

                    MsgBox "Your account is blocked", vbCritical + vbOKCancel  

                    Exit Do  

                    Else If Cells (i, 4).Value < 2 Then  

                    MsgBox "Invalid password", vbCritical  

                    Cells (i, 4).Value = Cells (i, 4) + 1  

                    Exit Do  

                Else  

                    Cells (i, 4).Value = Cells (i, 4) + 1  

                    Cells (i, 3).Value = "fail"  

                    Cells (i, 2).Interior.ColorIndex = 3  

                    Exit Do  

                End If  

            Else  

                i = i + 1  

            End If  

        Loop  

    End If  

    Application.ScreenUpdating = True  

End Sub  

Sub clear ()  

    TextBox1.Value = ""  

    TextBox2.Value = ""  

End Sub  

Private Sub TextBox1_Enter ()  

    With TextBox1  

        .Back Color = &H8000000E  

        .Fore Color = &H80000001  

        .Border Color = &H8000000D  

    End With   

    TextBox1.Text = ""  

End Sub  

Private Sub TextBox1_AfterUpdate ()  

    If TextBox1.Value = "" Then  

        TextBox1.BorderColor = RGB (255, 102, 0)  

    End If  

    i = 1  

    Do Until Is Empty (Cells (i, 1).Value)  

        If TextBox1.Value = Cells (i, 1).Value Then  

            With TextBox1  

                .Border Color = RGB (186, 214, 150)  

                .Back Color = RGB (216, 241, 211)  

                .Fore Color = RGB (81, 99, 51)  

            End With  

        End If  

        i = i + 1  

    Loop  

End Sub  

Private Sub TextBox2_Enter ()  

    With TextBox2  

        .Back Color = &H8000000E  

        .Fore Color = &H80000001  

        .Border Color = &H8000000D  

    End With  

    TextBox2.Text = ""  

End Sub  

Private Sub TextBox2_AfterUpdate ()  

    i = 1  

    Username = TextBox1.Value  

    Password = TextBox2.Value  

    If TextBox2.Text = "" Then  

        TextBox2.BorderColor = RGB (255, 102, 0)  

    End If  

    Do Until Is Empty (Cells (i, 1).Value)  

        j = 1  

        u = Cells (i, j).Value  

        j = j + 1  

        p = Cells (i, j).Value  

        If Username = u and Password = p Then  

            With TextBox2  

                .Border Color = RGB (186, 214, 150)  

                .Back Color = RGB (216, 241, 211)  

                .Fore Color = RGB (81, 99, 51)  

            End With  

            Exit Do  

            Else If Username = u and Password <> p Then  

            TextBox2.BorderColor = RGB (255, 102, 0)  

            Exit Do  

        Else  

            i = i + 1  

        End If  

    Loop  

End Sub  

Sub settings ()  

    With UserForm1  

        TextBox1.ForeColor = &H8000000C  

        TextBox2.ForeColor = &H8000000C  

        TextBox1.BackColor = &H80000004  

        TextBox2.BackColor = &H80000004  

        TextBox1.Text = "Username"  

        TextBox2.Text = "Password"  

        TextBox1.BorderColor = RGB (0, 191, 255)  

        TextBox2.BorderColor = RGB (0, 191, 255)  

        CommandButton1.SetFocus  

    End With  

End Sub  

Private Sub UserForm_Initialize ()  

    Call settings  

End Sub

  

Excel value...

Thank You....

                                                                                                      -This article is posted by karthikeyan