UP | HOME
AutoGrid | Home | Blog

Mastering EXCEL VBA

Table of Contents

So this is it. My friend Vinod and I used to maintain a site called AutoGrid.info which we don't really maintain but every now and then we use Excel VBA for solving problems.

So I am thinking of consolidating everything here on this page. The plan is to have a big, very big page with plenty of content that you can use to solve your problems as well. You will find code here and of course the explanation.

1 Custom Function to count background color

Last week one of my colleague asked me how to count the cells in the excel sheet with specific color. Well there is no such in build function to do this job. So lets write our function. Yes we can write a custom function, also known as User Defined Function that will count the number of cells in a specified range for the specified background color.

  1. This function takes two arguments, first argument is the range of cells like A1:A2 and the second argument is cell that has the background color that you want to count.
  2. How to use this function. Enter this in the cell where you want the count to be displayed.

=count_background_color(A2:A16,A5)

A2:A16 is the Range of Cells

A5 is the cell with the background color that you want to count.

Here is the function code.

Function count_background_color(input_range As Range, cell_has_color As Range)

Application.Volatile
'Automatically update the formula whenever any cell is updated
' First parameter: Range of Cells
' Second parameter: Cell that has background color

Dim item As Range 'input_range is the first parameter
'Dim cell_has_color As Range 'Second parameter

Dim color_number As Variant

color_number = cell_has_color.Interior.Color
'Extract the color index of the second parameter

For Each item In input_range
    If item.Interior.Color = color_number Then
	count_background_color = count_background_color + 1
    End If

Next

End Function

I hope you enjoyed this blog.

2 Replace Text from Excel Sheet to Notepad

In this post we will show how to modify data in the Notepad from the Excel spreadsheet. We will write one macro here to do our job. I am sure you must have done manual copy-paste from excel to notepad many times. This macro will not only save your time but also make sure that there is no error that you could do manually.

There is an excel sheet with the two columns, Employee ID and Employee Name. We also have a notepad file which has only the Employee ID of the employee.

Our task is to replace the Employee Id in the notepad with their corresponding Employee Name. The Employee Name needs to be picked from the excel file.

Our Macro will one by one go through each of the Employee Id in the excel sheet and search it in the notepad file, if Employee Id is found in the notepad it will be replaced by the Employee Name which is present right next to the Employee Id

Backup of the Notepad file

As a safety measure a backup of the Notepad file is created and saved in a folder with the name BackupExcelSheetFileName. The Backup Notepad file will have Backup_ Prefixed to original name and each time the Macro is run the old Backup file will be over written.

How to use this Macro

  1. Lets assume the name of your excel sheet is replacetextnotepad.xls and the name of your notepad file is replacetextnotepad.txt
  2. The data in the Excel sheet and Notepad file is same as shown in the examples attached. Of course your data could be different.

NOTE: The important point to note here is that the Employee Id should be present in the first column starting from Cell A2 and the corresponding Employee Name should be present in second column starting from Cell B2.

  1. Press ALT + F11 to open the Visual Basic Editor
  2. Create a Module and copy-past the below code.
  3. Again come back to the Excel sheet and press ALT + F8 and run the Macro MacroReplaceFromExcelToNotepad
  4. Enter the Notepad file name. In our case the name is replacetextnotepad.txt

Click OK to Run the Macro. The Notepad file should be present in the same folder where the excel sheet is located.

If you enter wrong File name here an error is thrown.

  1. Now the task has been executed successfully and the Notepad File is also processed. You can open the Notepad and see the changes.
  2. After the execution of the Macro you can see that a new folder named replacetextnotepad.xls (Folder name is same as the Excel Sheet name with .xls extension) has been created which has the backup of the original Notepad file with the name Backupreplacetextnotepad.txt

Have fun!

3 Transpose Columns with corresponsing values

We all know that there is a feature in the excel sheet to transpose rows into columns or vice-versa. To transpose just select the row or column and paste-special with transpose checkbox selected but what if you want to do the transposing a complete table? There is no such feature in excel so for achieving two dimensional transposing we will write a macro.

Lets take an example to understand the problem. We have a data in the following format.

Column 1 Column 2 Column 3 Column 4 A D X B E Y C F Z

