VBA Variable Olympics…. Integer vs Long Vs Byte vs Double vs Undeclared

When using variables in VBA (or any language) eventually every coder will have to make some choices on which type of variable to use for numbers.

The common practice is to either use Integer, Long, or Double (I never bother with single). A quick recap of each of these is that:

Integer uses less memory (2 bytes) but has a range of numbers of -32,768 to 32,767, and does not allow decimals (by definition this is what “integer” means in all programming languages).

Long uses double the memory (4 bytes) but the range of numbers is exponentially–2,147,483,648 to 2,147,486,647 (that’s 2.1 billion vs. 32 thousand on integer). Whole numbers only.

Double is the go-to variable if you may have something over 2.1 billion or if you have decimals. However its overhead is 8 bytes. The range of numbers is massive but Excel can’t handle anything over 15 digits, so it’s always seemed irrelevant to me (in case you care, here’s the supposed range: – 1.79769313486232E308 to –4.94065645841247E–324 or 1.79769313486232E308 to 4.94065645841247E–324

One other variable worth mention is:

Byte is a hardly used variable that can return a whole number from 0 to 255, and only uses 1 byte. Interestingly, this is less memory than a Boolean variable (true or false), which uses the same memory as Integer. It’s tempting to consider using Byte for small numbers to save memory. However, it’s not recommended for reasons I’m unclear about.

What brought this to my attention was an old StackOverFlow post that details how  Microsoft claims that they shift all VBA variables from INTEGER to LONG, which essentially nullifies the benefit of EVER using INTEGER.

“In recent versions, however, VBA converts all integer values to type Long, even if they’re declared as type Integer. So there’s no longer a performance advantage to using Integer variables; in fact, Long variables may be slightly faster because VBA does not have to convert them.”

I decided to conduct the VARIABLE OLYMPICS by timing how fast a basic macro ran, but with different variable types.

The results align with what Microsoft says, in that it probably makes sense to just use LONG instead of INTEGER in pretty much every case.


System Searcher My First Potentially Useful Tool

My latest contribution to the Google Chrome Store I think is the most broadly useful tool I’ve developed. It allows you to execute searches on multiple websites at the same time. This can be useful if you frequently search sites like Amazon, Walmart and Target for the same thing such as “Tables”.  With this extension you just punch in what you want, and it launches searches on the websites you setup.

You can download from Google store at: http://bit.ly/systemsearcher

Custom Excel Function For Next Highest Prime Number

I’m still having way too much fun on Pastebin.com. Below is a formula I started while working out, but ended up getting a little more fancy after I tried to clean it up to run quicker. It calculates the next highest prime number after a given number (example: =NEXTPRIMENUMBER(100) = 101)

There are some regular Excel Array formulas that can be used to test true or false on integers being prime, but they are limited to testing values matching row counts, so no higher than 1.05 million (boring!). I did run into a weird VBA bug where I discovered the vba Function MOD ALWAYS converts its values to LONG parameters. I discussed this a little on Stack Overflow as this became an issue when trying to do bigger numbers.

Anyway, check this out if you’re bored or if you’re dying to know what the next highest prime number is after 10 trillion (it’s 10,000,000,000,037). It does this calculation in under 4 seconds on my laptop. Initially it was taking about 5 seconds to do a billion, but I cleaned up some redundancies, and it’s pretty good now. If anyone can enhance (using EXCLE vba), I’d be curious to see.

Code Saving/Editing Option For Google Drive

For better or worse, I have been entangled with Google Drive since its inception. It’s not perfect, but the ability to access it through most customers’ networks or while using their mandated laptops has proven extremely useful. My most common usage is accessing notes or solutions I’ve compiled from previous projects. I realize lots of offer ways to do this, some probably better. But Drive was one of the first I found, so I have a lot in there which makes the concept of switching to GitHub, OneNote or something else seem not worth the effort.

One problem I did encounter was saving text in the Google Doc format. When storing saved code for VBA or Swift, Docs occasionally applies little adjustments in the formatting or attempt to spell check which was undesirable. What I really wanted was a text editor like Notepad.  Google drive does let you save Text files, but they weren’t very easy to edit from the normal interface.

I found a great solution with an app called Drive Notepad available in the Chrome Webstore. This does almost everything I want with some helpful features. Most notably, you can adjust what language your code is displayed in, which is extremely useful for its readability. Initially I was just looking for a text editor (which there are plenty of), but this works even better. See screenshots below.movie Rings trailer

Other positive features:

  • Free. Thank You DM!
  • Open source.
  • Can share with usual google share options, though not in real time like doc or sheets.
  • Searchable as a regular google drive. I use excessive comments in my archived solutions specifically to facilitate the searching based on what I will likely remember.

Small drawbacks:

  • Have to manually save. Unlike google drive document that saves literally in real time, Drive NotePad requires you to manually hit save or CTL S. This has never been a problem for me as it delivers a warning when you navigate away after changes, but it would be nice to have. I’m sure the creator has been bombarded by this request.
  • It inconsistently checks code for errors. I wouldn’t expect it to check any code, but it does for some languages like JavaScript, but not for  others like VBA. Basically people shouldn’t consider this a substitute for XCode or an actual development platform. Again, this is just a great way to save/share your finalized work for future usage.
  • Files count against your storage space on google drive. Nobody should care about this, I just am listing it in case anyone was curious. Source text takes up an infinitesimal amount of space that this will never be an issue. I always got some quirky satisfaction of knowing that Google Docs and Sheets never counted against my storage limit (which I’m not near close to hitting). If you’re rolling your eyes that I listed this as a small drawback, I don’t blame you.