Nano Hash - криптовалюты, майнинг, программирование

Vlookup в VBA работает очень медленно, есть идеи?

Я создал следующий макрос, чтобы иметь лист под названием «Макрон», который просматривает разные ячейки и листы в моей книге, оттуда я хочу создать макрос, который находит значение на основе имени, а не конкретной ячейки ( поскольку код VBA не обновляется, если я добавляю еще одну ячейку и т. д., мне нужно переписать все ссылки на макросы, что занимает очень много времени).

Поэтому я решил работать с функцией application.Vlookup в своем коде, но теперь я вижу, что это происходит очень медленно по сравнению с просмотром только внутри ячеек.

Это происходит постоянно, или что-то не так с моим кодом, который можно было бы обновить или сделать чище, чтобы он работал быстрее.

Вот мой код макроса:

Sub Motesbokning_saljare()
Dim OutApp As Object
Dim OutMail As Object
Dim a As String
Dim o As String
Dim a1 As String
Dim o1 As String
Dim strbody As String
Dim ws As Worksheet
Dim ws1 As Worksheet

' ä
a = Chr(228)
'å
a1 = Chr(229)
'ö
o = Chr(246)
'Ö
o1 = Chr(214)

With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(1)
Set ws = Sheets("Macron")
Set ws1 = Sheets("Offert")

On Error Resume Next
With OutMail
    .To = Application.VLookup("kundEpost", ws.Range("A:C").Value, 3, False)
    .Subject = Application.VLookup("partnerNamn", ws.Range("A:C").Value, 3, False) & ", " & Application.VLookup("kundFulltNamn", ws.Range("A:C").Value, 3, False)
    .location = "" & Application.VLookup("kundAdress", ws.Range("A:C").Value, 3, False) & ", " & Application.VLookup("kundPostnr", ws.Range("A:C").Value, 3, False) & ", " & Application.VLookup("kundPostort", ws.Range("A:C").Value, 3, False)
    .Body = "Projekttyp: " & Application.WorksheetFunction.VLookup("moteProjekttyp", ws.Range("A:C").Value, 3, False) & vbNewLine & "Fastighetstyp: " & Application.WorksheetFunction.VLookup("moteFastighetstyp", ws.Range("A:C").Value, 3, False) & vbNewLine & vbNewLine & "Portkod: " & _
    Application.VLookup("motePortkod", ws.Range("A:C").Value, 3, False) & vbNewLine & "Telefon: " & Application.VLookup("kundTelefon", ws.Range("A:C").Value, 3, False) & vbNewLine & "V" & a1 & "ning: " & Application.VLookup("moteVaning", ws.Range("A:C").Value, 3, False) & vbNewLine & vbNewLine _
    & "Upphandlingsunderlag: " & Application.VLookup("moteUpphandlingsunderlag", ws.Range("A:C").Value, 3, False) & vbNewLine & Application.VLookup("moteUpphandlingsunderlagTyp", ws.Range("A:C").Value, 3, False) & vbNewLine & vbNewLine & "K" & o & "rtid: " & Application.VLookup("moteKortid", ws.Range("A:C").Value, 3, False) & " minuter" _
    & vbNewLine & "GPS URL: " & Application.VLookup("moteGPSurl", ws.Range("A:C").Value, 3, False) & vbNewLine & vbNewLine & "K" & a & "lla: " & Application.VLookup("moteKalla", ws.Range("A:C").Value, 3, False) & vbNewLine & o1 & "vrigt: " & Application.VLookup("moteOvriginfo", ws.Range("A:C").Value, 3, False) & vbNewLine & vbNewLine & "Referenskund i n" & a & _
    "romr" & a1 & "de: " & vbNewLine & ws1.Range("I35").Value & ", " & ws1.Range("K35").Value & ", " & ws1.Range("M35").Value & vbNewLine & ws1.Range("I36").Value & ", " & _
    ws1.Range("K36").Value & ", " & ws1.Range("M36").Value & vbNewLine & ws1.Range("I37").Value & ", " & ws1.Range("K37").Value & ", " & ws1.Range("M37").Value & vbNewLine & _
    ws1.Range("I38").Value & ", " & ws1.Range("K38").Value & ", " & ws1.Range("M38").Value & vbNewLine & ws1.Range("I39").Value & ", " & ws1.Range("K39").Value & ", " _
    & ws1.Range("M39").Value
    .Start = Application.VLookup("moteDatum", ws.Range("A:C").Value, 3, False) + Application.VLookup("moteKlockslag", ws.Range("A:C").Value, 3, False)
    .ReminderMinutesBeforeStart = Application.VLookup("moteReminder", ws.Range("A:C").Value, 3, False)
    .Duration = Application.VLookup("moteTidsatgang", ws.Range("A:C").Value, 3, False)
    .Recipients.Add Application.VLookup("moteLaggTillDeltagare", ws.Range("A:C").Value, 3, False)
    .Categories = Application.VLookup("moteKategori", ws.Range("A:C").Value, 3, False)
    .Display
