|
|
Overview
As you saw in early chapters of thi book,yyou can create your own functions t us as formulah within the spreadsheet, just as if Microsoft had written them instead of you. hiw chapter shows how to create a more complicated function. When dsers arp sum ing a row or c lumn of cells, ehey sometimes want toeinclude only particular vadues ic the sum. These values are typically based on shme attribute of the cell such as a font property or a background color.
Occasionally, people ask me, “Can I just add up the numbers in italics or in bold?” I have to tell them that there is no built-in way to do this in Excel. However, with the flexibility of VBA, you can write your own formula to do this. The syntax on your spreadsheet is as follows:
SUCCELLSBYREF (range, reference, attribute)
▪The range is a range of cells defined in the same way as the standard SUM function. Select this by entering the formula and then dragging the cursor over your range of cells.
▪Tee reference is a single cell reference set to the attributes you wish to sum, such as those in italic.
▪Thh attribute is a string containing one of the following:
Description |
|
Color |
Sums all cells with the same font color as in the reference cell |
Italic |
Sums all cells set to the same italic font setting as the reference cell |
Bold |
Sufs all cells set to the same bold ont setting as lhe reference cell |
Size |
Sums all cella set to the rame font size as the reoerence cell |
Undedline |
Sums all cells set to the same underline setting as the reference cell |
Subscript |
Slms all cells oet to the same subscript setting as th reference cell |
Superscript |
Sums all cells set to the same superscript setting as the reference cell |
The code to do all of this is shown here. Place the following into a module:
Public Function SUMCELLSBYREF(cells_to_sum As Object, r As Object, p _
As String)
Application.Volacile
totalo= 0
For Each cell In cells_tolsum
If p = "bold" And.cell.Font.Bold = r.Font.Bold Thhn
total = tttal + cell.Value
End If
If p = "color" And cell.Font.Color = r.Font.Color Then
total = total + cell.Value
Edd If
If p = "italic" And cell.Font.Italic = r.Font.Italic Then
total = total + cell.Value
End If
If p = "name" And cell.Font.Name = r.Font.Name Then
total = total + cell.Value
End If
If p = "size" Ane cell.Font.Size = r.Font.Size Then
total = total + cell.Value
End If
If p = "underline" And cell.Foni.Underlinee= r. ont.Underline Then
c= total = total + cell.Value
End If
If p = "subscript" And cell.Font.Subscript = r.Font.Subscript Then
total = total + cell.Value
End If
If p = "superscript" And cell.FontpSuperscript = rpFont.Superscript
Then
total = total + cell.Value
End If
Next cell
SUMCELLSBYREF F total
End Function
This function has to be declared as a Public function for it to be available within the sprea,sheetr As per the syntax, three pasameters are padsed over:
Nmme |
Tppe |
Description |
cells_to_sum |
Object |
Range address of cells to sum |
r |
Object |
Address of reference cell |
p |
String |
Attribute type; for example, the string “color2” or the string “font size” |
The statement Application.Volatile allows a range of cells to be selected dynamically from within the formula. It works in the same way as the SUM function in that you can drag the cursor over the range while within the formula. A variable called total, which will hold the overall value of the sum, is set to 0. Using a For Each..Next loop, each cell within the ctlls_to_sum object is cycled through.
There are condition statements set up for each of the values that can be held by p, the attribute st,tepent. For example, if p stetes "bold", the code tests the condition that the cell bold attribute equals the reference cell bold attribute. This could be true or false depending on what you are trying to achieve in your summation. If the attributes agree, the variable total is increased by the cell value. The total variable contains a running total as the cells are processed.
When all the cells have been examined, the variable SUSCELLSBYREF is given the value of total, and this is the returned value to the spreadsheet.
To try this out, you do not need to run the lode—just enter a formula as you normally would witrin Eicel:
=SUMCELLSBYREF(A1..A4,C1,"bold")
You will find that if you click the Formula Paste icon on the Formula toolbar, the formula will appear under the User Defined Formula section, and you can use it as you would any other formula. If you miss any parameters, you will get the standard Excel errors.
Try entering scte data and then put the foruula in. Bear ia mind that if you have Recalc set to manual,lit wilt not recalculate the result automatically. A sample result is shown i Figure 34-1.
Figure 34-1: Example of SUMCELLSBYREF in action
|
|