(C) 2009 - 2014 by Mourad Louha · All rights reserved

Calculation of very large numbers in Excel – Part 5 – Add-In

This article presents my first version of my free Excel Add-In (Version 0.75 Build 151110) for calculating large numbers in Excel. The Add-In (you can download the file at the end of this article) currently implements functions for the addition, subtraction, multiplication and exponentiation of large numbers. The code in the Add-In is password protected; however you can unprotect the VBA project by using the password “excel-ticker” (without the quotes). Please also read the copyright notice and license terms in the last section of this article.

The Add-In is also digitally signed with a certificate which was issued to me by the company “TC TrustCenter GmbH, Germany”. So, only files including this digital signature have been created and published by me.

Currently, the Add-In can only perform calculations with integer numbers, an extension for calculating decimal numbers is in progress. Additionally, the Add-In will later provide more functions, such as the division or calculation of special formulas like “X ^ Y Mod N”. These new functions will use more complex algorithms based on the research from Donald Knuth and derivatives from his algorithms.

Naming conventions of the functions in the Add-In

The Add-In includes the functions described below, which can be accessed, for example, through the category “User defined” of the select function box in Excel. Each function of the Add-In is available in English, German and French.

VBA Large Numbers

I named the Add-In functions similar to Excel functions having the same purpose. For example, you can use the Excel function “SUM()” for adding numbers. The function for adding to large numbers is named “LNSUM()”, where “LN” means “Large number”. The German and French function names in my Add-In are corresponding to the localized Excel functions with a localized prefix: “GZSUMME()” in German and “GNSOMME()” in French, where “GZ = Große Zahl” and “GN = Grand nombre”.

The arguments used in my functions are also named in a similar manner to the Excel functions. For example when using “LNSUM()”, the arguments are “Number1”, “Number2, “Number3”, etc. like in the function “SUM()”. However, my functions for adding and multiplying numbers only allow up to eight arguments instead of 30 arguments in the corresponding Excel functions.

Important note: a cell in Excel can contain a maximum of 32,767 characters. If the result of a calculation from my functions exceeds this length, then the error #Value is shown in that cell. Such case can occur relatively quickly when using large exponents in an exponentiation. For example the result of the calculation of 4 ^ 123456 contains 74,328 characters.

Addition and Subtraction of large numbers by using LNSUM()

As mentioned before, the function “LNSUM()” can contain up to eight arguments. It is allowed to specify a large number in an argument by using its representation as a value, a string, a cell reference or a range. Example: =LNSUM($C$1; $D$15:$E$20; 1234; “56789“). If the concerned cells are containing numbers, then the function should deliver a correct result. Empty cells are treated as zeros. However, if one cell contains a text, which cannot be converted to a number, then the function “LNSUM()” fails. If you would like to subtract to numbers, you may prepend the sign before the number.

VBA Large Numbers

Multiplication of large numbers by using LNPRODUCT()

Similar to “LNSUM()”, the function “LNPRODUCT()” allows specifying up to eight arguments. If a range is specified in an argument, then the values of all cells of this range are multiplied. Empty cells are treated as one in this case. Therefore, this function can quickly generate very large numbers. Example: =LNPRODUCT($C$1; $D$15:$E$20; 1234; “56789“) multiplies the value from cell $C$1 with all values from each cell in $D$15:$E$20 and 1234 and 56789. In one cell contains a text, which cannot be converted to a number, then the functions also fails.

Please note that, even the VBA code is using the very fast Karatsuba algorithm for the multiplications, I recommend you to not excessively use large number multiplications because the calculations are done sequentially for each function used in a sheet.

VBA Large Numbers

Exponentiation of large numbers by using LNPOWER()

This function works similar to the Excel function “POWER()” and therefore expects two arguments; the first one is the number to be powered and the second is the exponent. Please use this function with care; a too large exponent can lead to long calculation times. For example, calculating “5 ^ 12345” takes approximately 30 seconds on my computer. PS: if I calculate “80 ^ 200”, the result appears instantly, demonstrating that the algorithm is really fast enough for usual high power calculations.

VBA Large Numbers

Code for the functions in this Add-In

I had first considered describing the code of the functions in this section. However, this would have resulted in a very long article and mixed with code and functionality descriptions for the users of my Add-In. So, I decided to publish a detailed description of the code in the next article.

Copyrights and license terms for the Add-In

