Excel Macro: 2013

Friday 13 December 2013

How to save excel in 2003 format in macro

You can use below code to save the excel in xls format

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.DisplayAlerts = False
objExcel.Workbooks.Add
objExcel.Cells(1, 1).Value = "No"
objExcel.Cells(1, 2).Value = "Test_ID"
objExcel.Cells(1, 3).Value = "Test_Case_Name"
objExcel.Cells(1, 4).Value = "Test_Case_Status"
objExcel.activeWorkbook.SaveAs "c:\sagar\Report11.xls", -4143

objExcel.Quit

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

Tuesday 26 November 2013

How to move the worksheet in excel macro?

Sub move_worksheet()

'We can move the worksheets by using move method
'We can also specify at which position we want to move the worksheet
'Worksheets(1).Move Worksheets(3)
End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

How to move the worksheet in excel macro?

How to copy the worksheet using macro?

Sub copy_worksheet()

'We can copy worksheet using copy method
Worksheets(1).Copy Worksheets(3)

End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

How to delete the worksheet using macro?

Sub delete_worksheet()

Application.DisplayAlerts = False

'We can delete the worksheet using delete method

Worksheets("sheet12").Delete

End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

How to add new worksheet to the workbook using excel macro?

Sub add_new_worksheet()

'We can add new worksheet by using below syntax
'We can also specify at which position we can add new sheet

Set ws = Worksheets.Add(Worksheets(1))

'Add some data to added worksheet
ws.Cells(1, 1) = "Obama kills Osama"

'release the object variable
Set ws = Nothing

End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

How to delete entire column using excel macro?

Sub delete_entire_column()

'We can delete entire column using below VBA code

'In below code we have deleted column A
Columns("A:A").Delete

End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

How to delete entire row using excel macro?

Sub delete_entire_row()

'We can delete entire row using below VBA code

'In below code we have deleted row 4
Columns("4:4").Delete

End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

Explain offset property in excel macro

Sub offset_property()

'Offset property is used to access the cell in a relative manner

'In below example we have set the font color of the cell to green which is placed at
'2 rows down and 3 columns to the right of cell A1

Worksheets(1).Cells(1, 1).Offset(2, 3).Font.Color = vbGreen

End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

How to change the font of multiple rows in excel macro?

Sub change_font_multiple_rows()

'We can select multiple rows using below syntax

'For single row
'In below example we have changed the font of the row number 1
Worksheets(1).Range("1:1").Font.Bold = False

'For multiple sequential rows
'In below example we have changed the font of all the rows from 1 to 4
Worksheets(1).Range("1:4").Font.Bold = False

'For multiple non-sequential rows
Worksheets(1).Range("1:1,3:3").Font.Bold = False

'Please note that you can not only change the font of the rows but also change the other
'properties like alignment, font style, indentation, border of the cells, color etc

'Alternative way to select the row
Worksheets(1).Rows(1).Font.Bold = False

'In below example we have change the color of the border of rows 1,2 and 4 to red
Set row124 = Union(Rows(1), Rows(2), Rows(4))
row124.Borders.LineStyle = xlNone
row124.Borders.Color = vbRed

End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

How to change the font of multiple columns in excel macro?

Sub change_font_multiple_column()

'We can select multiple columns using below syntax

'For single column
Worksheets(1).Range("A:A").Font.Bold = True

'For multiple sequential columns
Worksheets(1).Range("A:F").Font.Bold = True

'For multiple non-sequential columns
Worksheets(1).Range("A:A,F:F").Font.Bold = True

'Please note that you can not only change the font of the columns but also change the other
'properties like alignment, font style, indentation etc

'Alternative way to select columns is given below
Worksheets(1).Columns(2).Select '- select entire Column at 2nd position
Worksheets(1).Columns("B").Select '- select entire Column B

End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

How to select multiple ranges in a worksheet in excel macro?

Sub select_multiple_range()

'We can select multiple range using below code

Worksheets(1).Range("A1:B8,C1:D8").Font.Bold = True

'In above code we have selected 2 ranges. First one is A1:B8 and other is from C1:D8

'To access each range separately, we can use below syntax

'In below code we have displayed the total number of cells in first range (area)
MsgBox Worksheets(1).Range("A1:B8,C1:D8").Areas(1).Cells.Count

End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

How to clear the contents of entire worksheet in excel macro?

Sub clear_worksheet_contents()

'We can select all cells from the given sheet by using below code

Worksheets(1).Cells.Clear

'Above code will delete the data as well as formatting from all cells from the sheet1

End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

How to access the worksheets by its index in excel macro?

Sub access_worksheets_by_index()

'We can refer to the worksheets by it's index using below syntax
'In below example we have used the sheet at index 1

Worksheets(1).Select

'Index of the leftmost worksheet is always 1 and it is incremented by 1 from left to right
'We we move or add new worksheets in a workbook, the indices of the existing sheets also change

End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

How to access the worksheets by its name in excel macro?

Below procedure will access the worksheets by its names.

Sub access_worksheets_by_name()

'We can access the worksheets by its name by below syntax
'In below example we have selected the sheet with the name balancesheet
Worksheets("balancesheet").Select

'This is how we can access the sheets using sheet name

