Скопіюйте значення швидкої статистики в буфер обміну - Поради Excel

Зміст

Що таке код ASCII для вкладки?

Питання постало під час семінару Excel у Тампі: чи не було б круто, якби ви скопіювали статистику з рядка стану в буфер обміну для подальшого вставлення в діапазон?

Я натиснув на людину, яка задала питання, як саме повинна працювати паста. Звичайно, ви не можете вставити статистику відразу, оскільки у вас вибрано купу важливих клітинок. Вам доведеться почекати, вибрати інший порожній діапазон електронної таблиці, вставити (як у Ctrl + V) та статистика відображатиметься в діапазоні 6 рядків на 2 стовпці. Той, хто задав питання, припустив, що це статичні значення.

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

Але нещодавно я запустив макрос, щоб перевірити, чи можна це зробити. Моєю ідеєю було створити довгий текстовий рядок, який можна було б вставити. Для того, щоб змусити елементи відображатися у двох стовпцях, текстовий рядок повинен мати мітку для стовпця 1 (Сума), а потім Tab, а також значення для стовпця 2. Потім вам знадобиться повернення каретки, мітка для рядок 2, стовпець 1, потім інша вкладка, значення тощо.

Я знав, що Application.WorksheetFunction - це чудовий спосіб повернути результати функцій Excel у VBA, але він не підтримує всіх 400+ функцій Excel. Іноді, якщо VBA вже має подібну функцію (ВЛІВО, ВПРАВО, MID), тоді Application.WorksheetFunction не підтримує цю функцію. Я запустив VBA за допомогою Alt + F11, показав панель негайних за допомогою Ctrl + G, а потім набрав кілька команд, щоб переконатися, що всі шість функцій рядка стану підтримуються. На щастя, усі шість повернутих значень, які відповідали тому, що з’явилося у рядку стану.

Щоб скоротити макрос, ви можете призначити Application.WorksheetFunction змінній:

Set WF = Application.WorksheetFunction

Потім, пізніше в макросі, ви можете просто звернутися до WF.Sum (Виділення), замість того, щоб вводити Application.WorksheetFunction знову і знову.

Що таке код ASCII для вкладки?

Я почав будувати текстовий рядок. Я вибрав змінну MS для MyString.

MS = "Sum:" &

Це момент, коли мені знадобився символ табуляції. Я досить вигадливий, щоб знати кілька символів ASCII (10 = LineFeed, 13 = Повернення каретки, 32 = Пробіл, 65 = A, 90 = Z), але я не міг згадати вкладку. Коли я збирався їхати до Бінга, щоб розібратись, я згадав, що ви можете використовувати vblf у своєму коді для подачі лінії або vbcr у своєму коді для повернення каретки, тому я набрав vbtab з малої літери. Потім я перейшов до нового рядка, щоб дозволити Excel VBA писати великими літерами слова, які він зрозумів. Я сподівався побачити, як vbtab підбирає капітал, і, звичайно, рядок став великими літерами, вказуючи на те, що VBA збирається надати мені символ табуляції.

Якщо ви введете свою VBA малими літерами, коли ви перейдете до нового рядка, ви побачите, що всі правильно написані слова десь у слові підбирають велику літеру. На зображенні нижче vblf, vbcr, vbtab, як відомо, vba і отримують великі літери після переходу до нового рядка. Однак те, що я вигадав, vbampersand - це не відома річ для VBA, тому вона не отримує великих літер.

На даний момент мова йшла про об’єднання 6 міток та 6 значень в один довгий рядок. Пам'ятайте в коді нижче, що _ в кінці кожного рядка означає, що рядок коду продовжується в наступному рядку.

Sub CopyQuickStatsToClipboard1() Set WF = Application.WorksheetFunction MS = "Average: " & vbTab & WF.Average(Selection) & vbCr _ & "Count: " & vbTab & WF.CountA(Selection) & vbCr _ & "Numerical Count: " & vbTab & WF.Count(Selection) & vbCr _ & "Min: " & vbTab & WF.Min(Selection) & vbCr _ & "Max: " & vbTab & WF.Max(Selection) & vbCr _ & "Sum: " & vbTab & WF.Sum(Selection) & vbCr MsgBox MS End Sub

Після об’єднання всіх міток та цінностей разом, я захотів помилуватися своєю роботою, тому показав результат у MsgBox. Я запустив код, і він працював чудово:

Я думав, що я додому вільний. Якби я міг просто підключити MS до буфера обміну, я міг би розпочати запис Podcast 1894. Можливо, MS.Copy зробив би цю справу?

На жаль, це було не так просто. MS.Copy не був дійсним рядком коду.

Отже, я зайшов до Google і шукав "Змінна копії Excel VBA в буфер обміну". Одним з найкращих результатів стала ця публікація на дошці повідомлень. У цьому дописі мої давні друзі Хуан Пабло та NateO намагалися допомогти ОП. Фактичною підказкою було те, де Хуан Пабло запропонував використовувати якийсь код із сайту Excel MVP Chip Pearson. Я знайшов цю сторінку, в якій було пояснено, як перенести змінну в буфер обміну.

