QlikView Extensions: ChartJS

While D3 is the “Big Daddy” of visualizations, Chart.js is a much smaller library that provides a number of appealing, animated, chart visualizations.  If you don’t need the extra functionality of D3 – or you want to get a snazzy chart up and running quickly – then Chart.js is worth checking out.

Here is an example Extension I built that uses their “polarArea” chart:

ChartJS_PolarArea

The code is available on my GitHub repo.

What you don’t see in the above, however, is the cool animation that happens when the chart renders.  For that, you’ll need to download my code and create the Extension.

There are a number of other charts available and they all have pleasing animations.  There is crossover in chart types between Chart.js and QlikView but the former has animations a.k.a. the “WOW” factor.  I highly recommend that you go to their website and take a look at the examples.  As always, being HTML+CSS+JS, the charts are configurable up the wazoo.

Advertisements

QlikView Extensions: LeafletJS

If you’ve never heard of it before, Leaflet.js is an open source JavaScript library for interactive maps.  I’ve used it on my website as a way of showing, geographically, the places I’ve been to and where I’ve worked.  Its ease of use (zooming, popups, etc.) makes for a great interactive experience.  Given how simple it was to set up on my website, I thought I’d have a go at making a QlikView Extension Object that uses it.

The code I used is available on my GitHub repo.

Here’s the result:

QVExt_LeafletJS

Perhaps the first thing you notice (after the map) are the blue markers.  When you hover over them you get a popup indicating the City’s name and its population.

N.B. In order to see the markers you may need to alter the defaulting properties so that the dimensions are set correctly (ie. right-click on the caption and select ‘Properties’).  After amendment, your properties should look like:

QV

The next thing to note is the ListBox on the right.  If I click on any combination of cities, only the markers for those cities show up on the map.  You do not have to stretch your imagination to extend this concept to any dimension associated with the city, eg. region, currency, etc.  Clicking on those other dimensions would filter the cities which, in turn, would filter the markers on the map.

It’s worthwhile pointing out that Leaflet provides a lot of functionality out of the box.  You set the image’s centre and initial zoom level using the setView method:

var mymap = L.map('mapMap').setView([20, 10], 2);

and Leaflet allows you to zoom in using your mouse’s scroll wheel.  You can also click on the markers and this event can be captured in your JavaScript where you can handle it how you wish.  For example, there is the SetVariable function exposed by the QlikView JavaScript API so you could control your selections based on clicks within the Extension.  This is an exercise for the reader.

QlikView Extensions – rolling up the DOM in Style

QlikView Extension Objects allow a developer to create their own, custom objects in QlikView by using XHTML, CSS and JavaScript.  In this blog post, I’m going to show you how to (a) discover what type of HTML Element “this.Element” really is, (b) what its HTML Attributes are, (c) what its lineage is (parent, grandparent, great-grandparent, etc.), and (d) how to present this information is a way that ‘stands out’.

Create your Definition.xml file.  For this demo it can be the bog-standard ‘HelloWorld’ offering found in any old ‘QlikView Extension Object’ blog/forum post.

Next, create the Script.js file as so:

Qva.AddExtension("HelloWorld", function() {    
    var e = this.Element;
    var p = document.createElement('p');
    e.appendChild(p);

    p.innerHTML="";

    var j = 1;
    while (e.tagName !== undefined) {
        p.innerHTML += '<br/><br/>' 
            + j + ': ' 
            + e.tagName;
        for (var i = 0; i < e.attributes.length; i++) {
            p.innerHTML += '<br/>' 
                + e.attributes[i].nodeName 
                + '=' 
                + e.attributes[i].nodeValue;
        }
        e = e.parentNode;
        j = j + 1;
    }
});

Here, I’m assigning the ‘this.Element’ to a variable (‘e’), creating a new paragraph element (p) and adding it to the DOM (‘e.appendChild(p)’). I then re-set the innerHTML of this newly created and added element (p.innerHTML=””). Then I loop through e’s parents, one by one, until I reach the root element (HTML) which has no parent (ie. undefined). For each element found, I’m looping through its attributes and adding the attributes Name and Value to the innerHTML of p. The end result is:

