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
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


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

JDA Focus 2015

I don’t normally write about my work here, but I just spent the last week in sunny Orlando, FL at JDA Software’s Focus conference. I figured I might as well sum up the experience here before its all over and begins to fade.

For those of you not familiar with JDA, they’re a software company who has a range of products spanning the entire supply chain from manufacturer right down to actually placing the product on the shelf. My work brings me into the macro and micro space planning realms. Or rather, I support the people  in my company who use the Floor Planning and Space Planning software.  This involves automating that software and interacting with JDA’s database product.

Continue reading

Reusable OOP Progress Indicator for Excel VBA

Featured Image -- 405

Christopher J. McClellan:

The progress bar I wrote for Access is by and far my most popular post to date. I’ve been meaning to write one that uses a UserForm so that it would be portable to any of the Office Host Apps, but looks like a friend of mine beat me to it. You should check it out.

Originally posted on retailcoder:


For the past couple months I’ve been pretty (very) busy with my Rubberduck project, an open-source add-in for the VBE (the VBA IDE) packed with cool features that I encourage you to explore (see the “Features” page on the official website) – but I’m not here to talk about RD, otherwise I would have posted on Rubberduck News.

I’m here to blog about the last piece of VBA code I’ve written, in response to a recent tweet from @ExcelEasy to which I replied:

@ExcelEasy well done! #Challenge: make a more #OOP one without using the form’s default/global instance! #becausewhynot

So, building on this article, I went and implemented my own – this post is essentially a walkthrough for the ProgressIndicator class, the most recent addition to my VBTools GitHub repository.



First step is to create the UserForm that will be used for displaying the progress –…

View original 1,542 more words

VBA Connection Pooling

Christopher J. McClellan:

Interesting read about Connection Pooling in VBA and SQL Server. I knew circular references are bad juju, but I didn’t know about the “App” property of a connection, or that `conn.Close` doesn’t actually sever the connection. The connection isn’t really severed until the `ADODB.Connection` is garbage collected. Highly recommended reading here.

Originally posted on Ramblings:

I was messing around with SQL Server Profiler recently and thought to myself “this would be a good way to illustrate VBA connection pooling to people”. Many books recommend using connection pooling when building apps that involve more than a trivial amount of SQL database work. Most of those books describe connection pooling and how to perform it, but none backup their assertions with actual proof. Of course, they are correct in what they are saying and connection pooling is a very useful paradigm to implement. This blog post, however, goes one step further and shows you what is happening ‘on the server side’.

The screenshot below you’ll see I’ve got 3 apps open: SQL Server Profiler (top), MS Access VBA IDE (middle) and Task Manager (bottom).


You’ll see I’ve already started a trace on Profiler and also stepped into the VBA procedure ‘TestConnection’.  I have instantiated and instance of…

View original 1,030 more words