Compare versions

Version 4.6.4 compared to version 4.5.8

New tools and improvements:

• Find and run a utility...
  You can quickly add the tool from within the result window to your Favorites-menu by pressing the + button:
  
• NEW: Sheets » Split the selected range into multiple worksheets...
  With this tool you can quickly split your data into multiple sheets.
  You can do this either by value, for example to create a new sheet for each company, or you can specify the number of rows to create per sheet.
  We have also received the request for this tool from people that used workbooks with over 500 000 rows (Excel 2007/2010) and then needed to open the information in Excel 2003. In order to do that they had to split the information into pieces of a max of 65536 rows because that is the maximum amount of rows that a sheet in Excel 2003 can contain. Now, with this tool you can do that much faster.
  Note:
  Your original worksheet remains unaltered.
  If you want to save the new created worksheets as separate files, then you can do this with the utility "Export » Export worksheets as separate files...
  
  Example of the "For every new value/group in column" option. A new sheet is when the brand name in column A changes and that data is copied to the new worksheet:
• NEW: Formulas » Replace the range names used in formulas with their cell reference (on selected sheets)
  This utility replaces the range names used in the formulas on the selected sheets with their cell references.
  For example if you have a value in cell C10 with the range name 'quantity' and you used a formula such as '=A1*quantity', then this utility will replace that formula with '=A1*C10'.
  
  Select the sheets and start the utility:
  
  
  The range names have been replaced with their cell references.
  As you can see, if needed the utility adds the sheetname for the cell reference too if needed:
  
  
  At the end a report with the changes that were made is presented:
  
• NEW: Information »  Copy the path and filename of current workbook to clipboard
  This will put the path and filename of your current workbook to the clipboard.
  You can then paste it into another application.
  This can for example be useful  if you're working on a file and want to inform a collegue and tell him/her in an email where he/she can find the file.
• Web » Clean information that is copied from websites...
  Now has buttons to quickly check, uncheck or inverse all options.
  This utility is handy to quickly clean up data in general.
  
• Subscript the numbers in chemical equations
  via: ASAP Utilities Options » New tools in development (English only)
  This utility will subscript the numbers in chemical notations such as Al(OH)3, AlF6Li3, AlO2, Al(NO3)3, Au2(SeO4)3, Ca(VO4)2, H6TeO6, KAlMg3Si3O12H2, Ca(NO3)2 Â· 4H2O, etc.
  It automatically detects which numbers should be subscripted.
  
  
• NEW: Report the formulas used in the worksheets in your workbook
  via: ASAP Utilities Options » New tools in development (English only)
  This utility creates a report that lists all the formulas that are used in the worksheets in your workbook.
  
  
  
  You can click on the cell in the report to jump to the cell in the reported workbook:
  
• General, Startup-questions
  We removed the startup-questions (such as "Do you want to convert all text in the selected cells to UPPERCASE?") from several tools.
  These tools either don't change your data or settings, or can be easily undone or canceled nowadays.
  This applies to the following tools:
    . Apply standard format to selected cells
    . Center across selection (without merging)
    . Change not recognized numbers (text?) to numbers
    . Change to lowercase
    . Change to UPPERcase
    . Convert percentages to numbers (e.g. 5% to 5)
    . Copy values to empty cells below filled cells in selection
    . Count and/or color duplicates in selection...
    . Decode all URL and HTML encoded text
    . Delete all borders inside selection
    . Delete leading and trailing spaces
    . Delete leading, trailing and excessive spaces
    . Fill 'between values' linear in selection (horizontal)
    . Fill 'between values' linear in selection (vertical)
    . Flip selected cells
    . Make first character Uppercase, the rest lowercase
    . Make last character subscript
    . Make last character superscript
    . Move minus sign from back to front (e.g. 65- to -65)
    . Place a border around each page
    . Print preview selection
    . Random numbering of cells (without duplicates)
    . Remove all conditional formatting in selection
    . Reverse text in selected cells
    . Select cell in selection where value changes
    . Start Each Word With Uppercase
    . Start first word with Uppercase
    . Unmerge cells in selection
  For your information, you could already turn off all the startup questions via: ASAP Utilities Options » Settings, language and contact information...
  and then the checkbox "Ask for confirmation before starting certain utilities (recommended)" on the "General" tab.
