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

Doom Best Coin On Test Network

I have launched a currency called “Doom Best Coin” using the Ethereum#ERC20 standard (on the Rinkeby test network). Currently it doesn’t do anything meaningful besides demonstrate that ANYONE can start their own currency.

This is a GOOD thing and one of the primary reasons I was enthusiastic for Ethereum long before its existence. Private currencies are a great step forward for decentralization. There are almost 500 ERC20 coins now in use on the main network, several with interesting concepts. By next year I bet we see over 2 thousand.

Now the question for the world is… which currency do you trust?? Here’s a hint: currencies that say “In God We Trust” or are named “Doom Best Coin” probably shouldn’t be at the top of your list.

Ethereum’s Augur Project One Step Closer to Reality

One of the most exciting components with Ethereum is the Augur project, or as I call it, “The Bet on ANYTHING” market!”

Yesterday the project released an updated White Paper found here.  The technical details are not for most people, but what is exciting is being one step closer to the possibility of two strangers on the internet being able to place trustless bets on almost anything!

The word “betting” has a negative emotion attached to it in American culture, but being able to assign personal stake in prediction models is incredibly valuable. Indeed it is perhaps a much-needed component in our political system.

As I type this, one of the most hated presidents in American history is addressing the nation in a Statue of the Union. The internet is filled back and forth rage about who is “right” and “wrong”. As the fury between two groups grows, perhaps the ultimate test for the truth can be found if the two opponents can form an agreement of what their disagreement is, and bet on it.

Consider how this could be used in economic debates in uncovering the truth. Politicians (particularly in my home state of California) are incredibly dishonest at presenting understated costs of projects to voters around elections. Augur presents an option to expose what politicians truly believe:

“Do you really think that this project is going to be under what the budget states? Let’s bet on it!”

Do you want to bet if this is going to truly be a temporary tax?”

Additionally, consider how this tool could be used to hold a politician at their word. During a campaign, candidates could leverage this tool to offer promises to voters: “I’m Joe Nobody running for US Congress and I will not vote in favor of any budget that is not balanced, and I’ve placed a $100,000 bet to assure this in the Augur network!” Such a statement would be much more meaningful than the standard “I promise, I’m a good person!” assurance we get from current candidates.

Hopefully, this goes live soon. Here’s an older video showing its possibilities from 2015:

Monero’s Web Mining Option

Cryptocurrency Monero has made some noise recently with its ability to mine on websites, sometimes without user consent. While this probably sounds unethical (which it is), the opportunity to leverage user CPU power as an incentive to visit a website instead of advertisements is an interesting concept.

While I’m still kicking this idea around in my head, does this make Monero potentially the most stable currency option? Measure users’ CPU usage as a function of time?

While you think about this, please see the below is an example of mining using your consent. Please click “start mining” this and leave it running for the rest of your life.

If you’re interested in seeing a less ethical example, checkout this fake page I made that looks like it’s giving VBA advice, when in reality it’s hogging your CPU. Be on the lookout for stuff like this if you’re browsing. If your laptop starts to takeoff, because the fan is going so fast… you may be on a Monero mining website!

Loading…

 

Downgrading Utility Of Limitless Chrome New Tab

For about 5 months I have been using a Chrome extension called Limitless as my default “new tab”. During installation, Limitless requested my GMAIL credentials, which I I declined to provide. It works fine without them. However, in light of the recent Equifax data breach, I was curious to see what exactly Limitless was asking for.

As I suspected, the extension wants the ability to have total control of your email. Specifically:

View, manage, and permanently delete your mail in Gmail

At this point, alarm bells should be ringing loudly in people’s heads if they are entering in an active gmail address. Limitless’s privacy policy can loosely be interpreted as, “Even though the NSA, Equifax and Ashley Madison can’t keep their data secure, you can trust us and every employee that works for us now, or in the future, because your privacy is important to us! 🙂 ”

They do state that data is stored locally, and looking at their code, this appears accurate. However, they also state they may change this policy at some point in the future…

Sure, you could use a dummy gmail login, or none at all. However in principle, I’m tired of extensions asking to be trusted with information they simply cannot guarantee to remain secure. So I changed my feedback to negative 2 (out of 5) on chrome store and am going to drop the extension completely.

To be fair, Limitless is just one of MANY extension offenders wanting total access to your email. The point of this post is just to encourage people to drop these extensions, even if you don’t provide or even if they are semi-useful. If Limitless adjusts their policy I’ll change my Chrome extension feedback and followup with this post saying so.

