Ru-Board.club
← Вернуться в раздел «Прикладное программирование»

» Excel VBA (часть 3)

Автор: mp65
Дата сообщения: 16.08.2011 16:30
asbo
пасиб, оптимизация, туды-т её в качель
Автор: asbo
Дата сообщения: 16.08.2011 16:46
mp65, не за что.

Забыл сказать, что и к Switch это тоже относится. Она тоже все что в ней находится сразу просчитывает. А у нее аргументов дофига может быть. Поэтому для задач, критичных по быстродействию (а это, похоже, твой случай :), не рекомендуются. Запись только делают лаконичнее и все, имо... Я вообще стараюсь даже не If, а только Select Case использовать.
Автор: mp65
Дата сообщения: 16.08.2011 17:10
asbo
Да, спасибо еще раз. Я как раз хотел разные варианты реализаций на быстродействие протестить
Когда писал на C, там было как раз наоборот, всякие встроенные в оператор инструкции приводили к более оптимальному коду, поскольку компилятор пихал их в регистры.

Протестил сейчас простые двузвенные if, select case и iif
получилось по времени:
if - 1
select case - 3.06
iif - 4.74

Так что, прощай iif в ответственных местах кода

Добавлено:
Сделал более корректный тестовый пример, чтобы вероятности попадания в ветвлении были равномерно распределены, получился несколько иной результат, но для 2х и 3х вариантов выбора if все же прилично эффективней по быстродействию.

2 варианта выбора
if- 1
select case - 1.21

3 варианта выбора
if- 1
select case - 1.7
Автор: asbo
Дата сообщения: 16.08.2011 18:19
mp65
VBA в плане быстродействия не совсем однозначно себя ведет. Иногда заведомо более быстрый (на взгляд) код, более быстрый на тестах, считает медленнее на реальных данных, в реальной обвязке, чем "медленный".
Автор: SAS888
Дата сообщения: 17.08.2011 04:55
mp65
asbo прав. В VBA, для оптимизации времени выполнения кода, в каждом конкретном случае нужно применять различные методы для решения, казалось бы, одинаковых задач.
Так, например, в случае с оператором If: если требуется провести несколько сравнений, то после выполнения условия потребуется вставлять оператор безусловного перехода на конец блока этих If-ов. Что есть не рационально и медленно. В то время, как оператор Select Case сравнивает до первого совпадения (остальные, следующие ниже, игнорирует). Поэтому, проводя тест на скорость, все будет зависеть от условий и требуемого результата. Нельзя однозначно утверждать, что If "быстрее", чем Select Case.
Также, если, например, требуется проверить несколько условий, то существенно рациональнее использовать "кучу" вложенных If-ов, нежели применять And и Or в одном If-е. Т.к. в первом случае проверка будет происходить последовательно, до первого false. И если грамотно расставить порядок сравнений, то можно еще ускорить процесс. Во втором же случае, будут проверены все условия, затем применены все логические операции, и только после этого - результат.
Еще совет по оптимизации скорости: Не используйте функции типа Left, Mid, Trim и т.п. без значка "$", т.к. они возвращают значение типа Variant. Обычные функции Left$ и Trim$ возвращают результат немедленно, без его неявного преобразования в Variant.
Автор: mp65
Дата сообщения: 17.08.2011 09:45
SAS888
Есть такой замечательный конструктив, как ElseIf - он позволяет избежать операторов goto в большинстве случаев. По сути такой вложенный if должен быть эквивалентен Select Case, только его труднее читать.
Я согласен с тем, что select case быстро работает, но выигрывает он у if только на большом числе вариантов, как показывает экскремент , на коротких цепочках if работает быстрее.

Спасибо за информацию о mid и trim!

asbo
Вопрос не совсем по теме. Интересно, а какую версию MSOffice Вы используете? Я остановился на 2003, может быть это не совсем верный выбор в плане эффективности?
Я обратил внимание, что 2003 использует только одно ядро процессора, по крайней мере это справедливо для исполнения кода VBA Excel.
Автор: asbo
Дата сообщения: 17.08.2011 10:19
mp65
Я мирюсь с тем, что "на коротких цепочках" Select Case проигрывает - его читабельность, удобство редактирования, гибкость с лихвой перекрывают возможную медлительность. И то, она проявляется только на 10^5...7 итерациях.

