Code related to Excel Macros

Code related to Excel Macros

Code related to Excel Macros:

In this article I am trying to explore some important code related to VB macros which can help you to write robust VB macro based excel project.

For all code I am using the “Test.xls” excel file which have two work sheets:

<!--[if !supportLists]-->1. Inventory<!--[endif]-->

<!--[if !supportLists]-->2. Payroll<!--[endif]-->

Add the following reference to project:

<!--[if !supportLists]-->1. Microsoft Excel 12.0 Object Library<!--[endif]-->

<!--[if !supportLists]-->2. MicrosoftOffice 12.0 Object Library<!--[endif]-->

<!--[if !supportLists]-->3. Microsoft Forms 2.0 Object Library<!--[endif]-->

<!--[if !supportLists]-->4. Microsoft Scripting Runtime<!--[endif]-->

<!--[if !supportLists]-->i. If you want to find total numbers of used rows and columns in the work sheet Payroll:<!--[endif]-->

Dim wsheet_Payroll As Excel.Worksheet

Dim USEDROW_Payroll As Long

Dim USEDCOL_Payroll As Long

‘Create connection to Payroll worksheet

Set wsheet_Payroll = Worksheets("Payroll")

‘Activate the Payroll shett

wsheet_Payroll.Activate

‘Find total number of Used Row in the Payroll sheet

USEDROW_Payroll = wsheet_Payroll.UsedRange.Rows.Count

‘Find total number of Used Row in the Payroll sheet

USEDCOL_Payroll = wsheet_Payroll.UsedRange.Columns.Count

<!--[if !supportLists]-->ii. If you want to find first occurrence some text(for example: Deducted) and want to delete that column in which text found in the work sheet Payroll:<!--[endif]-->

Dim wsheet_Payroll As Excel.Worksheet

Dim USEDROW_Payroll As Long

Dim USEDCOL_Payroll As Long

Dim Driverrange_Payroll As Range

Dim oTemp As Range

Dim ROWSTART_Payroll As Long

Dim COLSTART_Payroll As Long

‘Create connection to Payroll worksheet

Set wsheet_Payroll = Worksheets("Payroll")

‘Activate the Payroll shett

wsheet_Payroll.Activate

‘Find total number of Used Row in the Payroll sheet

USEDROW_Payroll = wsheet_Payroll.UsedRange.Rows.Count

‘Find total number of Used Row in the Payroll sheet

USEDCOL_Payroll = wsheet_Payroll.UsedRange.Columns.Count

‘Define the range in which you need to search the text

Set Driverrange_Payroll = wsheet_Payroll.Range("a1:HV" & USEDROW_Payroll)

'---------Find Deducted in Payroll Sheet------------

Set oTemp = Driverrange_Payroll.Find("Deducted")

If Not oTemp Is Nothing Then

‘If Deducted exist in the Payroll sheet

‘----Store row number------

ROWSTART_Payroll = oTemp.Row

‘----Store column number------

COLSTART_Payroll = oTemp.Column

'----Delete column --------

wsheet_Payroll.Columns(COLSTART_Payroll).Delete

'------------------------------------

Set oTemp = Nothing

End If

<!--[if !supportLists]-->iii. If you want to find first occurrence some text(for example: EmplidSearch) and want to insert new column with value after that column in which text found in the work sheet Payroll:<!--[endif]-->

Dim wsheet_Payroll As Excel.Worksheet

Dim USEDROW_Payroll As Long

Dim USEDCOL_Payroll As Long

Dim Driverrange_Payroll As Range

Dim oTemp As Range

Dim ROWSTART_Payroll As Long

Dim COLSTART_Payroll As Long

‘Create connection to Payroll worksheet

Set wsheet_Payroll = Worksheets("Payroll")

‘Activate the Payroll shett

wsheet_Payroll.Activate

‘Find total number of Used Row in the Payroll sheet

USEDROW_Payroll = wsheet_Payroll.UsedRange.Rows.Count

‘Find total number of Used Row in the Payroll sheet

USEDCOL_Payroll = wsheet_Payroll.UsedRange.Columns.Count

‘Define the range in which you need to search the text

Set Driverrange_Payroll = wsheet_Payroll.Range("a1:HV" & USEDROW_Payroll)

'---------Find Deducted in Payroll Sheet------------

Set oTemp = Driverrange_Payroll.Find("EmplidSearch")

If Not oTemp Is Nothing Then

‘If Deducted exist in the Payroll sheet

‘----Store row number------

ROWSTART_Payroll = oTemp.Row

‘----Store column number------

COLSTART_Payroll = oTemp.Column

'----Insert column after empid--------

wsheet_Payroll.Columns(COLSTART_Payroll).Offset(, 1).Insert

'------------------------------------

'----------Enter value =Deducted in all columns---------

wsheet_Payroll.UsedRange.Columns(COLSTART_Payroll + 1).Value = "Deducted"

'------------------------------------

Set oTemp = Nothing

End If