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!

 

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%!

 

Today Excel is The Meaning Of Life!

For all you Excel geeks and fans of A Hitchhiker’s Guide to the Galaxy (yes, both of you), today’s date is 42,000. I’ll let you derive the deep meaning of this cosmic alignment. For everyone else, an early Happy New Year to you!
=round(now(),0)

For those that haven’t read A Hitchhiker’s Guide to the Galaxy, it’s a a great read. Lots of witty humor and has a nice anti-authority message. In the first chapter the main character fights his house being condemned by local government. The authorities argue that he should have seen the notice about the zoning rule taking place which was located at City Hall “on display in the bottom of a locked filing cabinet stuck in a disused lavatory with a sign on the door saying Beware of the Leopard.”

Another unrelated yet fun line from the book I felt could have been applied to me in my younger days when during which time I had a job that I had no idea what was going: “One of the major difficulties Trillian experienced in her relationship with Zaphod was learning to distinguish between him pretending to be stupid just to get people off their guard, pretending to be stupid because he couldn’t be bothered to think and wanted someone else to do it for him, pretending to be outrageously stupid to hide the fact that he actually didn’t understand what was going on, and really being genuinely stupid.”

Enjoy and happy 42,000!

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.