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:
PL/SQL: i := i – 1;
or create a function in the language that ‘misses’ functionality to help me (or this language ):
create or replace procedure inc(value_in_out in out pls_integer )
value_in_out := value_in_out + 1;
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
LPad = LText
In a cell I can now write a function like this:
which will result in
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 🙂
start a new document
start the Visual Basic Editor
add a module
add the desired function(s) to the module
save the document as a ‘Microsoft Office Excel Add-In (*.xla)’
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.