There is no limit to the number of columns. Each Column has some data in the Cells below it.

The challenge is to transpose this data into this format.

A Column 1 B Column 1 C Column 1 D Column 2 E Column 2 F Column 2 G Column 2 X Column 4 Y Column 4 Z Column 4

Here the data below each Column is clubbed together and their respective Headers are copied in the cell next to them. If there is no value under any Header then that Column should be ignored.

Sub TransposeColumns()

Dim input_sheet As Variant
input_sheet = ActiveSheet.Name
Worksheets(input_sheet).Select

Dim column1, row1 As Range
' create range variables to hold row and column values


Range("IV1").End(xlToLeft).Select ' select the last column cell

'#####################################################################
'############### Loop till each column ###############################

    For Each column1 In Range(Selection, Selection.End(xlToLeft))
    ' Loop from first column to last column
	'MsgBox "Column:" & column1.Value 'Comment this later
	If column1.Offset(1, 0).Value = "" Then
	'If there is no data in column then do nothing
	Else
	'If there is some data in the first column then proceed with Transposing

	   Worksheets(input_sheet).Select 'Select the Input sheet

	   column1.Offset(1, 0).Select
	   'Select first data which is below first Header

	   For Each row1 In Range(Selection, Selection.End(xlDown))
	    Call CopyTransposed_Output(row1.Value, column1.Value)
	   Next row1

	End If

    Next column1 ' Next Column

Worksheets("Output").Select
Range("A1").Select
MsgBox "Transposing Columns Completed"
End Sub

Sub CopyTransposed_Output(row1 As Variant, column1 As Variant)

Worksheets("Output").Select

Range("A65536").End(xlUp).Select 'Move just below the last row
ActiveCell.Offset(1, 0).Value = row1
ActiveCell.Offset(1, 1).Value = column1

End Sub

How to use this Code

  1. Create a Module and Copy the posted above.
  2. Create a Sheet and name it "Output".
  3. Come back to the input sheet that has the data to be transposed.
  4. Run the Macro.
  5. Now check the Output Sheet. It will have the transposed data.

4 Track who opened Excel Sheet - Access Logs

It very likely that multiple people would be working on the same excel sheet for updating the data. For example you want to gather some information from your employees like their address, then you can create an excel sheet and save it on a shared location. Your employees can open the file from that location and fill their details.

There might be a need where you might also be interested in knowing who has saved the excel sheet. This way you can track who has not filled their details!!

You can create a Macro in excel to enable user logs. Whenever a person saves any data in the sheet, his/her system username and time of saving can be stored. Follow the below steps.

  1. Create a Sheet named "Log" with the following format.

Timestamp Username

  1. Copy the following code in the WorkbookOpen Event
Private Sub Workbook_Open()
Worksheets("Log").Range("A65536").End(xlUp).Offset(1, 0).Value _
    = Format(Now(), "mm-dd-yy HH MM AMPM")
Worksheets("Log").Range("B65536").End(xlUp).Offset(1, 0).Value _
    = Environ("UserName")
End Sub
  1. Next whenever anyone opens the Excel Sheet his name will be stored in the Sheet "Log". You can hide this sheet so that others cannot see and modify it.

Simple and neat trick!!

5 Now Never miss to mention the subject in the mail

It happens many time that you write a mail but forgot to mention the subject. I know it is embarrassing to send an important official mail without a subject. It has happened to me also when I used to do it a lot and I remember my Manager making strange faces at me.

Well that was past, I realized that a mail without a subject brings a bad impression on others. After learning VBA I wondered whether it is possible to add some extra functionality in the outlook to alert us whenever we miss to mention the subject and I was able to write a very simple macro to achieve this extra feature which doesn't come with Microsoft Office Outlook.

