AET VBE Tools

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!

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.

Calendar (Date Picker) Control Alternative

When you want to add a calendar to a userform, you can try to make use of the Microsoft Date and Time Picker Control. Unfortunately this control is not 100% reliable and a number of people have decided to make their own.

In my case, I was asked by a client the other day to add a calendar. I decided to tweak a calendar I made way back in 2005. That calendar is based on a formula I saw on Daily Dose of Excel by John Walkenbach.

Anyway, I had a think about what I should try to imitate with regards to the date picker control. First off, it's compact. After all, you don't want a single control to overwhelm your main form.

So I made this.



You can change the week start date to either Sunday or Monday, as well as show/hide ISO week numbers. (I used to work in International Sales and I used the ISO Calendar to refer to week numbers for use in logistics with clients and overseas representatives. So I thought it would be a good idea to include this functionality. The ISO calculation uses a formula based on one by Evert van den Heuvel.)







The date you select becomes dtCalendar in the clsDateLabels module.


I also decided to add some shortcut keys:

Alt + Q: Show the next month
Alt + W: Show the previous month
Alt + E: Show the next year
Alt + R: Show the previous year
Alt + T: Show this month and year
Alt + O: Show/hide options
Alt + I: Show/hide ISO week numbers
Alt + S: Start the week from Sundays
Alt + M: Start the week from Mondays
Alt + C: Show the calendar (if not visible)
Alt + X: Close the calendar (if visible)

A reasonably useful calendar.

But then I thought that a lot of people don't need any options, so I made 2 more versions which are even more compact.

This one is a simple calendar.



You can change the week start date by going to the mCalendar module and changing vbSunday to vbMonday.



And this is one that shows ISO weeks.



Shortcut keys for both the simple and ISO calendars:

Alt + Q: Show the next month
Alt + W: Show the previous month
Alt + E: Show the next year
Alt + R: Show the previous year
Alt + T: Show this month and year
Alt + C: Show the calendar (if not visible)
Alt + X: Close the calendar (if visible)

Download them here.

AET Find and Replace v1.3

I decided to add an extra feature to my Find and Replace tool.

When using the Find and Select mode, I thought it might be useful to have an option where you don't just select cells containing the text you wish to find, but also highlight that text to make it stand out.

So adapting the code I used in this post, I present to you AET Find and Replace v1.3.

Here it is in action. Click the color wheel.



Choose a color.



Click Find and Select Fields.



Download it here.

Updates

I'm working on various updates. One thing I have done is make yet another change to my AET Data Checker. I changed it so that when you show the form, the sheet you have on your screen (the Active Sheet) will be the sheet constantly referred to, even if you navigate to other sheets or workbooks.

Previously, selecting other sheets had the effect of making them become become the Active Sheet (the sheet to be referred to). This probably confused a few people or may have been a distraction.

Download the new version here.

Coming soon, a new version of AET Excel Utilities.