End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

How to delete multiple work sheets with excel macro?

Below sub procedure will delete 2 sheets with name sheet2 and sheet3

Sub multiple_worksheets()

'We can select multiple worksheets by using below syntax

Worksheets(Array("Sheet2", "Sheet3")).Select

'We can also delete multiple sheets at the same time
Worksheets(Array("Sheet1", "Sheet2")).Delete

End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

How to add new workbook using excel macro?

Below code will add new workbook and write some data into it and save it as well.

Sub Add_New_Workbook()

'We can add new workbook by using add method of workbooks collection

Set new_workbook = Workbooks.Add

'access the cells in first sheet from the newly added workbook
new_workbook.Worksheets(1).Cells(1, 1) = "obama"

'save new workbook to hard disk
new_workbook.SaveAs "c:\temp.xlsx"

'close new workbook
new_workbook.Close

'release the memory of the object associated with new_workbook
Set new_workbook = Nothing

End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

Friday 25 October 2013

Excel Macro to read data from text file

Sub readFile()
'Excel macro to read a text file

Dim myfso As Scripting.FileSystemObject
Dim stream As Scripting.TextStream

Set myfso = New Scripting.FileSystemObject

'open text file in read mode.
Set stream = myfso.OpenTextFile("c:\tempfile.txt", ForReading, True)

MsgBox stream.ReadAll

End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

Excel Macro to create text file

Sub createFile()
'Please note that to perform file related functions in your project, you will have to add reference of microsoft script runtime

'Here is the excel macro code to create a text file
Dim myfso As Scripting.FileSystemObject
Dim stream As Scripting.TextStream
Set myfso = New Scripting.FileSystemObject
Set stream = myfso.CreateTextFile("c:\tempfile.txt", True)
'Create text file at given path
stream.Write ("This is file creation demo. if file exists, it will be overwritten")
'Write data into opened file's stream
stream.Close
Set stream = Nothing
Set myfso = Nothing

'This is how You can create a file in excel macro

End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

How to create and use dictionary object in excel macro

Sub createDictionary()
'Please note that to use dictionary in your project, you will have to add reference of microsoft script runtime

Dim d1 As Scripting.Dictionary
'Dictionary object allows you to store the elements in dynamic way using keys.
'Unlike arrays where values are stored using index, dictionary uses keys to store values

'To add key in dictionary, use below code
Set d1 = New Scripting.Dictionary
d1.Add "sachin", 41
d1.Add "dhoni", 32

'To get the value at particular key
MsgBox d1.Item("sachin")

'to remove key from dictionary
d1.Remove "dhoni"

'To get all keys from the dictionary, use below code
keyArray = d1.Keys

'to check if given key exists in the dictionary
If d1.Exists("sachin") Then
MsgBox "Key sachin exists"
Else
MsgBox "Key sachin does not exist"
End If

'To remove all keys from dictionary, use below code
d1.RemoveAll

'this is how you can use dictionary object in excel macro
'You can store the values from 2 columns as key value pair in dictionary
End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

How to create and use array in excel macro

Sub createArray()
'How to create and use array in excel macro
'Arrays are used to store large number of elements in sequence

'There are 2 types of Arrays in Excel Macro
'1.Fixed Size Array
'2.Dynamic Array

'**************************Fixed Size***********************************
Dim a(11)
a(0) = "sagar"
a(1) = 2
MsgBox a(1) & a(0)

'Or you can also create array like this
a = Array("sagar", "amol", "sachin", 33, 44, 55)
'**************************Dynamic Array***********************************
Dim b()

ReDim Preserve b(3)
'Above statement will resize the array to size 4
'Preserve statement will keep the existing values in the array intact
ReDim Preserve b(6)
'Again resize array to size of 7

'This is how we can array to store any number of values.
End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

How to create modules in Excel Macro

Modules are used to store the related procedures and functions

1.Modules contain the functions that provide unique functionality
2.We can group similar functions and procedures in separate module
3.Class module contains properties and methods of that particular class
4.For example - we can create a student class which can be used to store/retrieve student properties like student name, student id, student address etc.

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

Excel Macro to convert the data type of cell value

Sub conversion()

'We can convert the given variable in any desired format
' For example we we have a string as 09-jan-2013, we can convert it into date as mentioned below

date1 = CDate("09-jan-2013")
MsgBox "Data type of date1 is " & TypeName(date1)

'If the input to cdate function is not a valid date, type mismatch error will occur
'Similarly you can convert the string into integer, float ect
MsgBox CInt("243.6 ")

'To convert the number into string, you can use cstr function
no = 2344
MsgBox CStr(no)

End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

Excel Macro to split the cell value or string

Sub breakString()

'Excel macro to split the string with given delemiter or seperator

string1 = "sachin , suresh, Yuvraj, dhoni, Ishant, shikhar"

arrString = Split(string1, ",", 2)
'Split function will break the given string using seperator
'in above code we have split string1 using ","
'Last parameter indicates how many parts should be made of the given string
'Last parameter is optional and if ommitted string will be broken into maximum number of parts

For i = 0 To UBound(arrString)
MsgBox arrString(i)
Next

'Output - "sachin" and "suresh, Yuvraj, dhoni, Ishant, shikhar"
End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

