VBA UDF дает другой ответ при вызове из Excel vs VBA

Следующая функция VBA подсчитывает количество ячеек, содержащих формулы в заданном диапазоне. Он корректно работает при вызове из поддиапазона VBA. При вызове из Excel он возвращает общее количество ячеек в диапазоне.

Вызов из Excel равен =CountFormulas(A1:C7) , который возвращает 21, хотя в диапазоне находятся только две ячейки с формулами.

Что вызывает это несоответствие?

 Public Function CountFormulas(ByRef rng As Range) As Long CountFormulas = rng.SpecialCells(xlCellTypeFormulas).Count End Function Public Sub CountFormulasFromSub() Dim rng As Range Dim res As Integer Set rng = Sheet1.Range("a1:c7") res = CountFormulas(rng) End Sub 

Это невозможно. Следующая ссылка имеет то, что не будет работать внутри UDF.
Здесь – http://support.microsoft.com/kb/170787

EDIT: Ручной способ подсчета работает.

 Public Function CountFormulas(rng As Range) As Integer Dim i As Integer Dim cell As Range For Each cell In rng If cell.HasFormula Then i = i + 1 End If Next CountFormulas = i End Function 

Измените Integer на Long если вы думаете, что он превысит 32767.

Если бы я отправил рабочие листы в функцию, он проверил бы все ячейки на всем рабочем листе, довольно много и довольно медленно. Хотя Excel 2007+ поддерживает строки 16384 * 1048576, в память загружаются только те ячейки, которые действительно были использованы. Не нужно было проходить через все остальные 17 миллиардов ячеек для проверки. Самое близкое, что я мог определить, это использование Worksheet.UsedRange для ограничения произвольного ввода диапазона. Это не идеально, хотя в случаях, когда использовались клетки, расположенные далеко друг от друга. Например, если ячейки A1 и XFD1048576 содержат данные, весь лист будет включен в UsedRange. Любые советы о том, как ограничить диапазон для фактически используемых ячеек (всего две ячейки в приведенном выше примере), будут с благодарностью оценены.

Используя UsedRange, я создал функцию, которую я поделюсь, если кто-нибудь еще сможет ее использовать:

 Public Function CountIfFormula(ByRef rng As Range, Optional ByVal matchStr As String) As Long 'Counts the number of cells containing a formula and optionally a specific string (matchStr) in the formula itself. Dim i As Long Dim isect As Range 'Restricts the range to used cells (checks only cells in memory) Set isect = Application.Intersect(rng, rng.Parent.UsedRange) For Each cell In isect If cell.HasFormula Then If InStr(1, cell.Formula, matchStr) Then i = i + 1 End If Next CountIfFormula = i End Function 

Использование функции:

 Sub GetNrOfCells() Dim i As Long Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets i = i + CountIfFormula(ws.Cells, "=SUM(") Next 'i will now contain the number of cells using the SUM function End Sub 

С наилучшими пожеланиями, и спасибо за ваш ответ.

Fossie

Interesting Posts

Удалить метод диапазона с ошибкой vba

Как скопировать текст Excel и сохранить разрывы строк

Как использовать диапазоны excel в формулах excel vba

Excel: создание уникальных строк для данных, отобранных с течением времени

Через Excel изменить интервал между строками в документе Word

Можете ли вы дать столбцам «имя переменной» для определенного листа в Excel VBA

Добавление ссылок в Excel с помощью макроса

Косвенное использование диапазона переменных для имен рабочих листов

Как получить согласованную подстроку, где строка соответствует списку потенциальных подстрок

Формула Excel для перекрестной ссылки 2 листа, удалите дубликаты с одного листа

Как я могу заменить определенную часть текстовой строки в Excel, которая имеет переменное местоположение во всем документе?

Excel vba для каждой видимой ячейки в строке A, вставить значение в строку H?

Создание и чтение таблицы в Excel vbUsing VB

Почему FromOADate получает день и месяц неправильно?

Формула для создания сетки из списка в Excel

Давайте будем гением компьютера.