VBA and Git the Sequel: This Time it’s Integrated

I’ve talked before about using Git with VBA and also about the importance of using source control and other tools for our work. Truthfully though, using source control with VBA is still hard. This is mostly because getting the code modules into and out of the VB Project is hard, and harder to do right. Well, you may have heard that there’s a new duck in town. It’s taken me about 6 months of spare time, but Rubberduck v1.4 not only has a source control COM library that you can use right in VBA to work with Git VBA repositories, but you can also now branch, commit, pull, push, and merge right from inside of the editor.

Continue reading

Mocking the VBA Editor: Moq an IEnumerable that Works with Linq

It’s been a rough morning here. I’ve just spent six hours trying to properly create a mock for the VBProject interface. There’s very little information out there about this, so I thought I’d take a moment to jot this down and save someone else the headache. For all the grief this gave me, the solution is amazing simple.

Continue reading

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. Continue reading

What’s Up Duck?

I’ve been promising you an update on my pet project, Rubberduck, for quite some time now. I apologize for taking so long to get around to it. Both the project and this blog tend to come last in the grand scheme of my life, so it sometimes takes me some time to get around to things. I’m afraid that may be more or less the theme of this post.

Continue reading

Iterate over a date range in VBA

After years of VBA development, I’m still learning about the language every day. I was absolutely surprised when I tried this today. You can iterate over a date range just like you can iterate over a range of integers.

Public Sub LoopOverDateRange()
    
    Const startDate As Date = #5/13/2015#
    Const endDate As Date = #5/20/2015#
    
    Dim d As Date
    Dim i As Integer
    i = 1
    For d = startDate To endDate
        Debug.Print "Iteration " & i & ":" & vbTab & d
        i = i + 1
    Next
    
End Sub

This code will result in the following output.

Iteration 1:    5/13/2015
Iteration 2:    5/14/2015
Iteration 3:    5/15/2015
Iteration 4:    5/16/2015
Iteration 5:    5/17/2015
Iteration 6:    5/18/2015
Iteration 7:    5/19/2015
Iteration 8:    5/20/2015

Enjoy!

Capturing A Stored Procedure’s Return Value in VBA

Christopher J. McClellan:

Nice little primer on accessing stored procedure return values through ADODB in VBA.

Originally posted on Ramblings:

Those of you who have ever done any serious programming with SQL Server stored procedures will know that they return an integer value to their caller on completion. The default value is ‘0’, which indicates success, and any other value indicates a failure at some stage in its processing. VBA has the power to capture these return values through the ADODB.Command object but, in order to do so, there are a few points you should consider.

1. Invoke Parameters.Refresh before Execute.
2. Specify adCmdStoredProc as the CommandType.
3. Specify an OLE DB provider but don't use MSDASQL.
4. If the Stored Procedure consists of more than one statement (most do) make sure it starts with SET NOCOUNT ON.
5. Poke/prod any returned, open recordset before examining the Return Value.
6. Use a TRY..CATCH block in your Stored Procedure to handle exceptions.
7. Use additional error handling in your VBA code.

View original 1,012 more words

What is SQL, PL/SQL, T-SQL and difference between them

Christopher J. McClellan:

A brief history of SQL.

Originally posted on SQL with Manoj:

Today I got an email from a student who is pursuing his Bachelors degree in Computer Application (BCA). He visited my blog and sent me an email regarding his confusion with terms like SQL, T-SQL, PL/SQL, and asked me what is differences between them and how are they related? I had a chat with him and told the basic differences, but he further asked me how they are related to Microsoft SQL Server, Oracle, MySQL, etc? As he is studying SQL only based upon Oracle in his course curriculum, these all terms were not clear to him, so I cleared all his doubts while chatting with him.

After a while I had a same reminiscence that when I was a student I also had these doubts and confusions, and there was nobody to guide me, but I gradually came to know about this and it took some…

View original 728 more words