Archive for April, 2015
JDA Focus 2015
Posted by Christopher J. McClellan in .NET, JDA, Programming on April 29, 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.
Reusable OOP Progress Indicator for Excel VBA
Posted by Christopher J. McClellan in Programming on April 28, 2015
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.
Preamble
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.
ProgressView
Designer
First step is to create the UserForm that will be used for displaying the progress –…
View original post 1,542 more words
VBA Connection Pooling
Posted by Christopher J. McClellan in Programming on April 23, 2015
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.
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 post 1,030 more words
VB Attributes: What are they and why should we use them?
Posted by Christopher J. McClellan in Programming, VBA on April 21, 2015
Did you know that there’s code in your VBA projects that you can’t see in your editor? Want to see it? (Of course you do.)
Version 1.22 – Hotfix
Posted by Christopher J. McClellan in Programming on April 1, 2015
Squish the bugs. Real blog post coming soon.
There was an issue with some of the features when a password protected VBA Project was loaded. This caused several of the tool window, most notably the Test Explorer, to fail to open. As many of the more popular add-ins are password protected, this was a pretty big problem.
This has been corrected and the 32bit installer is available now. This post will be updated when the 64bit installer is available.