Мне потребовалось написать функцию для использования в формулах Excel. В функцию передается диапазон - несколько ячеек, находящихся в одной строке. Функция должна произвести сортировку числовых значений по возрастанию и затем вычислить среднее значение разностей (интервалов) между соседними ячейками. Вот исходник:
Function MediumIntervalCalculation(rng As Range) As Single
Dim r As Integer, c As Integer, nc As Integer
nc = rng.Columns.Count 'количество колонок в rng
r = rng.Row 'номер строки диапазона
c = rng.Column 'номер первой колонки диапазона
With rng.Parent
'сортировка значений в ячейках диапазона
Dim notSorted As Boolean, n As Integer, t As Variant, t1 As Variant, am As Integer
Do
notSorted = False 'предположение, что все отсортировано
For n = 0 To nc - 2
If .Cells(r, c + n).Value > .Cells(r, c + n + 1).Value Then
notSorted = True 'требуется перестановка
t = .Cells(r, c + n).Value
t1 = .Cells(r, c + n + 1).Value
.Cells(r, c + n).Value = t1 !!!!!
.Cells(r, c + n + 1).Value = t !!!!!
End If
Next n
Loop While notSorted
'вычисление среднего значения
t = 0
am = 0
For n = 0 To nc - 2
If .Cells(r, c + n).Value > 0 Then
t = t + (.Cells(r, c + n + 1).Value - .Cells(r, c + n).Value)
am = am + 1
End If
Next n
End With
t = IIf(am, t / am, 0)
MediumIntervalCalculation = t
End Function
Функция не работает. Конкретно, не работают операторы отмеченные воскл. знаками. При исполнении функции по шагам - все отлично, пока дело не доходит до оператора записи значения (t1 или t) в ячейку. На этом операторе VBA просто прекращает выполнение программы, как-будто нажата кнопка Reset, никаких сообщений об шибках не выдается. Значения всех переменных в ходе выполнения программы корректны, t и t1 содержат правильные данные из диапазона. Если выкинуть цикл сортировки, все работает нормально. Кто-нибудь может объяснить что это за глюк и как с ним бороться?
Примечание:
Вы невнимательно прочитали мое сообщение. Восклицательные знаки не являются частью программы, ими отмечены проблемные операторы.
Примечание:
Забыл указать версии ПО, может это важно. Windows 7, Microsoft Office Professional Plus 2010, Microsoft VBA 7
Примечание:
Задача действительно простая да и реализация тоже не сложная. Предпочитаю не использовать чужой код, если тоже самое не сложно сделать самому. Но все равно LeoCats спасибо за пример использования встроенной сортировки, мне, честно, было лень разбираться с ней в мелкософтовском, традиционно убогом, хелпе. Но среднее вы посчитали не правильно, мне надо по другому, пузырьковая сортировка тоже не сработает - она выпустит только один "пузырек" :). Имеется странность в использовании вашей функции - она срабатывает через раз. Т.е. я записал ее в формулу ячейки, указал диапазон, допустим 4 ячейки, далее изменяю данные в ячейках диапазона - функция вызывается, происходит сортировка, на следующее изменение нет реакции, на следующее - опять работает. И так далее через раз.
Примечание:
По поводу пузырьков беру свои слова обратно, оно все же сработает, но медленнее, чем у меня.
Примечание:
Это к вопросу отношения не имеет, так в порядке дискуссии. В цикле вычисления среднего у меня стоит оператор If .Cells(r, c + n).Value > 0 Then, т.е. интервалы считаются только между ненулевыми значениями, в переменной am накапливается количество интервалов - оно не обязательно равно rng.Count - 1. В сортировке у меня появилось две переменных в процессе отладки, чтобы видеть где что. Меня смутило два цикла For: при количестве ячеек в диапазоне равном n цикл будет выполняться n * n раз независимо от того отсортированы данные или нет. В моем (стандартном) алгоритме сортировки циклы повторяются пока данные не будут отсортированы, не больше. Если данные изначально отсортированы, цикл выполнится всего один раз. С VBA в том числе и применительно к Excel'ю работаю всего неделю, вижу, что он не далеко ушел от своего дедушки QBasic'а :).
Спасибо за Application.Volatile
Примечание:
Мне немного странно читать ваши комментарии по поводу моего варианта алгоритма сортировки. Собственно этот алгоритм придуман не мной - это канонический вид алгоритма пузырьковой сортировки. Действительно есть другие алгоритмы, однако этот самый простой и легко воспроизводимый, но, как правило, и самый медленный. В отличие от вашего варианта, где просто проверяются все данные независимо от их организации (отсортированы уже, или еще нет) здесь имеется "инспектор" - переменная notSorted, которая устанавливается в True если есть хотя бы один обмен содержимого ячеек. Если данные отсортированы, notSorted останется в состоянии False и цикл закончится.
Удачи!
RPI.su - самая большая русскоязычная база вопросов и ответов. Наш проект был реализован как продолжение популярного сервиса otvety.google.ru, который был закрыт и удален 30 апреля 2015 года. Мы решили воскресить полезный сервис Ответы Гугл, чтобы любой человек смог публично узнать ответ на свой вопрос у интернет сообщества.
Все вопросы, добавленные на сайт ответов Google, мы скопировали и сохранили здесь. Имена старых пользователей также отображены в том виде, в котором они существовали ранее. Только нужно заново пройти регистрацию, чтобы иметь возможность задавать вопросы, или отвечать другим.
Чтобы связаться с нами по любому вопросу О САЙТЕ (реклама, сотрудничество, отзыв о сервисе), пишите на почту [email protected]. Только все общие вопросы размещайте на сайте, на них ответ по почте не предоставляется.