Fakes for Application.FileDialog?

[owfischer]

Hello @retailcoder & everyone in the team,

a thousand thanks for developing Rubberduck - I am really happy about this great add-in!

I am not a professional programmer but a mechanical engineer who is using Excel VBA for computation projects. Recently I decided to learn how to use unit tests and TDD, and Rubberduck has helped me a lot to get started. But I am still trying to figure out how to do this correctly, so maybe my question is somewhat stupid.

I would like to test the return value of a function that uses the Application.FileDialog property. Since it requires user input, it is not straightforward to be used in an automated test.

So far, I did not really understand whether Rubberduck can also fake this kind of user dialog or not. I have successfully used the Fakes.MsgBox in other tests I wrote, but I don't know how to make this function testable without the need of user interaction - or if it is even reasonable to do so. How can I achieve this? Would I need to write my own fake or mock? How would that be done?

Many thanks for any help!

Best regards,
Oliver

[retailcoder]

Thanks for the feedback!

The Rubberduck COM API Fakes module hooks into the VBA7 library and essentially hijacks certain specific internal function calls, like rtcMsgBox and many others.

Application.FileDialog lives in the Excel COM library, which Rubberduck is agnostic to, so a different strategy is needed. With mocking you'd write test code that sets up a type that gets materialized at run-time and implements the exact same interface, but then you'd still need a way to provide the mocked dependency to your function under test - and that's where dependency injection comes into play.

But Excel.Application is a rather large interface to depend on, which makes it impractical for mocking. So what I'd do here, is to move the dependency to a separate class: write yourself a service class that wraps the FileDialog method, and make your function depend on that class instead. If the function is static / lives in a standard module, it could accept an instance of that service class as a parameter. If the function lives in an object/class instance, then the class instance could accept this service as a "constructor parameter" via a factory method. Either way, test code would now be able to supply an alternative implementation (a test stub class that implements the service class interface).

Basically this is a similar situation to, say, popping a UserForm: you decouple the testable logic from the UI by introducing an interface between the two, and then tests inject an alternative implementation that accepts the method call but implements it in a way that doesn't block the execution of a test.

Hope it helps!

[pflugs30]

Great question, and great answer! I agree with @retailcoder here. Since you say you are not a professional programmer, consider reading about Structural Design Patterns. I'm sure you'll recognize the ones which apply to this situation.

P.S. I'm a former mechanical engineer turned professional programmer. Be careful, or you may end up the same as me! ;-)

[owfischer]

I can't believe it but I made it!

This is pretty crazy for me because it was my first time working with both interface classes and this idea of service classes and stubs. But without this similar problem explained here in more detail, I would never have been able to figure this out. It took me two days to do it, but now I've learned a lot.

Thanks again for your help.

[pflugs30]

@owfischer let's see some code... :-)

[owfischer]

Okay! I hope it's not too embarrassing. I am curious about your comments.

For the given purpose it feels like a lot of overhead - as @retailcoder commented in the end of the blogpost cited above. And maybe someone could do more efficiently. But for now I am stoked that I have learnt something new!

And thanks @pflugs30 for the link on design patterns; I have realized that something like that exists and have started to learn about them.

The original function, not testable because depending on user interaction

My standard module LibFileOperations with my function to be tested "SelectFilename" looks like follows. Basically, I just want to pass some parameters to configure the Application.FileDialog window and return the selected path and filename as separate strings.

Below, the line DialogResult = .Show is the problem because this pops up the user dialog.

'@Folder "Libraries"
Option Explicit

Public Type PathFile
    Path As String
    FileName As String
    PathFile As String
    Selected As Boolean
End Type

Public Type FileDialogProperties
    DialogType As MsoFileDialogType
    DialogTitle As String
    ButtonCaption As String
    FilterFileType As String
    FilterFileExtension As String
    InitialPath As String
    InitialFilename As String
End Type

'...