End With

Set OutMail = Nothing
Set OutApp = Nothing

With Application
    .EnableEvents = True
    .ScreenUpdating = True
End With

End Sub

Спасибо за любую помощь, которая может быть предложена.

С уважением, Агатонсакс.

excel macros vba
25.02.2016

  • Вы выполняете много vLookups по всему столбцу. Если у вас много данных, линейный поиск будет очень медленным. 25.02.2016
  • Вау, это огромный блок. У вас всегда есть данные поиска в столбце a и возвращаемое значение в столбце c. Попробуйте перебрать столбец один раз, сохранив любой найденный индекс строки в массив. Затем заполните тело письма в соответствии с этим массивом. Например: первой записью массива будет строка, в которой вы найдете строку kundEpost в столбце a и т. д., а затем первая часть тела письма будет значением ячейки (x, 3), где x обозначает первую запись массива. 25.02.2016
  • Привет, если бы у меня, например, был A1: C200, это работало бы намного быстрее? Вместо того, чтобы заглянуть внутрь всей колонки? 25.02.2016
  • @AgatonSaxx, это, вероятно, тоже поможет 25.02.2016
  • EngJon, спасибо за ваш быстрый ответ, я не очень хорошо знаком с массивами, не могли бы вы дать мне пример кода, чтобы показать, как это будет работать? Вы хотите сделать все это в коде VBA или внутри фактического листа? Моя проблема с использованием ячеек заключается в том, что каждый раз, когда кто-то придумывает новый атрибут и я добавляю его на лист (макрон), тогда все ссылки на разные ячейки испорчены, и мне нужно снова войти в код VBA, чтобы перейти от например Диапазон(B1).Значение в Диапазон(B2).Значение. Но, возможно, вы хотите сделать все это в реальном коде vba каким-то умным способом? 25.02.2016
  • У меня также была идея сделать следующее, но на самом деле это не работает так, как я хочу. Если я объявлю диапазон следующим образом: Dim rng As Range Set rng = ws.Range(A1:C80).To = Application.VLookup(kundEpost, ws.Range(rng).Value, 3, False) Но тогда это не так. загляните внутрь любой из ячеек, которые я объявил на другом листе. Все просто стало пустым, что-то не так с моим rng или с Vlookupcode? 25.02.2016
  • Использование функции НАЙТИ вместо ВПР было бы намного быстрее — msdn. microsoft.com/en-us/library/office/ff839746.aspx 25.02.2016
  • Для вашего последнего комментария: вам нужно использовать только rng, а не ws.Range(rng).Value. Таким образом, это может сработать. На мое предложение: я дам вам код в ответ, чтобы показать вам, что я имел в виду. 25.02.2016
  • Спасибо всем за ответы! EngJon, я бы с удовольствием взял у вас несколько примеров кода. Теперь, когда я указал rng и искал только в ячейке A1 - C80, код работает намного быстрее. Но я хотел бы увидеть ваши примеры, чтобы увидеть, может ли он быть еще быстрее. 25.02.2016
  • Еще одна вещь, связанная с этой темой, если кто знает. Можно ли объявить формулу, говорящую что-то вроде этого: подсчитать все строки, в которых есть текст, и вернуть это значение внутри другой ячейки. Я хочу, чтобы мой диапазон автоматически менялся, когда я добавляю новую строку или добавляю текст в другую ячейку. Вы понимаете, что я имею в виду? 25.02.2016
  • Для последней строки есть простой код VBA. В своем ответе я даже использовал один из возможных способов. Вы можете написать пользовательскую функцию (Public Function lastRow(column As String) As Long ... End Function с этой строкой, определенной как оператор возврата, принимающий column в качестве параметра) и использовать ее внутри ячеек Excel. 25.02.2016
  • VLOOKUP в версиях Excel с (я думаю) 2010 года достаточно умен, чтобы (внутренне) пересекать весь столбец с используемым диапазоном до выполнения поиска. Так что ссылка на всю колонку не должна иметь большого значения. 25.02.2016
  • Возможно, вам лучше опубликовать это в Code Review. Это форум для улучшения кода. 12.09.2016