As a side note, I feel like the “new tab” extension market is kind of weak. I may tinker with making my own. Maybe I’ll call it “LIMITED” as in the amount of data I’ll seek to collect from users!

Of Course Equifax Got Hacked!

Why is anyone shocked that another data breach results in millions of people’s information falling into the hands of criminals? Stated differently, if Ashley Madison, LinkedIn, and the NSA can’t keep their data secure, what makes people think a credit score entity is going to perform any better

I don’t know the specifics of this Equifax hack and I don’t really care. If I had to guess, I’d say probably inside job. For people that think their data is secure because they trust sites like “Google” they are looking at it the wrong way. A better question is, “do you trust every single employee at Google who has access to your data?” The answer is the same as what the NSA would say if asked if they trusted all of their employees (even before Mr. Snowden left the country), of course not!

The only thing that really matters in this situation is will the public recognize the need for blockchain technology and zero-knowledge services? Steller examples include LastPass, SpiderOak, and Signal all can face data breaches with a much stronger level of confidence because even if someone gets every bit of information on their servers, it is highly unlikely anything useful could be leveraged from it.

We live in the digital age. Signatures made with pens that my 6-year-old could mimic effectively is not security. Entrusting your data to corporations that have human employees, and have human errors will ultimately have data leaked. Hopefully, today’s painful lesson will beef up the requirements to take security more seriously. It’s really pretty easy, trust no one.

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!

 

I, Woz A Terrific Read Showing The True Genius At Apple

I, Woz is the best book I’ve read thus far in 2017. I ripped through this 9 hour, 12-minute audio book in two days. Steve Wozniak’s autobiagraphy, especially the beginning, shows the internal thinking of a truly gifted mind and all the joy and curiosity it entails. I’m guessing that Wozniak really did write most of it, as the language definitely possesses his signature “stream-of-consciousness thinking”, prankster humor, and unmodest declarations of how awesome his work was, without really seeming arrogant. “Hey, it was the best, nobody had ever done anything of what I did…!”

A week earlier I finished reading Elon Musk Inventing the Future, and compared it to the Steve Jobs Biography. The Jobs biography gave clear examples of how brilliant Wozniak was, but uafter reading this, I consider Wozniak to be, the most admirable man to come out of Silicon Valley.

Hearing Wozniak talk about Steve Jobs just made me dislike Jobs even more than I already did (which is saying something). That man was bipolar, manipulative, inconsistent, narcissistic and not even that technically smart. Wozniak doesn’t even try to make him out as a bad guy, but he can’t exclude a couple key points that show a dark side of the former head of Apple.

Wozniak was smarter than pretty much everyone, and yet incredibly kind too. He threw concerts and gave away money to friends just because he felt the deserved it. One painful point was hearing how he gave up a bunch of shares of Apple to some dirt cheap rate to UK hedge fund guy he’d never met because he said he would months before. Wozniak felt he had to honor his unconditional promise even though the guy undoubtedly would have disappeared if the stock had plunged in value.

Yet the biggest value for me was just hearing Wozniak’s life motto. In so many words, he gives the advice that many successful people deliver. Do what you love, have passion, and really dig deep to understand something when you find that passion.

He made one point that hit pretty deep with me. Wozniak feels lucky that he was at the forefront of the PC age as his skillset was just right for that period of history. He also provides other illustrations of people living in the right place in history such as Ford at the start of the automobile. Then he went on to say that if you ever feel like you’re at the beginning of a technological revolution, that you feel you can contribute to — go for it.

I swear I felt he wrote that specifically for me to read this exact week (I know he didn’t, but it felt like it).

I am far from a Wozniak, but I do possess some technical skills. For years I’ve been fascinated with Ethereum and blockchain technology in general. I have been ranting to any poor soul who will listen how this technology truly could be the biggest technical game-changer since the internet. Ironically this week, Ethereum crossed the $50/share threashold — more than 80 times its inital value when it went live. While I don’t push it as an investment, this is quite frustrating that I didn’t dive deeper into it than I did. If I would done the braver thing of put my professional life on hold, took up a role in a startup as a developer — I likely would have felt more fullfillment and amazingly would probably be wealthier.

To be fair, I feel grateful to have regular work which I don’t think isn’t terrible. In fact, I think it is reasonably fulfilling! Yet it’s not something I’m really that passionate about. I work for a big corporation (which Wozniak also advises against!), and this blurb of the book about being at the forefront of a major technology got me thinking if I need to make a switch. The blockchain technology is still in its infancy stages so there’s still plenty of time to be a part of the inital movement.

Something to think about. If my employer reads this, I’m just kidding ha ha. Just trying to be prankster like Wozniak… ha ha…!