Создание функции CountRows в Excel

Я пытаюсь создать простую countRows функцию, которая будет подсчитывать количество ячеек в динамическом диапазоне. Обычно, если у меня есть значения в ячейках, скажем, B2: B500, счетчик вернет 499. Однако в следующий раз, когда значения будут в ячейке B2: B501, счетчик вернет 500. Но вам не придется ничего делать с ячейкой в который вы ввели в формулу.

Я подумал, что если я назову ячейку Variant, то можно будет принять любое значение. Затем найдите Address этой ячейки и верните Count из Range. Но получаю #Value ошибку.

Public Function countRows(startRange As Variant)

    Dim rng As Range
    Set rng = startRange.Address


    If IsEmpty(Range(rng, rng.End(xlDown))) = True Then
        countRows = 1
    Else
        countRows = Range(rng, rng.End(xlDown)).Rows.Count
    End If

End Function

function count range vba excel-udf
person Jack Armstrong    schedule 29.07.2017    source источник
comment
не могли бы вы быть более описательными?   -  person Jack Armstrong    schedule 29.07.2017
comment
Range.Address возвращает строку. Set rng = startRange.Address Выдает ошибку, используйте Set rng = startRange   -  person    schedule 29.07.2017
comment
Если вы собираетесь предположить, что startRange - это диапазон, вы должны ввести его как диапазон, а не как вариант. Переменная rng ничего не делает для вашего кода. Вы должны это устранить.   -  person    schedule 29.07.2017
comment
@ YowE3K Я считаю, что OP пытается создать что-то с нуля. Просто говорю.   -  person Tehscript    schedule 29.07.2017
comment
@Tehscript Было так много вопросов, когда плакат заканчивает тем, что говорит Ооо - я никогда не знал, что есть функция, чтобы сделать это! что я подумал, что стоит упомянуть об этом на всякий случай. (В этом вопросе сообщение Но вам не нужно ничего делать с ячейкой, в которую вы ввели формулу. Я подумал, что они ничего не знают о COUNTA.)   -  person YowE3K    schedule 29.07.2017
comment
@ YowE3K Вы абсолютно правы. Я предложил ему решение vba и объяснил его запрос, но он не ответил впоследствии, что заставило меня подумать, что ему нужно решение с нуля, удалил мои комментарии и поделился этим с вами. В любом случае, это немного раздражает, когда люди задают вопросы и исчезают или не хотят оставлять комментарий, когда у них есть то, что им нужно.   -  person Tehscript    schedule 29.07.2017
comment
@JackArmstrong, вам не нужно использовать If IsEmpty(Range(rng, rng.End(xlDown))) = True Then, это то же самое, что и If True = True Then (в любом случае для пустых ячеек). ... просто используйте If IsEmpty(Range(rng, rng.End(xlDown))) Then   -  person jsotola    schedule 30.07.2017
comment
@JackArmstrong, поясните, пожалуйста ... вы считаете все ячейки в динамическом диапазоне? вы пропускаете пустые ячейки? что определяет степень динамического диапазона?   -  person jsotola    schedule 30.07.2017
comment
@JackArmstrong Я откатил вопрос до точки, когда код не работал. Вопрос о том, почему не работает этот код, который показывает, что код действительно работает, бессмыслен для тех, у кого есть подобная проблема.   -  person YowE3K    schedule 31.07.2017
comment
@JackArmstrong. Вы понимаете, что если вы используете свою функцию, (например) вставляя формулу =countRows(B2) в ячейку C5, и она возвращает 499, потому что используются ячейки B2: B500, а затем вы вставляете несколько дополнительных строк (возможно, строки 501 и 502 ), ячейка C5 останется как 499, потому что функция зависит только от ячейки B2? Вам действительно следует передать B2: B500 (или B: B) в свою функцию, чтобы Excel знал, что ему необходимо пересчитать функцию, если что-либо в этом диапазоне изменится. (Или вы можете пометить функцию как Volatile, но это снижает производительность и не рекомендуется.)   -  person YowE3K    schedule 31.07.2017
comment
НЕ используйте голый Range(), потому что он предполагает ActiveSheet.Range(), и это может быть не вашим намерением.   -  person John Alexiou    schedule 31.07.2017

Ответы (2)


arrow_upward
1
arrow_downward

Это код, который я успешно использовал в течение многих лет на разных листах. Он обрабатывает множество ячеек, отдельных ячеек или пустых ячеек.