• A few small improvements.
• Improved startup error message
  ASAP Utilities does not (yet) support the 64-bit edition of Excel 2010.
  Besides a warning about this during the installation, the program will now also tell you this when you start ASAP Utilities in Excel 2010 64-bit (instead of giving an error message).
  Just to be clear, ASAP Utilities does work with both 32-bit and 64-bit Windows.
  
  At this moment we don't have a short-term planning for a 64-bit version of ASAP Utilities. This is due to the fact that we would most likely have to completely rewrite the program in order to keep it secure (from Visual Basic to MS Visual Studio), which costs a lot of time and money and such a solution wouldn't work with Excel 2000, 2002/XP and 2003 anymore. This requires us to maintain and publish two versions of ASAP Utilities.
  Furthermore, there seem to be only very few people that require the 64-bit edition of Excel 2010 instead of the 32-bit edition of Excel 2010. Most people might think that the 64-bit version is faster, but it isn't. The main advantage is that you can use Excel workbooks that are over 2GB (2 gigabyte!) in the 64-bit edition.
• Functions
  In Excel 2003, 2007 and 2010 the ASAP Utilities functions are now listed in Excel's "Insert Function" Wizard in their own category, called "ASAP Utilities". Furthermore, in Excel 2010 each of the function-arguments has a description in Excel's functions-wizard too.
  
  Excel 2003:
  
  
  Excel 2010:
  
  
  
• Web » Clean information that is copied from websites...
  The AutoFit column width and row height now have an option to specify a maximum width or height. Especially with columns this is very useful because columns that are too wide are hard to read or mess-up the layout.
  
• Export » Put selected range as bitmap-image on the clipboard (as on screen)
  This utility can now also be used to copy charts or other objects to the clipboard.
  Therefore the utility has been renamed to:
  Export » Put the selection as bitmap-image on the clipboard (as on screen)
• Export » Put selected range as image on the clipboard (as on screen)
  This utility can now also be used to copy charts or other objects to the clipboard.
  Therefore the utility has been renamed to:
  Export » Put the selection as image on the clipboard (as on screen)
• Export » Put selected range as image on the clipboard (as on printer)
  This utility can now also be used to copy charts or other objects to the clipboard.
  Therefore the utility has been renamed to:
  Export » Put the selection as image on the clipboard (as on printer)
• General
  A few small (speed) improvements.
• NEW: Columns & Rows » Drawing paper
  This tool allows you to specify the column width and row height of your cells in centimeters, inches or pixels.
  This is helpful if you use the Excel for drawing purposes, such as creating a flowchart or when you design your garden or need to create a floor plan.
  It can also be handy for quickly creating square cells.
  
• NEW: System » Save file as... and use the first cell as the suggested filename
  This tool will launch Excel's "save as?" dialog and uses the value of the first filled cell on your worksheet as the suggested filename.
  
• General
  The setup is now translated into Dutch, German, Spanish and French too.
  
• Objects & Comments » Comment tools...
  Your last used settings will now be remembered.
  If you choose to autofit/resize all comments, the utility used to use a maximum width of 300 so that you could read the contents without horizontal scrolling. You can now specify your own maximum width. This can be useful if you have a wider screen and when the comment contains many line-breaks.
  
• ASAP Utilities Options » New tools in development (experimental, English only)
  has been renamed to:
  ASAP Utilities Options » New tools in development (English only)
  The design is improved and it now also remembers the last started utility.
  
  
• NEW: Show indented structure of formula in current cell
  via ASAP Utilities Options » New tools in development (experimental, English only)
  This is useful when you have a nested formula. It shows the indented structure of your formula. This way you can more easily see if the brackets are placed properly. You can also edit the formula.
  
  
• NEW: Copy the path and filename of current workbook to clipboard
  via ASAP Utilities Options » New tools in development (experimental, English only)
  This will put the path and filename of your current workbook to the clipboard. You can then paste it into another application. This can for example be useful if you're working on a file and want to inform a collegue and tell him/her in an email where he/she can find the file.
  
• NEW: Show or hide formatting marks in selected cells
  via ASAP Utilities Options » New tools in development (experimental, English only)
  With this tool you can make spaces, tabs and linebreaks visible. This can be useful because sometimes characters such as these mess up your lookup formulas.
  The following characters are used to show these characters:
  ? = space
  ¬ = tab         
  ¶ = line breaks
  This tool changes your data when you choose "Show" but you can quickly switch back to the original when you choose "Hide". Basically the method is quite simple. It replaces spaces, tabs and line-breaks with a unique character. Then the "Hide" option changes these special characters back to spaces, tabs or linebreaks again.
  Unfortunately it is not possible to make it work without changing the values like  the hide/show formatting marks in MS Word. However we think that this tool is a useful alternative.
  