QVOE

Not very exciting but, nonetheless, very informative. From “1.” we can see straight away that “this.Element” is a DIV element. Furthermore, it is descended from many other DIVs – 4 deep from the BODY element. You can modify the above code to traverse the DOM. For example, go up to HTML and drop down to HEAD and see what LINKs are being called.

Now, to “spice it up” a bit, let’s add some CSS. QlikView 11 and 12 support CSS3 animations so let’s implement some. Create a “main.css” file in the same directory as your Script.js and populate it as follows:

@keyframes pulse {
    0% {
        color: rgb(255,255,255);
    }
    100% {
        color: rgb(255,0,0);
    }
}
@keyframes nudge {
  0%, 100% {
        transform: translate(0, 0);
    }
    50% {
        transform: translate(50px, 0);
    }
}
@keyframes fadein {
    0%   { opacity: 0; }
    100% { opacity: 1; }
}
#myPara {
   animation: nudge 3s linear infinite alternate;
}

Now change your Script.js file to:

Qva.AddExtension("HelloWorld", function() {    

    var template_path = Qva.Remote 
        + "?public=only&name=Extensions/HelloWorld/"; 
    Qva.LoadCSS(template_path + "main.css");

    var e = this.Element;
    var p = document.createElement('p');
    e.appendChild(p);

    p.innerHTML="";

    var j = 1;
    while (e.tagName !== undefined) {
        p.innerHTML += '<br/><br/>' 
            + j + ': ' 
            + e.tagName;
        for (var i = 0; i < e.attributes.length; i++) {
            p.innerHTML += '<br/>' 
                + e.attributes[i].nodeName 
                + '=' 
                + e.attributes[i].nodeValue;
        }
        e = e.parentNode;
        j = j + 1;
    }

    p.setAttribute("id", "myPara");
});

Here, I’ve used Qva.LoadCSS to load our CSS file and – at the very end – I’ve altered the ‘p’ element we created to give it an ID of “myPara”. In the main.css file (at the end) you’ll see we specify the “nudge” animation on the element having the ID “myPara”.

Now when you refresh your Extension in QlikView, you’ll see the text moving from left to right and back again. There are a many, many more CSS3 animations available on the web. I have included another couple (pulse and fadein) in our main.css file. Now that you’ve been shown, you can practice on your own. Experiment and make your QlikView Extensions come alive!

QlikView Extensions – Avoiding Global Scope

QlikView Extension Objects allow a developer to create their own, custom objects in QlikView by using XHTML, CSS and JavaScript.  However, it is easy for an experienced QlikView developer – but inexperienced JavaScript developer – to get caught out by JavaScript’s “foibles” and spend several hours trying to figure out why their code isn’t working the way it should.  One such nuance of the JavaScript language has to do with the declaration of Functions and Variables – two of the fundamental building blocks of the language.

Functions

In some of the examples I’ve seen on the web, the callback function is declared before it is included in the AddExtension method eg.:

function myCallback() {
    ...//code here
}
Qva.AddExtension("HelloWorld", myCallback);

This is not good.  Doing so adds ‘myCallback’ to the global object (which is ‘window’, btw).  If you have multiple Extensions, each with their own function called ‘myCallback’, then each successive declaration blitzes the ‘myCallback’ declared before it with the result that only the last man standing will be used in all of your Qva.AddExtension invocations.  Furthermore, just which one will be used depends on the order in which the QlikView JavaScript engine chooses to compile them.  This order can change unpredictably thereby resulting in your Extensions behaving strangely.

You can, of course, choose to name your callback functions so that they don’t clash with any of your others but this doesn’t help someone else who uses your Extension and – by chance – already has a function declared with the same name.  Sure you could try ‘security by obscurity’ and give your functions GUIDs or something like that but, why bother?  It’s so much simpler to take the better route: define the function in the AddExtension definition:

