Добрый день Вариант для листа, не всё допилено, но в принципе, если не считать наличия в формулах текста, содержащего буквы или цифры заканчивающиеся без пробела скобкой "(", то должно работать. [vba]
Код
Public Function GetSheetFormulaCount(ByVal this As Worksheet) As Long Dim pReg As Object, pDict As Object, pItems As Object Dim formulaRange As Range, pCell As Range, pItem As Object Set pReg = CreateObject("VBScript.RegExp") pReg.Global = True: pReg.IgnoreCase = True pReg.Pattern = "[a-zà-ÿ¸0-9]\(" Set pDict = CreateObject("Scripting.Dictionary") pDict.CompareMode = VbCompareMethod.vbTextCompare Set formulaRange = this.UsedRange.SpecialCells(xlCellTypeFormulas) If Not formulaRange Is Nothing Then For Each pCell In formulaRange Set pItems = pReg.Execute(pCell.Formula) If pItems.Count > 0 Then For Each pItem In pItems pDict(pItem.Value) = Empty Next End If Next End If GetSheetFormulaCount = pDict.Count End Function
[/vba] Успехов
Добрый день Вариант для листа, не всё допилено, но в принципе, если не считать наличия в формулах текста, содержащего буквы или цифры заканчивающиеся без пробела скобкой "(", то должно работать. [vba]
Код
Public Function GetSheetFormulaCount(ByVal this As Worksheet) As Long Dim pReg As Object, pDict As Object, pItems As Object Dim formulaRange As Range, pCell As Range, pItem As Object Set pReg = CreateObject("VBScript.RegExp") pReg.Global = True: pReg.IgnoreCase = True pReg.Pattern = "[a-zà-ÿ¸0-9]\(" Set pDict = CreateObject("Scripting.Dictionary") pDict.CompareMode = VbCompareMethod.vbTextCompare Set formulaRange = this.UsedRange.SpecialCells(xlCellTypeFormulas) If Not formulaRange Is Nothing Then For Each pCell In formulaRange Set pItems = pReg.Execute(pCell.Formula) If pItems.Count > 0 Then For Each pItem In pItems pDict(pItem.Value) = Empty Next End If Next End If GetSheetFormulaCount = pDict.Count End Function