Changes in version 4.5.5 - November 17, 2009

Finished

On November 17, 2009 the new version 4.5.5 is released. Download

New/improved

• NEW: Spanish language added
  All the tools are also available in the Spanish language (EspaÃħol) now.
  If you have the Spanish language of Excel, ASAP Utilities will now start in Spanish. You can change the language in the options dialogue: ASAP Utilities » ASAP Utilities Options » ASAP Utilities options and settings...
  Note: Only ASAP Utilities itself it translated at this moment. The setup is still in English and so is our website.

• General
  All the items in the menus and on the forms now have mnemonics (also refered to as accelerator keys, access keys, hotkeys or sometimes keyboard shortcuts). A lot of the tools already had these, but we have now added it to all of the tools, in all available languages.
  Accelerator keys are key combinations consisting of the letter key pressed along with  the Alt key. This makes it easier to use ASAP Utilities without a mouse. It can also increase the speed of your work and reduce the chances on RSI. Note: you can (and could) also navigate through the options with tab and shift+tab keys and you can always close a form with the ESC key.
  
  The following screenshot shows an example of which keys you can press in combination with the Alt key to activate certain controls:
  
• Information » Fast calculation on selected cells...
  Expanded. This utility showed the number of unique values, which were the values that appeared only once in your selection. We have now updated the utility to show both the unique (distinct) values, and the values that appear only once in the list. For example, with the values 1/1/2/1/3 the unique values are now 3 (1, 2 and 3) and the values that appear only once in the list is 2 (2 and 3).
  
• Sheets » Insert multiple sheets (uses the cell values as sheet names)...
  Improved speed
• Sheets » Create an index page with links to all sheets (clickable)...
  Improved speed. Furthermore you can now use all sort options when you have selected both worksheets and chart sheets.
• Columns Rows » Insert multiple empty rows at once...
  Instead of inserting the rows below your selected range the utility now inserts the rows above your selected range. We have changed this to reflect the default behavior of Excel when you choose in the menu Insert > Row.
• Columns Rows » Insert between empty rows or columns...
  has been renamed to:
  Columns Rows » Insert empty rows or columns in between...
• Four worksheet functions are updated for Excel 2007 and later.
  Before Excel 2007 a workbook could only contain 56 colors. We used the colorindex property of a cell or font for certain functions. Until Excel 2007 this worked perfect.
  However, in Excel 2007 and later this isn't always reliable anymore because the colorindex number can be the same for different colors. Therefore we have now updated the functions that are listed below. If you specify a range for the color instead of a colorindex number then the function work as supposed in Excel 2007 and later.
  =ASAPCELLCOLORINDEX()
  =ASAPCOUNTBYCELLCOLOR()
  =ASAPSUMBYCELLCOLOR()
  =ASAPSUMBYFONTCOLOR()
  For example to sum the cells in range A1:D100 that have the same color as cell A1, you can use the following formula: =ASAPSUMBYCELLCOLOR(A1:D100, A1). The formula will then return the sum of all cells in A1:D100 that have the same fill color as cell A1.
• Text » Replace accented characters (á, ë, Ãħ etc.)...
  The following characters are added: ấấặắḑėềẽǧįĺỏțųŭўỹ
  If you have used this utility before you have to press the 'reset' button to have these new characters added.
• Fill » Insert before current value...
  The last 20 values you use to insert before or add after will now be remembered. You can either type in a new value or select a previously used value from the list.
• Fill » Insert after current value...
  The last 20 values you use to insert before or add after will now be remembered. You can either type in a new value or select a previously used value from the list.
• System » Resize the Excel window to standard screen resolutions...
  The screen resolutions 1366 x 768 pixels and 1280x720 were added. The 1366 x 768 pixels display resolution is for example used on certain Sony netbooks.  Now you can quickly change Excel to this resolution so that you know how your spreadsheet model will look on such a computer. The resolution 1024 x 600 pixels which is used by most netbooks, such as the ASUS EEE, was already included.
• NEW: ASAP Utilities Options » New tools in development (experimental, English only)
  New tools that are currently in development. These tools are available in the English language only during this experimental phase.
  At this moment these tools are available:
  . Save as... and use the first cell as the suggested filename
  . Report pivot table(s) information
  . Refresh all external data ranges and PivotTable reports
  . Put all data from the worksheet into one single column
  . Change julian dates to normal dates in selected cells
  
  

Bug fixes

• Columns Rows » Remove unused empty ending rows/columns
  This could produce an error on worksheets with a pivot table and stop this tool from actually working. That is now fixed.
