Retrospective: How We Could Have Failed Faster

For the last several months, I’ve been working on a project that involves reading data from a device via Bluetooth and then sending that data to another machine via RS232 serial port. At least, that’s my piece of the puzzle. It’s proved to be a challenging, but not insurmountable, task for  someone who is traditionally a CRUD programmer.

Yesterday, we discovered that we built the wrong software. I built what I was asked to build, but it isn’t what the client needs. The sensor device that was chosen doesn’t provide appropriate data for the use case. I spent the day salvaging whatever code I could for reuse in the end product, but the application itself (and several months of development) will be thrown away. This has prompted me to have a personal retrospective on this project to see if there were things we could have done to fail faster.

Be Careful with ToList()

I recently came across some code during a review that seemed perfectly fine at first glance, but upon further inspection, had potential to perform terribly. Take a look. What’s wrong with this code?

using (var context = new DbContext())
{
context.SomeTable.Where(t => t.Id == someId)
.GroupBy(t => t.Category)
.Select(tg => new { tg.Category, Profit = tg.Sum(p => p.Profit) })
.ToList()
.ForEach(SaveToDb);
}

Do you see it? It’s easy to miss.

Calling ToList() on the Enumerable materializes the query, iterating over the Enumerable in order to generate the List. Then, just a moment later, we iterate over the List. We’ve potentially doubled the run time of this method. In most cases, likely not a big deal, but what if the List contains thousands of items? We’re doing more work than necessary here.

Fixing this is a simple matter of storing the IEnumerable in a variable and using the traditional foreach syntax instead of the ForEach extension method.

using (var context = new DbContext())
{
var query = context.SomeTable.Where(t => t.Id == someId)
.GroupBy(t => t.Category)
.Select(tg => new { tg.Category, Profit = tg.Sum(p => p.Profit) });

foreach(var item in query)
{
SaveToDb(item);
}
}

It just goes to show that you really have to be paying attention when reviewing (or writing!) code. Some issues can be terribly difficult to spot at a glance.

Comments on Doc Comments

I was reviewing some VBA code over at Code Review Stack Exchange the other day and it got me to thinking about comments. The code wasn’t too bad, but there were so many comments in the code that, well…

I just read over this again and I think the fact that I mention the comments so often really highlights the issue. I barely even looked at the code because I was too distracted by all the comments.

Using Hampers Testing: Enter the Factory

I’ve been writing a lot of C# lately, and paying a lot of attention to my test coverage while I’m at it. Everything was going great until I wanted to use the FolderDialogBrowser to let my users select a directory.

FolderDialogBrowser implements IDisposable, so I naturally reached for a Using block.

using (var folderPicker = new FolderBrowserDialog)
{
    if (folderPicker.ShowDialog() != DialogResult.OK)
    {
        return;
    }

    //...
}

Then I stopped dead. I can’t do that. This will display a GUI and any hope of running automated tests against this method is lost.

The Professional VBA Developer

I came across a post on Programmer’s Stack Exchange yesterday that really irked me. It took me a little while to really digest what upset me about it, but I think I understand now. This developer was asking for more reasons to back up his claim that he should move his solution from VBA to C#. That in itself is fine. As I stated in my response, I understand his desire to move his solution to C#. I wish I could move all of my projects to the .Net platform myself.

No. Wanting to move to a more modern technology was not my issue with his question. My problem was with how he acted like working in an old technology gave him a pass on being a professional.

Registering a COM Add-In with InstallSheild LE

I’m taking a quick time out from getting Version 1.2 of Rubberduck ready to talk a little bit about working with COM Add-Ins and Installshield’s installer builder. (Awkward phrase, I know.) First, let me say that I’m not impressed with InstallShield LE, not even a little bit. As awkward as that phrase was, this tool is so much worse. Granted, it could be that it’s a great tool and I’ve just not tried the alternatives yet… but I doubt it. Something out there has to be better. When I find it, I’ll let you know.

I’ve digressed though. I’m here for a reason. I fight with this setting in the installer each and every time I build a new version. Enough is enough. This is my documentation. You see, I spent a few days creating a new installer. The installer would build without error and install without error, only for me to receive a

Could not load the add-in.

message upon opening the VBA IDE. It’s incredibly frustrating, but here’s how to do it right.

When creating an add-in for the VBA Editor, or any MS Office application really, you have to register the assembly for COM Interop. Most of the blogs and tutorials out there show you how to do this with Regasm or Visual Studio. That’s fine for development, but if you want any one else to ever actually use your software, you’ll probably need to create an MSI package and/or a Setup.exe. That program will need to register the assembly on the target machine.