The producer – that’s me – provide to the user a limited, non-exclusive, free license for this product. The sole purpose of the Add-In is the calculation of large numbers in Microsoft Excel.

No warranty is given by the producer that the product is error free, does fit to all your requirements or will work with no interruptions. Furthermore, no warranty is given that errors will be corrected, that updates will be available or that new versions will be published. You will use the Add-In at your own risk; this includes any damage or loss of data resulting from the use of this software.

This Add-In may not be sold, rented, loaned, leased or commercially distributed in any other way. The product may definitively not be linked or distributed on fee-based websites. The product may not be offered on electronic or printed media without my explicit written agreement. This also applies to the code.

You are free to use the code in the Add-In for your own personal or internal company projects without any commercialization goals. However, the information about the author and other persons mentioned in the code may not be removed or changed.

The producer owns the title, copyright and all other commercial and non-commercial rights to this product. The use of this product grants you no rights to trademarks or service marks of the producer mentioned above.

Finally I would particularly like to thank Andreas Entenmann for supporting me when testing the Add-In.

» Download the Add-In for calculating large numbers in Excel



You should check out http://tukhi.com/tukhi_fun.html. That page has a link to an add-in that hooks up the GNU multi-precision integer library.

Hi Keith,

thanks for the suggestion, sounds very interesting :-)


Does this add-in only work for PC-based Excel? I have Mac Excel and it gives me a compile error….

Hi Andrew…

this Add-In uses some Windows API functions, which are not available on a Mac. If you are interested and will help testing on Mac (I haven’t a Mac), I can build a version without the API functions, which then *normally* should work on a Mac.

Regards :-)

Hi Mourad,

This is a very useful add in, thanks for sharing. Have you developed the one for decimal numbers yet?

Hi Ali…

Thanks; fine that the Add-In helps. Unfortunately, I did not got the time to continue the implementation for decimal numbers; however this is still on my list and I hope to release it soon.

Best Regards :-)

I am willing to test both integer and decimal add ins on mac (Numbers & Excel) . Just let me know.

Hi Ali…

Thanks for the offer, would be really great. I will notice you per e-Mail, when I am ready.

Best Regards :-)

Hi Mourad, thanks a lot! It helped me to solve a problem I didn’t know what to do with. Appreciated!

Hi Petr, Thanks :-)

I ran into a problem where I kept getting an error referring to MLP_API.
I’m running Office 2010 64bit and believe that was the root cause.
I managed to fix it by editing the Karatsuba.xla file. To do this: After adding Taratsuba.xla to your Excel add-ins – open VBA (alt-F11) from Excel. Open MLP_Maninweb (Karatsuba.xla) and enter the password mentioned on this page. Expand Modules, and open MLP_Api. Notice that if you run Debug > Compile MLP_Maninweb, you get an error mentioning PtrSafe. Just add “PtrSafe” in-between Declare and Sub for the five API function calls (apiSleep, apiDrawMenuBar, apiFindWindow, apiGetWindowLong, and apiSetWindowLong)

Private Declare Sub apiSleep Lib “kernel32” _
Alias “Sleep” (ByVal a As Long)
Private Declare PtrSafe Sub apiSleep Lib “kernel32” _
Alias “Sleep” (ByVal a As Long)

Then save the file. You will be prompted about something regarding signature – obviously this change invalidates it.

Now, there may be security issues with doing this, so I recommend that anyone considering doing this looks into security concerns – especially in a business environment. So be warned.

Hi Chris,

thanks for you comment and suggestions. The Add-In was originally implemented for Excel 32 Bit. If you run it in Excel 64 Bit, you’ll get the compiler errors you mentionned. In 64 Bit, the API declarations may be changed. To get a version which can be run on different platforms, you can also use the compiler constants as described here: http://msdn.microsoft.com/en-us/library/ee691831%28office.14%29.aspx

When someone changes the code, the certificate which signs the orginal Add-In, will be lost and Excel will inform you about this. The certificate I used had the purpose to prove the origin of the file (me). There is no difference in the code with the unsigned file and so no security concerns should be appear; excepted that some companies does not allow the execution of unsigned files.

Best Regards, Mourad

Hi Mourad Louha,
I’ve used function LNSUM like that LNSUM(1,2,3,4,123456789123456789) and i’ve get #VALUE for result. Note Arg5 has 18 characters.

Mourad Louha