А касательно GoTo - считается дурным тоном :) У меня он только в единственном случае возникает - On Error

SAS888
Спасибо за напоминание про $ в строковых ф-циях. Я постоянно забываю, млин... Уже в десяти местах себе писал и галки ставил - забываю :(

Возвращаясь к нашей дискуссии начала года - я все никак не могу причесать-окультурить до конца код для сравнения быстродействия :) Когда-нибудь выложу :)

А вот вопрос: что быстрее - явное или неявное приведение типов? У меня часто используются такие конструкции: -5 * True. Понятно, что канонически лучше приводить явно, но тогда снижается читабельность и компактность. А как с т.з. быстродействия? Все собираюсь померять, руки не доходят :)
Автор: asbo
Дата сообщения: 17.08.2011 12:31
Вот код для проверки быстродействия кода :)

Из всех возможных методов выбраны два - GetTickCount и High Frequency Timer (HFT). Оба из Kernel32. Понятно, что железо должно поддерживать последний. Почему только эти? - долго рассказывать. Я для себя давно сделал такой выбор по результатам изысканий.

Требуемая репрезентативность задается максимальным числом десятков циклов через переменную lTotal. Закомментированные bMore - для варианта с ограничением на минимальную длительность цикла в миллисек (не всегда удобно - последний цикл может занять много времени).

Собственно [more=код:]

Код:
Option Explicit

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' System declarations
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Declare Function QueryPerformanceCounter Lib "Kernel32" (x As Currency) As Boolean
Private Declare Function QueryPerformanceFrequency Lib "Kernel32" (x As Currency) As Boolean

Private Declare Function GetTickCount& Lib "Kernel32" ()

Dim cHft@, cHftBeg@, cHftEnd@, cHftFrq@, cHftOhd@, cHftTmp@ 'As Currency
Dim i&, j&, lCycQty&, lTotal&, lGtcBeg&, lGtcEnd&, lGtc&, lHft&
Dim bMore As Boolean
Dim sGtc$, sHft$


'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Test declarations
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim lTest&


Public Sub sb_SpeedTest()
i = 0: j = 0

lCycQty = 1
bMore = True

lTotal = 8

Do While bMore
'If bMore Then lCycQty = lCycQty * 10
lCycQty = lCycQty * 10
j = j + 1

'' *** Test code setup

'' *** Test code setup end

i = 0

QueryPerformanceFrequency cHftFrq
QueryPerformanceCounter cHftBeg
QueryPerformanceCounter cHftEnd
cHftOhd = cHftEnd - cHftBeg

QueryPerformanceCounter cHftBeg
lGtcBeg = GetTickCount

Do ' *** LOOP
'DoEvents
i = i + 1
'' *** Test code begin
'lTest = GetTickCount * CLng(bMore)
lTest = GetTickCount * bMore
'' *** Test code end

Loop Until i = lCycQty ' *** LOOP

lGtcEnd = GetTickCount
QueryPerformanceCounter cHftEnd

lGtc = lGtcEnd - lGtcBeg
cHftTmp = cHftEnd - cHftBeg

QueryPerformanceCounter cHftBeg
QueryPerformanceCounter cHftEnd
cHftOhd = (cHftOhd + cHftEnd - cHftBeg) / 2

lHft = CLng(1000000 * (cHftTmp - cHftOhd) / cHftFrq)

'bMore = (lGtc < 10000)
bMore = (j < lTotal)

sGtc = Format(lGtc, "#,##0")
sHft = Format(lHft, "#,##0")
Debug.Print j; "; Cycles: " & "10^" & j & "; Time: "; sGtc & vbTab & vbTab & sHft
Loop
Debug.Print "*** *** ***" & vbLf
End Sub
Автор: Strong64
Дата сообщения: 17.08.2011 14:08
Доброго времени суток, подскажите пожалуйста с чего лучше начать и чем пользоваться.

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

Для визуализации процесса прикрепляю файл с планом действия...


http://www.fayloobmennik.net/884953
Автор: aidomars
Дата сообщения: 17.08.2011 14:59
Strong64
Я обычно делаю так: создаю книгу с макросами и листами-шаблонами, данные беру из другой книги, обрабатываю их, копирую лист-шаблон в новую книгу и вставляю обработанное.
Автор: asbo
Дата сообщения: 17.08.2011 15:10
Strong64