Excel Macro to extract the part of cell value or string

Sub extractString()
'Excel macro to get the part of string
'you can use below functions to extract the part of the string

MsgBox Left("Lalu Prasad", 4) 'get 4 characters from left side of string
'output - Lalu
'Left function will get the specified number of characters from left side of the string

MsgBox Right("Lalu Prasad", 6) 'get 6 characters from the right side of the string
'Right function will get the specified number of characters from right side of the string
'Output - Prasad

MsgBox Mid("Lalu Prasad", 6, 6) 'get 6 characters starting from index 6
'Mid function is used to get the fixed length portion of string starting from given position or index
'Output - Prasad
End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

Excel Macro to remove the spaces from the cell values

Sub removeSpaces()
'Excel macro to remove the leading and trailing spaces from the string or cell value.
'In Excel macro we can use trim functions to remove leading and trailing spaces from given string

MsgBox LTrim(" Kapil Sharma ") ' output "Kapil Sharma "
MsgBox RTrim(" Kapil Sharma ") ' output " Kapil Sharma"
MsgBox Trim(" Kapil Sharma ") 'output "Kapil Sharma"

' As displayed in above code we have 3 functions to remove the spaces from the string
'ltrim - removes the spaces from left side of the string
'rtrim - removes the spaces from right side of the string
'trim - removes the spaces from both left and right side of the string

'Please note that we can not remove the internal spaces from the string
'To remove the spaces from internal part from the string, you can use below macro code

MsgBox Replace(" Kapil Sharma ", " ", "")

'Output of above code will print "KapilSharma"
'Note that all spaces from the string are removed

End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

Excel Macro to find the sub string

Sub findSubString()
'Excel macro to check if one string exists in another string

'We can use below macro code in excel to check if one string exists in another string
'if substring is found, InStr function will return the position where the match is found

x = InStr(1, "Salman Khan", "khan", vbTextCompare)
MsgBox x

'Above macro code will print 8 as "khan" appears in "salman khan" at 8th position
'Last parameter in the function instr is used to specify whether comparison is case-sensitive or not.
'vbtextcompare = Case insensitive comparison
'vbBinaryCompare = case sensitive comparison

End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

Excel Macro to convert the string text to upper case

Sub uppercase()

'Excel macro to convert the string to upper case
'here is the macro code that can be used to convert the string to upper case
MsgBox UCase("priynka chopra")

'Above excel macro code will print PRIYANKA CHOPRA
End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

Excel Macro to convert the text in lower case

Sub lowercase()

'Excel macro to convert the string to lower case
'here is the macro code that can be used to convert the string to lower case
MsgBox LCase("MS DHONI")

'Above excel macro code will print ms dhoni
End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

Excel Macro to convert the text in lower case

Excel macro to get the month name of given date

Sub getMonthName()

'Print current month name
MsgBox MonthName(Month(Now))

'To print month name of any given date you can use below macro code
MsgBox MonthName(Month("09-jan-1988"))

End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

Excel macro to get the day name of given date

Sub getDayName()
'Print today's day name
MsgBox WeekdayName(Weekday(Now))
'To print day name of any given date you can use below macro code
MsgBox WeekdayName(Weekday("09-jan-1988"))
End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

Excel Macro to get the date difference

Sub getDateDiff()
'Excel macro to get the date difference in term of day, week, year

'Suppose you want to find the total number of days between date1 = "09-jan-1987" and date2="12-jan-2012"
date1 = "09-jan-1987"
date2 = "12-jan-2012"
MsgBox "Total days between " & date1 & " and " & date2 & " is -> " & DateDiff("d", date1, date2)
MsgBox "Total weeks between " & date1 & " and " & date2 & " is -> " & DateDiff("ww", date1, date2)
MsgBox "Total years between " & date1 & " and " & date2 & " is -> " & DateDiff("yyyy", date1, date2)

End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

Excel Macro to get the stock data from yahoo finance

Below procedure can be used to get the stock information from yahoo finance.

Sub getYahooFinance()
'Below script will download the quotes for given symbol from yahoo finance
'more info at -> http://www.jarloo.com/yahoo_finance/

'qURL = "http://finance.yahoo.com/d/quotes.csv?s=AAPL+GOOG+MSFT&f=nv"
'http://finance.yahoo.com/d/quotes.csv?s=AAPL+GOOG+MSFT&f=nv
qURL = "http://ichart.finance.yahoo.com/table.csv?s=rcom.BO&ignore=.csv"

With Sheet1.QueryTables.Add(Connection:="URL;" & qURL, Destination:=Sheet1.Range("A1"))

.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With

End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

Excel Macro to process xml file

Sub processXML()

'online ref - http://msdn.microsoft.com/en-us/library/aa468547.aspx
Set xDoc = CreateObject("MSXML2.DOMDocument")
'to load the xml string
'xDoc.LoadXML ("<book><str>hello</str></book>")
'to load xml from file, use below syntax
xDoc.Load ("c:\abc.xml")
'Display all elements and their values
Call DisplayNode(xDoc.ChildNodes, 0)
MsgBox str1
'We can also use xpath to get the collection of nodes
xDoc.setProperty "SelectionLanguage", "XPath"
strPath = "//book"
Set nodelist = xDoc.DocumentElement.SelectNodes(strPath)
MsgBox "No of Nodes Found by using xpath is -> " & nodelist.Length & " Node"
Set xDoc = Nothing
End Sub

