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.


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.

Best Method for LastRow/LastColumn In Excel

It’s been awhile since I’ve posted, but I’ve just started messing around with PasteBin and so I’ll post something I put there.

I think the most common beginner VBA question is “how do I find the last row of a sheet?” There are dozens of answers out there, but there are better ways to accomplish this. In my opinion, the best way is below using the below VBA script which I’ve turned into a formula you can paste into your file and get your answer. My post includes text stating why I believe it’s better. Other methods are vulnerable to uneven columns, usedrange being out of sync, or filtered rows. Using the FIND method avoids a lot of confusion.

This particular formula has some if statements so if a user selects an entire column or columns, it will return the lowest row in that range.

Excel should have this function!

 

My Excel Contest Video

I am in an Excel popularity contest (talk about an oxymoron). A couple Excel bloggers were invited to compete in “Excel Dashboard contest” and naturally this is what I spent last Saturday night doing. The winner is declared by how many YouTube likes each effort earns, which essentially means I have no chance of winning because a couple competitors have pretty large followings.

However, I would like to at least have a good showing, so if you have a moment, I would be most grateful if you gave my video a “like”. Requires a youtube or gmail account and just clicking the “like” (thumbs UP) button located above where the number of video plays is displayed.

Don’t feel like you need to watch the whole video unless you’re suffering from insomnia and are seeking a cure.

We Are Legion (We Are Bob) A Fun Sci-Fi Book With Good Things To Come

I don’t read much fiction, but I heard We Are Legion (We Are Bob) by Dennis E. Tayor was pretty good. I enjoyed reading it, but this book is only for tech nerds, most likely men. Lots of Star Trek and Sci-Fi references. There’s a couple interesting scenarios that I’ll not discuss for spoiler purposes, but I was pretty well entertained. It’s a healthy mix of comedy, action, and drama.

My only major complaint was the author’s choice to pick the Catholic Church as an antagonist of the future. What are the odds of the Catholic Church, or any Christian Church, even TRYING to take over America in the next 100 years? Slim to none! It’s both boring and unrealistic to pick the irrelevant One True Faith as the villain of the 21st century.

Other than that, I really did enjoy the entire book.

As a side note, the Audible version was VERY well performed by Ray Porter. I may look for other narratives by him. Various characters including Homer Simpson and Admiral Ackbar were performed quite masterfully.

If you’re a tech guy, you’ll probably enjoy this. This also appears to be part 1 of 3.

Reduce Large Excel Files with .XLSB

Quick tip on how to slash the size of large Excel files, often by more than HALF! It’s a somewhat unknown file format .XLSB which stands for Binary. When Microsoft released Excel 2007, they switched the file format to be a zipped XML format. This enables a lot more cross compatibility with other Microsoft products like Outlook and Windows Explorer Preview. XLSB is essentially the Excel 2003 version, but with the full range of rows.

The savings on a lot of my big files has been pretty significant. Generally a reduction by about 60%. This only seems to happen on larger files. So if you have a file of 1 meg, it likely won’t get reduced by that much.

One other advantage of XLSB is that it retains VBA without a prompt, something I definitely prefer. Only drawback is if you have file linking to other Microsoft products like power-point, or even other spreadsheet programs like OpenOffice, the XLSX will definitely work better. However, most massive files exceeding 10 megs probably are probably only used in Excel.

Updated with a video. Spoiler Alert: I reduce a file size by about 60%!

 

Two-Dimensional Sumif’s In Excel

Yesterday I needed to perform a two-dimensional sumif’s, and finding syntax on how to do this wasn’t easy (meaning it took more than 3 minutes). So I figured I’d flood the internet with one more example.

My Video below gives a short walk-through of it, but essentially you use SUMPRODUCT function. The syntax is basically SUMPRODUCT((Range1CONDITION=<VALUE>)*(Range2CONDITION=<VALUE>)*DATARANGE).

In my initial example, I use formula:

=SUMPRODUCT((B3:B12=I2)*(C2:F2=I3)*C3:F12)

Order doesn’t matter in the above statements. What the function is actually doing is looping through each cell in the DATARANGE, testing if all the criteria is true. If so, it assigns a 1 in the first two fields, and the test, for each cell in the range, and if true, it assigns a 1 to it.

Sumproduct is also available on Google Drive Sheets, OpenOffice. Not available on Excel 2003 and earlier.

Link to file used in video.