In Excel 2007, it is possible to link a chart or an axis title to a cell by using a formula. However this was not possible when using VBA. The correponding objects have now been extended in Excel 2010, as you can see in the sample file which can be downloaded at the end of this article. The following image shows a screenshot of this sample file.
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.
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.
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.
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.
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”.
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.
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.
This fourth part of my article series about the calculation of very large numbers in Excel talks about the exponentiation of two large numbers. If we consider the original problem (see my first article) which was the calculation of 80 ^200 in Excel, we will need at least from 80 ^ 160 an own algorithm.
The calculation of 80 ^200 is equivalent to a total of 200 – 1 = 199 times multiplying the number 80 by itself. We already have a function for multiplying large numbers; see my third article. However the large number of multiplications could lead to a performance problem; so we will check if it is possible to reduce this number.
Exponentiation by squaring (or binary exponentiation) is, according to the Wikipedia, a general method for fast computation of large integer powers of a number. This algorithm has already been discovered around 200 BC in India and can be seen as a standard method for reducing the number of multiplication needed for the calculation of the power of an integer value.