Public Sub DisplayNode(ByRef Nodes, ByVal Indent)
'Recursive function to traverse all the nodes inside xml file/ String
Dim xNode
Indent = Indent + 2
For Each xNode In Nodes
If xNode.NodeType = NODE_TEXT Then
str1 = str1 & " " & xNode.ParentNode.nodeName & ":" & xNode.NodeValue
End If
If xNode.HasChildNodes Then
DisplayNode xNode.ChildNodes, Indent
End If
Next
End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

Excel Macro to get stock finance data from Google NSE BSE

'Below program will get stock/finance information in xml format from google finance.
'You can give the stocks from NSE and BSE as well.

'Specify the url to connect to
URL = "http://www.google.com/ig/api?stock=rcom"
Set objHTTP = CreateObject("MSXML2.XMLHTTP")
Call objHTTP.Open("GET", URL, False)
objHTTP.Send

'The response comes in the form of xml stream.
MsgBox (objHTTP.ResponseText)

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

Excel Macro to display a dialog box .

Sub messageBox()
'We can display message box / dialog box with yes/no buttons in excel macro using msgbox function

ret = MsgBox("Yes - No Buttons", vbYesNoCancel, "MessageBox Macro")
'Please note that second parameter in the function msgbox decided which buttons to display on the dialog 'box.

If ret = 6 Then
MsgBox "You cliked on Yes"
ElseIf ret = 7 Then
MsgBox "You cliked on No"
Else
'ret = 2
MsgBox "You cliked on Cancel"
End If

End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

Excel Macro to get the current system time

Call getSystemTime()

Sub getSystemTime()

'below program will display current system time in excel macro

MsgBox Time

'To display time stamp, you can use below code excel macro
MsgBox Now

'To extract the time part from the timestamp, you can use below code

MsgBox TimeValue(Now)
'Above code will print only the time part i.e current time
End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

Excel Macro to get the year from given date

Sub getYear()
'To get the year of given date, you can use below function in excel macro.

MsgBox Year(Now)
'Above code will print current year

MsgBox Year("09-jan-1986")
'Above code will print 1986

End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

Excel Macro to get yesterday's date

Call getYesterday()

Sub getYesterday()
'We can use dateadd function to find yesterday's date in excel macro.

x = DateAdd("d", -1, Now)
MsgBox DateValue(x)
'In above function we have used dateadd function which takes 3 parameters.
'First parameter is used to specify the interval type like days, weeks, months, years etc.
'Second parameter is actual interval. It can be positive or negative
'Third parameter is any valid date.

'Similary We can find out the tommorrow's date using below line

x = DateAdd("d", 1, Now)
MsgBox DateValue(x)

End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible

Excel Macro to add leading zero to cell value

'Excel Macro to add leading zero to any cell.

Sub addLeadingZero()

'get the value from cell
x = Sheet1.Cells(1, 1)
'add leading zero

x = "'000" & x

'print value with zero
MsgBox x

'store value back in cell
Sheet1.Cells(1, 1) = x

End Sub

List of commonly used Excel functions

I am using Excel Macros since last few years and from that experience I have created the list of commonly used excel functions that we can use.