• Columns & Rows » Remove all empty rows
  Improved speed. Furthermore, if you have hidden rows at the end of your data, they now remain hidden.
• Columns & Rows » Remove all empty columns
  Improved speed. Furthermore, if you have hidden rows at the end of your data, they now remain hidden.
• Fill » Create a list of filenames and properties in a folder...
  The amount of files listed is now a worksheet formula. This has the benefit that when you use the autofilter to narrow down the list it amount of files listed is updated and it shows only the number of files displayed by the filter.
• Select » Select cells based on content, formatting and more...
  You can now also select cells based on their date:
  . between
  . not between
  . equal to
  . not equal to
  . greater than
  . less than
  . greater than or equal to
  . less than or equal to
  . Weekday (for example select all Mondays)
  . Month (for example select all dates in December)
  . Year
  . Quarter (1st, 2nd, 3rd or 4th quarter)
  
• Text » Convert/recognize dates...
  The utility can now also convert dates where the month is written as text.
  This means that if someone has entered for example "January 3, 2008" in a cell that this tool can convert it to a proper Excel date. You can then use it for example in your calculations or have the dates sorted properly.
  The utility recognizes the months written out in the language you have specified for the dates in Windows (Regional Settings), and it regognizes the languages English, Dutch, German, Spanish and French.
  
• Select » Select cells based on content, formatting and more...
  We've added the option to choose between the cell's value, formula or displayed value (formatted text). This way you can for example select all cells with a formula that references to a certain file or sheet, uses a certain range name, etc., etc..
  
• Export » Export worksheets as separate files...
  Improved speed for some files when they are exported in a separated text format.

Bugs that are fixed:

• System » Reload all installed add-ins (rebuilds the menu)
  Didn't reload the shortcuts set for ASAP Utilities and gave an error. This bug was unfortunately introduced in 4.6.3, but it is now fixed.
• Columns & Rows » Conditional row and column select, hide or delete...
  Produced an error if there was a cell with an error in your selection and you had chosen the search option "like" and the "case sensitive" option unchecked.
  This is now fixed.
• Formulas » Copy formulas without changing their cell references...
  Fixed the bug in the undo.
• Objects & Comments » Comment tools...
  This tool could sometimes cause an error if a sheet contained a large amount of comments. This is now fixed.
• A few small bug fixes.
• Export » Export worksheets as separate files...
  Fixed the bug that caused export files, that were in text-format, to be empty.
  This bug was unfortunately introduced with version 4.6.1, our apologies for this.
• Excel 2010 (32 bit edition)
  Fixed the ASAP Utilities startup error that sometimes occurred.
  We created a workaround for a bug in Excel 2010 which prevented ASAP Utilities from starting correct. Despite all the suggestions in some cases if you were administrator and installed ASAP Utilities, the program kept being unable to connect to the ASAP Utilities library DLL. Somehow in Excel 2010 this DLL is registered not only in the LOCAL MACHINE part of the registry, as we have  specified it to do, but it also included ASAP Utilities in the LOCAL USER part of the registry, which it shouldn't do and which causes ASAP Utilities to sometimes not work correct and give the DLL startup error.
• Select » Select cells based on content, formatting and more...
  The option "Number format contains" didn't work (anymore).
  This is now fixed and it works again.
  You can use it to select cells based on a (part of a) certain number format string.
• Columns & Rows » Remove unused empty ending rows/columns
  Could sometimes produce an error if your sheet contained a table (Excel 2007/2010: Insert > Table). This is now fixed.
• Export » Export worksheets as separate files...
  Produced an error if you had a worksheet which as protected and you were using Excel 2000/2002/XP or 2003. This is now fixed.
  In version 4.5.9 we updated this utility because cells with more than 255 characters were stripped to the first 255 characters in Excel 2000/2002/XP/2003. However, this gave an error on a protected worksheet.
• Formulas » Copy formulas without changing their cell references...
  Now works without errors if the target is in another workbook.
• Fill » Insert before current value...
  Fill » Insert after current value...
  Fixed the bug that prevented you from using the same text in another case, such as first in lower case, and then later in upper case.
  For example if you added the text 'text', this setting was remembered. However if you later wanted to add the letter 'TEXT' in uppercase, it didn't work because the autocomplete detected that you already added this text before and due to the fact that this comparison is not case sensitive, you couldn't add the same text in other case. This is now fixed by turning autocomplete off, since we can't make autocomplete case-sensitive. You can still quickly choose a previously entered value by using the arrow keys or dropdown with the mouse.
