Знаю, що давненько не радував вас новими повідомленнями і прошу мене за це вибачити – зараз у мене не найлегший період – купа тренінгів, коледж, та ще й зі здоров’ям знову проблеми – страшна алергія (важко не лише дихати, а й взагалі жити),  знову температура, ломота та слабкість у всьому тілі, тож зараз мені трохи не до цього.

Однак, вже зовсім скоро я планую зібратись з силами й розповісти про два чудових пристрої, з якими мені пощастило погратись. Які саме – не скажу, скажу лише що один стосується автоматизації повсякденних завдань, а інший – віртуальної реальності.

Тож до зустрічі, друзі! Скоро тут буде цікаво!
Бережіть себе та своїх близьких – не хворійте й не нехтуйте засобами особистого захисту – вірус ми ще не здолали!

Сьогодні вночі я повернувся з Києва, де проводив черговий тренінг – цього разу з програмування мовою VBA у середовищі Microsoft Excel.

Дуже часто запитання, отримані під час тренінгу, наштовхують на роздуми і в результаті я пишу повідомлення в блог. Ось і сьогодні мені хочеться дати коротку пораду тим, кому часто доводиться писати в Excel одні й ті ж довгі формули – перенести їх до VBA і зекономити собі час наступного разу.

Не хвилюйтесь – для цього не потрібно бути програмістом – у більшості випадків, користуючись моїми інструкціями, ви легко впораєтесь самотужки.Якщо ж щось не виходить – просто зв’яжіться зі мною.

Розповідати краще на прикладі.Тож почнемо ми з розгляду такого сценарію. Уявіть собі, що ви обробляєте анкети зворотнього зв’язку – підраховуєте середні бали, якими учасники тренінгу або семінару оцінили якість навчальних матеріалів, адекватність обладнання, рівень організації заходу, ваші викладацькі якості тощо.

Давайте також уявимо, що обчислюємо середній бал ми за таким правилом – відкидаємо найбільшу і найменшу оцінки, а те, що зосталось усереднюємо.

Для розрахунків середнього балу, за припущення, що оцінки знаходяться у комірках А1:А25, вам доведеться написати формулу на кшталт такої:

=(SUM(A1:A25)-MIN(A1:A25)-MAX(A1:A25))/(COUNT(A1:A25)-2)

Звичайно, ви можете справедливо розкритикувати такий спосіб розрахунків, однак ще раз повторюю – це чисто гіпотетичний сценарій, приклад, який я щойно вигадав.

Формула, яку я навів, досить невеличка, однак якщо вам треба ввести її для обчислення оцінок різних аспектів заходу хоча б з десяток разів, ви вже замислитесь про те, як би її скоротити!

І дійсно, вихід є – один раз створити власну функцію на VBA – мові програмування, вбудованій в Excel – і потім користуватись нею весь час. Але як це зробити?

Почнемо з виклику середовища розробки VBA – натиснемо Alt+F11. Ви маєте побачити щось подібне:

Не лякаємось, а додаємо новий модуль з кодом. Для цього у меню Insert обираємо пункт Module:

У результаті відкриється віконце для вводу коду:

Тут ви маєте написати ось такий код (зараз я все поясню):

Public Function RealAVG(r As Variant) As Variant

      
        RealAVG = (WorksheetFunction.Sum(r) – WorksheetFunction.Min(r) – WorksheetFunction.Max(r)) / (WorksheetFunction.Count(r) – 2)
   

End Function

Середній рядок (починається з “RealAVG=”) пишемо в один рядок – тут, в блозі він просто не вмістися за шириною запису. Так, в VBA можна переносити рядки (за допомогою пробілу і підкреслення), однак в даному випадку ми обійдесось без цього.

Все! Тепер ви можете під час роботи в Excel замість довгої формули писати просто:

=RealAVG(A1:A25)

Працює так само, але набагато коротше, чи не так?

Давайте тепер розглянемо код нашої функції аби зрозуміти що є що, і як вам перенести до VBA свої власні формули.

У першому рядку ми написали, що далі буде опис публічної (доступної звідусюди у вашому документі) функції з іменем RealAVG (“справжнє середнє”). Ім’я ви можете вигадати будь-яке. Лише одна порада – воно має бути красномовним, щоб одразу можна було зрозуміти що робить функція.