Steps to write and store this Macro

  1. Open your Outlook.
  2. Press Alt+F11. This opens the Visual Basic Editor and then Press Ctrl+R which in turn open Project-Project 1 (left side)
  3. On the Left Pane, one can see "Microsoft Outlook Objects" or "Project1", expand this. Now one can see the "ThisOutLookSession".
  4. Double click on "ThisOutLookSession". It will open up a Code Pane on the right hand side.
  5. Copy and Paste the following code in the right pane (Code Pane) and save it
Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
Dim strSubject As String
strSubject = Item.Subject
If Len(Trim(strSubject)) = 0 Then
Prompt$ = "Subject is Empty. Are you sure you want to send the Mail?"
If MsgBox(Prompt$, vbYesNo + vbQuestion + vbMsgBoxSetForeground, "Check for Subject") = vbNo Then
Cancel = True
End If
End If
End Sub
  1. Now whenever u try to send a mail without subject, a pop-up is raised to remind you of the blank subject.

See how simple it is to use VBA to add these extra features!! If you like this post then why don't you subscribe to our mailing list to get such tricks right in your mailbox.

Have a nice day!

6 How to Recording a Macro in Excel?

The Easiest and the fastest way to learn Excel VBA Programming is by using the Recording Macro feature. This cool feature of Excel will record all the steps you perform on Excel sheet and generate a Code. You can later view the code and see how your actions are translated into VBA Code.

Procedure to Record a Macro

  1. Click Tools > Macro > Record New Macro
  2. Enter the desired Macro Name and click Ok to start Macro Recording .

If you want you can also give a shortcut Key to Recorded Macro. Select Store macro in “This Workbook” so that the Recorded Macro is saved right inside the Excel sheet. If you choose this option and send the sheet to someone then the Recorded Macro will also be sent.

Note: If you store macro in “Personal Macro Workbook” then the Recorded Macro will not be saved inside the Excel sheet. Recorded Macro will be stored in Personal Sheet. Personal.xls file is created when a Macro is recorded to it for the first time and it is saved in \XLStart directory of MS Office. It is advisable to store the frequently used Macros in “Personal Macro Workbook” so that they are always available from any sheet but if you send the Excel sheet to someone, the Macro will not be sent along with the Excel Sheet.

A Shortcut Key can also be assigned to the Recorded Macro. Press "s" to assign CTRL + s OR Press "S" to assign CTRL + SHIFT + S

In our next posts we will discuss some of the examples where we will record a macro to automate some day-to-day tasks.

7 Record a Macro to AutoFit cells in Excel

AutoFit is something that makes the data in the Sheet more presentable and readable. This is one example where we can create a simple Macro to apply AutoFit property to the sheet.

Procedure

  1. Start Recording a Macro and name it AutoFit
  2. Select entire sheet
  3. Double click on the divider between Column A and Column B
  4. Stop the Macro

The following code will be generated.

Sub AutoFit()
'
' AutoFit Macro
' Macro recorded 3/10/2008 by Ravi Sagar
'

'
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("A1").Select
End Sub

Notice the statement Cells.EntireColumn.AutoFit, it tells excel to apply Column wise AutoFit to the selected Cells. You can later use this Macro to AutoFit the entire selected worksheet. The advantage of creating such handy Macro is that you can assign these Macros to the custom buttons and place them on the toolbars. By creating Macros for operations that you use frequently you can save lot of time and work more efficiently.

8 Record a Macro to Merge/DeMerge cells in Excel

In our earlier posts we discussed how to record a Macro and we discussed one example to record a macro to AutoFit. Lets take one more example to create a Macro to Merge and De-Merge the selected cells quickly, which is otherwise a long procedure.

Steps to create a Macro for Merge

  1. Start Macro Recorder and name it as "MergeCells"
  2. Select the cells you want to merge. You can also select two blank cells.
  3. Click on "Merge and Centre" button on the Formatting toolbar.
  4. Stop the Macro Recorder.

Press ALT + F11 and open the Visual Basic Editor. You will see that the following code is generated.

Sub MergeCells()
'
' MergeCells Macro
' Macro recorded 3/7/2008 by Ravi Sagar
'

'
    With Selection
	.HorizontalAlignment = xlCenter
	.VerticalAlignment = xlBottom
	.WrapText = False
	.Orientation = 0
	.AddIndent = False
	.ShrinkToFit = False
	.MergeCells = False
    End With
    Selection.Merge
End Sub

Do not worry much about all the lines written here. The only thing that should be your concern is the line Selection.Merge, it tells Excel to Merge the selected cells.

