Sunday, 15 January 2017

AET VBE Tools v1.3

I've been working on the latest version of AET VBE Tools all weekend, literally.

It's been an all out effort, so I made some major improvements to the code, and added a little more functionality to the Multiple Find and Replace tool.

As per last time, I find myself without time to blog in detail (my wife thinks I'm insane, and she may be right!) so without further ado, I present AET VBE Tools v1.3.

You can download it here.

Thursday, 12 January 2017

AET VBE Tools v1.2

AET VBE Tools v1.2 is out.

More bugs were squashed and a new tool called Multiple Find and Replace was added.

The download link is here.

I'll add more details later. Time for sleep.

Sunday, 8 January 2017

AET VBE Tools v1.1 and Line Numbers

Last time I blogged, I mentioned that my new AET VBE Tools had more stuff coming.

Well, I've been VERY busy adding new tools and settings, redesigning and also fixing bugs, both major and minor.

There are two new tools and one new feature.

Code Snippets
You can now add snippets into a procedure where the cursor is located. And the Code Snippets tool can save up to 10 different items.

Highlight Code in Excel
There's no way to highlight code in the VBE. But we're using Excel, right?

This tool copies selected procedure code to a worksheet in a new workbook, and also inserts event code to highlight cells that looks for keywords such as "If, "For" and "With" so you can see which lines of code match.

Potentially useful if you have lots of nested code!

Undo last operation
Most tools now have an undo option. So if you want to revert to how things were before the tool was used, now you can! Refer to the Help Files for details.

Line Numbers
I improved my code that works with line numbers. And as I promised last time, I'll write a bit to explain how they might be useful.

You can add line numbers like 10, 20, 30 or 10:, 20:, 30: in front of your code.

There are some rules. For example, they must be unique per procedure. Some code doesn't accept them, such as the first Case statement in a Select Case block. (Other Case statements don't have this problem). For the most part, they are okay, and my tools are designed to only add them where they are allowed.

So why use them?

They are a handy reference. Imagine giving or following directions to a house. Isn't is easier when there are street signs? You can also use code like GoTo 120, which means the code will jump to Line 120 of your procedure.

And how about an accident? Street signs are going to make things easy again. You can use line numbers in the same way an emergency services worker would refer to a street sign when writing up a report.

For example, step though the code below using the F8 key to see how it works. (Click Ctrl + G to show your Immediate Window if i's not visible)

Sub ERLDemo()
On Error GoTo 40

10 Debug.Print 1 / 0

20 Debug.Print "Today's date is " & Format(Date, "d mmmm, yyyy")

30 Exit Sub

40 Debug.Print "An error occurred on Line " & Erl

End Sub

The result...

Because an error occurred on Line 10, the code went straight to Line 40 and also told us which line of code caused the problem.

Useful? You decide. Something to consider is that you may need to edit existing line numbers if you change your code. Fortunately, my Add Line Numbers tools replace old ones with new ones, and in sequential order, with one click. Also, I've noticed that adding colons to line numbers actually makes stepping through code take longer, so unless anybody can mention a benefit, I'd stick with just using numbers by themseves. But as you guess, my tools have the option to include colons if that's your preference.

Speaking of which, download the new version of AET VBE Tools here!

Saturday, 24 December 2016


T'was the night before Christmas,
when all thro' the house,
not a creature was stirring,
except for an eccentric developer, who can't rhyme to save his life.

But he can code a bit. So this is his present to you!

Let me tell you a story...

Once upon a time, there was a VBA coder who wanted to install a well known indenter. But he couldn't due to the IT policy where he worked, and he decided to make his own. Pleased with his success, he uploaded it to his blog for download. Years passed. He also made a couple of other VBE tools, and often thought of combining them to make a full suite of tools. One day, he was reading about line numbers in VBA code. "Gee" he thought, I should rewrite my code to work with them too." He had a try, but couldn't think an easy solution. One day, when he had nothing better to do, he thought a bit more and came up with an entirely new approach. "Eureka!" he shouted, "I haven't found gold or invented a law of displacement, but I've thought of a way to handle those line numbers!" He set about adapting his code and was thinking of writing a blog post when all of sudden his site went down. "Hmm, that's odd" he thought...

Now, by coincidence, he was having a look at a friend's blog. (Let's call him Dick). And he saw that another friend (We'll call him Dennis) announced his own amazing addin. Inspired by this, the coder was eager to upload his own. But still his site was down. He waited, and while he waited, he thought "Why not add more stuff?". And so he did. Finally he asked yet another friend to help get his site back up (Let's call him Colo. Thanks Colo!), and here we are. He's finally got his addin uploaded and is writing a blog post about them at this very moment. Literally.

So what does this addin do?

It indents VBA code in the active VB project, module, procedure or selected code, with or without line numbers. I'll blog about line numbers next time.

It also has the ability to add or delete line numbers to/from the active VB project, module or procedure.

And you can export your active VB project, module or procedure code to text files, with the option to assign unique file names (or the files will be overwritten with new version of your code if that's what you prefer)

Another useful thing is adding, replacing or deleting macro comments (comments directly beneath Sub and Function names, for the active VB project, module or procedure.

There's more stuff on the way too!

Here's a pic.

And here's a link!

Merry Christmas!

Saturday, 10 December 2016

Icon Set Alternative

I'll be honest. I never use icon sets. But I had a look at them the other day and thought that they didn't do what I expected.

Here's a screenshot of them in action.

For those not familiar, icon sets are used the following way. Select the range of cells you want to add them to, then on the Home tab, select Conditional Formatting, Icon Sets and choose which one is best for you.

Let's see what they are actually doing. Select Conditional Formatting from the Home tab, then Manage Rules, select the rule in use (you should be able to see Icon Set on the right), then click Edit Rule.

Okay, it appears that the icons change according to what range of values they are in. I would prefer to see when values change regardless of range, so an ↑ arrow for increases, ↓ arrow for decreases and a → arrow if values stay the same.

It's easy enough to set up an alternative. First the arrows - I could use Wingdings 3 font to get some arrows, but I've decided to go a different way for formatting preference, and because it's easier to use actual arrow characters inside formulas, rather than use "g", "h" and "i", then try to remember what they refer to later on.

So I selected Symbol on the Insert tab, then selected the Arrows subset on the Symbol dialog box and inserted them into a cell for later use.

Jumping ahead, the next screenshot shows what I'm up to. I've entered the following formula from the second cell down in an adjacent column. Left or right, it's up to you. Note that I'm not including the top cell. Formulas and formatting start in Cell B3.


And as you can also see, I've entered a conditional format. Green if Cell B3 is greater than Cell B2. Note that for Conditional Formatting, you only need to use a TRUE condition. And as the whole range is selected, Conditional Formatting will adjust the condition's cell references to work with each cell accordingly.

By the way, I formatted the font by clicking the Format button, then Color (Americans don't know how to spell. They did invent Excel though!) and selecting green from the pallette.

I do the same for when Cell B3 is less than B2, with the font becoming red.

Almost there. I don't need 3 conditional formats, I just change the existing font colour in the cells themselves. Not Yellow, it's too hard to see. Orange is a better choice, and I also make the font bold to make the arrows stand out a bit more.

Done! See you next time.