{"id":64,"date":"2007-01-28T14:02:55","date_gmt":"2007-01-28T13:02:55","guid":{"rendered":"http:\/\/bar-solutions.com\/wordpress\/?p=64"},"modified":"2007-01-28T20:10:06","modified_gmt":"2007-01-28T19:10:06","slug":"adding-custom-functions-to-excel","status":"publish","type":"post","link":"https:\/\/blog.bar-solutions.com\/?p=64","title":{"rendered":"Adding custom functions to Excel"},"content":{"rendered":"

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.<\/p>\n

What I am used to do, is either find a function or functions that perform the same task:<\/p>\n

\n

Delphi: Dec(i);
PL\/SQL: i := i – 1;<\/p>\n<\/blockquote>\n

or create a function in the language that ‘misses’ functionality to help me (or this language ):<\/p>\n

\n

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;<\/p>\n<\/blockquote>\n

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?<\/p>\n

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.<\/p>\n

\n

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<\/p>\n<\/blockquote>\n

In a cell I can now write a function like this:<\/p>\n

\n

=LPad(“1″;10;”x”)<\/p>\n<\/blockquote>\n

which will result in <\/p>\n

\n

xxxxxxxxx1<\/p>\n<\/blockquote>\n

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.<\/p>\n

Now, what if you want to make this function available to your Excel documents at all times. Follow these steps to Excel happiness \ud83d\ude42<\/p>\n

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

    To automagically load the add-in, check your add-in in the Tools-Add-Inns dialog.<\/p>\n","protected":false},"excerpt":{"rendered":"

    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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,5,4],"tags":[],"class_list":["post-64","post","type-post","status-publish","format-standard","hentry","category-common","category-plsql","category-tools"],"_links":{"self":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/64","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=64"}],"version-history":[{"count":0,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/64\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=64"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=64"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=64"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}