» Excel VBA (часть 2)

Вопрос к профи, хочу понять одну вещь.
Щас вот заюзал в рабочих целях Асап Утилит, функция -
выделение строк по содержанию в них определенного текста по маске с последующим удалением...
Вся процедура на 20000 проверяемых строках заняла порядка 20мин., и вот что мне непонятно...
Почему так? Т.е. в связи с чем такая большая во времени обработка данных, дело в процессоре или он выворачивает так много в оперативку?
Тогда момент!
Всю эту процедуру, я могу разложить на следующие составляющие:
1. Поиск строк по ключевому слову (Эксель для этих же 20000 строк сделал это за 1сек.!)
2. Копирование адресов строк содержащих ключевые слова (учитывая что Эксель их уже нашел и отобразил, я так понимаю это операция еще на 1сек.
просто в Экселе нет такой возможности отсюда и потребность в доп.утилитах)
3. Выделение строк по списку - адресам (что-то мне подсказывает, что это тоже мгновенная процедура)
4. Удаление выделенных строк (тут опять же в самом Экселе легко убедится, что если
строки нефильтрованы, то 20000ед. исчезает также за 1сек., в фильтре бывает чуть дольше)

Таким образом! Что мешало этому разработчику сформировать алгоритм согласно описанной схеме:
-использовать ПОиск Эксель
-через команду макроса запомнить найденное, выделить и удалить. Все! 4 секунды!!!

Нахрена он что-то там аккумулировал 20 минут в моей оперативке??? с очевидной регрессией скорости к завершению, т.е. переполнении памяти?

Ведь однозначно глупо выглядит, что Эксель 2007 допуская более 1млн. строк, посредством доп.утилиты не может за разумный временной промежуток выполнить примитивную процедуру пакетной обработки данных на типичном по мощностям компьютере на 20000строках вывода, что составляет всего лишь 2% от его потенциала!!! Разве не жесть?!
А если мне потребуется это выполнить на полумиллионном списке?!

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

Frantishek
думаю проблема связана с отсутствием в VBA возможности использования некоторых функциональных инструментов Excel. Функция написанная на VBA проигрывает по времени точно такой же, но встроенной в Excel функции, на порядки (!).
)))))))))))))))))))))))))))))))))))))))))))))))))))РРРРРРРПривет!!!!!!!!!!!!!!))))))))))))))))

visual73, а конкретные примеры, кроме Find(), есть? Я как-то писал функцию "расширенный vlookup" (заказ такой был - ну не хотела бухгалтерша сложные формульные конструкции пользовать) - результирующая функция работала НЕ медленнее штатного vlookup'а. А код ее был элементарен, что-то вроде перебора всех ячеек одного диапазона, если там найдено некое значение - возврат аналогичной ячейки из второго диапазона (т.е. по сути тот же vlookup).

Есть форма на которой находятся ComboBox1 и ComboBox2. ComboBox1 заполняется данными из столбца листа. Как сделать что-бы ComboBox2 заполнялся данными в зависимости от выбора ComboBox1. Тоесть если я в ComboBox1 выбрал значение "Служба линий" то в ComboBox2 сформировался список всех работников Службы линий.
Если я в ComboBox1 выбрал значение "Служба охраны труда" то в ComboBox2 сформировался список всех работников Службы охраны труда.
Спасибо

ZlydenGL
конкретный пример в книге Уокенбаха! Он там одну функцию написал дублирующую и он её сравнивает по времени выполнения со стандартной Excel. Это именно его заключение я и озвучил. Единственное - сразу не нашёл на какой странице это у него написано, а искать щас нету времени, сам погляди.

Люди, помогите решить проблему.
Нужно чтобы, когда пользователь делал вставку скопированных данных из другой книги, принудительно делалась бы "Специальная вставка" (значения), вместо обычного копирования.

Заранее спасибо

Alexikit, нет - так как Вы предлагаете, не пойдет. Дело в том, что если первый параметр остается невыбранным, то и соответствующий ему столбец (например А) тоже будет пустым. то есть, если задавать абсолютную адресацию, будет чередование пустых столбцов и заполненных (тех, которые выбраны). с этим невозможно работать. нужно чтобы независимо от того, какие столбцы выбраны, заполнялись столбцы попорядку. то есть если выбраны три любых параметра - заполнить на новом листе столбцы (А:С), а не например, А, D,F. так я точнее сформулировал задачу?

