Blogging Duck
VBA Rubberducking
I intended to write about Rubberduck 3.0 progress last December, but things snowballed during the Holidays and here we are two-three weeks later and wow, time flies! Happy New Year dear readers (belatedly, I guess), 2023 is full of promises, and there are very nice things going on that I need to take a moment… Continue reading Rubberduck 3.0: January Update →
One of the objectively coolest features in Rubberduck is the Fakes API. Code that pops a MsgBox for example, needs a way to work without actually popping that message box, otherwise that code cannot be unit tested… without somehow hijacking the MsgBox function. The Fakes API does exactly that: it hooks into the VBA runtime,… Continue reading Rubberduck.Fakes Gets an Upgrade …
The next major version of Rubberduck is currently in [very] early development stages – saying that there is a lot of work ahead would be quite an understatement, but the skeleton is slowly taking shape, and things are looking very, very good. Since the beginning of the project, Rubberduck’s user interface components (other than dialogs)… Continue reading Rubberduck 3.0 Progres…
As I wrote last July, I’ve started to get more time for myself lately, and that means I get to tackle a number of long-standing projects that have been on the backburner for way too long. One of them is the rewrite of the project’s website, which has been “under construction” ever since it was… Continue reading Website News →
So, I haven’t posted here for well over a year now, and Rubberduck ‘s last “green release” was in May of 2021 (18,773 downloads as of this writing – the download stats on the site’s front page are stale, as of last February). The website has been “under construction” forever, and all this time I… Continue reading Untitled Icebreaker →
As Rubberduck started to beef up its static code analysis capabilities in late 2015, it became evident that writing VBA (or VB6) code with Rubberduck loaded up in the Visual Basic Editor (VBE) would inevitably change not only how we work in VBA, but also how we write our VBA code in the first place.… Continue reading Rubberduck Style Guide →
Version 2.5.1 was released August 22, 2020. Since then, the installer was downloaded over 11,600 times; we are now 420 commits and 650 modified files later, and the time has come to deliver all that work into a convenient little installer package and move on to the next dev/release cycle. What’s New? If you’ve kept… Continue reading Introducing Rubberduck 2.5.2 →
If you haven’t tried it already, download VSCode and get the twinBASIC extension, and be part of the next stage of the Visual Basic revolution. When it goes live (it’s still in preview, and vigorously maintained), twinBASIC will compile 100% VB6/VBA compatible code and completely redefine how VB6 and VBA solutions will be maintained and… Continue reading Constructors in twinBA…
Most of the time, we don’t need any global variables. State can usually be neatly encapsulated in an object, and a reference to this object can easily be passed as an argument to any procedure scope that needs it. But global scope is neither a necessary evil, nor necessarily evil. Like many things in programming,… Continue reading Globals and Ambient Context →
Using Excel worksheet functions taps into the native calculation engine: using Excel’s very own MATCH function instead of writing a lookup loop or otherwise reinventing that wheel every time makes a lot of sense if your project is hosted in Excel in the first place, or if you’re otherwise referencing the Excel type library. You… Continue reading WorksheetFunction and Errors &#…
VBA code being embedded in a host document might be very practical for certain aspects of both development and deployment, but let’s face it, it also makes using source control (e.g. git, SVN, mercurial, etc.) with VBA projects rather frustrating. As a developer, committing source code to a repository is usually a very simple task,… Continue reading Synchronizing your VBA project wi…
In procedural code, a macro might be implemented in some Public Sub DoSomething procedure that proceeds to do whatever it is that it needs do, usually by dereferencing a number of library-defined objects and invoking their members in a top-to-bottom sequence of executable instructions. Clean, nicely written and well-modularized procedural code would have that be… Continue reading From Mac…
Using a WithEvents variable to handle the MSForms.Control events of, say, a TextBox control has the irritating tendency to throw a rather puzzling run-time error 459 “Object or class does not support the set of events”. To be honest, I had completely forgotten about this when I started working on this MVVM framework. I had… Continue reading Making MVVM Work in VBA Part 2: Even…
I have recently written (100% VBA) a proof-of-concept for a Model-View-ViewModel (MVVM) framework, and since the prototype works exactly as needed (with some rough edges of course)… I’ve decided to explore what Rubberduck can do to make MVVM fully supported, but going down that path poses a serious problem that needs a very good and… Continue reading Making MVVM Work in VBA Pa…
We’ve seen in UserForm1.Show what makes a Smart UI solution brittle, and how to separate the UI concerns from rest of the logic with the Model-View-Presenter (MVP) UI pattern. MVP works nicely with the MSForms library (UserForms in VBA), just like it does with its .NET Windows Forms successor. While the pattern does a good… Continue reading Model, View, ViewModel →
Here we are again, some 580+ commits and 1000+ modified files later, with 10 contributors involved (with particular thanks to @MDoerner and @BZngr, and honorable mentions to @IvenBach and @testingoutgith1) in over 60 pull requests since the last release: time to look back at what was done and call it version 2.5.1! If you’ve been… Continue reading Rubberduck 2.5.1 →
We’ve seen how to leverage the default instance of a class module to define a stateless interface that’s perfect for a factory method. At the right abstraction level, most objects will not require more than just a few parameters. Often, parameters are related and can be abstracted/regrouped into their own object. Sometimes that makes things… Continue reading Builder Walkthroug…
Unless you’re hosted in Access, your VBA project doesn’t have access to a database engine. If you’re in Excel, it’s easy to treat the host workbook as a database and each worksheet as a table. While we can build an application that uses Excel worksheets to store data, we probably shouldn’t do that. The reasons… Continue reading Secure ADODB →
Core contributor to the Rubberduck project, co-author of Microsoft Access in a Sharepoint World (2011), Professional Access 2013 Development (2013), and Effective SQL: 61 Specific Ways to Write Better SQL (2016), 10-times Microsoft Access MVP award recipient (2009-2019), Ben Clothier wrote a paper about class modules and OOP that makes a great on-topic addition to… Continue reading VBA Cl…
Apparently this is this blog’s 100th article (!), and since Rubberduck is also about the future of Office automation in VBA, I wanted to write about what’s increasingly being considered a serious contender for an eventual replacement of Visual Basic for Applications. Just recently Mr.Excel (Bill Jelen) uploaded a video on YouTube dubbing it the… Continue reading Office-JS &…
Authenticating the user of our application is a common problem, with common pitfalls – some innocuous, some fatal. It’s also a solved problem, with a fairly standard solution. Unfortunately, it’s also a problem that’s too often solved with naive, “good-enough” solutions that make any security expert twitch. We need to prompt the user for a… Continue rea…
Creating the pull request to merge the current [next] branch into [master] is always thrilling: the incredible amount of work that goes into Rubberduck, release after release, never ceases to amaze me. This time (again!), the pull request is well over 1.2K commits. Green-release version 2.4.1.0 was all the way back on March 25, 2019… Continue reading Hello, Rubberduck 2.5.0 →
There are so many ways to get ahold of a Worksheet reference: you can dereference it from a Sheets collection, and even then you need to decide whether that’ll be off Workbook.Sheets or Workbook.Worksheets, two properties that both return a Sheets collection that will contain the worksheet you’re looking for. The Workbook might be the… Continue reading Code Name: Sheet1 →
I wrote about this unfortunately hard-to-discover feature in 2017, but a lot has happened since then, and there’s 5 times more of you now! The wiki is essentially up-to-date, but I’m not sure of its viewership. So here’s a recap of annotations in the late Rubberduck 2.4.1.x pre-release builds, that 2.5.0.x will launch with. What… Continue reading Rubberduck Annotations &…
The first time you discovered the Visual Basic Editor and visualized the VBA project in the Project Explorer, when you first met ThisWorkbook and Sheet1, document modules were the world: your baby steps in VBA were very likely in the code-behind of ThisWorkbook or Sheet1 - document modules are the gateway to VBA-land.… Continue reading Document Modules →
For the 3rd year in 2019, Rubberduck celebrated open-source with Digital Ocean’s (6th) Hacktoberfest event. This year’s was our busiest ever, with ~50 pull requests created, 11 new forks and 24 new stars in the past month; 67 issues were closed since October 1st, by 11 authors – including 4 first-time contributors! This means… quite… Continue reading Hacktoberfest …
The big buzzy words are just a name given to what’s happening when we identify a procedure’s dependencies and decide to inject them. Like any procedure that needs to invoke Workbook.Worksheets.Add must depend on a given specific Workbook object. If the workbook we mean to work with is the document that’s hosting our VBA project,… Continue reading Dependency Injection in&…
Whether VBA can do serious OOP isn’t a question – it absolutely can: none of the SOLID principles have implications that disqualify VBA as a language, and this means we can implement dependency injection and inversion of control. A quick summary of these fundamental guidelines, before we peek at DI and IoC: SOLID Single Responsibility… Continue reading Dependency Injection + I…
If you’ve been following the project all along, this isn’t going to be news, but we kind of missed the v2.4.2 milestone we were slated to release back in April, and here we are with our [next] branch (“pre-release” builds) being a whopping 580+ commits ahead of [master] (“green-release” builds). These commits change a lot… Continue reading What’s …
Today I learned that VB.NET does in fact support Default properties. For years I was under the impression that dismissing the Set keyword meant default members couldn’t possibly exist in .NET, and I was wrong: dismissing the Set keyword meant that parameterless default members couldn’t exist in .NET, but VB.NET can still implicitly invoke a… Continue reading Modern VBA Best Pr…
If you’re an accountant, a sales analyst, or in any other office position where writing VBA code helps you do your job faster, you may have uttered the words “I’m not a programmer” before, and that wouldn’t have been wrong: once the code is written, you’d tweak it every once in a while to fix… Continue reading “I’m not a programmer” &#…
What is a class? The term is known to everyone that read anything about programming in VBA. It defines objects, yes. But what else do we know about them? What don’t we know about them? VBA being built on top of COM has a number of implications, and explains a number of language features and… Continue reading About Class Modules →
Error-handling in VBA can easily get hairy. The best error handling code is no error handling code at all, and by writing our code at a high enough abstraction level, we can achieve exactly that - and leave the gory details in small, specialized, lower-abstraction procedures.… Continue reading Pattern: TryParse →
The Most Dreaded Language The annual Stack Overflow Developer Survey has always ranked VBA pretty high on the “most dreaded” languages. For some reason this year VB6 and VB.NET aren’t making the list, but VBA is sitting at the very top of it, with 75.2% of respondents “dreading” VBA. VBA is a gateway language – it was… Continue reading What’s Wrong With…
VBA is often said to be an event-driven language: a lot of worksheet automation involves executing code in response to such or such workbook or worksheet event. ActiveX controls such as MSForms.CommandButton are trivially double-clicked, and code is written in some CommandButton1_Click procedure. But how does it all work, and can we leverage this event-driven… Continue reading Everything …
As was shared a week or two ago on social media, Rubberduck contributor and supporter Andrew “ThunderFrame” Jackson passed away recently – but his love for VBA, his awesomely twisted ways of breaking it, his insights, the 464 issues (but mostly ideas, with 215 still open as of this writing) and 30 pull requests he… Continue reading Rubberduck 2.4.1: ThunderFrame Edi…
Unlike quite a number of Rubberduck releases, this time we’re not boasting we above a thousand commits: this time we’re looking at well under 300 changes, but if the last you’ve seen of Rubberduck was 2.3.0 or prior, …trying this release you’ll quickly realize why we originally wanted to release it around Christmas. So, here’s… Continue reading Rubberdu…
You’re writing a rather large VBA/VB6 project, and you’re starting to have a sizable amount of passing unit tests. Did you know you can copy the test results to the clipboard with a single click? …and then paste them onto a new worksheet and turn it into a data table: If you’re not sure what… Continue reading Code Insights with Rubberduck + Excel →
Back in the 2.1.x announcement post over a whole year ago, one of the bullet points about the upcoming roadmap said we were going to “make you never want to use the VBE’s Project References dialog ever again“; it took a bit longer than expected, but as far as we can tell, this feature does exactly that.… Continue reading Introducing the Reference Explorer →
Version 2.2.0 was released in April 2018. Well over 1,700 commits and 2,185 modified files later, Rubberduck is now more stable than ever, and well overdue for a new release. November 25th will see Rubberduck 2.3 issued – as of this writing, we’re ironing a few wrinkles, but everything looks like we’re on track to release some… Continue reading Introducing Rubberduck v2.…
Clean code adheres to a number of principles. Does adhering to these principles make good code? Maybe, maybe not. But it definitely helps. One thing I find myself repeating quite a lot in my more recent Stack Overflow answers, is that code should “say what it does, and do what it says” – to me… Continue reading Clean VBA Code pt.2: Avoiding implicit code →
We know clean code when we see it. Clean code is a pleasure to read and maintain. Clean code makes its purpose obvious, and is easily extended or modified. I cannot recommend Robert C. Martin’s Clean Code: A Handbook of Agile Software Craftsmanship enough – to me it was an eye opener. Code examples are in Java, but the… Continue reading Clean VBA Code pt.1: Bad Habits …
A few months ago I merrily announced the first Rubberduck feature that actively interfered with typing code in the VBE. It wasn’t the first opportunity though: a rather long time ago, I flirted with the idea of triggering a parse task at every keypress, so that Rubberduck’s parse trees would always be up-to-date – but back then… Continue reading Self-Closing Pairs: Danci…
Sometimes a class needs to hold a reference to the object that “owns” it – i.e. the object that created it. When this happens, the owner object often needs to hold a reference to all the “child” objects it creates. If we say Class1 is the “parent” and Class2 is the “child”, we get something… Continue reading Lazy Object / Weak Ref…
You may have read that Me was a keyword, or that it was some kind of “special object that’s built into Excel”; or, you might have inferred that it’s some kind of hidden instance/module-level variable that’s only there in class/form/document modules: that’s pretty much how I was understanding Me, until I saw what the language specifications say about it……
If you recall the AIPlayer class from Part 2, the Create factory method takes an IGameStrategy parameter: An AIPlayer can be created with an instance of any class that implements the IGameStrategy interface. In any OOP language that supports class inheritance, we could have a base class e.g. GameStrategyBase, from which we could derive the… Continue reading OOP Battleship Part 4: AI …
Download the macro-enabled Excel workbook here Now that we have defined our model, we need a view. In MVC terms, the view is the component that’s making the game state visible to the player; it is responsible for the two-way communication with the controller. Since we’re in Microsoft Excel, we can use a worksheet to do this. So we craft… Continue reading OOP Battleship Part 3:…