• Sheets » Create an index page with links to all sheets (clickable)...
  Fixed a few small bugs. For example when you had the option to include chart sheets selected in Excel 2007 you could sometimes get the error 'Microsoft Excel cannot paste the data.'. It wasn't a consequent error that happened all the time, but just sometimes it popped up. We have now created a workaround for this and haven't seen the error since.
• Columns Rows » Insert empty rows or columns in between...
  After the utility completed an incorrect range of cells was selected. This is now corrected.
• Columns Rows » Remove unused empty ending rows/columns
  This utility removed the ending rows and columns that contain no data. However if you had for example a chart there and its properties were set to 'Move and size with cells', the chart was resized and could become invisible (a width of zero and/or height of zero). This is now fixed by using another method.
• Text » Change to UPPERcase
  Fixed the bug that caused certain Lithuanian characters (ąčęėįųū) to their diacritic marks.
  (A little background information on this: The VBA function StrConv() can be used to change text to uppercase, lowercase or proper case, however, that function caused these specific characters to lose their diacritic marks, for example č becomes c. We now use another method that is also reliable in with these characters).
• Text » Change to lowercase
  Fixed the bug that caused certain Lithuanian characters (ąčęėįųū) to their diacritic marks.
• Text » Start first word with Uppercase
  Fixed the bug that caused certain Lithuanian characters (ąčęėįųū) to their diacritic marks.
• Make first character Uppercase, the rest lowercase
  Fixed the bug that caused certain Lithuanian characters (ąčęėįųū) to their diacritic marks.
• Start Each Word With Uppercase
  Fixed the bug that caused certain Lithuanian characters (ąčęėįųū) to their diacritic marks.
• Range » Clear duplicated values in selection (leave one original)
  The utility skipped cells that were formatted as dates. This is a bug which was introduced in version 4.2.2 (January 22, 2008) but not noticed until someone reported it recently. It is now fixed. This also affected the following tools (and is fixed there too):
  - Range » Count and/or color duplicates in selection...
  - Select » Select cells based on content, formatting and more... > duplicate values/unique values
  - Select » Conditional row and column select, hide or delete... > duplicate values/unique values
  - Information » Fast calculation on selected cells... > duplicates
• Sheets » Insert multiple sheets (uses the cell values as sheet names)...
  When you used a template sheet that contained cells with more than 255 characters, the excess characters were stripped off on the newly created sheet. That also happens when you manually copy a sheet with more than 255 characters and Excel will then warn you about this. We have now updated the utility so that it also is an exact copy when there are cells with values of more than 255 characters.
• Text » Delete number of ending characters...
  When you had a text string and the result was a number, then the leading zeros were removed. For example a value such as 00032-2, from which your removed the last 2 characters, resulted in 32 without the leading zeros. Now it returns 00032 in such a case.
• Text » Delete number of leading characters...
  When you had a text string and the result was a number, then the leading zeros were removed. For example a value such as 12-0064, from which your removed the first 3 characters, resulted in 64 without the leading zeros. Now it returns 0064 in such a case.
• Text » Delete all after a given number of characters...
  When you had a text string and the result was a number, then the leading zeros were removed. For example a value such as 00032-2, from which your removed all after the first 5 characters, resulted in 32, without the leading zeros. Now it returns 00032 in such a case.
• Text » Delete leading and trailing spaces
  Updated to handle numbers with more than 15 digits (such as creditcard numbers) with leading/trailing spaces better. (We have create a workaround to prevent Excel from stripping off digits when a number with more than 15 digits is written back into the cell.)
• Text » Delete leading, trailing and excessive spaces
  Updated to handle numbers with more than 15 digits (such as creditcard numbers) with leading/trailing spaces better. (We have create a workaround to prevent Excel from stripping off digits when a number with more than 15 digits is written back into the cell.)
• Numbers » Change not recognized numbers (text?) to numbers
  Updated to handle numbers with more than 15 digits (such as creditcard numbers) with leading/trailing spaces better. (We have create a workaround to prevent Excel from stripping off digits when a number with more than 15 digits is written back into the cell.)
• Columns & Rows » Put together rows or columns from several sheets...
  Fixed the bug where in some cases when you chose the option "Copy the values", the result showed the error #REF! instead of the values from the sheets.
• Web » Extract hyperlinks...
  Fixed the bug where in certain situations it didn't produce any results. This could be the case if there were cells in your worksheet with very long text values.
• A few small bugfixes and improvements.