Formula	Value	Description
=ABS(-11)	11	gets Absolute value
=ADDRESS(1,1)	$B$1	gets the address of cell
=AGGREGATE(4,0,A1:A10)	33	Calculates aggregates
=BIN2DEC(101)	5	converst binary to decimal
=CELL("contents",A1)	33	gets the contents of given cell
=CHAR(65)	A	gets the character for ascii value
=CHOOSE(2,33,44)	44	selects nth value from the given set of values
=CONVERT(23,"m","ft")	75.45931759	Converts the number in different measurement units
=COUNT(A1,B3)	1	counts the number of cells having numbers in it
=COUNTA(A1:A7)	7	counts non empty cells
=COUNTIF(A1:A7,2)	0	counts the cells that meet the condition
=EXACT("sagar","sagar")	TRUE	checks if two string are similar or not
=FACT(5)	120	gets the factorial of the number
=FIND("ten","sachin tendulkar")	8	finds the position of given string in other string
=FIXED(19.434,2)	19.43	rounds the number
=FLOOR(45345.6756,1)	45345	gets the integer number
=CEILING(45345.676,1)	45346	gets the integer number
=NOW()	25-10-2013 17:19	gets current time
=DAY(now())	25	gets current day
=GCD(6,8)	2	find GCD
=HLOOKUP(2,A1:A10,3)	#N/A	Horizontal Lookup
=VLOOKUP(2,A1:A10,3)	#N/A	Vertical Lookup
=HOUR(NOW())	17	gets current hour
=HYPERLINK("http://www.google.com","goog")	goog	creates the hyperlink in the cell
=IF(12>10,"12 is big","12 not big")	12 is big	executes conditional statements
=INT(323.55)	323	gets the integer part of the number
=ISBLANK(a55)	TRUE	checks if the given cell value is blank
=ISEVEN(22)	TRUE	checks if the given value is even
=ISNONTEXT(a55)	TRUE	checks if the givenvalue is non-text
=ISNUMBER(22.44)	TRUE	checks if the given value is number
=ISODD(34)	FALSE	checks if the given value is odd
=ISTEXT("dsdsd")	TRUE	checks if the givenvalue is a text
=LARGE(A1:A44,1)	56	gets the nth largest value from the given set
=LCM(6,8)	24	gets the Least Common Multiple
=LEFT("sachin tendulkar",6)	sachin	gets the n characters from the left side of the string
=LEN("sachin")	6	gets the length of the given value
=LOWER("Sagar Salunke")	sagar salunke	converts the value into lower case
=MATCH(33,A1:A77,0)	1	searches the value in given range
=MAX(A1:A44)	56	finds the max value from given range
=Min(A1:A44)	3	finds the min value from given range
=MONTH(now())	10	gets the current month
=MOD(44,3)	2	gets the remainder of division
=MID("sachin tendulkar",7,33)	tendulkar	gets the n characters from the given string
=MINUTE(now())	19	gets current minutes
=NETWORKDAYS("20-jan-2013",NOW())	200	counts the weekdays from given dates
=OR(12>33,FALSE)	FALSE	checks the OR conditions
=POWER(2,2)	4	gets the power of given number
=PROPER("sagar salunke")	Sagar Salunke	Converst the first letter of word to upper case
=QUOTIENT(5,2)	2	gets the QUOTIENT
=RAND()	0.988171935	gets the random number betweem 0 and 1
=RANDBETWEEN(1,100)	35	gets the random number between 1 and 100
=REPLACE("sachin ten",8,3,"Anjali")	sachin Anjalien	replace the part of string by other string
=RIGHT("sagar salunke",8)	salunke	get n characters from the right side of the string
=ROUND(4545.67,1)	4545.7	rounds the number
=ROW(A1)	3	gets the row number of given cell
=SEARCH("ten","sachin tendulkar")	8	search one string in other
=SMALL(A1:A99,2)	5	gets the nth smallest number from the given set
=SQRT(4)	2	gets the square root of the number
=SUBSTITUTE("sagar salunke","sagar","ganesh")	ganesh salunke	replace the part of string by other string
=SUM(A1:A11)	268	gets the sum of given numbers
=SUMIF(A1:A10,33)	231	gets the sum of numbers if criteria is fulfilled
=TODAY()	25-10-2013	gets today's date
=TRIM(" sagar salunke ")	sagar salunke	removes the spaces from left and right side
=TYPE(22)	1	gets the data type of the parameter
=TRUNC(3434.56767)	3434.56	truncates the number
=UPPER("sagar salunke")	SAGAR SALUNKE	converts the letters to upper case
=WEEKDAY(NOW())	6	gets the weekday from the given date
=YEAR(NOW())	2013	gets the year from the given date

Thursday 23 May 2013

Macro to calculate average of specified cell values in Excel

How to Calculate the average of certain cells in Excel

Example :
In below example, you will see how we can find out the average of given cell values.

Code and Syntax for calculating average in Excel VBA is given below.

You can find out the average in 2 ways.

Use Excel formula
Use VBA Excel Macro Code

By using formula

=AVERAGE(A1:A10)

By Using Excel Macro

Set myrange = Range("A1:A10")
Range("A11") = WorksheetFunction.Average(myrange)

This is how we can calculate average of given values in excel sheet using macro

Macro to calculate system idle time in Excel.

Example-
In below example, I have shown how we can find the system idle time using excel vba macro.

Code and Syntax to calculate system idle time.
In below code, GetIdleTime() function returns the number of seconds the system has been idle for.
Here idle means that there is no input from user from keyboard or mouse.

Private Type LASTINPUTINFO
cbSize As Long
dwTime As Long
End Type

Private Declare Sub GetLastInputInfo Lib "user32" (ByRef plii As LASTINPUTINFO)
Private Declare Function GetTickCount Lib "kernel32" () As Long

Function GetIdleTime() As Single
Dim a As LASTINPUTINFO
a.cbSize = LenB(a)
GetLastInputInfo a
GetIdleTime= (GetTickCount - a.dwTime) / 1000
End Function

Sub check()
        Application.Wait (Now() + TimeValue("0:00:11"))
   ' make the system idle for 11 sec. Donot type from keyboard or click from mouse
        MsgBox getIdleTime()
End Sub

LASTINPUTINFO is a structure defined by microsoft and it Contains the time of the last input.

GetTickCount functions gets the number of milliseconds that have elapsed since the system was started, up to 49.7 days.

When you will execute above programm, you will be prompted approx. 11 as system idle time.

This is how we can calculate the systme idle time in VBA Excel macro.

How to pause the execution of Macro in Excel

Example :
In below example, you will see how we can pause the execution of macro before executing next statement.
Please note that wscript is not available in Excel VBA Macro. It is available only in vbscript.

Code and Syntax for making the programm wait in Excel VBA is given below.

a = 10
Application.Wait (Now() + TimeValue("0:00:11"))
msgbox a

As you can see in above code, I have used Application.Wait statement.
if you execute this code, you will see that value in a is prompted after 11 seconds.