Public Function old_SelectFilename(ByRef DialogProperties As FileDialogProperties) As PathFile
    Dim PathWithFileName As String
    Dim FilePicker As Office.FileDialog
    Dim DialogResult As Long

    Set FilePicker = Application.FileDialog(msoFileDialogFilePicker)
    With FilePicker
        .InitialFilename = DialogProperties.InitialPath & DialogProperties.InitialFilename
        .AllowMultiSelect = False
        .Title = DialogProperties.DialogTitle
        .ButtonName = DialogProperties.ButtonCaption
        If DialogProperties.FilterFileType <> "" _
            And DialogProperties.FilterFileExtension <> "" _
            And DialogProperties.DialogType = msoFileDialogFilePicker Then
            .Filters.Clear
            .Filters.Add DialogProperties.FilterFileType, DialogProperties.FilterFileExtension   'create custom filters
            .FilterIndex = 2                                    'preselect one of them
        End If
        DialogResult = .Show
        If DialogResult = -1 Then
            PathWithFileName = .SelectedItems(1)
        End If
    End With

    If Not DialogResult = 0 Then
        SelectFilename.PathFile = PathWithFileName
        SelectFilename = SeparatePathAndFileName(SelectFilename)
    Else
        SelectFilename = EmptyPathAndFileName(SelectFilename)
    End If
End Function

The function to be tested without dependencies

The refactored function looks as follows:

Public Function SelectFilename(ByRef DialogProperties As FileDialogProperties) As PathFile
    Dim FilePickerDialog As MyFileDialogService
    Set FilePickerDialog = New MyFileDialogService
    
    With New myTestableFileDialog
        SelectFilename = .Run(FilePickerDialog, DialogProperties)
    End With
End Function

The service classes to make the function testable

The class MyTestableFileDialog:

'@Folder("Libraries")
Option Explicit

Public Function Run(ByVal FileDialogService As IFileDialogService, ByRef DialogProperties As FileDialogProperties) As PathFile

    FileDialogService.PickFile DialogProperties
    
    Dim DialogResult As Long
    DialogResult = FileDialogService.Show

    If DialogResult = -1 Then
        Dim PathWithFileName As String
        PathWithFileName = FileDialogService.SelectedItems
        Run.PathFile = PathWithFileName
        Run = LibFileOperations.SeparatePathAndFileName(Run)
    Else
        Run = LibFileOperations.EmptyPathAndFileName(Run)
    End If
End Function

The functions SeparatePathAndFileName and EmptyPathAndFileName are not shown here but obviously return what the names say.

The abstracted service class IFileDialogService (the interface class):

'@Folder("Services.Abstract")
Option Explicit

Public Function PickFile(ByRef DialogProperties As FileDialogProperties)
End Function

Public Function Show() As Long
End Function

Public Property Get SelectedItems() As String
End Property

The concrete service class MyFileDialogService (which calls the real Application.FileDialog in the normal case) looks like that:

'@Folder("Services.Concrete")
Option Explicit

Implements IFileDialogService

Private m_DialogResult As Long
Private m_PathWithFileName As String

Private Function IFileDialogService_PickFile(ByRef DialogProperties As FileDialogProperties)
    Dim FilePicker As Office.FileDialog
    Set FilePicker = Application.FileDialog(DialogProperties.DialogType)
    With FilePicker
        .InitialFilename = DialogProperties.InitialPath & DialogProperties.InitialFilename
        .AllowMultiSelect = False
        .Title = DialogProperties.DialogTitle
        .ButtonName = DialogProperties.ButtonCaption
        If DialogProperties.FilterFileType <> "" _
            And DialogProperties.FilterFileExtension <> "" _
            And DialogProperties.DialogType = msoFileDialogFilePicker Then
            .Filters.Clear
            'create custom filters and preselect one of them
            .Filters.Add DialogProperties.FilterFileType, DialogProperties.FilterFileExtension
            .FilterIndex = 2
        End If
        m_DialogResult = .Show
        If m_DialogResult = -1 Then
            m_PathWithFileName = .SelectedItems(1)
        End If
    End With