Go to the Project Assistant, and then to the Application Files screen. You’ll need to take a couple of Actions here to properly create the installer. Obviously, you’ll need to add the Primary Output. What’s not so obvious is that you’ll also need to manually link the *.tlb file by selecting installation folder, then the Add File button, and pointing to the file in your \bin\Release directory.

The second not so obvious (and most frustrating) thing is you’ll need to set the properties on the assembly correctly. So, right click on the Primary Output, and select Properties. In the dialog, go to the COM & .Net Settings tab. Once you’re there, use the settings below.

Extract COM Information

Scan at Build == Dependencies and Properties

Check the COM Interop box.

 

You’ll need to do the same for the *.tlb file, only don’t check the COM Interop box this time. 

Now when you build the Assembly & Installer and then install, your COM Add-in will properly load. There’s a little more to getting your installer perfectly right than that, but I’m short on time and that hidden property is what tripped me up for the last few days. So, hopefully I’ve saved some one some pain. Even if that someone is me a month from now.

Until next time, Happy Coding.

VBA and Git

We all know that we should be using source control, but many of us don’t. For those of us working with Visual Basic for Applications, our excuse is often, “I can’t. What am I supposed to do? Keep the whole document file in source control? Yeah.. right….”. The answer is an emphatic “No”.  We shouldn’t be keeping Excel workbooks or Access databases or Word documents under source control. We should be keeping our *.bas and *.cls files in a repository though.

This is easier said than done though. You may be aware that you can export and import files into a VBA Project. If you are, you are also aware that exporting and importing all of those files is going to be painful if you have more than a handful of classes or modules. Luckily, our old friend the Microsoft Visual Basic for Applications Extensibility 5.3 library is going to come to the rescue again. The VbComponents collection has a couple of really handy methods: Import and Remove.  We’ll find the Export method is part of the VbComponent class itself.  We’ll need to get all of the files out of our project so we can set up our repo, so let’s start with the export.

The following method will loop through all of the code modules in a VBA Project and export them to a specified file path.

Public Sub ExportSourceFiles(destPath As String)

Dim component As VBComponent
For Each component In Application.VBE.ActiveVBProject.VBComponents
If component.Type = vbext_ct_ClassModule Or component.Type = vbext_ct_StdModule Then
component.Export destPath & component.Name & ToFileExtension(component.Type)
End If
Next

End Sub

Private Function ToFileExtension(vbeComponentType As vbext_ComponentType) As String
Select Case vbeComponentType
Case vbext_ComponentType.vbext_ct_ClassModule
ToFileExtension = ".cls"
Case vbext_ComponentType.vbext_ct_StdModule
ToFileExtension = ".bas"
Case vbext_ComponentType.vbext_ct_MSForm
ToFileExtension = ".frm"
Case vbext_ComponentType.vbext_ct_ActiveXDesigner
Case vbext_ComponentType.vbext_ct_Document
Case Else
ToFileExtension = vbNullString
End Select

End Function

This will work for any office application, but we only export standard modules and classes. I do this because we can’t import the code behinds of Forms, Worksheets, and the “ThisWorkbook” class back in. This is a drawback, but I find it to be acceptable because it encourages the use of classes and the separation of concerns. There shouldn’t be very much code in a code behind to begin with. Not being able to place that code into version control actively encourages us to put important code in a class or module that can be. Take note that the ToFileExtension function returns an empty string for objects that aren’t supported for import.

Now we can export all of the code in our project from the Immediate Window, very much like using a command line tool. Just type the name of the sub into the window (intellisense works here) and supply it a file path to export to. Pressing the Enter key will run the subroutine.

 

 

Now you can use this directory to set up a new Git Repository.  Seriously. Follow the link. I’ll wait.

Okay. You’re back? Good. Now you have your initial project under source control. Congratulations! You’re half way there! We just need to add a few lines to the .gitignore file. The .gitignore file tells the repository which files not to track. By ignoring office document files, we can keep our project in the directory with the rest of our repo, without worrying about Git tracking the changes. Here are the entries I’ve been using in my .gitignore file.

###################
## Microsoft Office
###################
*.xlsm
*.xlam
*.accdb
*.accde
*.accdr
*.laccdb

 

The next thing we need to be able to do is remove all of the code from our project. If we don’t remove the code before importing from our local repo, we’ll end up with doubles of all of the modules. Things with names like “Car(1)” and “Extensions(1)”. It’s ugly, but it’s pretty easy to clean our project out with a routine very similar to ExportSourceFiles. The important thing to note here is that we don’t want to remove the DevTools.bas module that contains the code for importing and exporting our other code. I don’t know what would happen if we removed the module that was currently running, but I’d rather not find out. If you’re feeling brave, please let me know what happens.

