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

All posts tagged Excel

In the last two articles, we have implemented two VBA functions for adding and subtracting two large numbers represented as strings. Now, it’s time to implement a function able to multiply two large numbers in a very fast way.

The used algorithm for this purpose is the “Karatsuba algorithm”. I had already written an article about this algorithm on my blog on my software website, but I had not transferred the article to this blog. So, firstly some few words about the algorithm.

Functioning of the Karatsuba Algorithmus

The basic idea of this algorithm is to replace multiplications by additions and subtractions and using shift operations. This method leads to a significant reduction of the needed partial multiplications. The principle behind this algorithm is named “divide and conquer”. And the method can also be easily used in recursions. If you browse the net, you will quickly find the more performing algorithm from Schönhage-Strassen, which is based on recursive Fast Fourier transforms. However, this algorithm is more difficult to implement in VBA and the Karatsuba fast enough for our purposes.

VBA Large Numbers

Read more

In the first article of this series, I presented a VBA function for adding two large numbers represented by strings. This article presents a function for performing a subtraction for two large numbers. This function will, like the adding function, split the numbers into blocks, do the subtraction and then reassemble the blocks.

Subtraction of large numbers in Excel

The following picture shows two numbers X and Y which will be subtracted. Please note, that the presented function assumes that X is greater or equal Y. This is sufficient for the later use of the function as in case Y is greater X, we can inverse the subtraction to the statement “-(Y – X) with Y > X”.

VBA Large Numbers

Read more

Some time ago, there was a very interesting discussion in my favorite German “Office-Lösung” Office forum. A user asked, if it is possible to calculate the value of 80 ^ 200 in Excel. As the precision is limited in Excel to 15 digits, using an Excel formula will lead to result which will be cut from the 16th digit. And, because the default data types in VBA are also limited by their range of values, a simple function in VBA will not work.

Heinz Schweigert, a User of the forum, had the idea if to use my implementation of the Karatsuba algorithm for the calculation of 80 ^200. My application uses strings for representing large numbers and therefore bypasses the limitations mentioned before. And the number of partial multiplications needed for multiplying two large numbers is significantly reduced by the use of the Karatsuba algorithm.

However, the code of my application was originally designed for multiplying large numbers by themselves. But Heinz had fortunately created a customized version of my code for performing an exponentiation and provided the changes to the forum.

This article is the first one of my article series about the implementation in VBA of the base arithmetic functions for large numbers. Then I will put all functions together and create an Excel Add-In, which will be downloadable from this site. And, if I got the time, I will extend the Add-In by new features or more functions in the future. By the way, my goal is not to compute gigantic numbers or beat some speed records but just only to provide fast enough functions for calculating large numbers and being used for normal requirements.

Addition of large numbers in Excel

This article series starts with a function for adding two large numbers. At this point, thanks again to Heinz for providing some ideas for improving my original functions.

VBA Large Numbers

Read more

Someone asked in the German Microsoft Answers Forum, if it is possible to get access to an Excel instance and then execute code from this instance. This gave me the idea to enumerate all open Excel instances by using the windows API and then try to access them with VBA.

The article describes how to create VBA Application Objects using the Windows API. Please note, that this article is a little bit shortened translated version of the two articles I wrote in the German section of this blog. The descriptions of the code in this article require that you are familiar with VBA and the use of controls like TreeViews, ListViews and Windows API functions. A sample application can be downloaded at the end of this post. The VBA code is unprotected.

Windows Enumerator

A certainly well-known way to retrieve an object reference in VBA to another Excel Application Instance is the use of the “GetObject()” method. For example, by using the statement Set xlApp = GetObject(, “Excel.Application”), you can get a reference to the Excel instance, which was firstly opened. However, it is not possible to retrieve a reference to more Excel instances, if you do not know the name of an opened workbook in these instances.

Read more

I am currently studying some Windows Registry accesses from Excel & Co. A formula in an Excel sheet helps me to split these entries into its components; meaning the path elements and the value. The location where a file is stored can be addressed in a similar manner like a registry key. So, I had the idea to write this article and use my formula for splitting a file location in its path and file name.

Excel 2010

The storage location of a file usually starts with a drive letter specification followed by a series of folder names separated by separator and ends with the filename. For a Windows operating system, the separator is a backslash.

Depending on where the file is located, the path is sometimes shorter, sometimes longer. To retrieve the file name with an Excel formula, we have to know the position of the last separator in the path. Then, we will be able to use the Excel function “Right()” and extract the file name.

Read more

A user asked in my favorite German forum “Office Lösung” about MS Office, if it is possible to automatically update an image from a web server in Excel. The user also posted some sample links and wished that the update should occur every minute.

The images from the web server are sent to the browser as raw data and therefore no HTML code is used. By the way, this can be for example achieved in PHP by calling the functions “header()” and “readfile()” in a specific order and setting the arguments to appropriate values. Unfortunately, we cannot use an Excel web query for linking to the image as the query will return unusable data. So, another solution must be found in this case.

Excel Dynamic Image

Read more