Steps to create a Macro for DeMerge

  1. Start Macro Recorder and name it as "DeMergeCells"
  2. Select the cell you want to DeMerge. You can also select Merged Cell from the above operation.
  3. Right Click on the selected cells > Format Cells > Alignment Tab > Under Text Control, uncheck the "Merge Cells" check box.
  4. Stop the Macro Recorder.

Press ALT + F11 and open the Visual Basic Editor. You will see that the following code is generated.

Sub DeMergeCells()
'
' DeMergeCells Macro
' Macro recorded 3/7/2008 by Ravi Sagar
'

'
    With Selection
	.HorizontalAlignment = xlCenter
	.VerticalAlignment = xlBottom
	.WrapText = False
	.Orientation = 0
	.AddIndent = False
	.ShrinkToFit = False
	.MergeCells = False
    End With
End Sub

In this code the statement .MergeCells = False unmerges the selected cell.

I hope now you will get good idea about recording macros in the Excel sheets. I our future post we will discuss the recording of macros in OpenOffice.

9 How to use VBA Editor?

So you have recorded your first Macro in Excel VBA but most of the times you want to do something more that cannot be achieved with just recording and for customizing the Macro you need to Modify the VBA Code. VBA Editor is just for that, modifying and writing your own code.

What is VBA Editor?

VBA Editor is the Window where Code for Excel VBA is written. It is just like any other Text editor like Notepad and Wordpad. Instead of plain English the Code is written in the Language which Excel can understand. VBA Editor is also a Program to create and Edit VBA Macros. You can also call it as IDE (Integrated Development Environment)

How to Open VBA Editor

Select Tools > Macro > Visual Basic Editor Or simply press ALT + F11, it is the shortcut to open the VBA Editor.

Parts of VBA Editor

VBA Editor can be customized by the user. Many Windows can be added and removed but It is important to know the following Windows.

  • Editing window: Displays VBA code for editing and User Forms for design.
  • Project Explorer window: Displays the loaded VBA projects and the components of each project.
  • Properties window: Used to view and edit the properties of the current object.
  • Immediate window: Displays the output of debugging statements in your code.

Create a new Module

Steps to create a new Module.

  1. Open the Excel Worksheet where you want to create a Macro.
  2. Press ALT + F11 to open the VBA Editor
  3. In the Project Explorer Window right click on the VBAProject(Excel Sheet Name) > Insert Module
  4. By Default a Module with the name "Module1" will be created under a Folder Modules.

Edit existing Module

Steps to edit any existing Module.

  1. In the Project Explorer Window > Click on the small plus sign in front of VBAProject (Excel Sheet Name).
  2. Sub Folders will be expanded
  3. Go to Modules Sub Folder > Double Click on the Module you want to edit
  4. A new window containing the content of the Module will be opened. Do the necessary modification > Save the Module and Close the Window.
  5. Now you have just edited the Module!

10 Hello VBA World!

Ok so we have learned how to record a Macro and we discussed couple of examples where we recorded the macros. As we discussed in our earlier blog that for modifying our recorded Macros or to write our own Macro from beginning we need to use VBA Editor.

Now lets write a very simple VBA code to just display a message to the user. Yeah it is compulsory to have a Hello World! Program in all the Programming Books. So here it is.

  1. Create a new Module as explained earlier.
  2. Type sub HelloWorld and press Enter.
  3. Press Tab, type msgbox "Hello VBA World!", and press Enter.
  4. Press CTRL + S to save the Module. After completing these steps your Macro would appear as below.
Sub Hello_World()
 MsgBox "Hello VBA World!" 
End Sub

Run this Macro and be amazed.

  1. Go back to the Excel Sheet
  2. Press ALT + F8
  3. Select "HelloWorld" and press Run button When you run this Macro "Hello VBA World!" will be displayed on the screen. Congratulations! you have written and executed your first Excel VBA Macro.

If you like this post then why don't you subscribe to our mailing list to get the latest post right in your mail box.

11 Procedures in VBA

A procedure holds a group of VBA statements that accomplishes a desired Task. A procedure is a series of VBA statements that resides in a VBA module, which you access in the Visual Basic Editor. A module can hold any number of procedures. You have a number of ways to call, or execute, procedures. A procedure is executed from beginning to end (but it can also be ended prematurely).

