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
Comments