flamencero
ну Alexikit как раз и дал вам относительную ссылку, и при переборе будут заполняться именно без пустых столбцов
Cells(1, i)
В чем проблема то?

flamencero
Что бы было более понятно, A1 это Cells(1, 1), а C5 - Cells(5, 3).
Если вместо цифры подставить i. то можно составлять цикл в котором писаться будут только выбранные столбцы по порядку.

Frantishek
Скорость выполнения описанной Вами задачи средствами VBA напрямую зависит от подхода к решению задачи и применяемого метода.

Цитата:

Функция написанная на VBA проигрывает по времени точно такой же, но встроенной в Excel функции, на порядки (!)

Совершенно верно. Т.к. во-первых, в Excel используется язык "C", во-вторых (и это главное), вся работа ведется в памяти компьютера, не касаясь ячеек рабочего листа Excel. При грамотном и рациональном программировании, скорость выполнения Вашей задачи, процедурой VBA Excel можно также уменьшить на порядки(!).
Могу продемонстрировать на примере. Пусть дан файл (см. пример) 20000 строк Х 10 столбцов. И пусть требуется оставить только те строки, в которых значение в столбце "A" удовлетворяет маске: "*1*1*" (т.е. имеющие не менее 2-х единиц). Остальные удалить.
Посмотрите Этот пример. Запустите макрос "Main". Обратите внимание на время выполнения процедуры.

Люди помогите!
Есть форма на которой находятся ComboBox1 и ComboBox2. ComboBox1 заполняется данными из столбца листа. Как сделать что-бы ComboBox2 заполнялся данными в зависимости от выбора ComboBox1. Тоесть если я в ComboBox1 выбрал значение "Служба линий" то в ComboBox2 сформировался список всех работников Службы линий.
Если я в ComboBox1 выбрал значение "Служба охраны труда" то в ComboBox2 сформировался список всех работников Службы охраны труда.
Спасибо

Igor_Paseka
А где брать список для формирования ComboBox2? Какова структура данных на листе?

Список для формирования ComboBox2 брать из электронной таблицы на том же листе где и ComboBox1. (Есть список работников предприятия с указанием всех даных о них, в ComboBox1 хочу сделать выбор службы где они работают, а в ComboBox2 соответственно всех работников из выбраной службы)

Igor_Paseka
Посмотрите Пример. Думаю, комментарии не требуются.

Цитата:

Посмотрите Пример. Думаю, комментарии не требуются.

Спасибо! То что нужно. Правда в коде нужно немного разобраться что-бы написать прод свою прогу.
Не могу понять, что делает первая процедура UserForm_Initialize

SAS888
Т.е. при желании все можно решить, подскажите а как с Access, если расчеты производить в нем, аналогичные Excel, есть резерв ускорения?

Igor_Paseka

Цитата:

что делает первая процедура UserForm_Initialize

Формирует значения для ComboBox1 из уникальных значений в столбце "A".
Конечно, если у Вас где-то на листе уже есть такой список, то эту процедуру можно не выполнять, а в свойствах ComboBox1 задать диапазон RowSource. Но такой подход не позволит произвольно добавлять новые записи.

Frantishek
По работе с Access ответ дать не могу (не берусь). Задайте вопрос здесь.

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

Есть книга, в книге 2 листа.

В 1 листе данные, во втором план графика, что нужно:

Когда на 2 листе выбираешь по выпадающему списку любой из пунктов, то в зависимости от выбранного пункта берутся данные из первого листа и автоматически справа прописываются данные (не одна ячейка, а группа ячеек, их всего 62).

доброго времени суток.
Прошу подсказать, есть ли возможность реализации следующей [more=задачи]. Имеются табличные отчеты, следующего вида
инфа.zip


