# All posts tagged VBA

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

Categories: Addins and Extra Tools, Development, Excel, VBA

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.

#### 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.

### Calculation of very large numbers in Excel – Part 4 – Exponentiation

Categories: Development, Excel, VBA

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.

#### Binary exponentiation

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.

### Calculation of very large numbers in Excel – Part 3 – Multiplication

Categories: Development, Excel, VBA

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.

### Calculation of very large numbers in Excel – Part 2 – Subtraction

Categories: Development, Excel, VBA

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”.

### Calculation of very large numbers in Excel – Part 1 – Addition

Categories: Development, Excel, VBA

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.

### Get the path and file name for a file location using formulas or VBA in Excel

Categories: Development, Excel, Office, VBA

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.

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.