Excel macro tutorial

Major topics covered in this excel macro tutorial are -

1. Excel Macro

1. Why we need excel macros
2. Applications of excel macro

2. Visual Basic Editor

1. Recording excel macros
2. Debugging excel macro
3. Running
4. Modules
5. Sub routines
6. Functions

3. Excel Macro Settings

1. Saving excel macro workbooks
2. Enabling excel macro
3. Assigning Macros to the buttons

4. Excel Object Model

1. Objects
2. Collections
3. Methods
4. Properties

5. Workbooks

1. Creating new workbooks
2. Deleting workbooks
3. Opening workbooks
4. Saving workbooks

6. Worksheets

1. Add new worksheet
2. Edit worksheet
3. Delete worksheet
4. Sort worksheets
5. Move worksheets to different workbooks and within a workbook

7. Working with data using Excel Macro

1. Sort data
2. Filter data
3. Insert rows and columns
4. Delete existing rows and columns
5. Format the cells 
6. Find blank rows and cells
7. Remove spaces from the cell values
8. Replace the cell values with other text

8. Working with Charts using Excel Macro

1. Create a chart
2. Delete a chart
3. Edit chart options

9. Pivot Table and Pivot charts

1. Create a pivot table
2. Edit pivot chart

10. Working with other office products

1. Send mail with attachment
2. connecting to access database

The link to buy this book on excel macro is here. 

Topics covered in this book are -

1. Excel Macro - what are they and why we need them.
2. Visual Basic Editor in Excel - recording, running, debugging, modules, sub, functions.
3. Macro Settings - saving, enabling, assigning buttons to macros on Tool bar.
4. Excel Object Model  - objects, collections.
5. Workbooks - create, open, read, save, edit, protect, delete, search in directory, find open books.
6. Worksheets - add, delete, hide, move, reorder, sort, changing tab Colour of sheet.
7. Manipulating and formatting with data->copy/paste, trim, font, padding, replacing, filter, add/delete rows and columns, working with cells in the range, functions and formulae.
8. Automating Charts ->creating new chart, delete, edit chart source, legend etc.
9. Working with Pivot Table and Charts.
10. Communicating with other office products like outlook, word, Powerpoint and access.

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 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.

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?

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 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.

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.