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.

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.


 

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 :

• Macro code to add new workbook and save it
• Macro code to count blank values
• How to set the background color of cell using Excel Macro
• Macro to rename a excel worksheet
• different excel cell types in Macro
• How to Select Entire Row/Column in Excel VBA Macro
• Excel Macro to Send Email from Outlook with attachment
• Macro to add rows in Excel Sheet 
• Macro to remove blank rows from Excel sheet
• Macro to change the font color and size of Cell in Excel Macro
• Macro to calculate average of specified cell values in Macro

• Excel Macro to read data from text file
• Excel Macro to create text file
• How to create and use dictionary object in excel m...
• How to create and use array in excel macro
• How to create modules in Excel Macro
• Excel Macro to convert the data type of cell value...
• Excel Macro to split the cell value or string
• Excel Macro to extract the part of cell value or s...
• Excel Macro to remove the spaces from the cell val...
• Excel Macro to find the sub string
• Excel Macro to convert the string text to upper ca...
• Excel Macro to convert the text in lower case
• Excel macro to get the month name of given date
• Excel macro to get the day name of given date
• Excel Macro to get the date difference
• Excel Macro to get the stock data from yahoo finan...
• Excel Macro to process xml file
• Excel Macro to get stock finance data from Google ...
• Excel Macro to display a dialog box .
• Excel Macro to get the current system time
• Excel Macro to get the year from given date
• Excel Macro to get yesterday's date
• Excel Macro to add leading zero to cell value
• List of commonly used Excel functions

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.

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

What is Excel Macro

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