Далі в дужках йдуть параметри – ті початкові дані, які функція буде використовувати для обчислення результату. У нас параметр тільки один – змінна з ім’ям r типу Variant, тобто її значення може бути будь-яким.  У нашому випадку ми повинні дати функції дійсно один параметр – адресу діапазону з початковими даними. Якщо вам потрібно більше параметрів, опишіть їх так само і через кому.

Далі ми бачимо слова As Variant – це ми говоримо, що результат, який поверне нам функція, може бути будь-якого типу – число, рядок, що завгодно. Аби не морочитись, ви можете описувати подібним чином всі свої функції.

А далі – найцікавіше – ми описуємо правило обчислення результату. Робиться це просто – пишете ім’я функції, потім знак рівності (тут він означає присвоєння нового значення) і далі – формулу для обчислення. Думаю, ви її впізнали. Єдина відмінність від екселівської формули – замість SUM ви пишете WorksheetFunction.Sum. Так само чинимо і з іншими функціями. Чому? Думаю, ви вже здогадались – VBA не знає екселівських функцій, тож треба йому сказати, що Sum – це не його власна функція, а одна з тих, які ви можете використовувати у комірках листів книги в Excel. До речі, VBA підтримує не всі з них! Будьте уважними!

На цьому можна було б і закінчити, але є ще два нюанси:

Перший: а що, коли у якості аргумента ви випадкого напишете нісенітницю? Наприклад, ось так: =RealAVG("FDDGhhkjk")

Хотілося б, щоб у таких випадках функція автоматично інформувала вас про помилки. І це можна зробити! Достатньо змінити вашу функцію таким чином:

Public Function RealAVG(r As Variant) As Variant
On Error GoTo Err

        RealAVG = (WorksheetFunction.Sum(r) – WorksheetFunction.Min(r) – WorksheetFunction.Max(r)) / (WorksheetFunction.Count(r) – 2)

On eroor GoTo 0
Exit Function

Err:
    RealAVG = "Wrong function parameter!"
    ActiveCell.Font.Color = vbRed
0:

End Function

Тепер, якщо результат не вдалось обчислити, функція замість середнього балу виведе червоним кольором напис "Wrong function parameter!". Думаю, як працює код зрозуміло. Це, звичайно, не ідеал функції,

Другий нюанс: ви зможете використовувати цю функцію тільки в межах даного документу. Аби можна було користуватись нею у будь-якій таблиці, відкритій на вашій машині, треба було зберігати функцію в іншому місці. Але про це – у наступній статті.

До зустрічі за тиждень!

Вже не раз я писав про ті чи інші “гарячі клавіші”, завдяки яким ви можете іноді значно економити час, працюючи з програмами зі складу Microsoft Office.

Сьогодні ж мені хочеться знову повернутись до цієї теми і навести повний список клавіатурних скорочень Microsoft Word. Звісно, ви можете згадати, що такий список є на Office Online. Однак, мій документ – чарівний, оскільки його нам згенерує сам Word!

Тож, почнемо!

Запускаэмо Microsoft Word, і переходимо на вкладинку Developer. Сподіваюсь, у вас вона ввімкнена (про неї я вже якось писав). Якщо ні – не біда. Відкриваємо Backstage View, натискуємо кнопку Options.

У діалоговому вікні, що з’явиться, переходимо на вкладинку Customize Ribbon, і ставимо галку біля назви Developer у списку Customize the Ribbon.

Все, з підготовчими діями ми впорались, тепер можна переходити до основної справи. Тож, діємо так:

1. переходимо на вкладинку Developer
2. натискаємо Macros у групі Code
3. У вікні, яке з’явиться, у списку Macros In вибираємо Word Commands
4. У списку Macro Name обираємо ListCommands
5. Натискаємо Run
6. У вікні, яке з’явиться, обираємо Current Keyboard Settings і натискаємо OK

Все! У відповідь Word згенерує список своїх клавіатурних скорочень, який займає кілька сторінок. Все! Магія спрацювала – Word сам розповів нам, яким чином ми можемо працювати з ним, використовуючи клавіатуру! Wow!

В результаті ви отримаєте щось на кшталт ось такого:

Вивчайте, насолоджуйтесь!. Тепер залишилось лише все це опанувати…

Про всяк випадок, хочу відмітити, що всі ці маніпуляції ми робили у Word 2010. Якщо ж вам кортить дізнатись, як отримати список клавіатурних скорочень у ранніх версіях Word, ласкаво просимо на сайт TechRepublic, який я уже не раз згадував, як джерело цікавої інформації з Microsoft Office.