End Function

Private Property Get IFileDialogService_SelectedItems() As String
    IFileDialogService_SelectedItems = m_PathWithFileName
End Property
Public Property Get SelectedItems() As String
    SelectedItems = IFileDialogService_SelectedItems
End Property

Private Function IFileDialogService_Show() As Long
    IFileDialogService_Show = m_DialogResult
End Function
Public Function Show() As Long
    Show = IFileDialogService_Show
End Function

My test stub class MyFileDialogServiceStub is this:

'@Folder "Services.Stubs"
Option Explicit

Implements IFileDialogService

Private m_DialogResult As Long
Private m_PathWithFileName As String

Private Function IFileDialogService_PickFile(ByRef DialogProperties As FileDialogProperties)
    m_DialogResult = -1
    m_PathWithFileName = DialogProperties.InitialPath & DialogProperties.InitialFilename
End Function

Private Property Get IFileDialogService_SelectedItems() As String
    IFileDialogService_SelectedItems = m_PathWithFileName
End Property
Public Property Get SelectedItems() As String
    SelectedItems = IFileDialogService_SelectedItems
End Property

Private Function IFileDialogService_Show() As Long
    IFileDialogService_Show = m_DialogResult
End Function
Public Function Show() As Long
    Show = IFileDialogService_Show
End Function

Here, in the line "m_PathWithFileName = ..." I was wondering if I could do something more realistic than just assembling the path & filename passed with the DialogProperties. Is this a tautology, or is it legitimate in a case like this?

The test using the stub

Then finally, the test (which calls the FileDialog stub ) looks like this:

'@TestMethod("Requires User Interaction!")
Private Sub Test_SelectFilename_Returns_Selection()
    On Error GoTo TestFail
    
    'Arrange:
    Dim PathFile As LibFileOperations.PathFile
    Dim DialogProperties As LibFileOperations.FileDialogProperties
    With DialogProperties
        .DialogType = msoFileDialogFilePicker
        .InitialPath = "C:\Temp\"
        .InitialFilename = "Test01.xlsx"
    End With
    Const Expected As String = "C:\Temp\Test01.xlsx"
    
    'Act:
    Dim FilePickerDialog As MyFileDialogServiceStub
    Set FilePickerDialog = New MyFileDialogServiceStub
    With New myTestableFileDialog
        PathFile = .Run(FilePickerDialog, DialogProperties)
    End With
    
    'Assert:
    Assert.IsTrue PathFile.PathFile = Expected

TestExit:
    '@Ignore UnhandledOnErrorResumeNext
    On Error Resume Next
    
    Exit Sub
TestFail:
    Assert.Fail "Test raised an error: #" & Err.Number & " - " & Err.Description
    Resume TestExit
End Sub

Is that somewhat acceptable for my debut work?

Greetings,
Oliver

[SmileyFtW]

Once I got your code into a workbook and compileable I looked at RD's code inspection (very helpful, BTW) and under "Argument With Uncompatible Type Inspection" it rerported that in your TestModule1 the test has

    Set FilePickerDialog = New MyFileDialogServiceStub
    With New MyTestableFileDialog
        PathFile = .Run(FilePickerDialog, DialogProperties)
    End With

the .Run is expecting FileDialogService As IFileDialogService, not As MyFileDialogServiceStub.
I haven't gone any further than that, but would encourage you to go through all of RD's code inspections and see where things have been overlooked.

All that being said, you are on a great journey. Maybe create a repo on GitHub so it will be easier for others to keep tabs on your updates, if you haven't already.
Great contribution!

[pflugs30]

At first glance, this code looks like a fantastic step in the right direction. Well done. I think it's worthy of a deeper dive on my part. Please give me a day or two to get back to you. 👍

[pflugs30]

Oliver (@owfischer):