Естественно, размер их будет разный (больше или меньше и колонок и строк). Можно ли макросом:
1) убрать все колонки "СуммаАванса", оставив только последнюю, в которой итоговые значения?
2) если колонка СуммаДолга имеет итоговое значение 0 - нафиг
3) если итоговое значение долга по потребителю = 0, удаляем его, чтоб не мешал (то же производим, если долгов нет по отрасли или городу)
3) разделить обрабатываемый файл на 3 - в одном будут потребители, имеющие и долги и авансы (итоговые значения), во втором - долги только за текущий год или месяц, в третьем - имеющие долги предыдущих лет, независимо от задолженности в текущем периоде (не обязательно, но очень желательно)

и желательно чтобы макрос запускался извне и натравливался на папку с отчетами и обрабатывал их оперативно.
Может подскажете, что почитать, где копать?[/more]

PerpleXOR
Вообще файл Excel из вне можно открыть программой на VB (внешней, насчет макросов не знаю). Соответственно эта программа может и создавать файлы, и записывать в них листы и т.д.
Почитать можно здесь
http://msdn.microsoft.com/ru-ru/library/bb386107.aspx
Но возможно есть более простые пути.

Нужна помощь! Есть форма с TextBox. Как сделать что-бы данные которые вводяться в
TextBox сохранялись. Например: я ввожу в TextBox "Киев", потом следующий раз мне нужно ввести "Харьков", на третий раз например мне нужно ввести опять "Киев" и я хочу выбрать его из всех ранее введеных городов. Как это сделать? Где будут сохранятся введеные данные?