Public Sub RemoveAllModules()
Dim project As VBProject
Set project = Application.VBE.ActiveVBProject

Dim comp As VBComponent
For Each comp In project.VBComponents
If Not comp.Name = "DevTools" And (comp.Type = vbext_ct_ClassModule Or comp.Type = vbext_ct_StdModule) Then
project.VBComponents.Remove comp
End If
Next
End Sub

Finally, we’ll need some code to import modules back into our project after we’re pulled an update from our remote repository. Again, this is called from the Immediate Window and needs to be supplied the path to your local repo.

Public Sub ImportSourceFiles(sourcePath As String)
Dim file As String
file = Dir(sourcePath)
While (file <> vbNullString)
Application.VBE.ActiveVBProject.VBComponents.Import sourcePath & file
file = Dir
Wend
End Sub

Of course, all of this code is available from my repo on GitHub. So you can download it and try it for yourself. Just import the DevTools.bas file manually, and you’ll be able to use the tools in this article to install the rest of the repository into any VBA Project. Hopefully now that it’s easy to get your code into and out of your repository, you’ll be more likely to use source control and code like a pro.

Update: Rubberduck now has Git integration.

In the Zen Zone

I was reading a blog the other day about Zen Coding and it really just bummed me out. Zen Coding is a very real thing. You all know what I’m talking about, even if you don’t realize it. It’s when you’re just in it, completely in it. It’s when you don’t exist anymore, you’re in the zone and there’s just the code, nothing else. There’s just the code and you make the computer dance. You bend it to your whim and will. It’s the most beautiful thing that can happen to a programmer… and I have not had that experience in a very long time now.

What’s worse than not being able to experience the bliss of letting myself go, of losing myself in the meaningless lines of text in front of me that I give meaning to, is not understanding why I can’t find that place. It’s utterly frustrating. If I did not know this state of mind, this Zen Coding, I would not care. However I do know it and this knowledge is a burden, but rather than whine and bemoan my sad state of affairs, I would prefer to explore why this state of zen has eluded me. Perhaps more so, I want to explore how I can attain it more often.

The biggest road block to finding the zazen of programming is distraction. When the phone rings or someone comes into your office, you have just been kicked out of the zone. It will take you longer to figure out where you were than it will take to deal with the interruption. These kinds of distractions can not be stopped and they are hands down my worst nightmare. It’s never just one either. These distractions come in packs. One phone call takes you away to deal with an emergency, then just as you’re finishing up that one, the phone will ring again. To medigate the damage from this you must finish your current thought. Whatever it is, it can wait 30-60 seconds for you to wrap up your immediate thought. You’ll thank yourself later for having one less “what the fuck?” to cope with when trying to get back to what you were working on six hours ago.

Interruptions aren’t the only distraction though, oh no. Email is a huge culprit too. If you can, only check your mail a few times a day. Don’t jump to every little thing. They emailed you. They don’t expect an immediate response, so don’t give them one at the sacrifice of your concentration and focus. While you’re at it, turn off those “oh so helpful” desktop notifications. They catch your eye and take your mind away from the task at hand. The longer you spend consecutively focused on your task, the easier it will be to slip into Zen Coding.

What is the real problem though? Why is it so hard to just get in the zone? I have one word for you, stress. Fear is the mind killer and stress is its asshole cousin. In order to be productive, stress must be removed from the equation. Stress will scatter your mind like dandelion seeds in the wind. Do whatever you have to do to reduce and eliminate stress from your workday. Five minutes doing push-ups, or simply walking away from your desk, will buy you twice that time back. I promise.

There’s one more trap that I know I fall into far too often. I’m not enjoying myself. In order to be productive and happy, you must be enjoying yourself. There is no other way to be one with you code, but to want to be. Sometimes this means putting off a new feature and fixing that crap you wrote six months ago. You know the code I’m talking about. You wrote it in a hurry, or just didn’t really know what you were doing yet. It sits in your code base like a giant stinking turd taunting you every chance it gets. There’s never time to fix it, so it stays there just festering and driving you crazy. I’m telling you, take the time to polish that turd into something you’re proud of. The next thing you know, you’ll have refactored half of your code base because cleaning up that one method or class slipped you into zen mode. You’ve gotten your mojo back and each new change and feature will be that much easier to implement. You owe it to yourself. It will be cathartic. Just do it. Trust me on this one.