Public Function CountRows(ByRef r As Range) As Long
    If IsEmpty(r) Then
        CountRows = 0
    ElseIf IsEmpty(r.Offset(1, 0)) Then
        CountRows = 1
    Else
        CountRows = r.Worksheet.Range(r, r.End(xlDown)).Rows.count
    End If
End Function

Public Function CountCols(ByRef r As Range) As Long
    If IsEmpty(r) Then
        CountCols = 0
    ElseIf IsEmpty(r.Offset(0, 1)) Then
        CountCols = 1
    Else
        CountCols = r.Worksheet.Range(r, r.End(xlToRight)).Columns.count
    End If
End Function
person John Alexiou    schedule 30.07.2017
comment
РЕДАКТИРОВАТЬ: возврат функции должен быть 32-битным целым числом (тип Long) - person John Alexiou; 01.08.2017
comment
ПРИМЕЧАНИЕ. Многие другие реализации содержат голый объект Range(), который эквивалентен ActiveSheet.Range(), и это может быть нежелательным поведением. Приведенный выше код не зависит от рабочего листа. - person John Alexiou; 02.08.2017

arrow_upward
0
arrow_downward

Не совсем понятно, что вы ищете, когда вы упомянули, что в ячейках «B2: B500» есть значения, и счетчик должен вернуть 499, поскольку может быть несколько возможных сценариев:

  • Вы просто хотите подсчитать строки в диапазоне «B2: B500». Код будет:

    • Range("B2:B500").Rows.Count

  • Вы хотите подсчитать непустые ячейки в диапазоне «B2: B500». В этом случае, как предлагается в комментариях:

    • WorksheetFunction.CountA(Range("B2:B500"))

  • Как указано в вашем коде rng.End(xlDown), вы, вероятно, захотите подсчитывать непрерывные непустые ячейки, начиная с диапазона «B2» в общем диапазоне «B2: B500». Вы можете создать такую ​​функцию:

    • Public Function countRows(rng As Range) As Long
    Dim rw As Range
    For Each rw In rng
        If IsEmpty(rw) Then Exit For
        countRows = countRows + 1
    Next
End Function

    Уточнение:

    Основываясь на последующих комментариях, я подумал, что стоит объяснить, почему переменная countRows не была инициализирована добавлением строки countRows = 0.

    Некоторые языки программирования, такие как язык ассемблера, C, C ++, требуют явной инициализации. Это было специально разработано из-за философии, в которой конфликты между производительностью и безопасностью обычно разрешались в пользу производительности.

    Однако это не относится к другим языкам программирования, таким как VBA или Java.

    Что касается VBA, то при запуске макроса все переменные инициализируются значением. Числовая переменная инициализируется нулем, строка переменной длины инициализируется строкой нулевой длины (""), а строка фиксированной длины заполняется кодом ASCII 0. Переменные варианта инициализируются как пустые. Пустая переменная представлена ​​нулем в числовом контексте и строкой нулевой длины ("") в строковом контексте.

    Поэтому отдельная строка кода countRows = 0 не была добавлена ​​в приведенный выше блок кода.

    При кодировании нужно иметь в виду, что это может быть неверно для других языков.

    person curious    schedule 29.07.2017
    comment
    Также countRows никогда не инициализируется перед использованием. - person John Alexiou; 31.07.2017
    comment
    привет ja72, вы, вероятно, не тестировали код n, поэтому предполагаете, что код очень очень медленный, даже если все строки имеют данные (1048576 строк максимально возможно в текущей версии Excel), это едва ли займет секунду, чтобы подсчитать это. на вашем 2-м пункте, пожалуйста, обратите внимание на строкуPublic Function countRows(rng As Range) As Long. Значение VBA по умолчанию для Long равно 0, поэтому countRows = 0 не требуется - person curious; 31.07.2017
    comment
    Для кода, который может вызывать CountRows() несколько раз, задержка складывается. Не понимаю, почему бы не использовать End(xlDown). Я считаю дурным тоном полагаться на значения компилятора по умолчанию. Даже с C#, который требует значений по умолчанию, равных нулю, компилятор принудительно устанавливает значение перед использованием. - person John Alexiou; 31.07.2017
    comment
    На самом деле я начал с End (xlDown), но он оказался излишне длинным, поэтому я выбрал цикл short n sweet For Each, поскольку нет заметной разницы в производительности. Кстати, я взглянул на ваш пост, он хорош, требуется всего несколько улучшений, чтобы в крайних случаях не было ошибок. Зарегестрируйтесь по поводу вашего беспокойства по поводу инициализации, я добавил свое объяснение в ответ - person curious; 01.08.2017