Qva.AddExtension("HelloWorld", function() {
    ...//code here
});

Here we pass in an ‘anonymous function’ as our callback (‘anonymous’ because the function has no name – it’s just “function() {…}”) and, because we define the function within the enclosing AddExtension call, it is only ‘visible’ within the AddExtension method.  There is no pollution of the Global scope.

Variables

The same applies to variables.  I have seen:

var template_path = Qva.Remote + ...
Qva.AddExtension("HelloWorld", function() {
    Qva.LoadCSS(template_path + 'styles.css');
    ...//more code here
});

Again, ‘template_path’ is being defined on the Global object (window) and, accordingly, multiple Extensions using the same variable name, will blitz each other and you’ll be left scratching your head as to why your CSS doesn’t seem to work anymore.  It’s much better to:

Qva.AddExtension("HelloWorld", function() {
    var template_path = Qva.Remote + ...
    Qva.LoadCSS(template_path + 'styles.css');
    ...//more code here
});

Of course, don’t forget the ‘var’ prefix. If you do, you’ll be back to square one as using a variable without first declaring it results in that variable being defined on the Global object – waiting to be blitzed.  I’ve used ‘var’ in the above examples.  QlikView 11 and 12 support both ‘let’ and ‘const’ from ES6 so, you may opt to use those instead.  YMMW for earlier versions of QlikView (eg. 10).

To see what I mean, implement the following 4 extensions and compare their results:

var myPath = "hello myPath";
Qva.AddExtension("HelloWorld", function() {
    this.Element.innerHTML = myPath;
});

Qva.AddExtension("HelloWorld", function() {
    var myPath = "hello myPath";
    this.Element.innerHTML = myPath;
});

var myPath = "hello myPath";
Qva.AddExtension("HelloWorld", function() {
    this.Element.innerHTML = window.myPath;
});

Qva.AddExtension("HelloWorld", function() {
    myPath = "hello myPath";
    this.Element.innerHTML = window.myPath;
})

The 1st and 2nd give the expected behaviour and the 3rd and 4th match the 1st and 2nd respectively.  However, notice that in the 3rd and 4th we’re calling the myPath property of the window object (the Global object).  In the 3rd our variable was defined outside of the AddExtension function and in the 4th we used our variable without declaring it first (the ‘var’ prefix is missing).  Both situations resulted in myPath being added to the Global object and, thus, having Global scope.  Polluting the Global scope like this won’t necessarily lead to any problems – particularly if you never use the same variable/function name, but it is so easy to avoid taking this risk that you really have to question why you would choose to take it in the first place.  Hopefully, armed with above, you now know how to avoid it.

 

The QlikView Incremental Load GOTCHA!

Qlik touts the Incremental Load paradigm as a way to improve the performance of your load scripts.  The idea with an incremental load is to load from a (usually non-QVD) data source and then CONCATENATE that table with a load from a QVD, but filtering the concatenating (QVD) data with the filter:

WHERE NOT EXISTS (fieldName)

N.B. EXISTS() also takes a second parameter (an expression) but for this blog post, I’m ignoring it.

The idea is that – for each row in the concatenating QVD data set – if the value for fieldName of that row already exists in the (non-QVD) data set.  ‘EXISTS’ will return TRUE and, therefore, ‘NOT EXISTS’ will be FALSE and that row in the QVD data set will not be concatenated with the (non-QVD) data set.

In this way, only those rows in the QVD data that are not in the (non-QVD) data will be added to the latter.  Which is what we want.

However, things are not so easy.  The Qlik Help states:

EXISTS() determines whether a specific field value has already been loaded into the field in the load script… The field must exist in the data loaded so far by the script.

I’ve emphasised ‘so far’ as the practical impact of these two words cannot be understated.  It appears that the check whether the value of fieldName exists, is done repeatedly – after each row from the concatenating QVD file is loaded.  This means:

If you have more than one row in your (concatenating) QVD data that has the same value for fieldName, only the first row of that QVD will be concatenated and the remaining rows (having the same value for fieldName) will not be concatenated (as there is now a row in the table having that value for fieldname, so ‘NOT EXISTS’ will return FALSE and those rows will not be concatenated.

The way around this, of course, is to make sure that the values for fieldName are unique in both data sets.  This will usually mean you need to create a composite key from more than one field as, mentioned above, EXISTS only accepts a single fieldName parameter.

For another explanation, with an example, see this post:

https://community.qlik.com/docs/DOC-7020

JavaScript for VBA Developers: Scope

In VBA, the three scopes available for variables are public, module and procedure – usually referred to as Global, Module-level and Local.  Global variables are recognised by every module in the active workbook. Module-level variables are available to all of the procedures in that module, but are not available to procedures in other modules.  A Local variable is recognised only within the procedure in which it is declared

JavaScript has two scopes – Global scope and Function scope.  Global scope operates in a similar fashion to VBA’s Global scope in that such variables can be referenced from anywhere in the code. Function scope, however, has no direct analogy in VBA.  Function scope, while bearing some similarity to VBA’s Local scope, allows variables declared within a function to be available not just within that function but also to functions that are defined within that function, ie. nested/inner functions have access to variables declared in their parent function, grandparent function, great-grandparent function and so on – all the way up this ‘scope chain’ to the Global scope.

The Scope Chain

The scope chain is a list of objects that define the variables that are in scope. When the interpreter starts to resolve a variable, it looks at the first object in the chain.  It it doesn’t find it, it looks at the next object in the chain.  If that doesn’t have it, the search moves on to the next object, as so on until it arrives at the Global object where, if it fails to find the variable, it throws a ReferenceError.

Each function has its own object.  So, a function defined on the global object has two objects in its scope chain – itself and the global object.  A function defined within another function has three or more objects in its scope chain – itself, the objects of its outer function(s) and the global object.  

The following code illustrates the scope chain:

<!doctype html>
<html>
  <head></head>
  <body>
    <p id="p1"></p>
    <p id="p2"></p>
    <p id="p3"></p>
    < script type="text/javascript">
 
      var a = "global scope";
      document.getElementById("p1").innerHTML = a;
 
      function outerFunc() {
        var b = "outer function scope";
        document.getElementById("p2").innerHTML = a+"-"+b;
        function innerFunc() {
          var c = "inner function scope";
          document.getElementById("p3").innerHTML = a+"-"+b+"-"+c;
        }
        innerFunc();
      }
 
      outerFunc();
 
    < /script>
  </body>
</html>
Note: I’ve put leading whitespace in the above ‘script’ tags.  Wordpress doesn’t like its bloggers to run their JavaScript on reader’s web pages so if I’d put </script> it would be removed by WordPress.  Consequently, if you copy and paste the above into a blank text file, don’t forget to remove the whitespace in the ‘script’ tags before you load it into your browser.

A consequence of the scope chain is that, while inner functions have access to variables of their outer functions, the reverse is not true.  To prove this, change the line:

document.getElementById("p2").innerHTML = a+"-"+b;

to:

document.getElementById("p2").innerHTML = a+"-"+b+"-"+c;

and you’ll see things don’t work properly.  If you look at the console output in the dev tool you’ll see an ‘is not defined’ error.

Thinking about scope in this way – ie. it being a series of linked objects that contain variables – helps us to understand another curious feature of the language: Hoisting.

Hoisting

In VBA, the compiler won’t let your code refer to a variable before it has been declared.  JavaScript has no such constraints.  In JavaScript, a variable referenced before it is declared will not give an error.  Another way to think of this, is that the scope chain is determined before the code in that current scope is executed.

Consider the below code:

<!doctype html>
<html>
  <head></head>
  <body>
    <p id="p1"></p>
    <p id="p2"></p>
    < script type="text/javascript">

      document.getElementById("p1").innerHTML = "a = " + a;

      var a = "Hello World!";

      document.getElementById("p2").innerHTML = "a = " + a;

    < /script>
  </body>
</html>

If you run the above you’ll get the results:

a = undefined

a = Hello World!

Notice that you don’t get an error.  When JavaScript enters a new scope it binds the set of identifiers defined by the declarations contained within that scope.  In practice,  this means a new object is created (called an Environment Record) and all of the variables are added (bound) to it.  If a variable exists on the Environment Record but a value has not been assigned to it, JavaScript uses the ‘undefined’ value, in lieu.  It’s important to remember that a variable must be declared at some point in the scope in order to be bound to the Environment Record.  If it’s not declared, it’s not on the Environment Records.  Consequently, referring to such a variable results in a ReferenceError:

<!doctype html>
<html>
  <head></head>
  <body>
    <p id="p1"></p>
    < script type="text/javascript">

      document.getElementById("p1").innerHTML = "a = " + a;

    < /script>
  </body>
</html>

The above code results in a blank page.  If you check the Console in the dev tool (F12 in IE, Ctrl+Shift+I in Chrome) you’ll see a ReferenceError.  It’s also worth remembering that the rules governing variable hoisting operate in the same way for functions:

<!doctype html>
<html>
  <head></head>
  <body>
    <p id="p1"></p>
    <script type="text/javascript">

      myFunc();

      function myFunc() {
        document.getElementById("p1").innerHTML = "Hello World!";
      }

    < /script >
  </body>
</html>

Here the call to myFunc still works as expected even though it’s not defined (declared) until after the call.

While Hoisting is a feature of the language its use is not considered good practice and you should maintain the habit of declaring your variables before they’re referred to by your code.

 

Conclusion

While VBA has 3 scopes, JavaScript has 2: Global and Function.  The Scope Chain allows nested functions to access the variables in its outer functions as well as those in the global scope.  In JavaScript you don’t need to declare a variable before using it, but relying on this is discouraged.

The aim of this blog was to explain to a VBA developer who is unfamiliar with JavaScript, how ‘scope’ operates in the latter compared to the former.  If anything was unclear or you think I’ve missed something, please leave a comment and I’ll see if I can remedy the gap.

JavaScript for VBA Developers: Getting Started

The first thing you need to understand about JavaScript is that it’s not a general purpose programming language. For instance, it doesn’t come with any APIs that deal with IO (Input-Output) operations. On its own it cannot open a text file, read from it, or write to it. Instead, it requires a host environment in which to work. The host environment provides the APIs. This situation is due to historical reasons.

In the beginning, JavaScript – or LiveScript as it was originally known – was designed to be used in a web browser. Recently, however, ‘server-side’ JavaScript has appeared in non-browser applications such as Node.js and MongoDB – applications written in C/C++ that allow the user to write and execute JavaScript code using the APIs provided by the application. The installation and use of Node.js and/or MongoDB can be a bit daunting for the first-timer so, in this blog, I’m going to stick to JavaScript in the browser, ie. ‘client-side’ JavaScript.

To write JavaScript, you can use a fully-blown IDE, such as Visual Studio or WebStorm or any one of the multitude of others which have all the bells and whistles you’ll possibly ever need but, as a beginner, all you really need is a simple text editor. I use Notepad++ on Windows and Chrome Dev Editor on my Chromebook, but any text editor will do. A popular alternative is SublimeText.

So, having installed your text editor, create a text file called ‘index.html’ with the following code in it:

<!doctype html>
<html>
  <head></head>
  <body></body>
</html>

and save it.

“What’s with the HTML and why not JavaScript”? In ‘client-side’ JavaScript, when an HTML file is loaded by the browser it creates a ‘document’ object. This object exposes properties and methods (ie. it’s an API) and JavaScript can use these to enhance the appeal of the web page running in the browser (ie. the document object).

As you can see, the above HTML document is pretty bare and, to be frank, pretty useless. If you load it into your browser (eg. open Internet Explorer and, using Windows Explorer, drag your index.html file onto IE) you’ll see a blank page that does nothing.

Dev Tools

With this useless page open, press the F12 function key in IE (Ctrl+Shift+I, if you’re using Chrome). Now you’ll see the browser’s development tool (typically at the bottom of the screen in IE and on the right of the screen in Chrome). These tools are very, very useful.  It is worth your while spending some time exploring them. Amongst other things, they allow you to:

  1. debug your JavaScript code using the ‘Console’ (something very, very handy); and
  2. make changes to the loaded document and see the effect immediately (also very, very handy).

For now, click on the ‘DOM Explorer’ tab (‘Elements’ in Chrome). Here you’ll see the HTML code of your web page as the browser sees it. Again, not very interesting right now, but we’ll come back to the this later.  For now, I want you to go back into your text editor and add some JavaScript to index.html:

<!doctype html>
<html>
  <head></head>
  <body>
    <script type="text/javascript">
      document.body.style.backgroundColor = 'red';
    </script >
  </body>
</html>

Please note that the whitespace after the word ‘script’ in the closing script tag (ie. </script >)  should not be there.  I had to put it there for this blog as WordPress doesn’t like people running scripts from their blog posts on other peoples’ web browsers.  So, in your .html file don’t include this trailing whitespace (ie. have …script> not …script >).

Now reload this file – you can either ‘drag and drop’ as you did before or, you can simply click the ‘reload’ icon in your browser.   Provided you didn’t change the file name or its location, the latter will work just as well as the former.

You should see that your web page now has a red background.  This should come as no surprise as your JavaScript code was pretty self-explanatory: you take the ‘body’ element of the ‘document’ object, examine its ‘style’ collection and set its ‘backgroundColor’ property to red.  You should be familiar with this style of object model coding in VBA where you’ve probably had to refer to the ‘value’ of a ‘range’ on a ‘sheet’ in the current ‘workbook’:

ThisWorkbook.Sheets(1).Range("A1").Value = "red"

Now go back into the dev tool and take a look at your code.  On the ‘Elements’ page in the frame at the bottom half of the screen you’ll see a ‘Style’ tab.  If you activate it (if it isn’t already) you’ll see the ‘background-color’ is ‘red’.  Go ahead and double click on this ‘red’ value.  It’ll change to a text box expecting you to input something.  Go ahead and choose another color, eg. orange.  You’ll see your web page changes colour instantly.  This ability to change a documents style ‘on the fly’ makes the dev tool a useful companion when developing a web page.  Note that this change only applies to the document in memory.  The code in your index.html file is not updated.

Now I want to demonstrate how to use the dev tool to check for any errors in page loading, be they JavaScript, HTML or CSS errors. I won’t be talking about the Debugger in this post as that deserves a whole Blog post. Instead I’m going to talk about the Console. Alter your index.html file to look like this:

<!doctype html>
<html>
  <head></head>
  <body>
    <script type="text/javascript">
      document.body.style,backgroundColor = 'green';
    </script >
  </body>
</html>

Note how I’ve used a comma instead of a full-stop in between ‘style’ and ‘backgroundColor’. Go ahead and reload this modified file. You’ll see that you get a blank page, not a green one. Now open the dev tool and instead of ‘Elements’, click on the ‘Console’ tab. You’ll see an error message in red and you’ll also see (to the right) a reference to the file and line number (index.html:6 in my file). If you examine the error message in full you may also get the column number of where the error occurred on the line. I find the Console is the first place I go to if my page is not working as I expect it to. Unsurprisingly, 100% of the time the computer is NOT wrong.

That concludes this blog. As a VBA developer, I hope it’s piqued your interest and enables you to ‘get started’ with JavaScript.

JavaScript for VBA Developers: The One-Line Web Server

I’ve been taking a look at server-side JavaScript recently, in particular, Node.js. I’m absolutely blown away by how it simplifies the task of creating your own web server. To prove it, I ‘created’ a one-line web server. Here it is:

require("http").createServer(function (req, res) {require("fs").createReadStream("./index.html").pipe(res);}).listen(3000);

Note: due to its length, WordPress spreads the one line over two but, the above is a single line of code. Let’s take a closer look at it since it demonstrates some of the features of the JavaScript language.

FUNCTION THE ULTIMATE!
Sry. I had to get that one in there. I just love that imperative, for it says so much about the JavaScript language. In JavaScript, functions are first class citizens. This means you can, inter alia, pass a function as an argument into another function. We can see that above. We’ve passed an anonymous function – function (req, res) { } – into the receiving function ‘createServer’.

In VBA, while you can pass an object and a string into a function and then use the VBA built-in ‘CallByName’ function to call a method on that object, you can’t pass a function directly into another function in VBA. Functions are not first class citizens in VBA.

Method Chaining
There are 2 instances of method chaining at work above. The first is the obvious piping of the output of createReadStream to res, ie:

createReadStream( ).pipe(res);

Note: ‘res’ is the result that is passed back to the web browser – in this case the stream read from the index.html file.

The second is the less obvious binding of the newly created server object to listen on port 3000, ie:

createServer( ).listen(3000);

Here the result of calling ‘createServer’ – ie. the server object that is returned by the call – is having its ‘listen’ method invoked with an argument of 3000 passed through. The VBA equivalent would be:

(Set obj = New Server).listen 3000

I’ve used brackets around the instantiation to indicate that the output of that instantiation (ie. the new Server object) is having its listen method called with an argument of 3000 passed in. Of course, the above is not possible to do in VBA.

You can chain your own objects in VBA, using class composition, to get to a method of the last object in the chain, but this is not the same thing as calling a method, on the output of the immediately previous method.

So, there you have it: the one-line-web-server. So what does it actually do? Well, not a lot, actually. It simply streams the contents of index.html to the web browser that connected to it. That’s it. If you wanted to dumb it down even further you could replace the function body:

require("fs").createReadStream("./index.html").pipe(res);

with:

res.end('Hello World!');

‘Even simpler. ‘And even more static. But what do you expect for 1 line of code?

Object Oriented VBA: Design Patterns: The Singleton

The Singleton Design Pattern: when you only ever want one instance of a class to be in existence at any point in time. The Singleton is perhaps the easiest pattern to implement in VBA and to do so you only need to perform the following steps:

1. Declare a static variable of the same type as the object you intend to be a singleton;
2. When it comes to instantiating the object, perform a check of the static variable. If it doesn’t point to anything – ie. Is Nothing – then create an instance and, after creation, point the static variable to the instance. If the static variable does point to something, then throw an exception to indicate that you can’t create another instance of the class while one already exists.

That’s it. It really is almost trivial to do. Consider the Simple Factory:

Private Singleton As Object


Public Function Instantiate(ByRef className As String) As Object
Select className
Case "Dog"
If Singleton Is Nothing then
Set Instantiate = New Dog
Set Singleton = Instantiate
Else
Err.Raise 1 + vbObjectError, "Factory.Instantiate", "Instantiation failed. There already exists an instance of " & className
End If
End Select
End Function

Here, Singleton is the static variable and the If condition performs the check of its existence.
That’s it. I told you it was trivial. Of course, the example assumes you’re making use of a Factory and, if not, the code is more involved. In such a case you’ll need to implement the steps each time you want to instantiate an object, effectively duplicating code. You could put it in a function… but, wait, re-read my blog on the Simple Factory paying particular attention to the bit where I state that a Factory really is just a function. Anyway, however, you decide to implement it I’m sure you’ll agree implementing a Singleton in VBA is not hard.

Object Oriented VBA: Design Patterns: Complex Factory

In my last post I mentioned the ability to create a factory at run-time by accessing the VBComponents collection. To do this you must first create a Reference to:

Microsoft Visual Basic for Applications Extensibility 5.3
(C:\Program Files\Common Files\Microsoft Shared\VBA\VB6\VBE6.DLL)

Having done this you now have access to the VBIDE library and, in particular, the VBComponents collection.

Below is a Complex Factory. Please note that some lines continue onto the next line and I’ve not included line continuation characters, but then, you never blindly copy and paste from a HOWTO, do you!


Public Function CreateFactory() As Variant
Dim str As String: str = BuildString("IAnimal")
Call WriteCode(str)
End Function

Private Function BuildString(ByRef interfaceName As String) As String
Dim str As String
str = "Public Function Instantiate(ByRef className As String, ByRef varState As Variant) As " & interfaceName & vbCrLf
str = str & "Select Case className" & vbCrLf
' go through all classes and find the ones that implement interfaceName
Dim comp As VBIDE.VBComponent
For Each comp In VBE.ActiveVBProject.VBComponents
If comp.Type = vbext_ct_ClassModule Then
If comp.CodeModule.Find("Implements " & interfaceName, 1, 1, comp.CodeModule.CountOfDeclarationLines + 1, 1) Then
str = str & vbTab & "Case """ & comp.Name & """" & vbCrLf
str = str & vbTab & vbTab & "Set Instantiate = New " & comp.Name & vbCrLf
str = str & vbTab & vbTab & "Call Instantiate.Constructor(varState)" & vbCrLf
End If
End If
Next comp
str = str & "End Select" & vbCrLf
str = str & "End Function"
BuildString = str
End Function

Private Sub WriteCode(ByRef str As String)
Dim cls As VBIDE.CodeModule
Set cls = VBE.ActiveVBProject.VBComponents("Factory").CodeModule
' remove any existing code lines
If cls.Find("Instantiate", 1, 1, 2, 1) Then
Dim start As Long: start = cls.ProcStartLine("Instantiate", vbext_pk_Proc)
Dim count As Long: count = cls.ProcCountLines("Instantiate", vbext_pk_Proc)
Call cls.DeleteLines(start, count)
End If
' write the new code lines
Call cls.AddFromString(str)
End Sub

As you can see, the process in creating the complex factory is two-step: first create a string of VBA code and then write it to a code module – see the procedure “CreateFactory”. Creating the string (function “BuildString”) should be pretty simple to understand. Note that I’m create a polymorphic factory that creates instances of all classes that conform to the “IAnimal” interface I have created. To find these classes I loop through all of the VBComponents and only examine the class modules (VBComponent.Type = vbext_ct_ClassModule). For each class module, I’m only interested in the ones that contain the string “Implements IAnimal” in its declaration lines. Having passed these conditions, I then concatenate the string I’m building with the necessary “meat” in the Select Case sandwich. Note that my interface definition for IAnimal includes a procedure “Constructor” that accepts a Variant as an input (“varState”). Each class that implements IAnimal will contain its own code that operates on the Variant input in order to set the state of the instantiated object before this newly created object is returned by the factory. In practice, being a Variant, varState could be as simple as a string containing the value for one of the class’s fields or, it could be a complex array containing many different objects that are used in the creation of an object that conforms to the class.

Having created the string “str” and passed it back to CreateFactory, the latter then goes on to write the code in the CodeModule of the dedicated “Factory” module. As I stated in my previous post on the Simple Factory, “Factory” could be a Standard Module, a Class Module or a Static Class Module. Before writing the code, however, “WriteCode” removes any existing code in “Factory”. it does this because “CreateFactory” is designed to be called when the Access/Excel file is opened. In Access, this is achieved by creating a Macro that runs “CreateFactory” upon opening of the file. In Excel, this is achieved by calling “CreateFactory” when the Workbook_Open event is triggered (in the “ThisWorkbook” code module).

And that’s it really. Although it’s called “Complex” the only thing complex about it is the unfamiliarity of working with the VBComponents collection. In reality it’s quite simple when you understand what’s being done. The beauty of the above Complex Factory comes when expanding the number of classes that implement the chosen interface (IAnimal in my example). All the developer need do is create the class module and save the file. The next time the file is opened, the above code will capture this new class and include it in the factory – the developer does not need to worry about manually updating the Factory object.