To wait for say 10 mins, you will have to use below code

a = 10
Application.Wait (Now() + TimeValue("0:10:00"))
msgbox a

So here the format of TimeValue is -

TimeValue("hh:mm:ss")) - So you can give any value in hour/minute/second to make macro wait for that particular time.

This is how we can pause the execution of macro in Excel.

Macro to clear contents of certain cells in Excel sheet

Example -

In below example I am going to show you how we can clear the contents of specified cells in excel sheet.
I will also show you how we can use macro to clear the contents of entire row or column.

Code and Syntax to clear the contents of specified cells using macro.

Code to clear the contents of certain cell.
    Range("A18").Select
    Selection.ClearContents

Code to clear the contents of multiple cells.
    Range("A2:B22").Select       ' clear contents of a range
    Selection.ClearContents

Code to clear the contents of Entire row in Excelsheet
    rows("2:2").Select
    Selection.ClearContents

Code to clear the contents of Entire Column in Excelsheet
    columns("A:A").Select
    Selection.ClearContents

In similar way we can clear the contents of any cells, ranges in excel sheet using VBA macro. It is just two step process.

Select the area/region you want to clear.
Use Selection.ClearContents statement.

Wednesday 22 May 2013

Macro to find the date difference in days/months/years in Excel

Example - Macro to find the date difference in days/months/years in Excel

There are many times in real life that you need to find the difference between 2 dates. For example - You want to calculate how many days have passed since you joined the company, you will have to use the date difference function in VBA macros.

Code and Syntax - Macro to find the date difference in days/months/years in Excel

We can calculate the difference in terms of days, months or years as described below.

You can do this in two ways -

By using Excel functions
By using VBA/Macro functions

By using Excel functions -
Suppose you have date1 in A1 and date2 in A2. Cell A3 will have difference.

To find Days difference -
A3 Cell formula "=A1-A2"    Days between the two dates
A3 Cell formula "=NETWORKDAYS(A1,A2)" Weekdays between the two dates . This will exclude the saturday and sunday

To find Months difference -
A3 Cell formula  "=MONTH(A1)-MONTH(A2)" Months in the same year (3)
A3 Cell formula  "=(YEAR(A1)-YEAR(A2))*12+MONTH(A2)-MONTH(A1)" Months in different years

To find Year difference -
A3 Cell formula  ="YEAR(A1)-YEAR(A2)" Years occurring between two dates (3)

By using VBA macro functions -

VBA provides one function to find the difference between 2 dates as mentioned below.

You can use this function to determine how many specified time intervals exist between two dates. For example, you might use to calculate the number of days between two dates, or the number of weeks between today and the end of the year.

Function calculatediff(myd)
   msgbox  "Diff between dates in days " & datediff("d", Now, myd)
End Function

In above example, we will be able to calculate the number of days between given date and today.

Macro to change the date format to DD MM YYYY in Excel

Example -
In below example, Cell A1 will be having the format as dd-mm-yyyy

Code and Syntax -

Range("D19").NumberFormat = "dd-mm-yyyy"

To Change the date format of all cells in columns say from F to H, Use below code

Columns("F:H").NumberFormat = "dd-mm-yy"

To Change the date format of all cells in given range , Use below code

For Each cell In Sheet1.Columns("A2:B8")
MsgBox cell.NumberFormat = "dd-mm-yy"
Next

This is how we can change the date format of any cell in Excel using macro.
If you want any other date format, you must change the format string to anything like

dd/mm/yyyy
dd/mm/yy
mm/dd/yyyy
mm-dd-yyyy
mm-dd-yy

etc

Macro to change the font color and size of Cell in Excel

Example -

Below example will change the font size as well as font color of text inside the cell in worksheet in Excel Workbook.

Code and Syntax

    Activeworksheet.Range("A1").select

    Selection.Font.Italic = True              'make the font italic in macro
    Selection.Font.Bold = False              'make the font bold in macro

    Selection.Font.Underline = xlUnderlineStyleSingle         'Underline the macro
    Selection.Font.ThemeColor = xlThemeColorLight2          ' Change the color of cell
    Selection.Font.TintAndShade = 0.399975585192419      'Change the shade of color

            Selection.Font.Name = "Calibri"                             'Change the name of font
            Selection.Font.Size = 20                                        'Change the size of macro
            Selection.Font.Strikethrough = False                      'Other features of font like strikethrough
            Selection.Font.Superscript = False                         'Subscript, Shadow, Underline etc.
            Selection.Font.Subscript = False
            Selection.Font.OutlineFont = False
            Selection.Font.Shadow = False
            Selection.Font.Underline = xlUnderlineStyleSingle

Selection.Font.Color = -16776961
Selection.Font.TintAndShade = 0
This is how we can change the Name , Size of font of Cell in excel. We can also change the color of font.

Tuesday 21 May 2013

Macro to append data from one sheet to another in Excel

Example :
Below code will copy data from sheet1 to sheet2. First of all we activate sheet1 and select all cells ranging from A1 to R8. Then we copy the selection.

After copying, we activate sheet2 and select A1 cell and paste the copied contents.

    Sheet1.Select
    Worksheets("Sheet1").Range("A1:R8").Select
    Selection.Copy
    Sheet2.Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

