Собственная функция поиска тормозит Эксель

[Maxx]

Добрый день!

Смысл в следующем:
- на листе Data есть список котировок ценных бумаг
- на листе Trading с помощью собственной функции поиска, помещенной в отдельном модуле, формируется таблица.

Функция:

Function mySearch(Table As Range, StartSearchDate As Variant, SearchDate As Variant, SearchStock As String, SearchPrice As String)

Dim i, r As Long
Dim iCount, SearchColumn As Integer
          
If SearchPrice = "OPEN" Then SearchColumn = 3
If SearchPrice = "HIGH" Then SearchColumn = 4
If SearchPrice = "LOW" Then SearchColumn = 5
If SearchPrice = "CLOSE" Then SearchColumn = 6

For i = StartSearchDate To Table.Rows.Count
 If Table.Cells(i, 2) <> SearchDate Then
  Exit For
 Else:
  If Table.Cells(i, 1) = SearchStock Then
   mySearch = Table.Cells(i, SearchColumn): Exit For
  End If
 End If
Next i

End Function

Все бы ничего, но во вложении я прикрепил всего лишь часть котировок. На самом деле на листе Data около 300 000 строк, да и на листе Trading надо формировать таблицы по 16 бумагам.
И получается, что при редактировании любой ячейки этой книги (да и другой тоже), моя функция начинает все пересчитывать, что занимает время.
Как оптимизировать функцию, или как сделать, чтоб она срабатывала только при изменении данных на листе Trading???

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Row > 1 And Target.Column > 1 Then
 mySearch
 End If
End Sub

ругается.

[doober]

Возможно так,что бы не отключать и подключать пересчет формул

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "Trading" And Target.Row > 1 And Target.Column > 1 Then
Moshno = True
Else
Moshno = False
End If
End Sub

Public Moshno As Boolean

Function mySearch(Table As Range, StartSearchDate As Variant, SearchDate As Variant, SearchStock As String, SearchPrice As String)
If Moshno = False Then Exit Function
Dim i, r As Long
Dim iCount, SearchColumn As Integer
---------------------------------------
End Function

[Maxx]

Разместил

Public Moshno As Boolean

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "Trading" And Target.Row > 1 And Target.Column > 1 Then
Moshno = True
Else
Moshno = False
End If
End Sub

в модуле Эта Книга, а
функцию оставил где была (в модуле1).

Получилось следующее:
при редактировании любой ячейки Книги, пересчет все равно происходит, но функция уже вставляет не выбранные с Листа Data значения, а нули

[doober]

Public Moshno As Boolean

Разместите в Модуле с функцией,а не в модуле с книгой

If Sh.Name = "Trading" And Target.Row > 1 And Target.Column > 1 Then

разрешаете работу с второй строки и второго столбца

Если

 If Sh.Name = "Trading"  And  Target.Cells.Count  >1 Then

разрешает работу,если выбрано более одной ячейки

[EducatedFool]

Сообщение от doober

Public Moshno As Boolean

Разместите в Модуле с функцией,а не в модуле с книгой

Даже в этом случае функция будет запускаться.
Другое дело, что она тут же будет заканчивать свою работу, возвращая 0...

Я бы порекомендовал использовать один из сделующих вариантов:

1) Временно отключать автоматический пересчёт формул
2) Заменить пользовательскую функцию стандартными (ВПР + СМЕЩ) - наверняка, работать будет быстрее.

[Maxx]

Даже в этом случае функция будет запускаться.
Другое дело, что она тут же будет заканчивать свою работу, возвращая 0...

, и возвращает очень долго!

1) Временно отключать автоматический пересчёт формул

я не умею. Как это сдолать?
Разобрался Application.Calculation = xlManual

2) Заменить пользовательскую функцию стандартными (ВПР + СМЕЩ) - наверняка, работать будет быстрее.

Думаю, так и будет. Но если посмотреть вложение в первом посте, то с помощью (ВПР + СМЕЩ + ИНДЕКС + ПОИСКПОЗ + еще всяких там функций) я не смог построить поиск Весь день промучился!

[Maxx]

Сделал так:
модуль Листа Trading

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Row > 2 And Target.Column = 1 Then
  Moshno = True
 Else:
  Moshno = False
 End If
End Sub

Модуль 1

Public Moshno As Boolean
Function mySearchUP(Table As Range, StartSearchDate As Variant, SearchDate As Variant, SearchStock As String, SearchPrice As String)

If Moshno = False Then Exit Function

Application.ScreenUpdating = False

Dim i, r As Long
Dim iCount, SearchColumn As Integer
          
If SearchPrice = "OPEN" Then SearchColumn = 3
If SearchPrice = "HIGH" Then SearchColumn = 4
If SearchPrice = "LOW" Then SearchColumn = 5
If SearchPrice = "CLOSE" Then SearchColumn = 6

For i = StartSearchDate To Table.Rows.Count
 If Table.Cells(i, 2) <> SearchDate Then
  Exit For
 Else:
  If Table.Cells(i, 1) = SearchStock Then
   mySearchUP = Table.Cells(i, SearchColumn): Exit For
  End If
 End If
Next i
Application.ScreenUpdating = True

End Function

Теперь всё работает "0" возвращается быстро!!!

Но вопрос с формулами ВПР и пр. остается открытым, хотя я думаю - это сложно!

EducatedFool, doober СПАСИБО!

[Maxx]

И все же я сделал это формулами.
Вот решение, кому интересно: