Sub demo()Dim w As Worksheet 's和, w 表格, r 单元格, r1 子单元格For Each w In Worksheets
w.Cells(30,6)= redCount(w.UsedRange)
w.Cells(30,6).Font.Color= vbBlue
Next w
EndSub--------------------------------------------------------------------------------------------------' ' 本函数接收一个Range类型变量作为参数' 然后扫描其中每一个单元格,将红色数字汇总返回>>>>>>>>>>>>>>可以当做自定义公式Function redCount(r As Range)Dim s AsLongDim r1 As Range
'和For Each r1 In r
If r1.Font.Color= vbRed Then
s = s + r1.Value
EndIfNext r1
redCount = s
EndFunction
需求2: 按行求积再相加
Function mySumProduct(r As Range)Dim i&, j&, k&, s&
s =0'求和
b = r.Column + r.Columns.Count -1Debug.Print b
For i = r.Row To r.Row + r.Rows.Count -1
k =1'求积For j = r.Column To r.Column + r.Columns.Count -1
k = k * Cells(i, j)Next j
s = s + k
Next i
mySumProduct = s
EndFunction
需求3: 公式转换为值
Sub replaceFormula()Dim w As Worksheet
For Each w In Worksheets
w.UsedRange.Value = w.UsedRange.Value
Next w
EndSub