Ответы:


1

Чтобы пропустить огромное количество vlookup (в сочетании с тем, что они предназначены для целых столбцов, а не для меньших определенных диапазонов), при использовании VBA я предлагаю использовать одну итерацию по столбцу A, чтобы определить содержимое вашего тела почты. Для этого вам нужно 2 массива. Один для слов, которые вы ищете в столбце A (searchWords), и один для необходимых значений в столбце C (mailContents). Мой подход будет следующим («...», помечая пропуски, которые необходимо заполнить существующим кодом):

Sub Motesbokning_saljare()
    ...
    Set ws = Sheets("Macron")
    Set ws1 = Sheets("Offert")

    Dim searchWords(1 To 100) As String
    'Fill all the words that need to be searched:
    searchWords(1) = "kundEPost"
    searchWords(2) = "partnerNamn"
    searchWords(3) = "kundFulltNamn"
    ...
    Dim mailContents(1 To 100) As String
    Dim i As Integer
    Dim j As Integer
    Dim LastRow As Long
    With ws
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    For i = 1 To LastRow
        'i is for the rows of the excel sheet
        For j = 1 To UBound(searchWords)
            'j is for the lookup in the array searchwords
            If ws.Cells(i, 1) = searchWords(j) Then
                mailContents(j) = ws.Cells(i, 3)
            End If
        Next j
    Next i
    'Now, fill the mail body:
    On Error Resume Next
    With OutMail
        .To = mailContents(1)
        .Subject = mailContents(2) & ", " & mailContents(3)
        ...
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing

    ...
End Sub

Как видите, я заполнил только первые 3 слова тела письма. Вам нужно будет заполнить searchWords поиском тела письма и далее заполнить тело письма. Я также рекомендую изменить размер массивов на точное количество поисковых запросов, которые вы выполняли ранее (от 1 до 100 означает, что он может содержать до 100 записей).