Hi huyns,

123456789123456789 can not be recognized as a number. You may pass the value as string, example: LNSUM(1,2,3,4,”123456789123456789″).

Regards, Mourad

I get the #value error when I try lnsum of any value over 16 digits.
I need to calculate so can’t use the “” or text work around.
Do you have any updated advice?

Hello GDwitt,

you can’t calculate in Excel with number with more than 15 digits. But the Add-Ins performs this by using text and internally transforming the text into numbers. So, if you user LNSUM(Arg1;Arg2) the result is the sum of both arguments. Hope, this helps.

Regards, Mourad

Is there a way to use this add-in to do a Decimal to Hex conversion on 18 digit numbers?

Mourad Louha

Hello Mark,

the tool is for performing basic calculations with large numbers. I think, for converting big dec. numbers to hex, an extra function should be implemented, which can be completely independent from this tool.

Best regards, Mourad

Mourad, you sooo saved my day – thank you so much!!

Best Regards, Riad

Hello Riad,

thanks, glad I could help :-)

Best Regards, Mourad

I am interesting to try it on MAC Excel. Can you provide version without WinAPI please ?

Hi Telesfor W,

you can remove the API functions by yourself. Just delete the module MLP_Api and remove the line mlfpApiDeframe Me, True in the MLF_Info dialogue.

Regards, Mourad

I can not edit Karatsuba.xla under MAC Excel VB Editor. Whatever I try it request me for password and “excel-ticker” w/o quotes does not work. Can you assist please ?

Hello Telesfor,

Ok, I sent the changed file to you at the e-mail address you leaved here for your comment.

Regards, Mourad

Works perfect ! It requires to replace not exiting at MEC Excel CDec() by CDbl() but still it is doing a great job of computing. The performance is OK (I am using Mac Book Pro with i7 4x core).

Karatsuba was really a great mathematician. Thank you.

Hi Telesfor,

thanks, fine that all works now :-)

Best regards, Mourad

Hi Mourad,

I’m looking for assistance on Excel from Office 2008 for MAC. I’m wanting to add integers of 16 digits in length without having the rounding errors associated with the 15 digit limitation of excel. I can have the result displayed in text. If you could offer any assistance that would be greatly appreciated.



Hello Robert,

I don’t know if VBA is included in Office 2008 for Mac. If so, then you can use this Add-In above. However, the Windows API functions should be removed. Delete the MLP_Api Module and compile the project. On each line, you get an error, it was a call to functions from the deleted module. Just also delete the line. After that, it should be possible to use the Add-In functions as described above. If Excel 2008 for Mac has not VBA included, then I am afraid, that I can not help you and I suggest to ask in a Office for Mac forum.

Regards, Mourad

Hi Mourad, Thanks very much for your response. I’ll download the file you suggested and make the appropriate changes. Will let you know how I go. Thanks again.

Regards, Robert

Hi Mourad, thanks a lot! This was really helpful for solving my problem.
I found a little “glitch” in the code which you might find interesting to look at. Using LNPOWER() with power=0 returns null instead of 1. Of course this is only a minor thing compared to the superb job your code is doing.
Keep up the good work!

Regards, Igor

Hello Igor,

first, sorry for the delay in approving the comment; we have just moved to a new home and therefore I was too busy.

Yes, you are right, thanks for pointing out this bug. If necessary, you can change the code by replacing/add a small section. In the module (the password is excel-ticker for the VBA-Project) MLP_Functions, search the function mlfhExponentiation. At the bottom of the function add an If-statement between If blnNumber And blnExponent Then … End If for checking the power value. After the change, the code looks like:

'     Calculate...
      If blnNumber And blnExponent Then
        If CLng(vntExponent) <> 0 Then
          vntResult = mlfpNumbersCalculate(CStr(vntNumber), _
                      CStr(vntExponent), mlcpNumbersExponentiation, _
          vntResult = 1
        End If
        vntResult = CVErr(xlErrValue)
      End If

Best, Mourad

How can I put more then 19 digit? If I select text then it show as like 1e1548545f54515e22 like this. How can I fix this problem?

Thank you

Hello onu

first, no links to sites which are not related to Excel please. So I removed your link to the site. You can enter an single apostroph when entering large numbers. Example ‘123456789012345… The apostroph says Excel to keep the value as entered. Another possibility is to format the number as text.

Best regards, Mourad