Some procedures are written to receive arguments. An argument is simply information that is used by the procedure that is "passed" to the procedure when it is executed. Procedure arguments work much like the arguments you use in Excel worksheet functions. Instructions within the procedure generally perform logical operations on these arguments, and the results of the procedure are usually based on those arguments.

Declaring a Sub procedure

A procedure declared with the Sub keyword must adhere to the following syntax:

[Private | Public| Static] Sub name ([arglist]) [instructions] [Exit Sub] [instructions] End Sub

Now lets discuss this syntax a little bit in detail.

Private (Optional): Indicates that the procedure is accessible only to other procedures in the same module.

Public (Optional): Indicates that the procedure is accessible to all other procedures in all other modules in the workbook. If used in a module that contains an Option Private Module statement, the procedure is not available outside the project.

Static (Optional): Indicates that the procedure’s variables are preserved when the procedure ends.

Sub (Required): The keyword that indicates the beginning of a procedure.

name (Required): Any valid procedure name.

arglist (Optional): Represents a list of variables, enclosed in parentheses, that receive arguments passed to the procedure. Use a comma to separate arguments. If the procedure uses no arguments, a set of empty parentheses is required.

instructions (Optional): Represents valid VBA instructions.

Exit Sub (Optional): A statement that forces an immediate exit from the procedure prior to its formal completion.

End Sub (Required): Indicates the end of the procedure.

Procedures are used a lot when Macros are written, mainly because they make the code re-usable. Suppose you have written a code a sort the numbers in ascending order. Now if you want to again use this code then you can just write the sorting code inside a procedure and call it from by passing the arguments (the list of numbers you want to sort).

12 Change color of cell content using a shortcut

It is frustrating to format the report in excel. Most of the companies follows certain guidelines to present the report in a specified format like column headings should be in grey background color, the font size should be 8 etc. Changing the content color of the cell is done too often. To work faster you can use a shortcut to change the color of any cell or group of cells that you have selected.

  1. Create a new Module and enter the following piece of code in it.
Sub change_color()

 Selection.Font.Color = RGB(255, 0, 0)

End Sub

The code above is self explanatory. It will change the font color of the selected cells to whatever specified in the right hand side.

RGB(255,0,0) is the Red color. RGB(0,255,0) is the Green color. RGB(0,0,255) is the Blue color.

  1. Now press ALT + F8. A new macro with the name changecolor will now be visible. Now you can assign this Macro to a shortcut to run it directly. Click on the Options and assign a Shortcut Key like "e". Don't use system defined shortcuts like "s" which is for saving the file.
  2. After doing this just select the cell(s) and press CTRL + e or whatever shortcut key you have assigned in the step 2 above. The color of the cell content will be changed instantly.

See how simple it is to use VBA to create these small macros to make you more efficient in Excel!

13 Refresh all Pivots in the Excel workbook

As a part of my work I have to make many complex reports with so many Pivot tables to represent the data into various formats. On an average the reports that I work with has at least four to five pivot tables in a single workbook. Pivots work great but you have to manually refresh all the pivots whenever you update the data. Well there is a feature in the excel that will auto-refresh all the pivots in your workbook each time you open it, if you have time to do that then it will work fine but personally I don't want to close and re-open the excel sheet to refresh the pivots.

I would like to share this Macro that I wrote sometime back that will do your job. You can assign this macro to a Keyboard shortcut or create a custom button.

Sub RefreshAllPivots()
'
' RefreshPivot Macro
' Macro created by Ravi Sagar
'

''Call it from workbook_activate event

Dim pt As PivotTable
' Variable to store the Pivots in the workbook

Dim ws As Worksheet
' Variable to store the worksheets in the workbook

    For Each ws In ActiveWorkbook.Worksheets
    ' we are iterating through all the worksheets in the active workbook

	For Each pt In ws.PivotTables
	' Among the worksheets iterated in the outerloop we are checking for the pivots only
	    pt.RefreshTable
	    ' if there are any pivot then this line of code will refresh the first pivot stored
	    ' in the variable pt
	Next pt
	' Iterating to the next pivot

    Next ws
    ' Iterating to the next worksheet
