Friday, May 16, 2008

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:

1. Inventory

2. Payroll

Add the following reference to project:

1. Microsoft Excel 12.0 Object Library

2. MicrosoftOffice 12.0 Object Library

3. Microsoft Forms 2.0 Object Library

4. Microsoft Scripting Runtime

i. If you want to find total numbers of used rows and columns in the work sheet Payroll:

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

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:

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

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:

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

Share:

0 comments:

About Me

My photo
Based in Bangalore, INDIA, Amit is a MCC. He has an MCA from the U.P.T.U., INDIA and holds Microsoft certifications including SharePoint 2007/2010/2013. He is an contributor to MSDN and http://amitkumarmca04.blogspot.com/. Amit have experience in SharePoint,ASP.Net with C#, Classic ASP, VB,Macros,HTML,JavaScript,JSON,BootStrap and AngularJS.

Followers

Labels