Find Function in Excel - VBA

Introduction

• Find is a very powerful option in Excel and is very useful. 

• This post describes Find Function in Excel - VBA

• Objective is to Find a String or Value in Excel Workbook/Worksheet

VBA - Function

• The below function finds the given string and returns the row number. 

• If you want the cell address to be return by the function, then use the below line of code  

          find = ActiveCell.Address

Function find(ByRef findString As String) As Integer   

    Dim Rng As Range

    ActiveSheet.Range("A1").Select

    With ActiveWorkbook.ActiveSheet.Range("A:B")

        Set Rng = .find(What:=findString, LookIn:=xlValues)

        If Not Rng Is Nothing Then

          Application.Goto Rng, True

          find = ActiveCell.Row

        Else

          find = 0
          MsgBox "Search String Not Found"

        End If

    End With

End Function

And here's how you call it in your Program:

Public Sub callingProgram()

    Dim rowNumber As Integer
    rowNumber = find("Allwyn")
 
End Sub

                                       !********************* Find it.? J ***************************!

Send an Email from Excel - VBA

Introduction:

Excel VBA allows us to send emails from Excel. In this article i am going to explain about this.

We can use a VBA macro to create/send a new message and preset any of the fields, including To/CC/BCC, the subject, flags, voting options and more.

Steps:

1. Open Excel, and press Alt+F11 (this will open a new window Visual Basic Editor)

2. Select Tool option in the main menubar >> and then select References

3. Select Microsoft Outlook 14.0 Object Library or higher version reference

4. Click on Insert >> Module and then paste the below code

Code:

Sub Sendmail()

    Dim olApp As Outlook.Application

    Dim olMail As Outlook.MailItem

    

    Set olApp = CreateObject("Outlook.Application")

    Set olMail = olApp.CreateItem(olMailItem)

    

    With olMail

        .To = "xyz@abc.com"

        .CC = "wxy@abc.com"

        .Subject = "Automated email from Excel"

        .Body = "Hi, This is test Email"

        .Display

        '.Send

    End With

    

    Set olApp = Nothing

    Set olMail = Nothing

End Sub

You can change .Display to .Send if you want to send mail automatically (Use .Display when testing)

To add attachments in the mail. use the below line of code

                                                 !********************* Try this J ***************************!

Create your first macro in Excel - VBA

Introduction :

 In this post, I will show you how to create your first macro (VBA program).

Objective :

To create a Macro which will show a Message Box "HELLO WORLD" (world classic "Hello World!" example J)

To write your 1st program and enter into world of VBA, follow the below steps

Steps :

1. Open Excel, and press Alt+F11 (this will open a new window Visual Basic Editor)

2. Click on INSERT >> MODULE

3. Then in the right side window type the below Code

      

      Sub MyFirstMacro()

           Msgbox "Hello World"

      End Sub 

       

4. Then Press the F5 button to Execute(You can also click the Button)

You will see a message box saying "Hello World". You can type whatever you want to see in the message box
we should save the file in .xlsm or .xlsb file extension, so that we can run the macro later

                                        !********************* Try it out J ***************************!

Reference : Microsoft Excel Tutorials - Daily updates (Facebook page)

How to use Split Function - VBA

Introduction

Split: It is a function that can split a text string into an array, by making use of a delimiter character.

• As the name tells, the work of Split statement is to break, split or divide a string based on particular criteria.

• Split Function returns a String Array and not a String.

• Split (text_string, delimiter, limit, compare)  - where limit & Compare optional parameter

Objective

Let’s consider we have an Email ID: “someone@gmail.com” and now our objective is to break this email id into username and domain name separately.

Code: Usage of Split function

Sub Get_Domain_and_Username()

  Dim result() As String

  Dim email As String

    email = "someone@gmail.com"

    result() = Split(email, "@")

    domain = result(0)

    userName = result(1)

    MsgBox "Domain is " & domain & "UserName is " & userName

End Sub

Example 2:

Separate a list of Pipe separated names.  eg - "Yuvi|Viru|Msd|Lee"

Objective to get the third name in that list

Sub Splitdemo()
  Dim result() As String
  Dim lists As String

    lists = "Yuvi|Viru|Msd|Lee"
    result() = Split(lists, "|")

    thirdEntry = result(2)
    MsgBox "The third name in the list is: " & thirdEntry
    
    'To Loop all values in the list use the below code
    For i = LBound(result) To UBound(result)
        MsgBox "Name " & i & ": " & result(i)
    Next  
End Sub

                                   !********************* Leave your comments about the topic J ***************************!

Open and Close Workbook - VBA

Introduction

    In this post you can learn how to Open and Close a workbook using VBA in Microsoft Excel.

Objective:

    A Macro which can open and close Excel workbook.

Solution:

    Let's see a VBA code, which does it.

Simple Method:

    Macro purpose: To open & close a excel workbook

Sub OpenRCloseWorkbook()     
    On Error Resume Next
    Set wk = Workbooks.Open("C:\MyExcel.xlsx")
    
    'Error Handling If file not found
    If Err.Number = 1004 Then
        MsgBox "File Not Found", vbCritical, "Warnings"
        Err.Clear
        Exit Sub
    End If
    
    ActiveWorkbook.Close
End Sub

Tips :

If you want to close a workbook without the user being prompted about saving the workbook, use the below code

    ActiveWorkbook.Close True 'SaveChanges:=TRUE
    ActiveWorkbook.Close False 'SaveChanges:=FALSE

Dynamic Method:

    This GetOpenFilename method displays the standard open Dialog box where user can select the file.

Sub OpenRCloseWorkbook()
    Dim MyFile As String

    MyFile = Application.GetOpenFilename()
    Workbooks.Open MyFile

    Filename = ActiveWorkbook.Name
    Workbooks(Filename).Close True
End Sub

                      !********************* Let me know how it works J ***************************!

Delete Files and Folders - VBA

In this blog you will learn how to:

• Delete a file
• Delete all the files in a folder
• Delete/Create a directory using VBA 

Please note that Kill command permanently deletes the file. we cant "undo" the delete.

'Macro Purpose: To Create/Remove a folder

Sub Create_RemoveDirectory()

    On Error Resume Next

    Kill "C:\Test\*"          ' Deletes all the files in the folder Test

    RmDir "C:\Test\"       ' Deletes empty folder

    MkDir "C:\Test"         ' Creates a Folder name Test

    On Error GoTo 0

End Sub

'Macro Purpose : Deletes given file & deletes all *.TXT files in current directory.

Sub DeleteFile()

    On Error Resume Next

    Kill ("C:\Test1.xlsx")

    Kill ("C:\Test\*.txt")

    On Error GoTo 0

End Sub

Execute the code step by step for better understanding

Sub SampleProcedure()

    Dim fpath As String, ffile As String

    fpath = "C:\Test\"

    ffile = Dir(fpath + "*.txt")

    

    Do While ffile <> ""

        Kill (fpath & ffile)

        ffile = Dir

    Loop        

    MsgBox "Files deleted in the given folder"

End Sub

                                                Hope this would help beginners J