MsgBox "All Pivots Refreshed"
' Once both loops are executed then display a meesage to the user.
End Sub

Just run this macro whenever you have done any changes in the data and the pivots will refresh in one go. It will also display a message box when the job is done.

I hope you like this post. If you want to get similar tips and articles directly in your mailbox then why don't you subscribe to our mailing list.

Have a wonderful day!

14 Use Excel as a Pocket Calculator

Sometimes you need to calculate a value before you enter it into your worksheet. Before you reach for your pocket calculator, you may like to know that Excel lets you enter a formula in a cell, and then use the result in that same cell. This way, the formula disappears and you're left with the result of the calculated value. This will not only save your time but also make you more efficient in excel because you won't need to open a separate application for finding out simple multiplications or other calculations.

Start by typing your formula into the cell (for example =65*88). Then, press F2 to put the cell into edit mode. Next, press F9 to perform the calculation. Finally, just hit Enter to insert this value into the cell.

Remember, when you use this technique, you replace your formula with the calculated value. If your calculation is based on the values of other cells, then Excel won't update the result if you change those other cells' values. That's the difference between a cell that has a value, and a cell that has a formula.

15 Function to find Unique Items in a list

It happens many times when you are writing a Macro and you need to find the unique items out of the a range of items. Suppose you have an excel sheet that has the employee ids of you the people working in a company but the list has duplicate entries. Though there is a feature in excel where you can just display the unique name but to do the same using Macro you need to write a code. This is required whenever you are dealing with duplicate items in a list but you are only interested in the unique values.

The below code take range of items that has duplicate value and return the range of unique items. Use this code anywhere in your Macro.

Function UniqueItemList(InputRange As Range, _
    HorizontalList As Boolean) As Variant
Dim cl As Range, cUnique As New Collection, i As Long, uList() As Variant
    Application.Volatile
    On Error Resume Next
    For Each cl In InputRange
	If cl.Formula <> "" Then
	    cUnique.Add cl.Value, CStr(cl.Value)
	End If
    Next cl
    UniqueItemList = ""
    If cUnique.Count > 0 Then
	ReDim uList(1 To cUnique.Count)
	For i = 1 To cUnique.Count
	    uList(i) = cUnique(i)
	Next i
	UniqueItemList = uList
	If Not HorizontalList Then
	    UniqueItemList = _
		Application.WorksheetFunction.Transpose(UniqueItemList)
	End If
    End If
    On Error GoTo 0
End Function

If you like reading this post then why don't you subscribe to our mailing list to get latest tips on office automation right in your mailbox.

16 Application Volatile

In our earlier post we showed one example of User Defined Function or Custom function that one can write to perform operations that are not provided by in build functions in the excel. Though there is one limitation of User Defined Function that the formula will not automatically recalculate the values whenever the cells are updated.

There is one way to overcome this limitation and that is to use Application.Volatile in the beginning of the Macro function. This will ensure that whenever any cell is updated in the sheet the function is recalculated.

Using Application.Volatile has further limitation. It makes the excel sheet a bit slow, this is because whenever you work on the sheet that has volatile User Defined Functions then the formula is recalculated. It's not a major lag though, much like other computer applications such as Open Office and free poker standalone games, it hardly affects your computer's processing power.

You can avoid using Application.Volatile if you are not doing any changes in the sheet.

17 Highlight Duplicates in Excel

sometime we have to highlight Duplicates in excel and later work on them rather then deleting them using option " Remove Duplicates " . here we will use " Conditional Formatting " for finding-out duplicates .

Let us assume the range we want to highlight duplicates is the range A1:A100.

  1. Select range A1:A100 Starting from A1. This will ensure the active cell is A1
  2. Go to Format>Conditional Formatting and choose "Formula is"
  3. In the right hand box type the formula as show below

=COUNTIF($A$1:$A$100,A1)>1

  1. Click the "Format" button and choose any desired format to apply to any duplicate entries. i advice to use some colour so that later u can " Filter by color " Option .
  2. Click "Ok" then "Ok" again .
  3. Now use " Filter by color " option to Consolidate all the Duplicates .