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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
Function various(myCell As Range, i As Integer) Application.Volatile Select Case i Case 1 various = myCell.Address Case 2 various = myCell.Value Case 3 various = myCell.FormulaLocal Case 4 various = 50 Case 5 various = myCell.Worksheet.Name Case 6 various = ActiveWorkbook.Name Case 7 various = ActiveWorkbook.FullName Case 8 various = Application.UserName Case Else various = "vitoshacademy.com" End Select End Function |
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:
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:
The code of the formula:
|
1 2 3 4 5 6 7 8 9 10 |
Function RedAndBoldSum(myCells As Range) Application.Volatile For Each myCells In myCells.Cells If IsNumeric(myCells) Then If myCells.Font.Bold = True And myCells.Font.ColorIndex = 3 Then RedAndBoldSum = RedAndBoldSum + myCells.Value End If End If Next myCells End Function |
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:
And this is the code of the functions:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Function FirstDigitLocation2(myCell As Range) As Integer Application.Volatile Dim i As Integer For i = 1 To Len(myCell) Select Case Asc(Mid(myCell, i, 1)) Case 48 To 57 FirstDigitLocation2 = i Exit Function End Select Next i FirstDigitLocation2 = 0 End Function |
So, that is all. If you want to take a look at the excel file, containing the formulas you may do it here.