Partnering Excel with VBA (Visual Basic for Applications) macros enables a powerful tool for small applications. In particular, the rapid application development platform enabled in this way is particularly powerful given the freedom for entering and manipulating data and formulae in Excel, coupled with a programming language behind it which enables professional forms, and functional controls to be easily created.
Setting up a VBA macro in Excel which performs function useful to more spreadsheets than the one currently open (e.g. swapping two sets of cells) is made all the more useful through Add-Ins - macros which sit behind Excel and are available to any open workbook.
Some examples of add-ins created by add-innovation are below. Most of these will always remain free, while a few more are free for the next few months so install them while you can. Many of the extra functions provided are very simple in their operation - one (convert formula entries to values) is simply a matter of copying the selected cells, and using the paste-special-values, operation to copy the cells over themselves. So this could be achieved using Excel anyway, but the add-in enables this with a single click.
 Toggle
between Numbers and Text
: Does what it says ! Numbers are converted to
text, text to numbers ! |
Download |
|
|
Download |
 Formulas to Values : As
in Copy, Paste Special, Values over the top of themselves in a single click. |
Download |
|
|
Download |
|
|
Download |
|
|
Download |
 Printer
Splitter Macro
; Assuming you've got sheets on your spreadsheet that lay out
information copied from a master sheet, in an easy to read form (e.g. via
Vlookups, and you allow your user to change a single value which updates all
information on the form), this macro enables you to cycle through all possible
values of that lookup cell, and save or print selected ranges of cells.
|
Download the Printer Splitter Macro
|
|
Many of us have found VLOOKUPs to be
extremely empowering tools in Excel, not only meaning we don't need to repeat
information in our spreadsheets, but more importantly meaning that we only
need to maintain that data once.
Many of us have also banged our heads on our desks, walls, keyboards, and torn our hair out when trying to rationalise a VLOOKUP which doesn't find data that we can physically SEE is there ! The answer as to why a VLOOKUP doesn't work, with experience, becomes commonplace, but still tedious to find. There are a number of ways in which we can either change our data so that it can be found, or change the vlookup to add extra formula items which fix the formula.
|
Download |
|
The VLOOKUP Assistant Test Page
|
|
 What shape is my data ?
As the Excel status bar can show you the SUM, AVG, MIN, etc... of your
selected data, this add-in shows you interactively many more
statistics, a distribution plot, and a box and whisker plot of your
data so that you can visualise how your data is distributed in one
single click. The results can be captured to your spreadsheet. |
download |
 Swap Cells. As the description suggests, swaps two selected ranges of text of the same shape and size. |
download |
 Reset Excel's Delimiters.
A rarely used, by sanity saving add-in. If you ever open .csv files or
use text to columns, then subsequently paste data with embedded commas
into a cell, you'll find the data split across several cells, and any
pre-existing data overwritten. This macro resets the delimiters to none. |
download |
Add-Innovation toolbar macro help. Opens a form allowing you to select
help for any add-innovation tool you have downloaded. |
download |
Trim
Left and Right
Change Case
Ignore #N/A
