A few weeks ago I attended a friend's stag-do in Dublin. He, I, and almost everyone else there had met while studying (in my case "attending" or "appearing on a register" might be a better description) at Imperial College, and we are all now techies of varying degrees of hardness (the groom does webby, 2.0-esque stuff, but we try to be kind). During a conversation I mentioned that as I was doing more automation work now than previously I was dealing with a lot of VBA, and that, despite its many shortcomings, I rather enjoyed it. My interlocutor snorted in a rather pitying way. "Don't you ever get frustrated by not being able to multiply two five-digit numbers together?" he asked, wiping Wee Whore (it was a stag-do in Dublin; of course there were humorously-named ales) from his chin. I replied, perhaps more sharply than I would have done had my face not been covered by a fine mist of Wee, that automation rarely required me to do so, and changed the subject to a recent systems cock-up at his grocery company.
There is, though, something rather silly about the way in which VBA handles number types. Wikipedia describes VBA's typing discipline as a static/dynamic/strong/weak hybrid, which, while lacking the verve of "to or for, by with and from everybody", is nevertheless a very good description. To see just how omni-shambolic it is, load up your favourite VBA IDE (by which I mean, press Alt+F11 in Excel) and play along. Let's start by printing a VLN (very large number):
If you've played any submarine games or read Willard Price's Adventure series of books, you'll know that under the sea, in addition to singing crustaceans, there is "thermal layer" where warm water rests on top of cold, and that a submarine or other object passing through this layer must do so at speed lest it bounce off (actual submariners will know that almost none of that is true). The analogy is, I assume, obvious to you all. No?
Well, while other, fancier, la-di-da languages have signed- and unsigned- 32- and 16- and even 64-bit integers, VBA eschews the trappings of its decadent capitalist creators and instead supplies three honest, working-man's number-types: Byte, Integer, and Long (yes, I know it also has doubles and singles and those weird string-number things, but it's Party policy to treat those like Tony Benn fiddling his inheritance tax and pretend they never happened). VBA, as already mentioned, is weakly, strongly, statically and dynamically typed (which makes it fantastic for meeting diversity quotae), and will do its best to choose types for you should you fail to do so. This would work perfectly well, if not for the fact that output objects (such as those that are returned in the Debug.Print statements) are, if not otherwise specified, assumed to be of the same type as their input objects. When we asked VBA to multiply 11,111 by 11,111, it created two Integer objects to hold those values, performed the multiplication, and tried to pipe the output into an object of the same type - an Integer. Of course, 123,454,321 does not fit in an Integer object, and so we get an overflow. However, when we asked it to add 1 to 32,768, it created a Long object to hold 32,768 (which would not fit in an Integer), and assumed the output to be Long as well (which it is). Once we've passed the thermal layer we can move around as we please; it is only when crossing it that we have difficulties. Curiously, this limitation does not apply when dealing with explicitly typed values; this:
There is, though, something rather silly about the way in which VBA handles number types. Wikipedia describes VBA's typing discipline as a static/dynamic/strong/weak hybrid, which, while lacking the verve of "to or for, by with and from everybody", is nevertheless a very good description. To see just how omni-shambolic it is, load up your favourite VBA IDE (by which I mean, press Alt+F11 in Excel) and play along. Let's start by printing a VLN (very large number):
Debug.Print 123456789123456789You will notice that not only is VBA capable of handling VLNs, but that it automatically converts such numbers into scientific notation. So far, so good. Now, let's try the relatively simple task of multiplying two five-digit numbers together:
Debug.Print 11111 * 11111Readers who still recall arithmetic tricks from before the shapes of the office began to close upon them will know that the output should be 123,454,321 - much smaller than our VLN above. However, VBA declines to frame that fearful symmetry and instead hands us an overflow error. Clearly, something else is going on. Rather than try to build the mystery any further (because to do so would only lead to a terrible bathos, and because most of you have probably already guessed the answer), let's jump straight to it. Here's another fine and revealing mess:
Debug.Print 32768 'This prints perfectly well Debug.Print 32768 + 1 'And so does this Debug.Print 32767 + 1 'But this returns an overflow errorThat the magic number here is 32,767 will give the game away to those of you who have, in proper Snow Crash style, re-wired their brains to see the world in binary. For those who haven't, 32,768 is 2^15, and 2^15-1 is, as any fule no, the upper limit of the VBA Integer type (protip: that trivium can, at STP, end 90% of first dates within ten seconds of use. The more you know, etc.).
If you've played any submarine games or read Willard Price's Adventure series of books, you'll know that under the sea, in addition to singing crustaceans, there is "thermal layer" where warm water rests on top of cold, and that a submarine or other object passing through this layer must do so at speed lest it bounce off (actual submariners will know that almost none of that is true). The analogy is, I assume, obvious to you all. No?
Well, while other, fancier, la-di-da languages have signed- and unsigned- 32- and 16- and even 64-bit integers, VBA eschews the trappings of its decadent capitalist creators and instead supplies three honest, working-man's number-types: Byte, Integer, and Long (yes, I know it also has doubles and singles and those weird string-number things, but it's Party policy to treat those like Tony Benn fiddling his inheritance tax and pretend they never happened). VBA, as already mentioned, is weakly, strongly, statically and dynamically typed (which makes it fantastic for meeting diversity quotae), and will do its best to choose types for you should you fail to do so. This would work perfectly well, if not for the fact that output objects (such as those that are returned in the Debug.Print statements) are, if not otherwise specified, assumed to be of the same type as their input objects. When we asked VBA to multiply 11,111 by 11,111, it created two Integer objects to hold those values, performed the multiplication, and tried to pipe the output into an object of the same type - an Integer. Of course, 123,454,321 does not fit in an Integer object, and so we get an overflow. However, when we asked it to add 1 to 32,768, it created a Long object to hold 32,768 (which would not fit in an Integer), and assumed the output to be Long as well (which it is). Once we've passed the thermal layer we can move around as we please; it is only when crossing it that we have difficulties. Curiously, this limitation does not apply when dealing with explicitly typed values; this:
Dim int1 As Integer Dim int2 As Integer int1 = 11111 int2 = 11111 Debug.Print int1 * int2works perfectly well. So, now you know: you can multiply two five-digit numbers together in VBA, but only if you pass them in variables. In other words, don't hard-code your values, you Whore-drinking muppet.
0 comments:
Post a Comment