(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

 

 
Comments

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 :-)

Regards

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)

Example:
Private Declare Sub apiSleep Lib “kernel32″ _
Alias “Sleep” (ByVal a As Long)
becomes
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.
Why?

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