Is automated testing of VBA UserForm possible?

[mldUK]

I have a fairly large Excel add-in that revolves around a GUI composed of a few UserForms, with standard VBA controls (listboxes, text boxes, radio buttons, command buttons etc.). It's a large enough project that testing it by hand is becoming unmanageable, so I am looking to automate parts of the testing process.

The main problem is that most of the automated testing tools I have looked into (Rational Robot, AutomatedQA TestComplete, etc) don't support the VBA controls so their tests are effectively just replaying mouse clicks and key presses, so aren't really maintainable. Network Automation's Automate 6 did a reasonable job of recognising and manipulating the controls but had the odd glitch (it didn't like dealing with radio buttons, for instance).

I also considered building the test automation into the add-in, but most of the user's time is spent interacting with a modal dialog which stops the automation script running in the background, so that's pretty much out of the question.

My question- does anyone here have any experience with an automated testing tool that
a) recognises VBA user form controls and can reliably interact with them
b) has support for test scripting with conditional branches and success/failure logging, rather than just record and playback
?

I've spent a while looking around QA forums but haven't found much there, so any help or leads would be appreciated.

Thanks for your help,

- Martin

 

[fumei]

Could you please clarify: "so I am looking to automate parts of the testing process."

Is this testing logic testing? That is, testing of the values of the controls? If so, then:

My question- does anyone here have any experience with an automated testing tool that
a) recognises VBA user form controls and can reliably interact with them
b) has support for test scripting with conditional branches and success/failure logging, rather than just record and playback

can be answered by stating that VBA itself:

a) recognises VBA user form controls and can reliably interact with them

b) supports error-handling and logic statements

Perhaps if you gave some more detail in what you have tried, and some specific conditions you are trying to determine.


Gerry

 

[mldUK]

Sorry- I'm not a QA person so the terminology is quite new to me.

The main reason we want automation is for regression testing- the product is mature and any new features are thoroughly tested by hand, so we are really looking for something to run through a number of realistic user interaction scripts by using the controls (press buttons, check checkboxes, select items from lists) and checking that the outputs are correct to catch regressions.

I tried writing a VBA script to drive the add-in, but most of the user interaction is with a modal dialog which stops Excel's VBA responding until it receives input from the dialog. This means that as far as we know, using VBA to drive the process isn't possible, but if you know of a way we can get around this limitation that would be helpful.

What we were looking for is a third party tool (like the automation tools mentioned above) that can interact with the GUI that Excel presents, in a semantic and scriptable manner, but again if VBA can in some way be made to do this that would be equally useful.

Thanks,

- Martin

 

[fumei]

I still do not understand.

"that can interact with the GUI that Excel presents"

VBA interacts with the GUI Excel presents. In fact, the GUI Excel presents is VBA.

"most of the user interaction is with a modal dialog which stops Excel's VBA responding until it receives input from the dialog. "

That sounds like a userform - a VBA userform - that is modal. A modal userform means that the userform has focus. Focus will NOT go back to the Excel file until the userform loses focus. However, VBA does not stop responding, it simply requires you to complete whatever code needs to be completed.

1. you can make the userform non-modal. This means the user can interact with Excel. You are not actually making a case for this...although it is hard to tell as you are not giving details.

2. It still sounds like a logic issue. You are testing values of the controls on the userform. THAT is VBA.

I will say it again. ALL logic requirements regarding the values of VBA userform controls (dropdowns, commandbuttons, selecting from lists, checkboxes) can, and is, done in VBA. You just have to write the logic.

Pseudo-code:

If Checkbox1.Value = True Then ' checked
Select case Listbox1.Text
Case "yadda"
SomeVariable = Somevalue
..do some other stuff
Case "blah blah"
SomeVariable = ADifferentValue
..do different other stuff
Case "Santa Claus"
If Checkbox2.Value = True
' In other words, IF
' Checkbox1 is checked, AND
' Listbox1 is selected as Santa Claus
' AND Checkbox2 is checked[/color red]
.. do abunch of other different things
End If
End Select
End If

...or whatever

Logic. "by using the controls (press buttons, check checkboxes, select items from lists) and checking that the outputs are correct "

The above uses the controls and checks the outputs. Whether they are "correct", or not, is totally up to you and is completely based on the LOGIC you attach to those values.

Unless you can clearly specify actual conditions and situations I can not see how I can suggest anything more.

Gerry

 

[mldUK]

Ah- I see the misunderstanding. The add-in already contains the logic that interprets the values of the controls, the "If Checkbox1.Value = True Then" part. What I need is something to pretend to be the user and actually use those controls- the equivalent of a VBA script that would read something like

UserForm.Show
UserForm.Checkbox1.Value = True
UserForm.Listbox.ListIndex = 3
UserForm.UsernameTextbox.Text = "John Doe"
UserForm.OK_Click

to simulate the user checking a checkbox, selecting an item in a list box, entering their name and pressing OK.

Writing a script like this in the same Excel project that contains the userform doesn't work- either the modal userform stops the script executing, or the script prevents the userform from updating until it finishes, neither of which permit testing more than a few "actions" at a time. Example- if I wrote a script like the above and set it as the action for a command button on the form (without UserForm.Show), it runs through the entire script before actually updating the form. If I run it as a macro, it pauses after "UserForm.Show" because the dialog is waiting for user input. I have tried making the userform modeless (not a solution I am optimistic about because I suspect it will cause a lot of breakages elsewhere in the code) but that just makes it run without updating the form- the problem still remains, I can't have a VBA script running and the userform responding at the same time.

"VBA does not stop responding, it simply requires you to complete whatever code needs to be completed."

This is precisely the problem- operating the form from within Excel is like trying to run two "threads" of VBA in parallel. This is why (as far as I can tell) an external tool is required.

 

[combo]

The .Show method passes control to the userform. You can either use something like:

Load UserForm1
With UserForm1
    .Caption = "test mode"
    .Show
End With

or use code in userform's module when it is on.

combo