How to unit test a excel form with database connection RRS feed

  • Question

  • Dear all,

    I was wondering how I could possibly  unit test a user form with database connection.

    I will quickly list the process steps and then a simplified version of my code.

    1) User fills out activex controls embedded in excel spreadsheet.

    2) User clicks Button1 to submit

    3) VBA Button1 Handler will retrieve all user inputs from the excel spreadsheet

    4) User inputs are injected in DataLoader Object

    5) DataLoader Queries database

    The coding looks as follows:

    Sub Button1_Click() Dim DataLoader As DataLoader Dim checkbox As Shape Set DataLoader = New DataLoader Set checkbox = ActiveSheet.Shapes("Check Box")

    'more checkboxes and input boxes follow ..... 'Get data from activex control user inputs and inject it into my DataLoader Object (property Let) On Error Goto ErrHandler: DataLoader.LoadAll = checkbox.ControlFormat.Value 'DataLoader establishes database connection 'DataLoader queries database based on checkbox values injected into it Exit Sub ErrHandler: ' error handling code Resume Next End Sub

    I am not sure how I to unit test this.

    1) As ActiveSheet.Shapes("Check Box") directly accesses the user form, I can not really inject mock data into this?

    2) As the errors are thrown and caught in the button handler:

    DataLoader.LoadAll = checkbox.ControlFormat.Value

    The above will throw an error if the checkbox is a String and LoadAll is a boolean. However, this means unit testing the DataLoader Object does not cover the error that is thrown in the handler. How would I unit test the button handler?

    Your help would be greatly appreciated. Thank you very much.

    Kind regards,


    Monday, May 13, 2013 2:00 AM