Tag: VBA

AET VBE Tools v1.7

Posted on by Andrew Engwirda

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

    Posted on by Andrew Engwirda

    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

    Posted on by Andrew Engwirda

    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

    Posted on by Dick Kusleika

    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.

    Excel corrupts certain workbooks in migrating from 2003 to 2007

    Posted on by Tushar Mehta

    I got a email from a client asking for help because Excel was “destroying,” to use his terminology, his 2003 workbook after conversion to the 2007 format. And, after analyzing the kind of change Excel made, I had to agree.

    The following in 2003

    badnames 1
    Figure 1

    becomes, in 2013 (and in 2010),

    badnames 5
    Figure 2

    The basic problem is that names that are legitimate names in Excel 2003 may become unacceptable in 2007 (or later). But, a more devastating problem is with a formula using a name with a dot in it. Even though it is completely legitimate, Excel changes the dot to a colon. This causes the formula =SW1.SW2 to become =SW1:SW2. Don’t ask me why. It just does. The result is the formula is all wrong and destroys the integrity of the workbook.

    It appears that the cause may be Excel trying to help manage the transition of a XLS workbook into the newer format. In 2007, Microsoft increased the number of columns from 256 to 16,384. Consequently, the reference to last column went from IV to XFD. So, a name such as SW1, completely OK in 2003, became unacceptable in 2007. On converting a XLS file to a XLSX file, Excel will convert such names by adding an underscore at the start of the name. But, it seems to go beyond that, converting formula references to certain names with dots in them to a colon. This happens if both the tokens to the left and to the right of the dot could be legitimate cell references. So, Excel converts the formula =XFD1.XFD2 to =XFD1:XFD2 but it will leave =XFD1.XFE2 alone.

    To replicate the problem:

    • Start with Excel 2003. Create a workbook and add the names shown in the Figure 1. Save and close the workbook.
    • Open the workbook in Excel 2013. Save it as a XLSX file. Acknowledge the warning message (see Figure 3),

      badnames 3
      Figure 3

    • Close and reopen the new XLSX workbook. The formulas will have the errors shown in Figure 2.

    The safest way to work around this problem is to add an underscore before every name in the workbook before making the transition to the 2007 format. Obviously, the quickest way to do this would be with a very simple VBA procedure. But, through trial and error I discovered the code will not work in 2003. It runs without any problems but it doesn’t do anything!

    So, the correct way to use the code is the following sequence.

    • Open the XLS file in 2013 (or 2010).
    • Run the macro below.
    • Now, save the file in the newer format. If your original workbook had no code in it, save the file as a XLSX file and acknowledge the warning that the VB project will be lost.
    • Close and reopen the file. You should see the correct data with all the names now starting with an underscore.

      badnames 7
      Figure 4

    Tushar Mehta

    Opening a PDF from VBA

    Posted on by Dick Kusleika

    Several years ago I needed to open the newest CSV file from a particular directory. Now I’m faced with a similar problem. My accounting system produces PDFs in some kind of proprietary PDF reader. It doesn’t have near the feature set of Foxit, my preferred reader. Instead of fighting it, now I immediately save the pdf and open it in Foxit.

    Drawing from that previous post, I made a function to find the name of the most recently created PDF.

    Once I have the name, a simple FollowHyperlink method will get me where I want to go. Oh, except that hyperlinks are bad and Excel needs to show me a warning. That’s not going to work. Instead, I take the long way around. I create a batch file to open the PDF and run that.

    The file opens and there’s no warning to click through. Coincidentally, JW has been working around that same security measure, only for MP3 files. There’s an interesting approach.

    It works and no message. Good one John.

    Finally, I wanted a third method. fzz commented that I should use a console command because that’s what consoles are good at. I made a batch file following his example:

    for /F %%a in ('dir /b/o-d "K:*.pdf"') do (start %%a & exit)

    No warnings, obviously, and even though the VBA above is lightning quick, I think we can all appreciate that this is the quickest and most direct way. I’m having a problem running from VBA though.

    It got the file name right, but says it can’t find it. As you can see, I tried ShellExecute too. Same result. It doesn’t matter. I have a batch file, so I don’t need Excel. I put a shortcut to the batch file on my desktop and set the shortcut key. Now I can open it regardless of which applications are open or have the focus.

    The Range.Find method and a FindAll function

    Posted on by Tushar Mehta

    Two things that could be better about the Range.Find method have been 1) up-to-date and correct documentation, and 2) adding the UI’s ‘Find All’ capability to the Excel Object Model. As of Office 2013 neither has happened.

    Consequently, every time I want to use the Find method, I continue to have to jump through hoops to figure out the correct values for the different arguments.

    I also discovered that FindNext does not work as expected when one wants to search for cells that meet certain format criteria. Consequently, I updated my long available FindAll function so that it works correctly with format criteria.

    For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/1001%20range.find%20and%20findall.shtml

    Tushar Mehta