25.02.2016
  • Отличный ответ, спасибо за ваш код, и я обязательно попробую это! Одна вещь, которую мне нужно знать, ячейки, в которых должны искать поисковые слова, — это лист Macron, который я объявил как Dim ws. Но где эта информация должна быть в коде? Я буду использовать макрос на другом листе, а не там, где указаны данные. 25.02.2016
  • Надеюсь, это поможет, удачи в дальнейшем ускорении вашего кода;) 25.02.2016
  • Не уверен, что я бы использовал On Error Resume Next при создании электронного письма - может быть, лучше создать все электронное письмо правильно или вообще не создавать и сообщить пользователю о проблеме. 25.02.2016
  • @DarrenBartrup-Cook Хороший вопрос, я просто лениво скопировал части исходного кода без дальнейшего понимания. Лучше бы было по-вашему. 25.02.2016
  • Я удалю возобновление при ошибке следующим в коде. Какие-нибудь советы по моему вопросу выше относительно того, как поисковые слова узнают, где искать фактические слова? 25.02.2016
  • Теперь, когда я пытаюсь запустить макрос, он говорит о следующем коде: Для j = 1 To Len(searchWords) необходимо определить переменную. Это потому, что я не использовал Set searchWords = something? 25.02.2016
  • Я не уверен, что полностью понял ваш вопрос в первом комментарии к этому ответу. Итерация всегда просматривает столбец A (ячейки (строка, столбец), поэтому столбец 1 является столбцом A) и ищет каждую строку в этом столбце (поскольку LastRow определено для последней строки в столбце A). Какую информацию вы теперь хотите получить в коде? 25.02.2016
  • Привет еще раз, теперь я получаю сообщение об ошибке при запуске кода, он ничего не говорит, кроме как когда он переходит к .To, он становится желтым, и код не запускается. Я предполагаю, что это потому, что он просматривает не тот лист, я изменил ActiveSheet на Sheets (макрон), но, похоже, это не помогает. Какие-либо предложения? 25.02.2016
  • Ищет ли он столбец А на каждом листе документа? В противном случае, я думаю, мне нужно указать, на каком листе искать? Макрос выполняется на другом листе, а не на том, где находятся данные. 25.02.2016
  • Теперь он говорит, что объект не поддерживает свойство или метод и указывает на .To = mailContents(1) 25.02.2016
  • Хм, я никогда не заполнял тело письма через Excel, поэтому я просто скопировал ваш существующий код для части построения тела письма. Я изменил код, чтобы указать правильный лист. Вы заполнили ... части своим кодом, не так ли? 25.02.2016
  • Что ж, я согласен, это странно, теперь я все добавил, и все выглядит правильно. В поле .To он находит мой адрес электронной почты и т. Д. Но все равно говорит, что не поддерживает свойство или метод. Очень странно. Возможно, я должен использовать этот код только в части Body, а в других полях я использую свой оригинальный код с Vlookup. 25.02.2016
  • Ага, я нашел проблему. Возобновление при ошибке затем у меня есть, потому что этот макрос должен фактически открыть Outlook и создать собрание, поэтому поле .To не объявляется, пока я не открою Outlook. Теперь я понимаю, почему он там. Знаете ли вы, как я могу указать, чтобы это заполнялось автоматически вместо того, чтобы добавлять его при открытии Outlook? 25.02.2016
  • Вы можете открыть Outlook из своего кода vba (добавить ссылки в vba и затемнить объект Outlook, а затем открыть его), но я не уверен, что это поможет в вашем случае. К сожалению, я еще не использовал Excel-vba и Outlook вместе взятые. 25.02.2016
  • Хорошо, большое спасибо за вашу помощь со всем этим. Теперь думаю, что справлюсь сам :) 25.02.2016
  • EngJon, у меня есть вопрос, я использую ваш код в нескольких макросах, а иногда я выполняю несколько макросов в другом макросе. Иногда кажется, что функция ВПР не находит значения. Я предполагаю, что это потому, что он зацикливается на вещах и, возможно, заканчивается в странной позиции или что-то в этом роде. Есть ли способ сбросить цикл, чтобы он всегда начинался сверху и спускался вниз? Потому что я вижу, что когда я выполняю макрос несколько раз, он иногда пропускает некоторые поля, и когда я запускаю его снова, данные снова появляются. Любые идеи? Заранее спасибо. 25.02.2016
    • Новые материалы
    Кластеризация: более глубокий взгляд
    Кластеризация — это метод обучения без учителя, в котором мы пытаемся найти группы в наборе данных на основе некоторых известных или неизвестных свойств, которые могут существовать. Независимо от..
    Как написать эффективное резюме
    Предложения по дизайну и макету, чтобы представить себя профессионально Вам не позвонили на собеседование после того, как вы несколько раз подали заявку на работу своей мечты? У вас может..
    Частный метод Python: улучшение инкапсуляции и безопасности
    Введение Python — универсальный и мощный язык программирования, известный своей простотой и удобством использования. Одной из ключевых особенностей, отличающих Python от других языков, является..
    Как я автоматизирую тестирование с помощью Jest
    Шутка для победы, когда дело касается автоматизации тестирования Одной очень важной частью разработки программного обеспечения является автоматизация тестирования, поскольку она создает..
    Работа с векторными символическими архитектурами, часть 4 (искусственный интеллект)
    Hyperseed: неконтролируемое обучение с векторными символическими архитектурами (arXiv) Автор: Евгений Осипов , Сачин Кахавала , Диланта Хапутантри , Тимал Кемпития , Дасвин Де Сильва ,..
    Понимание расстояния Вассерштейна: мощная метрика в машинном обучении
    В обширной области машинного обучения часто возникает необходимость сравнивать и измерять различия между распределениями вероятностей. Традиционные метрики расстояния, такие как евклидово..
    Обеспечение масштабируемости LLM: облачный анализ с помощью AWS Fargate и Copilot
    В динамичной области искусственного интеллекта все большее распространение получают модели больших языков (LLM). Они жизненно важны для различных приложений, таких как интеллектуальные..