none
What parts of VB code require error checking? RRS feed

  • Question

  • Hi Folks -

    I have recently written quite a few pieces of VB Script, however I want to go back and clean them up.  For instance, I want to add error handling where applicable.

    Is there a general rule as to what piece of VB code should include error handling, whereas other area may not be as important?

    Could someone suggest where I SHOULD put error handling in my code?

    Thank you!

    set objexcel = createobject("Excel.Application")
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    Dim objexcel, FSO
    Dim objworkbook1, objworkbook2, objworkbook3, objworksheet, objWorksheet1, objWorksheet2, objWorksheet3
    Dim i, startrow, intnewrow, endrow, countsheet
    Dim Firstcell, Lastcell, LastCell2, LastCell3 , LastCell4
    Dim CMSO, USACW, UKEU
    
    Dim args, HC_EXCEL_PREPPATH, HC_EXCEL_SUBPATH, HC_TEMP_UTIL_WB, HC_UTIL_WB, HC_ACT_FILE, HC_EXCEL_TAB, FILEXT, DATESTRNG
    Set args = Wscript.arguments
    
    HC_EXCEL_PREPPATH= args(0)
    HC_EXCEL_SUBPATH= args(1)
    HC_TEMP_UTIL_WB= args(2)
    HC_UTIL_WB= args(3)
    HC_ACT_FILE= args(4)
    HC_EXCEL_TAB= args(5)
    FILEXT= args(6)
    DATESTRNG= args(7)
    
    CMSO = "SAP_to_Cost_Center_CMSO_MU_Reference_Mapping.csv"
    USACW = "USA_Finance_Headcount_Contractors" & FILEXT
    UKEU = "UKEU_Finance_Headcount" & FILEXT
    
    Set objWorkbook2= objExcel.Workbooks.Open(HC_EXCEL_SUBPATH & HC_UTIL_WB)
    	
    If HC_EXCEL_TAB = "Start" Then
    
    	WScript.Echo "Updating Workbook : " & HC_UTIL_WB 
    	WScript.Echo "Updating Tab      : HC_Input_Data"
    	WScript.Echo "From Workbook     : " & HC_TEMP_UTIL_WB
    	
    	Set objworkbook1= objExcel.Workbooks.Open(HC_EXCEL_PREPPATH & HC_TEMP_UTIL_WB)
    	
    	'::-- Get LastCell of 'HC_Input_Data' tab of HC_UTIL_WB--::'
    	Set objWorksheet1 = objWorkbook2.Worksheets("HC_Input_Data")
    	xlUp = -4162
    	Set LastCell = objWorksheet1.Range("A" & objWorksheet1.Rows.Count).End(xlUp)
    	
    	'::-- Clear specified range in HC_UTIL_WB --::'
    	objRange = objWorkbook2.Worksheets("HC_Input_Data").Range("A2:E" & LastCell.Row).Clear
    	objRange = objWorkbook2.Worksheets("HC_Input_Data").Range("H3:O" & LastCell.Row).Clear
    	
    	'::-- Put all Sheets in for loop in HC_TEMP_UTIL_WB --::'
    	objExcel.Application.DisplayAlerts = False
    	
    	countSheet = objworkbook1.Sheets.Count
    	intnewrow=2
    	i = 1
    	for i = 1 to countsheet
    	Set objWorksheet = objWorkbook1.WorkSheets(i)
    	objworksheet.Activate
    	startrow = 2
    
    	'::-- Count the number of used rows --::'
    	endrow = objWorkbook1.Worksheets(i).UsedRange.Rows.Count
    
    	'::-- Copy the data --::'
    	 
    	objWorkbook1.Worksheets(i).Range("A" & startrow &":E"& endrow).Copy
    	
    	'::-- Paste in HC_UTIL_WB --::'
    	objWorkbook2.Worksheets("HC_Input_Data").Cells(intNewRow,1).Pastespecial
    	
    	'::-- Increment the row --::'
    	intNewRow = intNewRow + (endrow - startrow + 1)
    
    	Next
    	
    	'::-- Adjust formula columns to match last row on Column A --::'
    	Set objWorksheet1 = objWorkbook2.Worksheets("HC_Input_Data")
    	xlUp = -4162
    	Set LastCell = objWorksheet1.Range("A" & objWorksheet1.Rows.Count).End(xlUp)
    	Set SourceRange = objWorksheet1.Range("H2:O2")
    	Set FillRange = objWorksheet1.Range("H2:O" & LastCell.Row)
    	SourceRange.AutoFill FillRange
    	
    	objWorkbook2.RefreshAll
    	
    	objExcel.Application.DisplayAlerts = False
    	WScript.Echo " "
    	WScript.Echo "Updating Master CMSO CostCenter List..."
    
    	Set objworkbook3= objExcel.Workbooks.Open(HC_EXCEL_PREPPATH & CMSO)
    	
    	'::-- Clear used range in HC_UTIL_WB --::'
    	objRange = objWorkbook2.Worksheets("Master_CC_Data").UsedRange.ClearContents
    
    	'::-- Find last cell in CMSO Workbook --::'
    	Set objWorksheet3 = objworkbook3.Worksheets("SAP_to_Cost_Center_CMSO_MU_Refe")
    	xlUp = -4162
    	Set LastCell = objWorksheet3.Range("A" & objWorksheet3.Rows.Count).End(xlUp)	
    	
    	'::-- Copy Column A from CMSO Workbook and paste in tab "Master_CC_Data" of HC_Utility Workbook --::'
    	objRange = objworkbook3.Worksheets("SAP_to_Cost_Center_CMSO_MU_Refe").Range("A2:A" & LastCell.Row).Copy
    	objWorkbook2.Worksheets("Master_CC_Data").Range("A2:A" & LastCell.Row).PasteSpecial objRange
    	
    	'::-- Find last cell in CMSO Workbook --::'
    	Set objWorksheet2 = objworkbook2.Worksheets("Master_CC_Data")
    	xlUp = -4162
    	Set LastCell = objWorksheet2.Range("A" & objWorksheet2.Rows.Count).End(xlUp)
    	
    	objWorkbook2.Worksheets("Master_CC_Data").Range("C2").Value = "=TRIM(A2)"
    	
    	Set SourceRange = objWorksheet2.Range("C2")
    	Set FillRange = objWorksheet2.Range("C2:C" & LastCell.Row)
    	SourceRange.AutoFill FillRange
    	
    	objWorkbook2.Worksheets("Master_CC_Data").Range("C2:C" & LastCell.Row).Copy
    	objWorkbook2.Worksheets("Master_CC_Data").Range("A2:A" & LastCell.Row).PasteSpecial -4163
    	objRange = objWorkbook2.Worksheets("Master_CC_Data").Range("C2:C" & LastCell.Row).Clear
    	
    	'::-- Used LastCell to add "CMSO" to Column B of "Master_CC_Data" of HC_Utility Workbook --::'
    	objWorkbook2.Worksheets("Master_CC_Data").Range("B2:B" & LastCell.Row).Value = "CMSO"
    		
    	objworkbook1.close
    	objworkbook3.close	
    	
    Else
    
    '::-- Prepare Ascend FTE Submission Files --::'
    
    a=Split(HC_EXCEL_TAB,"$")
    b=ubound(a)
    For i=0 to b
    
    HC_EXCEL_TAB = a(i)
    HC_ACT_FILE = a(i) & FILEXT
    
    	'::-- Get LastCell in HC_EXCEL_TAB of HC_UTIL_WB --::'
    	Set objWorksheet2 = objWorkbook2.Worksheets(HC_EXCEL_TAB)
    	xlUp = -4162
    	LastCell2 = objWorksheet2.Range("C" & objWorksheet2.Rows.Count).End(xlUp).Row
    	xlUp = -4163
    	
    	'::-- Data starts on Row 6 in all Workbooks --::'
    	'::-- If LastCell returns less than 6, it means no data exists for that HC_EXCEL_TAB --::'
    	
    	If LastCell2 > 5 Then
    		
    		If HC_EXCEL_TAB = "HCT_ACT_06" Then
    			If fso.FileExists( HC_EXCEL_PREPPATH & USACW) Then
    				Call BuildHeadcountFile()
    			End If
    		ElseIf HC_EXCEL_TAB = "HCT_ACT_07" Then
    			If fso.FileExists( HC_EXCEL_PREPPATH & USACW) Then
    				Call BuildHeadcountFile()
    			End If
    		ElseIf HC_EXCEL_TAB = "HCT_ACT_08" Then
    			If fso.FileExists( HC_EXCEL_PREPPATH & USACW) Then
    				Call BuildHeadcountFile()
    			End If
    		ElseIf HC_EXCEL_TAB = "HCT_ACT_09" Then
    			If fso.FileExists( HC_EXCEL_PREPPATH & UKEU) Then
    				Call BuildHeadcountFile()
    			End If
    		ElseIf HC_EXCEL_TAB = "HCT_ACT_10" Then
    			If fso.FileExists( HC_EXCEL_PREPPATH & UKEU) Then
    				Call BuildHeadcountFile()
    			End If
    		End If
    	
    	Else
    		WScript.Echo "=========================================================================="
    		WScript.Echo "Takeda entity that corresponds to " & HC_EXCEL_TAB & " has no Headcount Data"
    		WScript.Echo HC_EXCEL_TAB & "_" & DATESTRNG & FILEXT & " Ascend FTE Submission File will not be built"
    		WScript.Echo "=========================================================================="
    				
    	End If
    Next
    
    WScript.Sleep 10000
    End If
    
    '::-- Exit Protocol --::'
    	
    objworkbook2.save
    objworkbook2.close
    objExcel.Quit
    WScript.Quit 0
    
    '::::::::::::::::::::::::::::::::::::::::::::::::::'
    '::-- P R O C E D U R E S  B E L O W   H E R E --::'
    '::::::::::::::::::::::::::::::::::::::::::::::::::'
    
    Sub BuildHeadcountFile
    
    	WScript.Echo "=========================================================================="
    	WScript.Echo "Takeda entity that corresponds to " & HC_EXCEL_TAB & " has Headcount Data"
    	WScript.Echo " "
    	WScript.Echo "Building " & HC_EXCEL_TAB & "_" & DATESTRNG & FILEXT & " Ascend FTE Submission File..."
    	WScript.Echo "=========================================================================="
    	
    	Set objWorkbook3= objExcel.Workbooks.Open(HC_EXCEL_SUBPATH & HC_ACT_FILE)
    			
    	'::-- Get LastCell in HC_ACT_FILE --::'
    	Set objWorksheet3 = objWorkbook3.Worksheets("Sheet1")
    	xlUp = -4162
    	LastCell3 = objWorksheet3.Range("A" & objWorksheet3.Rows.Count).End(xlUp).Row
    						
    	objRange = objWorkbook3.Worksheets("Sheet1").Range("A2:Z" & LastCell3).Clear
    		
    	'::-- Subtract 4 cells to make up the difference between where content starts between HC_EXCEL_TAB & HC_ACT_FILE  --::'
    	LastCell4 = LastCell2 - 4
    		
    	'::-- Copy content from HC_UTIL_WB in HC_EXCEL_TAB to HC_ACT_FILE  --::'		
    	objWorkbook2.Worksheets(HC_EXCEL_TAB).Range("H6:M" & LastCell2).Copy
    	objWorkbook3.Worksheets("Sheet1").Range("A2:F" & LastCell4).PasteSpecial -4163
    			
    	objworkbook3.save
    	objworkbook3.close
    
    End Sub

    • Moved by Bill_Stewart Tuesday, November 7, 2017 9:54 PM This is not "fix/debug/rewrite my script for me" forum
    Wednesday, September 20, 2017 10:21 AM

All replies

  • Every line that can throw an error.

    You would do better to not waste your time on VBScript as it is almost completely obsolete and will not be available in future versions of Windows.  It cannot manage any of the newer systems since MS is no longer supporting upgrades that VBS can use.

    All of this is much easier in PowerShell.


    \_(ツ)_/

    Wednesday, September 20, 2017 11:32 AM
  • You can update excel via powershell?
    Wednesday, September 20, 2017 12:08 PM
  • Indeed.

    You can do virtually the same as what you did here in powerShell.

    Example:

    # VB Version
    set objexcel = createobject("Excel.Application")
    
    # PowerShell Version
    $excel = New-Object -ComObject "Excel.Application"

    Only powershell has better error handling and live debugging options ...

    Cheers,
    Fred


    There's no place like 127.0.0.1

    Wednesday, September 20, 2017 12:14 PM
  • There are PowerShell modules for Excel.  To add to Fred's comment PS is interactive so you can test code one line at a time.

    Start here: https://mva.microsoft.com/en-us/training-courses/getting-started-with-microsoft-powershell-8276


    \_(ツ)_/

    Wednesday, September 20, 2017 12:17 PM