VBA – Custom Excel Formulas (2)

After the first article for custom excel formulas with VBA (here) I have decided to enlarge my knowledge in the area with some additional examples. Here I present three different custom formulas for excel.


1. The first one is an interesting formula, due to the fact it has 9 different functions, based on its parameters. Quite strange, but the usage of “Select Case” gives us the ability to do so. Here is the code:

So how does it function? We simply have two parameters to the formula – a range to refer to and a number from 1 to 8 (or any other number for the Else option). Pretty much, this is the result you get from the 9 possibilities:

variousFormula

As far as the VBA code is self explanatory, I will not explain it more. I just want to point out, that the line “Application.Volatile” is needed, in order to recalculate the formula each time anything in the excel sheet is changed. If this line is missed, the formula would be recalculated only at its first execution and whenever you ask Excel to recalculate (by pressing Shift + F9 or just F9).


2. The second custom formula is created to calculated only red and bold numbers in a range. Red and Bold could be easily changed to Yellow and Italic, if you wish but this is not the most important part here. The custom formula is quite easy, the only parameter it takes is the range it should calculate. Then, if a cell in that range is numeric it checks whether it is bold and red and it adds its value to the sum. The result looks like this:

RedAndBold

 

The code of the formula:

The only more interesting part of the formula, is that you should know that the ColorIndex of Red in VBA is “3”. If you do not know that, you may check the color indices here.


3. The third custom formula simply gives you the position of the first digit within a cell. Somehow, this may be useful, if you are processing data for postcodes. At first I thought about checking the ASCII table and to check every sign within the cell against it. Later I have noticed that this does not function for cyrillic letters (and others, which are not present in the humble ASCII table), so I thought of a simpler decision – checking the signs for the position, where the digits stay in the ASCII table. These positions are between 48 and 57. Thus, this is the way the formula functions:

FirstDigitLocation

 

And this is the code of the functions:


So, that is all. If you want to take a look at the excel file, containing the formulas you may do it here.

About

VBA Developer

Tagged with: , , , ,