Для того, щоб щось додати до буфера обміну, потрібно спочатку перейти до меню Інструменти вікна VBA та вибрати Посилання. Спочатку ви побачите кілька посилань, перевірених за замовчуванням. Бібліотека Microsoft Forms 2.0 не перевіряється. Вам потрібно знайти його в дуже довгому списку і додати. На щастя, для мене це було на першій сторінці вибору, де зелена стрілка це показує. Як тільки ви додасте галочку поруч із посиланням, вона переміщується вгору.

Код чіпа не буде працювати, якщо ви не додасте посилання, тому не пропустіть наведений вище крок!

Додавши посилання, закінчіть макрос, використовуючи код Чіпа:

Sub CopyQuickStatsToClipboard() Set WF = Application.WorksheetFunction MS = "Average: " & vbTab & WF.Average(Selection) & vbCr _ & "Count: " & vbTab & WF.CountA(Selection) & vbCr _ & "Numerical Count: " & vbTab & WF.Count(Selection) & vbCr _ & "Min: " & vbTab & WF.Min(Selection) & vbCr _ & "Max: " & vbTab & WF.Max(Selection) & vbCr _ & "Sum: " & vbTab & WF.Sum(Selection) & vbCr ' Thanks to Chip Pearson http://www.cpearson.com/excel/Clipboard.aspx Dim DataObj As New MSForms.DataObject DataObj.SetText MS DataObj.PutInClipboard End Sub

Перш ніж записати подкаст, я зробив тест, щоб переконатися, що він працює. Звичайно, коли я запустив макрос, потім вибрав новий діапазон і натиснув Ctrl + V, щоб вставити, буфер обміну звільнився в діапазон стовпців 6 рядків x 2.

У-у-у! Я підготував титульну картку PowerPoint до епізоду, увімкнув Camtasia Recorder і записав усе вище. Але … коли я збирався показати завершальні титри, мене набридло почуття. Цей макрос вставляв статистичні дані як статичні значення. Що робити, якщо базові дані змінилися? Чи не хочете ви, щоб вставлений блок оновився? У підкасті була довга пауза, де я думав, що мені робити. Нарешті, я натиснув піктограму паузи запису Camtasia і пішов перевіряти, чи можу я додати формулу всередину рядка MS і чи буде вона правильно вставлена. Звичайно, так і сталося. Я навіть не закінчив повністю макрос або не зробив більше одного тесту, коли знову увімкнув диктофон і заговорив про цей макрос. У подкасті я висунув теорію, що це ніколи не буде працювати для несуміжних виборів, але в подальшому тестуванні це працює.Ось макрос, який слід вставити як формули:

Sub CopyQuickStatsAsFormulas() Set WF = Application.WorksheetFunction MA = Selection.Address MS = "Average: " & vbTab & "=AVERAGE(" & MA & ")" & vbCr _ & "Count: " & vbTab & "=CountA(" & MA & ")" & vbCr _ & "Numerical Count: " & vbTab & "=Count(" & MA & ")" & vbCr _ & "Min: " & vbTab & "=Min(" & MA & ")" & vbCr _ & "Max: " & vbTab & "=Max(" & MA & ")" & vbCr _ & "Sum: " & vbTab & "=Sum(" & MA & ")" & vbCr _ ' Thanks to Chip Pearson http://www.cpearson.com/excel/Clipboard.aspx Dim DataObj As New MSForms.DataObject DataObj.SetText MS DataObj.PutInClipboard End Sub

Після публікації відео звичайний глядач Майк Флісс запитав, чи є спосіб створити формули, які постійно оновлюватимуться, щоб відображати статистику для будь-якого вибраного діапазону. Для цього знадобиться макрос Worksheet_SelectionChange, який постійно оновлюватиме названий діапазон відповідно до вибору. Незважаючи на те, що це крута хитрість, вона змушує макрос запускатися кожного разу, коли ви переміщуєте покажчик комірки, і це буде постійно очищати стек UnDo. Отже, якщо ви використовуєте цей макрос, його потрібно додати до кожної області коду робочого аркуша, де ви хочете, щоб він працював, і вам доведеться жити без Undo на цих робочих аркушах.

По-перше, у програмі Excel клацніть правою кнопкою миші на вкладці аркуша та виберіть «Переглянути код». Потім вставте цей код в.

Private Sub Worksheet_SelectionChange(ByVal Target As Range) Selection.Name = "SelectedData" End Sub

Поверніться до Excel. Виділіть нову комірку та введіть формулу =SUM(SelectedData). Спочатку ви отримаєте циркулярне посилання. Але потім виберіть інший діапазон числових комірок, і загальна кількість формули, яку ви щойно створили, оновиться.