Цитата:
подскажите пожалуйста с чего лучше начать и чем пользоваться

Начните, пожалуй, с внимательного чтения шапок тем, в которых собираетесь писать.
Вопросы по работе с MS Excel, не относящиеся к программированию, задаем в теме Excel FAQ
После этого - хэлп к Ексель по ключевым словам "шаблон xlt"
Затем реализуйте свою схему стандартными средствами. Не будет что-то получаться - пишите в указанную смежную тему.
Захотите автоматизировать какие-то ее участки - обращайтесь ужЕ сюда. Пишите, что не получается.
Автор: SAS888
Дата сообщения: 18.08.2011 05:18
asbo
Цитата:
...что быстрее - явное или неявное приведение типов?... как с т.з. быстродействия?
Если возможно, то всегда делаю явно. По-моему, наоборот, это более читабельно. А вот на счет быстродействия... как-то не задумывался. Не могу однозначно ответить.

Еще хотел высказать свое мнение по поводу этого кода:
Я бы не использовал оператор ReDim Preserve в теле цикла, т.к. при изменении размерности массива он создает другой массив и переписывает в него все имеющиеся элементы исходного. Можно определить размерность результирующего массива как максимальную (факториал от количества данных чисел), затем использовать Ваш алгоритм вычисления и просто заполнять этот массив по счетчику действительных значений. По выходу из цикла "обрезать" полученный массив, использовав ReDim Preserve один раз. Это, конечно, потребует большей памяти (думаю, что на сегодняшний день это не является проблемой), зато процедура должна выполняться быстрее.
Скажу сразу: сам не проверял. К тому же, разница в скорости выполнения будет изменяться при увеличении количества исходных значений. При небольшом их количестве, возможно, что Ваш вариант будет предпочтительнее.
Автор: asbo
Дата сообщения: 18.08.2011 10:10
SAS888

Цитата:
Если возможно, то всегда делаю явно. По-моему, наоборот, это более читабельно. А вот на счет быстродействия... как-то не задумывался. Не могу однозначно ответить.

В общем случае я тоже сторонник всяких "явностей". Особенно, если код куда-то передается. Только я использую некое подобие венгерской нотации, и сразу понятно что, когда булева переменная участвует в арифметических операциях, то это значит, что используется ее целочисленное значение. Таким образом можно очень эффективно и читабельно записывать сложные условия сравнения. А при использовании явностей это преимущество резко снижается.

А скорость - у меня получилось, что неявное приведение несколько быстрее явного см. выше.



Цитата:
Я бы не использовал оператор ReDim Preserve ...

Совточно. Мне они тоже не очень нравятся ни концептуально, ни по быстродействию - и сами ReDim, а, тем более, Preserve. Но иногда от них некуда деться. Или неохота :) В данном случае быстродействие было некритично.

А касательно алгоритма:
Цитата:
Можно определить размерность результирующего массива как максимальную (факториал от количества данных чисел), затем ...
Факториал тут будет явно избыточным. Мне ведь нужны пары, а не все возможные перестановки всех членов ряда. Я ужЕ основательно призабыл комбинаторику - может и есть там какая-то метода... Факториал тогда будет просто частный случай, когда число членов, участвующих в комбинации равно числу членов ряда, т.е. максимально. Но, повторюсь, это опять же не критично, т.к. ряд у меня может содержать максимум 10-12 членов. А концептуально - совправильно. Заложиться на большее, с запасом. На память, конечно, сегодня можно и не так пристально смотреть, как раньше :)
Автор: surgutfred
Дата сообщения: 18.08.2011 11:19
Подскажите пожалуйста такую вещь:
Есть Диапазон ячеек на листе "Пикет-расстояние", типа
285 60
286 60
287 60
288 60
289 60
290 60
291 60

на другом листе "АДС итоговая" из этого диапазона идет выборка формулой =ВПР(АДС!D7;Карьер1;2;ЛОЖЬ), где Карьер1 - тот самый диапазон и подставляется соответствующее значение из второго столбца диапазона в столбец I . Все работает нормально с одним значением. Но нужно сделать так, что бы АДС!D7 в одной ячейке можно было указать несколько значений через разделитель (например 285;286;288), а в столбец I выводилась бы сумма этих аргументов из второго столбца диапазона. сомневаюсь, что можно сделать это формулами. Хочу на VBA попробовать.

