markblandford.co.uk

Development Work

profile for creamyegg at Stack Overflow, Q&A for professional and enthusiast programmers

MSB Tools Excel 2007 / 2010 Add-In

MSBTools.xlam add-inMSBTools.xlam add-in
MSBTools

How to Install

  1. Download the MSBAddIn.zip zip file.
  2. 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 Application.UserLibraryPath() in Excel
  3. Open a new Excel session and in the Excel Add-Ins dialog, tick the 'MSB Tools' option and click OK. This will now install the add-in and a new Ribbon will be available in Excel.
    • 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...'
    Excel Add-Ins dialogExcel Add-Ins dialog
    Excel Add-Ins dialog with MSB Tools Installed

The Features

Useful Tools

Export Comments

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
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.

Dir()

The user is able to select a directory and the directory contents together with the file size, is listed on a new worksheet.

Face IDs

Menu listing all the possible Face IDs available for VBA context menu images.

Search Buttons

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.

Validate ISIN

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.

Validate Cusip

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.

Cusip Check-Digit

Returns the check-digit for a given, 8-digit Cusip.

Function Library

MSB Tools

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:

=CheckDate(<value>)

Tests to see if the given value is a valid date. Similar to the VBA IsDate() Function.

=CompareRanges(<Range1>, <Range2>, <RoundToDCP>)

Takes two ranges as inputs and returns a Formula Array highlighting any differences between the values in the ranges.

=CountDistinct(<reference>)

Returns a count of the unique values in a range.

=SheetName()

Simply returns the name / caption of the current worksheet.

Format Selection

Calculate Selection

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

Highlight Selection
Highlight Selection

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
Add Borders

Adds my preferred type of border to the current selection. Shortcut: Ctrl+Shift+B

Clear Borders

Clears any cell borders applied to the current selection.

Change Case

Toggles the case of the selection between UPPERCASE and lowercase.

Clear Hyperlinks

Removes all hyperlinks from the cells in the current selection.

Quote Column

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

Date Format
Date Format

Applies the DD-MMM-YYYY Number Format to the current selection. Shortcut: Ctrl+Shift+D

Date/Time Format

Applies the DD-MMM-YYYY HH:MM:SS Number Format to the current selection.

Remove Duplicates
Remove Duplicates

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.

Extract Duplicates

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.

Format Workbook

Apply Header & Footer
Add to Active Sheet

Applies my preferred custom Heading & Footer to the current worksheet.

Header / Footer Layout
Header/FooterLeft SectionCenter SectionRight 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.

Settings

About

Displays a message box with details of the version etc. of the MSB Tools add-in.

Reload

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.

Non-Ribbon Features

Bookmarks
Bookmarks Menu ItemBookmarks Menu Item
A new menu item on the right-click context menu of a cell

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.
  • CompareXLDirectories() / CompareXLSheets()
  • Extends the CompareRanges() function to compare entire workbooks / worksheets. Untested.
  • CopyFiles(<strSource>, <strDest>)
  • Copies all files from strSource to strDest.
  • 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 GetOpenFilename() function except this uses the Windows API which enables the setting of a default directory without changing the users settings.
  • InsertBlankAlternateColumns() / 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.

Shortcut Keys

Click here for a list of shortcuts keys used by the add-in (opens in a new window).

Useful Tools

A selection of tools which are either useful on their own or contain code which can be useful.

  • ADOTool.xls

  • Basic code for connecting to a database using ADO. The main code is for connecting to an Oracle database.
  • BBGDownload.xls

  • Uses the Bloomberg Framework to download data from Bloomberg.
  • CollateExcelWksData.xls

  • Tool to output all the data from various workbooks / worksheets into a single worksheet.
  • CommandBarsFaceIDs.xls

  • Returns all the FaceID images available for Excel buttons.
  • DAOAccessExporter.xls

  • A useful tool providing the ability to export all MS Access database objects to either a clean database (in case of corruption) or to export queries to text files.
  • DatabaseComparisonTool.xls

  • Tool for comparing the queries and tables of two different MS Access databases.
  • DownloadHistoricBBGPFs.xls

  • Download all the historic Bond Pool Factors for a selection of securities from Bloomberg. This code was written to save these to an Oracle database (which has been removed) so additional code is required to deal with the results.
  • ExportWksTool.xls

  • Exports worksheets from the given list of workbooks into a new workbook.
  • GetAccessDBObjectNames.xls

  • Lists all the objects (tables & queries) in an MS Access database.
  • MSAccess Metrics Tool.xls

  • Provides the abilty to performance test MS Access queries. This is also useful for running queries following a database Compact & Repair to rebuild the query tables.
  • MSBTools.xls

  • The original incarnation of the MSB Tools Excel Add-In but as an Excel workbook.
  • RangeComparisonTool.xls

  • Similar functionality to the =CompareRanges() function in MSB Tools Excel Add-In but as a seperate workbook (the original version).
  • SubQueryTree.xls

  • Draws a query dependancy tree for all the queries in an MS Access database.