This is how we copy or append the data from one sheet to other in excel macro

Macro to add columns in Excel

Example - Below code will add a new column at B. Old data at column B will be shifted to right.

activeworksheet.Columns(“B:B”).Select
Selection.Insert

To delete the data in column B, just use delete method.
This is how we can add column at any position in excel using macro.

Macro to activate a workbook in Excel

Example -

In below example, workbook with title abc will be activated using excel macro.

Code / Syntax -

Application.workbooks("abc").activate

After you have activated the workbook, you can use the reference of that workbook as mentioned below

msgbox activeworkbook.name

This is how we can activate the workbook in excel using VBA macro.

Macro to add two numbers in excel

Example - In below excel macro example, two numbers a and b will be added .

Code -

a = activesheet.range("A1").value
b = activesheet.range("A2").value

activesheet.range("A3").value = a+b

In this example, cell A3 will have the sum of two cells A1 and A2.

This is how we can add two numbers in excel macro.

Macro to remove blank rows from Excel sheet

Example - Below code will remove all blank rows from the excel sheet

Range("A1:A100").Select

For i =0 To Selection.Rows.Count

If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then

Selection.Rows(i).EntireRow.Delete

End If

Next i

In above code we are deleting all empty rows between A1 to A100.
CountA function is returns the number of non-empty cells. When it returns 0 , That means entire row is blank.

Macro to add rows in Excel Sheet

Example - Below code will add/ Insert blank row in Excel Sheet

Macro Code / Syntax to Add new Row in Excel.

Rows("6:6").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Above code will add / Insert new row at row number 6. Old row at row number 6 will be pushed down.

This is how we can add or insert new / Blank row in excel.

What is olmailitem constant value

olmailitem constant value is 0.

In Microsoft Outlook, There can be 8 kinds of items as mentioned below.

olAppointmentItem - 1 - New Appointment
olContactItem - 2 - New Contact
olDistributionListItem - 7 - New DL
olJournalItem 4 - New Journal
olMailItem 0 - New Mail
olNoteItem 5 - Note
olPostItem 6 - Post
olTaskItem 3 - Task.

All these are constants defined in Microsoft outlook library.

Excel Macro to Send Email from Outlook with attachment

Below Excel Macro Example will send the email from outlook with attachment. You can attach any file like ,xls, .pdf etc.

You can also send an email based on cell value. For example - if cell value is Yes, Then it will send an email.
The email is sent automatically provided that your email client outlook is configured correctly.

Please note that this macro cannot be used to send an email from lotus notes.

You can send an email to multiple addresses/recipients by just editing the TO property of mail item object in below example.

Code / Syntax to send an email from outlook through excel macro.

Set Outlook = CreateObject("Outlook.Application")

Set Message = Outlook.CreateItem(0)

With Message

.Subject = "Any Subject "

.HTMLBody = "Any TextBody "

.Recipients.Add ("abc@gmail.com")

End With

 Set myAttachments = Message.Attachments 
 myAttachments.Add "C:\my.doc",1, 1, "my" 
 Message.Display

Message.Send

Above program will send an email to abc@gmail.com with my.doc attached to it.

Please note that to send the workbook as attachment, it can be done very easily with below line of code

ActiveWorkbook.SendMail "sagar.salunke@sunagrd.com"

Please note that your outlook client should be working fine.

Excel Macro Interview Questions and Answers

Well - If you are going to face the Excel Macro Interview then you have come to right place.
On this site, You will see important frequently asked questions in Excel Macro Interviews.

I have also given the answers to those questions with examples and codes. On left side of this page, you will find all advanced Interview Questions that are asked in Excel Macro Interviews.

Some sample interview questions are listed below :

If you want to download the excel macro in pdf format, you can put your comment below.

All above questions are asked in Interviews of various IT companies like Infosys, IBM, Cognizant, TCS, HCL etc. I advise you to go through all the top 50-100 questions mentioned on this site before you face any Excel Macro Interview.

Monday 20 May 2013

Difference between Cells and Range in Excel VBA Macro

Cells is used to refer to Single cell. While we use Range to refer to more than one cell.

Example -

Worksheets("Sheet1").Cells(2, 3).Value = 20
In Above example, We are writing 20 into C2 cell.

Using Range object, Same thing can be coded like below -

Worksheets("Sheet1").Range("C2").Value = 20

This is the difference between cells and Range object in VBA Excel Macro.

How to Select Entire Row/Column in Excel VBA Macro

Example - We can select entire row or column vey easily using below code.

Code and Syntax :

To Select Entire Row No 2 -
Workbooks("Book1").Sheets("Sheet1").Range("2:2").Font.Bold = True

To Select Entire Column A -
Workbooks("Book1").Sheets("Sheet1").Range("A:A").Font.Bold = True

To Select Cell A1 -
Workbooks("Book1").Sheets("Sheet1").Range("A1").Font.Bold = True

To Select Cell Range -
Workbooks("Book1").Sheets("Sheet1").Range("A1:B8").Font.Bold = True

To Select Multiple Cell Range -
Workbooks("Book1").Sheets("Sheet1").Range("A1:B8,G3:I9").Font.Bold = True