т.е. задача сводится к тому, что бы взять из ячейки значение "285;286;288" по разделителю ";" разделить на три значения, найти соответствие в диапазоне ячеек и просуммировать их.

Натолкните на мысль, как разделить значение? По переменным? Значений может быть разное кол-во - вообще эти значений порядка 90.

Файл тут

Автор: Oyger
Дата сообщения: 19.08.2011 15:26
surgutfred
Сумма функций ВПР вам поможет.

А если по:

Цитата:
взять из ячейки значение "285;286;288" по разделителю ";"


Цитата:
как разделить значение? По переменным? Значений может быть разное кол-во


Dim Arr 'Задаете безмерный массив

Arr = Split(Range("A5").Value, ";") 'заносите в массив значения из ячейки А5 (к примеру), с разделителем ";". Можете использовать любой разделитель, по вашему усмотрению. Значения в ячейки должны быть записаны подряд, без пробелов (12;34;53).

Получаем массив с разделенными значениями. Первое значение массива записано в ячейку 0 (Arr(0)) и далее по возрастанию.

Чтобы узнать количество элементов в массиве воспользуйтесь функцией UBound(Arr)
Автор: odyn1
Дата сообщения: 22.08.2011 00:51
Здравствуйте!
Учусь VBA и возник вот такой вопрос:
Пытаюсь написать процедуру, но при попытке запуска выдает "run-time error 13 type mismatch"
... If CSng(m - n) < 0 Then ... - вот здесь.
переменные m n об"явлены single, кроме того и функцию CSng присобачил для надежности.
Какое тут может быть несовпадение типов при сравнении числа с нулем??? Ничего не понимаю...
Подскажите, пожалуйста!...
Автор: asbo
Дата сообщения: 22.08.2011 01:06
odyn1
1. Непонятно - если "m n об"явлены single", то зачем их разницу приводить к single?
2. С нулем ты не сравниваешь. Меньше - должно проканывать без обиняков.
3. Разница m и n выходит за размерность single -1.401298E-45 - m отрицательное, а n - положительное.


Добавлено:
Пиши
If m < n Then
Автор: odyn1
Дата сообщения: 22.08.2011 01:15
asbo
Так мне надо сравнить разницу (m-n) с нулем...
m и n находятся в диапазоне 1...2
Автор: asbo
Дата сообщения: 22.08.2011 01:16
odyn1
If m < n Then
Автор: DmitryPrint
Дата сообщения: 22.08.2011 16:44

Цитата:
Так мне надо сравнить разницу (m-n) с нулем.

If m = n Then
asbo же Вам написал — в приведенном Вами куске кода нет сравнения на равенство нулю.
Автор: asbo
Дата сообщения: 22.08.2011 17:04
DmitryPrint
Не-не. = не пойдет. Исходя из "переменные m n об"явлены single", я предложил заменить
If CSng(m - n) < 0 Then
на
If m < n Then

В результате:
1. Выбросится ненужное приведение
2. Избавимся от операции вычитания
3. Застрахуемся от ошибки переполнения
4. Зделаем запись логики более естественной

Но, судя по тому, что "m и n находятся в диапазоне 1...2" и ТС молчит, - ошибка была именно в этом - "переменные m n об"явлены single". Не single они были :)
Автор: Strong64
Дата сообщения: 24.08.2011 10:17
Здравствуйте, есть ли возможность сделать кнопочку с функцией VBA которая показывала бы границы печати (пунктирной линией), как после нажатия предварительного просмотра (но чтобы туда не заходил(в предварительный просмотр))
Автор: smirnvlad
Дата сообщения: 24.08.2011 10:31
Strong64
ActiveSheet.DisplayPageBreaks = True
Автор: Strong64
Дата сообщения: 24.08.2011 11:49
smirnvlad
Спасибо большое!
Автор: odyn1
Дата сообщения: 24.08.2011 18:01
asbo
DmitryPrint
Спасибо, дошло...
Автор: mp65
Дата сообщения: 26.08.2011 12:11
Advanced Memberы, подскажите, плз, из вашего личного опыта, под какой версией MS Office получается самый быстрый код?
Смотрю на свой 2003, он всегда использует только одно ядро процессора, а ведь на многих операциях мог бы и поболее задействовать.
Автор: stduba
Дата сообщения: 26.08.2011 22:57
Доброго времени суток. Помогите решить такую задачу. В excel генерирую код. Его надо сохранить в csv файл в UNICODE для последующего импорта в другой программе. Как я понял excel не позволяет решить эту проблему напрямую. Пытаюсь делать это макросом из Excel копируя текст в word примерно так