Great job with this code, especially for not being a professional programmer! I can tell from your style that you have at least some programming background. It's always fun for me to talk with degreed MEs again.

I looked through your code here in Github, and I also put it into a simple Excel file. Sometimes, that just makes it easier for me to review it. All in all, WELL DONE. It looks pretty good, and I won't beat you up about anything. At the end of the day, as one of my former managers said, "to an engineer, a project is always 90% done. But 90% complete published code is better than 100% perfect code in your head!"

As such, please consider the following comments as simply another perspective. Try some of them, and see if you like them.

1. Nice job using Type to contain related data elements. These are analogous to "structs" in other languages. I recommend you only use them for "DTO"s (Data Transfer Objects) which are only going to hold data and need no functionality. As you code more, you'll find you want to add logic to validate the incoming values or define custom formatting logic. When that happens, switch to Class Modules with properties and Get/Set/Let functions.

2. In your test, you have this assertion:
   Assert.IsTrue PathFile.PathFile = Expected.

While this works, most testing frameworks would prefer you do something like this:
Assert.AreEqual Expected, PathFile.PathFile, "Custom message here".

This change is subtle, but it produces more readable code. RubberDuck's test runner can also produce a nice message for you when the test fails.

3. You commented that there's overhead. In one view, yes, you're correct, especially as you feel like you're building as much test code as production code. However, I assure you that it's usually worth it. Remember, tests ARE first-class code, as important as the code they protect.

4. Initially, the MyTestableFileDialog class was very confusing to me. I wasn't sure what it was trying to do. Eventually, I realized that you were using it to encapsulate logic around what to do with the results from the IFileDialogService. In other words, you're injecting the dependency into the Run method. Once I figured this out, then I understood your intent.

To improve the readability, I might suggest some name changes:

• IFileDialogService --> (same)
• MyFileDialogService --> ExcelAppFileDialogService (describe what it abstracts)
• MyFileDialogServiceStub --> StubFileDialogService (stub goes to front)
• MyTestableFileDialog --> FilePicker (distinguish from the other service classes)

5. Consider adding tests for cases when the User cancels the File Dialog.

6. Consider making the FileDialogProperties input parameter optional or moving it into the FilePicker.

7. Consider making the Stub class more configurable by allowing the test code to set whether the result will be an "ok" or a "cancel". Look at Moq for C# for examples of what can be done with tests in other languages.

8. Consider reading about constructor dependency injection. I hesitated to even mention this one because VBA doesn't support constructors. However, I would like you to know that it exists and to see how other languages make great use of it.

I could probably add a few more, but I think that's enough for now. Again, really great job. I don't even see code like this from some junior developers for a few months... :)

Here's the Excel file I assembled with your code:
FileDialogTests.zip

[owfischer]

Hello Matthew @pflugs30,

this is exactly the kind of comments I have hoped for but didn't dare to expect! I appreciate very much the time and effort you spend on the review, on helping me and bringing me on the right track. And thanks for the positive feedback - that's very encouraging!

I will need some time to think about it, try it out and see what comes from that. I might come back here with potential questions.

I hope the thread will be as helpful to others as it is to me.

Cheers!

[pflugs30]

My pleasure, @owfischer. Again, great job. Do remember... you're learning a language more than 30 years old, and it hasn't really kept up with the times. It's rather difficult to do "modern things" in this language and developer environment. Such things include:

• Dependency injection
• Object construction
• Source code management
• Modularization
• Testing

It's not to say that these things CAN'T be done (some of which are made nearly enjoyable by Rubberduck), but it's just that it's harder than in other languages, like .NET, Java, and React.

That being said, one thing I enjoy about coding in VBA is just that - the challenge. I'm forced to remember how to be efficient and deliberate in my coding. I have to be more declarative, and I can't rely on "syntactic sugar" like in other languages. Keeps me feeling young (or old, depending on how I feel that day). ;-)

All the best,
Matthew