• Columns & Rows » Autofit row height (also on merged cells)
  The utility didn't work when the combined column width of the merged cells was more than 255. We have created a workaround for this so that it doesn't give the error anymore. However in such as case with long text the row height might not be adjusted correct with long cell values. At this moment we don't have a better solution (for what seems to be a very rare situation anyway).
• General
  We changed a small thing in the startup of ASAP Utilities which could lead to a problem (an extra empty workbook) when opening a workbook in/via a program called ProSystem fx Engagement.
• System » Find and remove external links...
  Bug fixed. If a formula with a link to another file was on a protected sheet, this utility produced an error and the calculation mode for that sheet was left in manual calculation mode. This is now fixed and the utility will inform you nicely if a link exists (in a formula) on a protected worksheet.
• Format » The paper saver (split columns)...
  If you used this utility on a workbook with one or more pivot tables and were using Excel 2007 or 2010, then Excel could crash. We have no explanation for this, theoretically that is impossible. However, we now created an alternative code to set the page breaks for these Excel versions and that has solved the problem.
  Excel 2000, 2002/XP and 2003 didn't have this problem.
• Export » Export selection or active sheet as new file...
  Improved exporting the formulas in text format. The utility now also uses the A1 or R1C1 referencing based on your current settings in Excel.
• A few small bugfixes/improvements.
• Select » Select cells based on content, formatting and more...
  Fixed the bug during startup that appeared on computers that had the regional date settings set to use leading zeros for months:
  Sorry, something unexpected went wrong at code line 1520:
  Error 438: Object doesn't support this property or method
  Despite the error on startup, the utily kept functioning correct.
• Export  » Export selection or active sheet as new file...
  If you had the option "Do not export hidden cells" unchecked, then the tool gave an error and didn't do the export. This is now fixed.
  Sorry, something unexpected went wrong at code line 250:
  It isn't sure if the export succeeded.
  Error 91: Object variable or With block variable not set
• ASAP Utilities Options » New tools in development (English only) » Report pivot table(s) information
  Fixed the bugs that caused an error when you created a report for pivot tables  that had an external datasource. We also added a few more details to the report.
• ASAP Utilities Options » New tools in development (English only) » Split data table into multiple worksheets
  The rowheight and column width is now properly preserved for cells with long contents too.
• The following tools were updated to better distinguish the large amount of colors that are available starting from Excel version 2007.
  . Format » Copy and apply cell's formatting settings...
  . Sheets » Insert multiple sheets (uses the cell values as sheet names)...
  . Range » Advanced sorting... (left to right)
• In a non-English version of the pre-release of Excel 2010, the browse-for-folder technique in ASAP Utilities produced an error. This is now fixed.
• Undo
  Fixed the bug that caused that the undo for the following tools sometimes didn't work. This could happen for example if one of the following tools was the first and only tool used from ASAP Utilities and then the undo was performed:
  . Text » Delete all text characters (a-z) in selected cells
  . Import » Insert (delimited) text file...
  . Import » Insert text file separated by space...
  . Import » Insert text file separated by tab...
  . Import » Insert text file separated by comma (csv file)...
  . Import » Insert text file separated by "|" (pipe/bar)...
  . Import » Insert text file separated by "!" (exclamation mark)...
  . Import » Insert text file (with options)...
• Fill » Create a list of filenames and properties in a folder...
  Fixed the bug where ASAP Utilities thought incorrect there weren't enough rows available (-2) to report the files.
• Columns & Rows » Autofit row height (also on merged cells)
  If you added this tool to your favorites&shortcuts menu and assigned a shortcut to it, then that shortcut didn't work. When you clicked on it in the menu it worked correct but when you used the shortcut for this to you got an error "the macro cannot be found". This is now fixed.
• Export » Export worksheets as separate files...
  In Excel 2000, 2002/XP and 2003: If you choose to export the sheets as Excel files, cells with more than 255 characters were truncated to 255 characters. This is now fixed. This problem did not occur in Excel 2007 and 2010.
• Export » Export worksheets as separate files...
  In Excel 2000, 2002/XP or 2003: If you choose to export the sheets as Excel files, cells with more than 255 characters were truncated to the first 255 characters.
  This is now fixed. This problem did not occur in Excel 2007 and 2010.

• A few small bugfixes/improvements.