MSB Tools Excel 2007 / 2010 Add-In
How to Install
- Download the MSBAddIn.zip zip file.
- Extract the MSBAddIn.xlam file from the zip file and save it to the local add-ins directory:
C:\Documents and Settings\<UserName>\Application Data\Microsoft\AddIns\
- This path can also be found using the
- To open the Add-Ins dialog, goto: File -> Options -> Add-Ins
- Where you see the 'Manage:' combo box, select 'Excel Add-ins' from the list and click 'Go...'
This tool will export all cell comments, from the active worksheet to a new worksheet. Details include, the cell address, the cell value and the text of the comment.
Export Code Base
This option provides the ability to export all the code, from any unlocked projects in the current session, to text files. This is particularily useful to easily check for code differences between different versions of the same project using an application such as WinDiff.
Worksheet Security Level
Simply provides a quick view as to what permissions have been set on the activeworksheet.
Crack Sheet Password
A basic algorithm which, more often than not, will unlock a protected worksheet. Unfortunately, it is meant to also return the password which eventually worked but this feature does not always work.
The user is able to select a directory and the directory contents together with the file size, is listed on a new worksheet.
Menu listing all the possible Face IDs available for VBA context menu images.
Searches all the shapes in all the worksheets of the active workbook for the value given in the OnAction property of the button and writes the worksheet name, shape name & procedure name to the Immediate window.
ISIN / Cusip Validator
Basic validator of an ISIN or a CUSIP.
Will perform basic validation on a given ISIN. Note, this does not guarentee that it is a valid ISIN, it simply checks that it conforms to the specification of an ISIN.
Performs basic validation on a given Cusip. Note, this does not guarentee that it is a valid Cusip, it simply checks that it conforms to the specification of an Cusip.
Returns the check-digit for a given, 8-digit Cusip.
A selection of UDFs for use within Excel. As well as being available from the MSB Tools Ribbon, they are also all listed in the 'MSB Tools' section of the Function Wizard.
Here are some details of some of the more useful ones:
Tests to see if the given value is a valid date. Similar to the VBA
=CompareRanges(<Range1>, <Range2>, <RoundToDCP>)
Takes two ranges as inputs and returns a Formula Array highlighting any differences between the values in the ranges.
Returns a count of the unique values in a range.
Simply returns the name / caption of the current worksheet.
Useful when Automatic Calculation is switched off. This procedure will calculate the current selection. Shortcut: Ctrl+Shift+C
Autosize Selected Columns
Will auto-fit the columns in the selection to fit the cell contents. Shortcut: Ctrl+Shift+A
Toggles the background colour of the selection between a colour (as specified by Highlighter Colour) of no colour. Shortcut: Ctrl+Shift+H
Highlight Odd Rows
Changes the background colour of the odd rows in the selection (as specified by Highlighter Colour).
Highlight Group of Rows
Similar to Highlighter Odd Rows except rows are highlighted by those that share the same value in a given column. Please note, unexpected results may occur if the range is not sorted by the 'Group By' column.
Grey All Zeros
Simply applies Conditional Formatting to the selected range so that all cell values =0 have the font colour changed to grey.
Enables the highlighter colour for the above procedures to be changed. The default highlight colour is yellow.
Add / Remove Borders
Adds my preferred type of border to the current selection. Shortcut: Ctrl+Shift+B
Clears any cell borders applied to the current selection.
Toggles the case of the selection between UPPERCASE and lowercase.
Removes all hyperlinks from the cells in the current selection.
Concatenates all the values in the first column of the selection, applies single-quotes around each value and copies the resulting string to the Clipboard. I use this for creating SQL
IN() values from a column of values. Shortcut: Ctrl+Shift+Q
Applies the DD-MMM-YYYY Number Format to the current selection. Shortcut: Ctrl+Shift+D
Applies the DD-MMM-YYYY HH:MM:SS Number Format to the current selection.
Similar to the built-in 'Remove Duplicates' feature. Deletes any duplicate rows from the current selection. However, this provides the functionality to just remove the individual, duplicate cells or the entire rows.
Performs the same functionality as Remove Duplicates with one enhancement. This will place all duplicate values, together with a count of how many there were of each duplicate, and place them on the Clipboard. That way it is possible to see what the duplicates were in the first place.
Apply Header & Footer
Add to Active Sheet
Applies my preferred custom Heading & Footer to the current worksheet.
|Header/Footer||Left Section||Center Section||Right Section|
|Header||<Author>||<Title>||Page &[Page] of &[Pages]|
|Footer||<Date> : <Time>||<FileName>||Sheet: <Sheet/Caption Name>|
Add to Active Workbook
Applies the same custom Header & Footer used for the Active Sheet but for each sheet in the current workbook.
Remove from Active Sheet
Removes any Header or Footer from the active worksheet.
Remove from Active Workbook
Removes any Header or Footer from each worksheet in the current workbook.
Remove Custom Styles
Deletes all Custom Styles in the active workbook. This is useful for avoiding the 'Too many different cell formats' error. This can be an issue when saving a workbook in an older, Excel format.
Reset All Last cells
This handy tool helps reset all the
UsedRange() of each worksheet in the current workbook. This is especially useful if you notice that even when you delete a load of data from a workbook (and save it) the filesize does not reduce. Using this tool can help reduce the filesize of your workbook.
Displays a message box with details of the version etc. of the MSB Tools add-in.
Invalidates / resets all the Ribbon controls on the MSB Tools add-in. This can be useful if you are experiencing issues with the Ribbon.
Enable Custom Settings
Enable / Disable my preferred, custom, Excel settings including:
- Calculation Options (Automatic, Manual etc.)
- Show / Hide workbooks in the Taskbar
- Autoformat Hyperlinks
When the 'Apply On Startup' option is selected and the custom settings are enabled, an entry is made in the registry so the settings are applied whenever Excel is started.
A new menu is added providing users with the ability to 'bookmark' cells which then through the use of the various options, jump to and from bookmarked cells.
Other Functions / Procedures
BubbleSort(<varToSort>, <blnAsc>)Basic Bubble Sort procedure to sort an array.
CopyFiles(<strSource>, <strDest>)Copies all files from
ExportToText(<strText>, <strDir>, <blnAppend>)Exports the provided text to the given directory and either appends to the existing file or overwrites it.
GetDayOfWeekDate(<enmDayOfWeek>, <dteDate>)Returns the date of the previous Day of The Week. I.e. the date of the Monday prior to Today.
GetFileLastModified(<strFileName>)The Last Modified Date/Time of the given file.
GetFilePath(<strFilter>, <strTitle>, <strDefaultDir>)Similar to the built in
InsertBlankAlternateRows()Insert blank, alternate columns /rows within the current selection.
- Menu Actions Not tested in Excel 2007/2010
LockToolbars()Locks all the Excel toolbars so they can not be dragged.
ResetContextMenu()Reloads the right-click Context menu in case there are issues with its display.
UnLockToolbars()Unlocks all the Excel toolbars so they can be dragged (as is their default behaviour).
RecordsetToArray(<rs>, <lngRows>, <lngColumns>, <blnIncludeHeadings>, <blnTrim>)Converts the given ADO Recordset into an Array. The result array can be limited to a given number of rows and columns and can also include the headings from the recordset.
SplitSQLScript(<strScriptPath>, <strSaveToDir>, <strDbName>)Use to split a SQL Server SQL Script containing multiple commands, into seperate .sql files for each DDL.
- VBE Functions
SearchTextinVBE(<strSearchString>)Returns TRUE if the given string is found in the code of the active workbook.
CommentVBELine()Provides an interface in which it is possible to (un)comment certain lines of code.
CompareRanges()function to compare entire workbooks / worksheets. Untested.
GetOpenFilename()function except this uses the Windows API which enables the setting of a default directory without changing the users settings.
Click here for a list of shortcuts keys used by the add-in (opens in a new window).