выкладываю файл в котором сделал все что мог .. в принципе все кроме поиска о котором прошу (
на первой вкладке : вводится пара фамилий ... в текстбоксах вводится если фамилии нет еще в списке в сомбобоксе выбирается из списка если существует фамилия ! Кнопка ЗАПИСАТЬ добавляет фамилии из полей в соответствующие колонки ( текстбоксы имеют приоритет перед комбо)
на второй вкладке : как раз и должен быть нужный мне поиск ( под кнопкой ПОИСК ВАРИАНТОВ расположен лэйбл3 в нем и должны выводится варианты ! пример запроса и ответов на него на самом листе справа от столбиков фамилий !
ПОМОГИТЕ пожалуйста с этим самым поиском!!!

файл с примером
http://rapidshare.com/files/299110332/2.zip.html

ps сорри не знаю как сюда файл прикрепить (

PerpleXOR

Цитата:

Sub HideTrash()
Dim c As Integer
Dim r As Integer
Dim i As Integer

'1)
If Cells(9, 256) <> Null Then c = 256 Else c = Cells(9, 256).End(xlToLeft).Column
For i = c - 2 To 4 Step -2
Cells(1, i).Select
Selection.EntireColumn.Hidden = True
Next i

'2)
If Cells(65536, 2) <> Null Then r = 65536 Else r = Cells(65536, 2).End(xlUp).Row
For i = c - 3 To 3 Step -2
If Cells(r, i) = 0 Then
Cells(1, i).Select
Selection.EntireColumn.Hidden = True
End If
Next i

End Sub

не вполне ясно с потребителями/городами, так и выглядит столбец 2 в реальных условиях?
по поводу открытия всех файлов папки и разбивки (рекомендую три листа в одной книге, но дело ваше) напишу позже.

вопрос к знатокам, как вызвать процес в экселе устанавливающий позиции скроллбаров листа по последнему непустому столбцу/строке? грубо говоря, после удаления значений, скроллбар все-равно достигает своей прежней крайней точки, но после команды save или preview (или еще каких), устанавливает новую крайнюю точку. как его вызвать, ума не прилажу.

и еще, в обработке события OnSelect, можно ли узнать, было ли предшевствующее событие Change?

PerpleXOR

файл

Можно ещё улучшить, подправить и подчистить, наверное. Но можно работать и так...

Нужна помощь! Есть форма с TextBox. Как сделать что-бы данные которые вводяться в
TextBox сохранялись. Например: я ввожу в TextBox "Киев", потом следующий раз мне нужно ввести "Харьков", на третий раз например мне нужно ввести опять "Киев" и я хочу выбрать его из всех ранее введеных городов.
Как это сделать?
Может нужно использовать не TextBox а что-то другое?
Где будут сохранятся введеные данные?

Igor_Paseka

Лучше использовать для этих целей комбобокс. Например, так, как в примере ниже.

(Код модуля формы, на форме 2 элемента - ComboBox1 и CommandBatton1)


Код: Dim varCBlist() As Variant

Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then 'По нажатию клавиши "Enter" (KeyCode = 13) запоминаем значение, введённое в комбобокс, в его списке
With ComboBox1
If ComboBox1.MatchFound = False Then _
.AddItem (.Text) 'Если такого значения в списке ещё нет, добавляем его в список
ReDim varCBlist(.ListCount - 1)
varCBlist = .List
End With
End If
End Sub

Private Sub CommandButton1_Click()
'Если есть желание поместить список на лист Excel, жмём кнопку
Range(Cells(1, 1), Cells(UBound(varCBlist) + 1, 1)) = varCBlist
End Sub

vlth
Спасибо БОЛЬШОЕ! Помог.

ребят помогите пожалуйста с задачкой,190 страниц ниосилил..
на VBA попросили написать :
Дан файл, содержащий текст на русском языке. Подсчитать количество слов, начинающихся и заканчивающихся на одну и ту же букву и выдать эти буквы с указанием соответствующего количества слов.
ну можно сделать чтоб не выдавало эти буквы,а чтоб слова которые начинаются на одну и ту же букву выводились справа от всех слов.

buburuzecakl

В модуле VBA Excel:


Код: Option Explicit
Option Compare Text
Dim lngLastRow As Long
Sub test()
Dim strText As String, strLine As String, i As Integer
Dim strFile As String

lngLastRow = 0
With Application.FileDialog(msoFileDialogOpen)
.Filters.Clear
.Filters.Add "Файл с текстом для обработки", "*.txt"
.Title = "Поиск слов, начинающихся и заканчивающихся на одну букву"
If .Show = False Then Exit Sub
strFile = .SelectedItems(1)
End With
With ThisWorkbook.Worksheets(1)
Range(.Columns(1), .Columns(2)).Clear
End With
DoEvents
Open strFile For Input As #1
Do Until EOF(1)
Line Input #1, strLine
strText = strText & " " & strLine

'Для обработки больших файлов частями по 3000 строк раскомментировать код ниже
'-----------------------------
' i = i + 1
' If i = 3000 Then
' prcWordsCount strText
' i = 0
' strText = " "
' End If
'-----------------------------
Loop
Close #1
prcWordsCount strText
MsgBox "В файле искомых слов: " & lngLastRow
End Sub

Sub prcWordsCount(strInput As String)
Dim astrWords() As String, astrSigns() As String * 1, i As Long, j As Long

If Len(strInput) = 0 Then Exit Sub

strInput = Replace(strInput, ".", " ")
strInput = Replace(strInput, ",", " ")
strInput = Replace(strInput, ":", " ")
strInput = Replace(strInput, ";", " ")
strInput = Replace(strInput, "!", " ")
strInput = Replace(strInput, "?", " ")
strInput = Replace(strInput, "—", " ")
strInput = Replace(strInput, "-", " ")
strInput = Replace(strInput, "+", " ")
strInput = Replace(strInput, "<", " ")
strInput = Replace(strInput, ">", " ")
strInput = Replace(strInput, "%", " ")
strInput = Replace(strInput, "#", " ")
strInput = Replace(strInput, "«", " ")
strInput = Replace(strInput, "»", " ")
strInput = Replace(strInput, "(", " ")
strInput = Replace(strInput, ")", " ")
astrWords = Split(strInput, " ")

With ThisWorkbook.Worksheets(1)
For i = LBound(astrWords) To UBound(astrWords)
astrWords(i) = Trim(astrWords(i))
If Len(astrWords(i)) > 1 Then
If Asc(astrWords(i)) > 191 Then
If Left(astrWords(i), 1) = Right(astrWords(i), 1) Then
ReDim Preserve astrSigns(j)
astrSigns(j) = Left(astrWords(i), 1)
j = j + 1
.Cells(j + lngLastRow, 2) = astrWords(i)
End If
End If
End If
Next i
j = lngLastRow + j
For i = lngLastRow + 1 To j
.Cells(i, 1) = UCase(astrSigns(i - lngLastRow - 1))
Next i
End With
lngLastRow = j
End Sub

vlth
спасибо большое