Category: VBA Code

The following code can be used to import the contents of an excel workbook into an array from within a Visual Basic application.

Option Explicit

'Purpose     :  Reads an excel workbook's worksheet into a 2d array
'Inputs      :  sWorkbookPath           The path of the workbook to return the data from.
'               [sSheetName]            The name of the worksheet to return the data from.
'Outputs     :  Returns True if successful
'Author      :  Andrew Baker
'Date        :  31/12/2000 13:51
'Notes       :
'Revisions   :

Function ImportWorksheetFromExcel(sWorkbookPath As String, Optional sSheetName As String = "") As Variant
    Dim oExcel As Object
    Dim oWorkbook As Object
    Dim oWkSheet As Object
    Dim avValues As Variant
    
    On Error Resume Next
    'Check if file exists
    If Len(Dir$(sWorkbookPath)) > 0 Then
        Set oExcel = CreateObject("Excel.Application")
        If (oExcel Is Nothing) = False Then
            On Error GoTo ErrFailed
            'Successfully created an Excel Application
            'Open workbook
            Set oWorkbook = oExcel.Workbooks.Open(sWorkbookPath, False, True)
            'Add sheet to store results
            If Len(sSheetName) > 0 Then
                Set oWkSheet = oWorkbook.Sheets(sSheetName)
            Else
                'Just use first sheet
                Set oWkSheet = oWorkbook.Sheets(1)
            End If
            
            'Get used range
            Call RangeToArray(oWkSheet.UsedRange, avValues)
            
            'Close Excel
            oWorkbook.Close False
            oExcel.Quit
            Set oExcel = Nothing
        End If
    End If
    
    ImportWorksheetFromExcel = avValues
    Exit Function

ErrFailed:
    Debug.Assert False
    Debug.Print Err.Description
    On Error GoTo 0
End Function

'Purpose     :  Reads the values of a range into an array (much quicker than looping through a range)
'Inputs      :  rngInput                The range to extract the values from.
'               avValues                See outputs.
'Outputs     :  Returns the True on success.
'               avValues                An 2d array containing the values in the range.
'Author      :  Andrew Baker
'Date        :  31/12/2000 13:51
'Notes       :
'Revisions   :
'Example     :  Call RangeToArray(Worksheets(1).Range("A1:K1000"), avValues)

Function RangeToArray(rngInput As Object, avValues As Variant) As Boolean
    On Error GoTo ErrFailed
    avValues = Empty
    avValues = rngInput.Value
    RangeToArray = True
    
    Exit Function

ErrFailed:
    'Failed
    Debug.Print "Error in RangeToArray: " & Err.Description
    Debug.Assert False
    RangeToArray = False
    On Error GoTo 0
End Function

'Demonstration routine.
Sub Test()
    Dim avData As Variant, vCell As Variant

    avData = ImportWorksheetFromExcel("C:\Book1.xls")
    For Each vCell In avData
        Debug.Print "Cell: " + vCell
    Next
End Sub

Below is a simple function to add or subtract a number of weekdays to a specified date.

/// <summary>
/// Takes a reference date and add or subtracts a specified number of weekdays.
/// </summary>
/// <param name="date">The reference date.</param>
/// <param name="offset">The number of days to offset by.</param>
/// <returns>The reference date plus or minus the specified number of weekdays.</returns>
private DateTime AddWeekDays( DateTime date, int offset)
{
	int daysAdded = 0;
	int addDay = 1;
	if (offset < 0)
	{
		addDay = -1;
	}
	DateTime result = date;
	do
	{
		if (daysAdded == offset)
		{
			break;
		}
		if (result.DayOfWeek != DayOfWeek.Saturday && result.DayOfWeek != DayOfWeek.Sunday)
		{
			daysAdded = daysAdded + addDay;
		}
		result = result.AddDays(addDay);
	} while (true);
	return result;
}