If you are, like me, programming in different languages, maybe you have also noticed that you miss certain functions in a language, which are available in a different language.

What I am used to do, is either find a function or functions that perform the same task:

Delphi: Dec(i);
PL/SQL: i := i – 1;

or create a function in the language that ‘misses’ functionality to help me (or this language ):

Delphi: inc(i);
PL/SQL:
create or replace procedure inc(value_in_out in out pls_integer )
is
begin
  value_in_out := value_in_out + 1;
end inc;

Now I happened to need a function like LPAD in Excel. I couldn’t find it, so I decided to build it myself. But how can I make sure that my function can be called from a function in a cell?

First of all, I have to create the function. Start the Visual Basic Editor. Select [Insert – Module] to add a module component to your workbook. In this module add a function that implements the required functionality. Be sure to make this function PUBLIC, so it can be called from a function in a cell.

Public Function LPad(AText As String, ALength As Integer, APadchar As String) As String
  LText = AText
  While Len(LText) < ALength
    LText = APadchar + LText
  Wend
  LPad = LText
  Exit Function
End Function

In a cell I can now write a function like this:

=LPad(“1″;10;”x”)

which will result in

xxxxxxxxx1

But you can of course use this function for more complex calculations. I used it to create an import file from an excel document. The import needs a fixed length string, so I needed to LPad cells to their desired length.

Now, what if you want to make this function available to your Excel documents at all times. Follow these steps to Excel happiness 🙂

1. start a new document
2. start the Visual Basic Editor
3. add a module
4. add the desired function(s) to the module
5. save the document as a ‘Microsoft Office Excel Add-In (*.xla)’
6. be sure to make the location:
   C:\Documents and Settings\<username>\Application Data\Microsoft\AddIns

To automagically load the add-in, check your add-in in the Tools-Add-Inns dialog.