Andrew's Excel Tips

Application Settings is now Excel Settings

noreply@blogger.com (Andrew) — Sun, 15 Oct 2017 08:26:00 +0000

I decided to add more some more functionality to my Application Settings addin. In fact, I also changed the name to Excel Settings. (It's actually a different file, so please be sure to click "Check settings when opening/saving files" on the menu if you want these to fire).

What's new? As you can see, I added 2 new sections for Workbook Scope and Worksheet Scope settings. The picture pretty well speaks for itself.

And, if your file is saved (has a path), you can see File Size, Last Saved (Date and Time) and Last Edit (Author) details.

The download link is here.

Application Settings Add-in Version 2

noreply@blogger.com (Andrew) — Sat, 02 Sep 2017 05:14:00 +0000

I made a new version of my Application Settings addin as per Sébastien's comments in my last post (mirrored on the Daily Dose of Excel).

As you can see, there are new settings for the following.

Application.Iteration

Application.MaxIterations

Application.MaxChange

In the case of the last 2 settings, you'll notice that there are Set, Reset and Save buttons. This is how they work,

Set: Set to the number that is entered in the text box.

Reset: Reset to the real default or “alias” default.

Save: Save an “alias” default instead of Excel’s real default. For example, if you prefer 120 instead of 100 for Max Iterations, you can set the “alias” default so that the form does not appear when opening or saving the active workbook if Application.MaxIterations is set to 120. Also, clicking the Reset button thereafter will reset Application.MaxIterations to 120. And using the Set button to set Application.MaxIterations to any other value than 120 will show the value in red font to indicate it is not the “alias” default.

Hope this is useful. Download the new version here.

Application Settings Add-in

noreply@blogger.com (Andrew) — Tue, 22 Aug 2017 13:44:00 +0000

Back in 2005, I noticed something that worried me.

You may know already that switching Application.Calculation to xlCalculationManual can make various code run faster. It can be a big time saver.

The problem, as I see it, is not switching it back to xlCalculationAutomatic. Given that some people for whatever reasons might use Manual Calculation all the time, most people don't, especially the vast majority of average users who probably haven't heard of this setting. With Calculation still set to Manual, they might be looking at values that haven't be updated. Even experienced programmers might be temporarily confused until they figure out what's going on. Imagine someone in a sales department quoting incorrect pricing to a customer or doing a faulty presentation at an important meeting. Not good.

And now for what really worries me - saving files with this setting. Let's try something. Close all Excel files, except one to use for testing. Now switch to Manual Calculation. If you don't know how to do it in code, you can click Calculation Options on the Formulas tab, then select Manual. Now save in that setting, close Excel, and reopen the file. If that file is the first one to be opened, Excel Calculation will be set to Manual by default, and all other files opened thereafter will be affected too. Save any of them with this setting, and the same thing will happen if they happen to be the first file opened...

So, how do you know Calculation is set to Manual without specifically checking?

You can't.

Now, imagine sending one of these files to colleagues or customers, then realizing something is amiss days later. Again, not good. In fact, downright scary.

So, also in 2005(?), I made an addin called Calculation Checker. It checks Calculation when you save and prompts you to do so as Automatic if set as otherwise (including Automatic Except for Data Tables).

I've found it useful, but since then I've thought there's room for improvement, so I made something new.

As you can see there's 3 menu items. The bottom 2, when toggle to "On", check files when opened/saved for the following settings.

Application.DisplayFormulaBar

Application.DisplayStatusBar

Application.Calculation

Application.ReferenceStyle

If any of those settings are not at their default, the Application Settings form will be displayed. Non-default settings are displayed in red. (Yes, the first 3 should be obvious, but easy enough to miss if you're busy, tired or both!)

Click the form's controls to reset them individually, or just click the Reset Everything button, then the Save File and Exit button if you choose to. Alternatively, click the X button not to save the file. Note that any other file that are open will also be saved with these settings (unless you change them later), because they are Application settings, not Workbook settings.

And because the form can be opened directly from the Ribbon, you can easily change any of the settings at any time for whatever reason. Click the Show Settings button and you can see other settings that can also be reset when clicking the Reset Everything button, if the Include other settings checkbox is ticked.

Note
Keep in mind that these additional settings aren't checked automatically. The form only resets them if you click the Reset Everything button as mentioned above. Also, if Application.EnableEvents is set to False by VBA code, my addin won't check files when opening or saving as these are the events that trigger it. In fact, you should be setting this False if any of your code does open or save workbooks to prevent my code from running, then set it back to True before the code ends.

Hopefully this tool will be of use. You can download it here.

Monthly Report Tutorial

noreply@blogger.com (Andrew) — Mon, 15 May 2017 11:00:00 +0000

At a former client, I was asked to submit monthly reports that show details of work performed in 15 minute increments.

My line of thought went something like this,

"Let's see, a monthly calendar, something like the one on my fridge door comes to mind and making one in Excel should be easy..."

One problem is space. If I do several tasks in one day, do I use tiny font to make the details fit, or do I make the calendar larger to the point that I have to scroll copiously?

Also, just how practical is that style of calendar going to be when it comes to adding up total time per task? Something along the lines of a regular timesheet would be better.

I can easily fit 32 rows on my laptop screen. That's a good start. So here's how to do the same thing I did, if you are interested.

Leave the first row for your headers. In cells A1 and B1, enter "Date" and "Day", then change the orientation. Right click the cells, select Format Cells, Alignment, and change Orientation to 90 degrees.

(You might want to change the Alignment too. Choose from the options on the Alignment Group on the Home Tab)

Enter the first day of the month in cell A2. Select range A2:A32, then change the format to either "d/m" or "m/d" as you prefer. Right click the cells, select Format Cells, Number, and enter the format in the Type text box in the Custom section.

Now enter =A2+1 into Range A3:A32 and click your Ctrl and Enter keys simultaneously to enter the formula into all selected cells.

In the same way, enter =CHOOSE(WEEKDAY(A2,1),"Su","Mo","Tu","We","Th","Fr","Sa") into Range B2:B32.

Adjust the width of both of these columns and set the alignment to suit.

You should have something like this.

And now for the details. Long descriptions take up space, so let's use numbers instead. Keep in mind that longer tasks won't be completed in 15 minutes, and recurring tasks will be duplicated so that's going to cut down the number of tasks in total. With any luck, we can keep things within double digits.

Start times allotted for the 15 minute intervals go in Row 1. Adjust the Orientation to 90 degrees. "h:mm" is a suitable format.

The task descriptions that match the numbers can go on the right. But note the numbers to their left to perform a lookup.

Important: adjust the following ranges to suit your requirements. Use Named Ranges if you prefer.

Enter formulas to add up the time. Type the following formula into Cell AI2, and drag down to the end of your list.

=IF(COUNTIF($C$2:$AE$32,AG2)=0,"",COUNTIF($C$2:$AE$32,AG2)/4)
You should have something like this.

You can freeze the first row if the number of tasks exceed the number of visible rows on your screen. (View Tab, Windows Group, Freeze Panes, Freeze Top Row)

Now for some extra features to enhance visibility. Why not add some Conditional Formatting to highlight the weekends? With Range A2:AE32 selected, click the Home Tab, Styles, Conditional Formatting, New Rule, then "Use a formula to determine which cells to format" and enter this formula. (Click the Format button to choose a suitable format)

Here's the result.

An ActiveX Combo Box and a bit more Conditional Formatting makes it easy to see when the work was done. If you can't see the Developer Tab on the Ribbon, select the File Tab, Options, Customize Ribbon, then tick "Developer" on the list to the right and click the OK button.

On the Developer Tab, select Insert from the Control Group to add an Active X Combo Box. (I've already added one to Cell AH1)

Right click the Combo Box and select Properties. Set the LinkedCell and ListFillRange properties. I've hard-coded my ListFillRange range reference but you can use Named Ranges too, as in "=Tasks" without the quotation marks.

When finished, toggle off Design Mode on the Developer Tab.

Note the linked cell. That gives me the selected item of the list. Now I use another formula to get the reference number which I have put in the cell below the linked cell (In this case, Cell AJ3).

=MATCH(AJ2,AH:AH,0)-1
If I select the first item on the Combo Box, Cell AJ3 will show 1.

Here's the Conditional Formatting for the details part of the report. (Range C2:AI32)

And here's the Conditional Formatting for the list. (Range AG2:AH32)

I also added some Data Bars to the hours.

And we're done.

No VBA was used so you can send the file without explaining the need to enable macros.

Here's a download link if you want to skip making one yourself.

AET VBE Tools v1.7.3

noreply@blogger.com (Andrew) — Wed, 26 Apr 2017 00:12:00 +0000

Yet another tool has been added!

You can now run a report that shows project statistics. It's a good way to get a general feel of how much work you have done, and where it is located.

Details include:

Module Names

Procedure Names

Count of Children (Project and Modules)

Procedure Scope

Count of Lines (Project, Modules and Procedures)

Count of Comments (Project, Modules and Procedures)

Count of Declaration Lines (Project and Modules)

Count of Declaration Comments (Project and Modules)

Download the new version here.

P.S. If you have purchased an earlier version, contact me by email and I'll send a new set of tools - free of charge!

AET VBE Tools v1.7.2

noreply@blogger.com (Andrew) — Thu, 06 Apr 2017 12:56:00 +0000

Already? Didn't you just release v1.7.1?

Yes, and I'm pleased to announce 2 new additions to the tools!

Find Code In Project
This tool creates a report that works in a similar way to the built-in Find dialog in the Visual Basic Editor.

Details include,

Module Name

Procedure Name

Instances (count of code found)

Select Current Procedure
Do you hate scrolling?

Selecting all code in a module is easy, just use the Ctrl + A keys on your keyboard. A procedure - not so easy, especially if it's a loooong procedure.

Trust me, this tool helps. You and your hand(s?) can thank me later.

Download the new version here.

P.S. Tick the G+1 icon at the bottom of my post to tell me if you like what you see!

AET VBE Tools 1.7.1

noreply@blogger.com (Andrew) — Mon, 27 Mar 2017 14:59:00 +0000

It has been a busy couple of weeks, but I added some new features to my VBE tools.

Line Continuation
Here's a pic of what's available.

My version of line continuation adds spaces and underscores after And and Or keywords.

So something like
If Left$(LTrim$(.Lines(lCodeLine, 1)), 4) = "Sub " Or Left$(LTrim$(.Lines(lCodeLine, 1)), 12) = "Private Sub " Or Left$(LTrim$(.Lines(lCodeLine, 1)), 11) = "Public Sub " Or Left$(LTrim$(.Lines(lCodeLine, 1)), 9) = "Function " Or Left$(LTrim$(.Lines(lCodeLine, 1)), 17) = "Private Function " Or Left$(LTrim$(.Lines(lCodeLine, 1)), 16) = "Public Function " Or Left$(LTrim$(.Lines(lCodeLine, 1)), 12) = "Property " Or Left$(LTrim$(.Lines(lCodeLine, 1)), 12) = "Private Property " Or Left$(LTrim$(.Lines(lCodeLine, 1)), 12) = "Public Property " Then

becomes
If Left$(LTrim$(.Lines(lCodeLine, 1)), 4) = "Sub " Or _
Left$(LTrim$(.Lines(lCodeLine, 1)), 12) = "Private Sub " Or _
Left$(LTrim$(.Lines(lCodeLine, 1)), 11) = "Public Sub " Or _
Left$(LTrim$(.Lines(lCodeLine, 1)), 9) = "Function " Or _
Left$(LTrim$(.Lines(lCodeLine, 1)), 17) = "Private Function " Or _
Left$(LTrim$(.Lines(lCodeLine, 1)), 16) = "Public Function " Or _
Left$(LTrim$(.Lines(lCodeLine, 1)), 12) = "Property " Or _
Left$(LTrim$(.Lines(lCodeLine, 1)), 12) = "Private Property " Or _
Left$(LTrim$(.Lines(lCodeLine, 1)), 12) = "Public Property " Then

Much easier to read. But there are 2 things to watch out for.

1. If And or Or is between quotation marks, an error will occur. If this happens, click Undo last operation.

The best way to check for errors is to use Compile VBA Project at the top of the Debug menu after adding line continuation.

2. I haven't experienced problems myself, but I occasionally read about people whose code doesn't work properly if there is too much line continuation in a project. Something to keep this in mind anyway. Again, I recommend compilation of code and saving your work at regular intervals in case things go wrong.

New Cleanup Project Options
In addition to deleting excess blank lines (if you have several blank lines between code, only one will remain), you can now choose to delete all blank lines too. Not only that, you can delete comments and indenting.

Personally, I like to have blank lines between regular code and indenting for readability, as well as comments to keep track of what is going on. But when the code is ready for distribution, the 3 new options can drastically reduce the size of your project which is definitely a good thing.

Download the tools here.

P.S. Tick the G+1 icon at the bottom of my post to tell me if you like what you see!

Note: Thanks to everyone who participated in the Facebook sharing event. All good things come to an end, and the offer is now closed! (Don't forget to send your email address so I can send a copy of the tools. My email address is on the blog sidebar).

AET VBE Tools v1.7

noreply@blogger.com (Andrew) — Sun, 12 Mar 2017 04:09:00 +0000

Finally they're ready!

What's new?

1. Rename Userform Controls
This tool allows you to batch rename all controls on a userform via a simple Find and Replace interface. In addition to changing the control names, it also changes code with the old control name to the new control name.

So, if you want to change all controls that start with "CommandButton" to "btn" or "cb", etc, this is the way to do it all at the same time!

2. Make Project Variables List
The bigger the project, the more complex it becomes.

This tool analyses your code and makes a list of variables and constants.

Details include:

Module Name (Where the variable or constant is)

Scope (Public, Private, Type or Procedure)

Location (Declaration or procedure name)

Name (Variable or constant name)

Type (Variable or constant type, eg String, Long, etc)

Const (True or null, to discern between variables and constants)

Const Value (Value if constant)

Found in Project (Count within project)

Found in Report (Count within report)

Modules Count

Procedures Count

Unique Values

Duplicate Values

More features are planned in the near future.

3. Copy Code for Web
If you need to show your code on the web, normal indenting won't show. The way to get around it is to convert indent spaces to " ".

This tool does that and copies the code to the clipboard so you can paste it where required.

4. Delete Orphan Code
This feature was added at the request of Kellsens Willamos, who has supported me and the development of these tools from the beginning.

What's "orphan code"?

When developing, did you ever click userform controls by mistake? Maybe you get something like this.

Private Sub lblSelectProject_Click()

End Sub

Chances are you don't want the code, and if there's nothing between the first and last line, it's a fair bet that you don't. The Delete Orphan Code tool looks for code like this and gets rid of it.

I've added it as an option on Cleanup Project Code. To include it, tick the checkbox on the Settings form.

AET VBE Tools are no longer free. But I'm keeping the price low so they are affordable. (And you will get future versions free of charge!)

You can download them here.

P.S. Tick the G+1 icon at the bottom of my post to tell me if you like what you see!

AET VBE Tools v1.6.4 and Overall Progress

noreply@blogger.com (Andrew) — Sun, 05 Mar 2017 23:54:00 +0000

Latest patch - 2 bugs have been fixed, and one change has been made to certain wording.

Fixed an indentation bug when the first procedure of a module was at the very top (No Option Explicit and no Declarations).

Also fixed a bug with Macro Comment Tools when more than one tool was used with the form still open.

And I changed the wording of some messages to simplify translations for international versions!

2 translations are being prepared - Portuguese and Japanese - and I want your help to translate into other languages!

Please contact me the email address below.

Other Progress
It's been a super busy week getting the tools ready for translations, but there's more good news!

I've started work on a version that works with Microsoft Word and plan to make a version for other Office products.

Will keep you updated!

For now, download AET VBE Tools v1.6.4 here.

Ticking the G+1 icon at the bottom of my posts fuels my enthusiasm!

AET VBE Tools v1.6.3

noreply@blogger.com (Andrew) — Tue, 28 Feb 2017 08:46:00 +0000

This patch concerns another change to the Undo code, and is in case you unchecked "Trust access to the VBA project object model" after launching Excel. Perhaps unlikely, but I like to avoid potential bugs whenever possible.

v1.6.3 is available here.

Also, thanks to everybody who ticks the G+1 icon at the bottom of my posts. It gives me incentive to keep doing my best!

AET VBE Tools v1.6.2

noreply@blogger.com (Andrew) — Sun, 26 Feb 2017 11:00:00 +0000

Just a quick post to let you know the a new patch has been uploaded.

Indentation had a bug fixed when there were comments immediately after "Then" in If... Then code.

Cleanup Project Code had a minor tweak to alert you when no options were chosen.

I disabled Undo when a different tool is run.

Undo should be used (if you wish to) immediately after you use a tool. So having it still enabled after using another tool may have caused confusion.

Many thanks to those who have helped me improve these tools. Your support is appreciated!

I'm happy to say the next version is already finished with 3 new tools, I'm just waiting for the online store to get things set up. Hoping for early March at this stage.

Until then, v1.6.2 is available here.

Update: I just released patch v1.6.3 that has another improvement to the Undo code.

If you like my tools, please click the G+1 icon on this post and/or my blog's sidebar to let me know ;-)

AET VBE Tools v1.6.1

noreply@blogger.com (Andrew) — Sat, 18 Feb 2017 04:04:00 +0000

Yes, it's still freeware. Think of it as a patch.

I'm still committed to updating this version by fixing any bugs that I find, or tweaking this and that with improvements. It won't have as many features as upcoming versions, but I don't intend to abandon it either.

So, what's new?

An indenting bug was fixed for Select Case constructs when working with Projects and Modules.

I also improved the indenting of Add Line Numbers code.

When adding the date and time to text files being exported, the code has been adjusted to show the correct time.

I made a change to the Copy Code and Compare Code userforms. Sometimes duplicate file names were showing.

I edited Highlight Code In Excel so that individual Case statements are also highlighted with Select Case constructs.

Note: It is a big help to see them highlighted this way. Unfortunately I had to hard code that part, so it will only work if your tab settings are set to 4. (In the VBE, Tools, Options, Editor, Tab Width). If I find a way to determine this programatically, I will adjust the code to suit.

As a small bonus, I also added the functionality to delete Debug.Assert and Stop to the Cleanup Project code.

Download it here.

AET VBE Tools v1.6

noreply@blogger.com (Andrew) — Sat, 11 Feb 2017 13:09:00 +0000

This will be the final freeware version.

Let's see what we have so far. Access AET VBE Tools by right clicking within the active code pane.

Here are the tools.

Indent Code
Indent code within the active VB project, module, procedure or selected text.

Add Line Numbers
Add line numbers to code within the active VB project, module, procedure.

Delete Line Numbers
Delete line numbers from code within the active VB project, module, procedure.

Export Code
Export code from the active VB project, module, procedure to text files.

Compare Code
Select modules from projects.

Code from both modules will be exported to worksheets in a new workbook.

Code that exists in one module, but not the other (and vice versa) will be highlighted.

Copy Code
You can copy code/modules between projects.

Standard modules, class modules and userforms will be be replaced if they exist (have the same name), or added if they don't exist.

ThisWorkbook code will be replaced.

Sheet module's code will be replaced if the sheet exists, or worksheets will be added with the new code if they don't exist.

Macro Comment Tools
A handy way to add generic comments to all macros in the active project or module.

Insert Code Snippets
Tired of entering the same code all the time? This makes it easier.

Run Favourite Macros
Yes, you can already run them from your Personal workbook.

But now you can also export your favourite code to text files. Note: Not all code will run. This is a experimental tool, but I have found it quite useful when coding myself.

Multiple Find And Replace
You can find and replace code with several fields at once. Fields are saved between sessions.

Cleanup Project
Just 2 options at the moment. You can delete lines of code that have "Debug.Print", and also delete excess blank lines. (Only a single blank line will remain)

Highlight Code In Excel
Export your code to a worksheet in a new workbook. Selecting cells in Column A that have keywords like If, With, Select, etc will be highlighted so you can see where that part of the code begins and ends. This is very beneficial to your mental health when trying to figure out what connects with what in those long, long procedures.

Last, but not least, there are various settings available.

That's it for now. I've already started writing code for my upcoming shareware version, and one cool new tool is already added.

Please support me by purchasing a copy when it's ready.

For now, download AET VBE Tools v1.6 here.

Update: A patch has been released. See here for details.

PS: Stay updated by joining my mailing list. You can subscribe where indicated on the sidebar to the left.

Excel Dashboard Pro Course

noreply@blogger.com (Andrew) — Sat, 28 Jan 2017 13:00:00 +0000

Jordan Goldmeier is an expert at creating dashboards in Excel and is releasing a training course to help you master them too.

I've had a look at the course myself and have this to say.

I am really impressed with the level of detail. Jordan doesn't skip over stuff, yet doesn't drown you with too much information.

The course is easy to understand. Jordan explains how things work in everyday language.

There's a good balance of hands on learning and theory. Both will benefit you as you progress through the course.

I can guarantee you are getting your money's worth. There are 60 plus video lessons, plus matching files that you can use to practice.

It's not just the quantity. The course covers everything you need to know and more. There's even bonus materials at the end.

As you can see, I like the professionalism of the course and it's presentation. Jordan excels at both and I recommend this course as a comprehensive way to become a dashboard guru. Even if you already have some experience, I'm sure the course will be of benefit.

Disclaimer: As an affiliate, I make some commission in recommending this course on my blog. That said, I would never recommend anything that I thought wouldn't benefit you, my readers. After all, you are the reason I blog in the first place.

To access the course site, click the Power of Foresight banner above.

AET VBE Tools v1.5

noreply@blogger.com (Andrew) — Mon, 23 Jan 2017 12:49:00 +0000

So soon?

Yep, I made a couple of small improvements under the hood. Better now than later.

Download the new version here.

AET VBE Tools v1.4

noreply@blogger.com (Andrew) — Sun, 22 Jan 2017 07:40:00 +0000

Another week, another tool.

Copy Code
You can now "copy" code between projects.

By this I mean the following,

Standard modules, class modules and userforms will be be replaced if they exist (have the same name), or added if they don't exist.

ThisWorkbook code will be replaced.

Sheet module's code will be replaced if they exist, or worksheets will be added with the new code if they don't exist.

I may add some more functionality to this tool at a later date.

Line Numbers
I've rewritten the code so indenting is no longer influenced by line number length. This was a bug that really bugged me ;-)

Download the latest version of AET VBE Tools here.

AET VBE Tools v1.3

noreply@blogger.com (Andrew) — Sun, 15 Jan 2017 13:37:00 +0000

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.

AET VBE Tools v1.2

noreply@blogger.com (Andrew) — Thu, 12 Jan 2017 14:51:00 +0000

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.

AET VBE Tools v1.1 and Line Numbers

noreply@blogger.com (Andrew) — Sun, 08 Jan 2017 07:36:00 +0000

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!

AET VBE Tools

noreply@blogger.com (Andrew) — Sat, 24 Dec 2016 12:42:00 +0000

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

noreply@blogger.com (Andrew) — Sat, 10 Dec 2016 08:52:00 +0000

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

noreply@blogger.com (Andrew) — Thu, 07 Jul 2016 09:53:00 +0000

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

noreply@blogger.com (Andrew) — Sat, 28 May 2016 14:10:00 +0000

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

noreply@blogger.com (Andrew) — Sat, 12 Sep 2015 07:31:00 +0000

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.

AET Data Checker - New Features

noreply@blogger.com (Andrew) — Sat, 08 Aug 2015 05:47:00 +0000

I've added some new stuff to my AET Data Checker addin.

First, I added some labels so you can see what the Main Column and Look At Column are referring to (not just the Column letter)

The assumption is you will have your data set up with column headers in the first row.

Next up, you can now select the Main Criteria from a combo box (dropdown list). Or you can still type it in like before. Plus you can use wildcards like * and ?.

With all combo boxes, you can use the up and down arrow keys to rapidly change columns and criteria.

You'll also notice some new checkboxes.

What do they do? This!

When used, the highlighted labels make it easy to see what values matter. Use the square button at the top to turn all highlighting off or the checkboxes to toggle it individually.

Download the new version here.

See some examples of it's use here.

See you next time.