Renumbering Arrays in Code

I’ve got this bit of code where I’m listing table fields that I’m going to eventually Join into a SELECT statement.

As you can see, I needed to add a new field in position 1. Now I’m faced with renumbering the rest of the array. Terrible. So I wrote this:

Now I can copy the code, run this procedure, and paste the results.

Ahhh. Satisfying. Here’s how the stuff inside the loop works.

This splits the line into:

0 fields(17
1 = “BOLState”

This results in:

0 fields
1 17

Then I just concatenate the relevant parts back together with a different number.

Applications Settings v2

I made a new version of my Application Settings addin as per Sébastien’s comments in my last post.

Application Settings Version 2

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




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

As posted on my blog a few days earlier,

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. (Actually there is a way to make it more obvious. See the comment from Jake Collins)

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.





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.

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.

PS. I’m going on holidays for a few days so I’ll reply to comments (if there be any!) when I get back.

AET VBE Tools v1.7

They are finally ready!

This verson includes 3 additional tools, and another option to the existing Cleanup Project Code tool.

Here’s a description of 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.

    As mentioned previously, I’ve made AET VBE Tools shareware. But when you see the price, I hope you will agree they are well worth the cost. (And you will get future versions free of charge!)

    You can download them here.

    P.S. I’m looking for affiliates and translators. If interested, email me at aengwirda at gmail dot com.

  • AET VBE Tools v1.6.1

    Over the last week, I made some changes to my AET VBE Tools.

    Although still free, I want to ensure they are as good as I can make them for you, before I release a paid version.

    This is what has been done –

    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.

    Case Statements

    Note: It is a big help with regards to visibility 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. Alternatively, if you know a way, please leave a comment.

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

    Download the new version (now v1.7) here.

    AET VBE Tools

    Late last year, I started work on a new set of VBE tools. It’s an extension of a code indenter that I made several years ago. I thought it would be nice to update it.

    So far I’m up to Version 1.6. It’s freeware (sorry, now shareware), but I’m starting work on a shareware version (v1.7) that will have more stuff. Anyway, here’s what I blogged about it on my site. Give it a try if you like!

    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.

    Download AET VBE Tools v1.6 (v1.7) here.

    Good VBA Dates and Bad Excel Dates

    No, I’m not launching a match making service for Excel nerds. I’m talking about calendar dates. I was importing some data from a system that doesn’t seem to care what dates you might enter. Here’s the offender

    Instead of 2016, the user entered 1206. VBA doesn’t care.

    But Excel cares. As you know, Excel stores dates as the number of days since December 31, 1899. Anything before 1900-01-01 isn’t considered a date. The way this manifested was strange to me. I got the error (Application-defined or object-defined error) on this line

    When I filled the class, and specifically the TranDate property, no problem as VBA recognizes it as a date. When I fill the array vaWrite, no problem – the array contains text, numbers, and dates so it’s typed as a Variant. It’s only when I try to write it to a range that it complains. But why? I can type 12/13/1206 into a cell with no problem. It won’t recognize it as a date, but it doesn’t throw an error either.

    A user (who is not me) got the error and clicked End. They’ve learned that clicking Debug only makes things worse – not that there are ever bugs in my code. When he clicked End, the code filled up the range all the way up to the bad date, line 1103.

    That’s unexpected behavior. I would think the whole write operation would fail, but apparently not. The good news is that this partial writing of data led me to the root of the problem very quickly. The bad news is that the user was perfectly happy that clicking End produced data and he didn’t realize that the data wasn’t complete. He went on about his day until things just weren’t tying out properly. As much as I like the debugging help of a partial write, I think I would prefer if it didn’t write anything at all.

    In any case, it’s an interesting insight into writing an array to a range all at once. It’s long known that filling an array and plopping it into a range is way faster than writing out cell-by-cell. But whatever you do in VBA to avoid looping, you’re not really avoiding looping. All you’re doing is moving the loop to a faster place. For instance, when you Join an array, something in VBA is looping, just not you. In this case, VBA is looping through vaWrite and filling up cells and it’s doing it faster than you or I could. The interesting thing to me is that it’s really a screen refresh that’s hiding the loop (maybe). As opposed to, say, VBA building a bunch of XML in the background and replacing part of the sheet.

    But back to the error. Why an error anyway? As I said, I can type that non-date into Excel without error. I can even type that non-date in a cell and format the cell as a date without error. If I enter the formula =DATE(1206,12,13) in a cell, it returns 12/13/3106. Not a good result, but not an error either.

    I think it all comes down to the fact that VBA has cast this data as a date and Excel won’t accept a date outside of its range. At least it won’t accept it from VBA. If I look at the locals window, I can see that my Variant Array has cast that value as a Variant/Date.

    And this command in the Immediate Window fails with the same error as above

    but if I override the cast by casting it as a String, it works

    If you’ve followed me down this rabbit hole and are still reading, then may god have mercy on your soul. Here’s my fix

    Instead of assigning the recordset date field to the property (I commented out that line), I assign it to a date variable and test the year. If the year is less than 1900, then I change it to 1900. I considered changing it to the current year, but I think having a different incorrect year that doesn’t cause an error is better than trying to guess what it should have been.

    You might have noticed that I prefixed the Year function with VBA. I have a property in this class called Year, so when I try to use it, VBA thinks I’m referring the class property and not the VBA function. Prefixing the function call with the library name ensures that it uses the right one.

    I wrote that Nz function back in 2007 but it’s been updated since. So here’s the new and improved version.

    This is great example of a function that needs some comments.