To Select Multiple Columns sequentially -
Workbooks("Book1").Sheets("Sheet1").Range("A:C").Font.Bold = True

To Select Multiple Columns Not sequentially -
Workbooks("Book1").Sheets("Sheet1").Range("A:A,C:C").Font.Bold = True
'Here Columns A and C will be selected.

So This is how we can do selections in VBA Excel Macros.

How to set the background color of cell using Excel VBA Macro

In below example, I have changed the background color of the A1 using a Macro.

Code / Syntax : -

Range("A1").Interior.ColorIndex = 8 ' Change the background color of Cell A1

Range("A1").EntireRow.Interior.ColorIndex = 8 'Change the background color of Entire Row.
This is how we can change the background color of Cell/Range in Excel using VBA Macro

Different Excel Cell Types in VBA Macros

Below is the list of All Cell Types in VBA Macros

xlCellTypeAllFormatConditions - Cells of any format
xlCellTypeAllValidation              -  validation criteria Cells
xlCellTypeBlanks                        - Empty /Blank cells
xlCellTypeComments                  - Cells with comments
xlCellTypeConstants                   - Cells with constants values
xlCellTypeFormulas                    - Cells having formulas
xlCellTypeLastCell                     - last cell in the used range
xlCellTypeSameFormatConditions - Cells having the same format
xlCellTypeSameValidation            - Cells having the same validation criteria
xlCellTypeVisible                          - All visible cells

Example -

msgbox Range("h3:h30").Cells.SpecialCells(xlCellTypeBlanks).Count

This will print the number of cells in given range that are of type - xlCellTypeBlanks. Means it will print the count of blank cells.

How to count blank cells in given range in Excel using VBA Macro

Below Example will count all blank cells from given range in the worksheets

VBA Macro Code / Syntax :

n = Range("h3:h30").Cells.SpecialCells(xlCellTypeBlanks).Count
MsgBox n 'Print all blank cells

Here xlCellTypeBlanks means that cell is blank.

n = Range("h3:h30").Cells.SpecialCells(xlCellTypeConstants).Count
MsgBox n ' Print all cells with constant values not formulas

This is how we can find out the blank cells in Excel Macro

How to Sort and Move the worksheets using Excel Macro

Below Example will sort the worksheets in alphbetical order and then move them accordingly.

VBA Macro Code / Syntax :

    Mysheets= Sheets.Count

    For i = 1 To Mysheets- 1

        For j = i + 1 To Mysheets

            If Sheets(j).Name < Sheets(i).Name Then
                Sheets(j).Move before:=Sheets(i)                   ' Swap the sheets
            End If

        Next j

    Next i

Above code will sort the worksheets in Workbook using Excel Macro in 2007/2010/2003.

How to Access Sheets (Worksheets, Charts, Dialogs) Using Name/Index in Excel Macro

Below Example will show you how we can Access various sheets like worksheets, charts, modules, and dialog sheets using name in Macro.

Macro Code / Syntax -

Worksheets("Sheet1").Activate
Charts("Chart1").Activate

DialogSheets("Dialog1").Activate

In above code we are accessing the sheet1, chart1 and dialog1 using Excel Macro.

How to rename the Excel Worksheet Using VBA Macro

Below Example will rename the given worksheet using Macro.

   Syntaxt / Code to Name a worksheet.

   Set ws= ActiveWorkbook.Worksheets("Sagar")
    ws.Name = "NewName"

   In above example, We have rename the "Sagar" worksheet with "NewName"

How to Create New WorkSheet using Excel VBA Macro

Below Example Shows how we can create a new Worksheet using Macro.

VBA Macro Code / Syntax : -

mySheet = "Sagar"

On Error Resume Next

Temp = Worksheets(mySheet).Name

If Err.Number = 0 Then

MsgBox "The sheet already Exists "

Else

Err.Clear

Worksheets.Add.Name = mySheet
MsgBox "The New sheet Created "

End if

This is how we can create a new worksheet Using a VBA macro in Excel 2010/2003/2007.

How to find out if particular Worksheet Exists Using Excel VBA Macro

Below Code/Example can be used to find if given worksheet exists in Workbook or not.

Macro Code to check if particular Worksheet Exists or Not in Workbook.

   Dim mySheet
    mySheet = "Sagar"
    On Error Resume Next
    temp = Worksheets(mySheet ).Name
    If Err.Number = 0 Then
        MsgBox "Given Sheet Exists "
    Else
        Err.Clear
       MsgBox "Given Sheet Does not Exist"
   End If

This is how we can find out whether worksheet exists or not using Macro

How to Create a New Excel Workbook using VBA Macro

Below Code/Example can be used to create a new Excel workbook and save it using VBA Macro.

Syntax and Example of VBA Macro to Create a new workbook:

Sub AddNewWorkbook()

      Set WB = Workbooks.Add
     WB .Title = "New WB"
      WB   .SaveAs Filename:="D:\MYNEWFile.xls"

End Sub

Above code will create a new workbook with title as New WB and It will Save the new Workbook to location D:\MYNEWFile.xls

Monday 13 May 2013

What is Excel Macro

Excel Macro is nothing but vb script code that is used to automate some repeated tasks in Excel Workbook.