Rubberduck
Rubberduck copied to clipboard
rubberduck-vba
VBA unit test coverage
Justification Rubberduck is a fantastic tool that has allowed me to unit test legacy VBA code. I just don't know how much of the VBA logic is covered by the tests. Having coverage indication would enable more tests to be added leading to higher quality.
In a way related to issue #4840 but wanted dedicated issue.
Description As a minimum: after each test run, Lines not covered by the tests would be highlighted; green if they are covered, red if not. The highlighting could be the whole line or just in the side bar.
Ideally would be great to have some coverage statistics by line and even branch, of VBA functions that are called from unit tests
Additional context A subject matter expert in my company has created a series of models (or varying degrees of complexity) in excel using VBA. These models were then given to a development team to implement in a product. The problem being that there was no easy way of verifying of the original models were correct. While we had tests for the implementation there was no direct link to excel and so debugging was slow and painful... until I discovered Rubberduck. It is a fantastic tool and I have since implemented tests for 5 out of 6 models in excel. We then use those tests as the reference to ensure the implementation in the product is correct.
I know there are areas that are not covered by tests, so it would be fantastic if these could be highlighted after each test run.
I would love to be able to integrate this into our CI so that the VBA tests would run automatically everytime VBA code is changed pushed to the central repo, but that's a different story.
green if they are covered, red if not. The highlighting could be the whole line or just in the side bar
I remember asking Wayne about the outlines of a roadmap towards injecting our own "indicator bar" along the vertical scrollbar - we do have it somewhere in the dev chat history. It was never acted upon though... but it would indeed be a great idea.. I'd also put inspection results for the active module in it (with the added complexity that it now has to be an interactive control).
But we can't red- or green-highlight lines in the VBE - not without seriously hijacking the code panes, something we've been musing about for a long time.. the current best idea is, rather than hijacking the VBE and dealing with low-level COM at every corner, to put our entire UI in a single (maximized) toolwindow... that has been slated for v3.0, but the 2.x cycle still has a lot to accomplish before that can happen.
Test coverage as a feature is another challenge altogether, but one that "only" requires rock-solid code path analysis (CPA) - something that's currently blocking a growing number of inspections and tools. CPA is the next logical step in Rubberduck's understanding of VBA code.
Once that's implemented, we should be able to statically determine the test coverage of each module after each parse... assuming everything is early-bound and Rubberduck can "see" everything that's going on.
Coverage would have to be statically determined, because we're not the ones running the tests - we just tell the VBE to run the test methods, but we don't have any insights on what source (or compiled) code actually runs: that has to be determined by some post-compilation pass that essentially simulates the execution of each test, and marks traversed tree nodes as, well, traversed: the percentage of executable statements traversed during a complete test run, vs the total number of executable statements in the project, would be the closest we could dream to get I think.
About CI: that won't be possible, I think. Firstly because running Office in a headless server environment isn't a supported or recommended scenario, but then also because you'd need to somehow get the host process to load the VBE (which then loads its add-ins), and then instruct Rubberduck to parse the project / discover unit tests / run them / report results to CI server.... but there's no command-line argument for that.
If there's a way for RD to be hosted in a VBA environment that runs on a CI server, I agree it would absolutely be a game-changing must!
I would really like to see this feature become a reality. However, the implementation is kind of a headache.
First of all, a static analysis will probably not suffice. The problem is that tests might use test data from a sheet or table. There is no way a static analysis could know which test data will be in use.
Maybe let me explain how one would usually implement test coverage dynamically. What you need is to track which branches are executed in the tests while they run. I see basically two approaches here.
The non-invasive one requires a debugger providing sufficient data programmatically. With that one could track all executed statements. The problem with VBA is that we do not have any programmatic access to the debugger.
This leaves the universally applicable method, adding callbacks in every code block. This could be managed through a class similar to RD's Assert class. This would take a parameter indicating the module and lines the block contains. There are subtlties though. For one, one would have to split blocks at every line label and line number. The same applies for GoTo, GoSub and other jumping statements. In addition, one has to account for the possibility of errors altering the execution path and leading to partially executed blocks.
To avoid running the risk of damaging the user's code, this code instrumentation would have to happen in a copy of the project under test, which provides its own challenges.
To sum it up, I think this would be a nice feature, but the implementation would be a massive undertaking with a lot of pitfalls.
What if we prepend every logical line of code that contains executable statements (skip Dim, Sub, etc.), instead of just "code blocks"?
Dim i As Long
TestTracker.Hit "Project1.Module1.DoSomething#L42": For i = LBound(values) To UBound(values)
TestTracker.Hit "Project1.Module1.DoSomething#L43": DoSomethingElse i
TestTracker.Hit "Project1.Module1.DoSomething#L44": Next
Lines with a larger number of "hits" could ultimately be visualized using a darker shade of green, and the %coverage is the number of lines with >0 hits vs the total number of tracked lines; that can be aggregated at any level, too: procedure, module, project.
Caveats include line labels; the tracking code needs to not interfere with those.
ErrHandler: TestTracker.Hit "Project1.Module1.DoSomething#L73":
TestTracker.Hit "Project1.Module1.DoSomething#L74": MsgBox Err.Description
TestTracker.Hit "Project1.Module1.DoSomething#L75": Resume CleanExit
End Sub
Would that work?
I have a vba project with ~20,000 lines of vba and just started using RD unit testing now. I understand knowing which lines have run is very complicated, but would it be possible to at least indicate which Subs / Functions have unit tests written for them so I can track how many I have covered in my project.
It would then still be up to me to think of multiple tests per Sub/ Function to ensure I cover all lines/ paths inside those procedures.
I'm actually looking to exercise some VBA code using python's pywin32 library and a legacy macro-enabled workbook.
Does any viable workaround exist to generating some semblance of a coverage report on, for instance, a VBA module? I am a bit daunted by the prospect of having to debug hundreds and hundreds of lines containing legacy code to make sure my unit tests are covering all edge cases.
I understand that there are some fundamental issues as indicated by @retailcoder and @MDoerner which mean that a full-blown coverage report like that generated for other languages is currently not possible. However, seeing as lack of coverage makes refactoring legacy spreadsheets near impossible, I am certain that those experienced in VBA development have some workaround... Would you guys agree? If so do you perhaps have any suggestions?
@awa5114 Very basic approach: stick Debug.Print "{module}.{procedure}" as the first line in every method, then see which modules and procedures are called. Compare to a list of all modules in the project to find the % hit. Optionally use conditional compilation so these debug messages only appear in debug mode.
@Greedquest That's not a bad approach, but I was thinking on a more per-line basis...