Код:
Sub main()
Set wa = CreateObject("Word.Application")
wa.Visible = True
wa.Activate
Set wd = wa.Documents.Add
Range("A1:A3").Copy
wa.Selection.Paste
Application.CutCopyMode = False
wa.ChangeFileOpenDirectory "D:\Users\USER\Desktop\"
wa.ActiveDocument.SaveAs2 Filename:="w333.csv", FileFormat:=wdFormatUnicodeText, Encoding:=1200 ' _
LockComments:=False, Password:="", AddToRecentFiles:=True, WritePassword _
:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
SaveNativePictureFormat:=False, SaveFormsData:=False, SaveAsAOCELetter:= _
False, Encoding:=1200, InsertLineBreaks:=False, AllowSubstitutions:=False _
, LineEnding:=wdCRLF, CompatibilityMode:=0
Set wa = Nothing
End Sub
Автор: Oyger
Дата сообщения: 29.08.2011 13:07
stduba
А чем Вам не подходит при сохранении из Excel свойство функции SaveAs FileFormat:=xlCSVMSDOS ?
Автор: chipius
Дата сообщения: 30.08.2011 21:46
Добрый день!

Такая ситуация: существует шаблон рабочего листа с табличками и диаграммами (данные они берут из этих же таблиц), он хранится в отдельной книге, назовем ее "исходной". Стояла задача написать макрос, который бы создавал новую книгу с пользовательским количеством листов, основанных на существующем шаблоне из "исходной" книги. Задача вроде бы была решена: макрос копировал существующий шаблон из "исходной" книги и "размножал" его в новой книге (по принципу Ctrl+C - Ctrl+V), но возникла проблема: все диаграммы в новой книге за данными лезут в "исходную", из которой копировался шаблон.

Так вот вопрос: есть ли какой-нибудь способ заставить диаграммы брать данные именно с тех листов на которых они находятся? Пробовал переназначать уже на новом листе диаграммам диапазоны данных - получалось только с одной диаграммой, а остальные оставались без изменений.

ActiveSheet.ChartObjects("Chart 8").Activate
ActiveChart.SetSourceData Source:=Range("B15:B17,D15:D17,U15:U17,Y15:Y17")
ActiveChart.SeriesCollection(1).Delete ' эта строчка для удаления лишнего ряда данных с диаграммы
ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("D15,U15,Y15") ' здесь задаются значения по оси Х


ActiveSheet.ChartObjects("Chart 3").Select
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.SetSourceData Source:=Range("B15,B29,B33,D15,D29,D33,U15,U29,U33,Y15,Y29,Y33")
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("D15,U15,Y15")


Над кодом прошу сильно не смеяться. В VBA пока-что новичек. По-этому тыкаюсь везде немного наугад.
Автор: DmitryPrint
Дата сообщения: 31.08.2011 01:13
chipius
Можно так:

Код:
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).XValues = Range("N2:N33")
ActiveChart.SeriesCollection(1).Values = Range("D2:D33")

ActiveSheet.ChartObjects("Chart 3").Activate
With ActiveChart
.SeriesCollection(1).XValues = Range("A1:A33")
.SeriesCollection(1).Values = Range("K2:K33")
.SeriesCollection(2).XValues = Range("A2:A33")
.SeriesCollection(2).Values = Range("L2:L33")
.SeriesCollection(3).XValues = Range("A2:A33")
.SeriesCollection(3).Values = Range("M2:M33")
'и т.д.
End With

Страницы: 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127

Предыдущая тема: VS 2010


Форум Ru-Board.club — поднят 15-09-2016 числа. Цель - сохранить наследие старого Ru-Board, истории становления российского интернета. Сделано для людей.