Copiați valorile statistici rapide în clipboard - Sfaturi Excel

Cuprins

Care este codul ASCII pentru o filă?

Întrebarea a venit în timpul unui seminar Excel din Tampa: Nu ar fi grozav dacă ați putea copia statisticile din bara de stare în clipboard pentru a le lipi ulterior într-un interval?

Am presat persoana care a pus întrebarea cu privire la modul exact în care ar trebui să funcționeze pasta. Desigur, nu puteți lipi statisticile imediat, deoarece aveți selectate o grămadă de celule importante. Ar trebui să așteptați, să selectați un alt interval gol al foii de calcul, să lipiți (ca în Ctrl + V) și statisticile să apară într-un interval de 6 rânduri cu 2 coloane. Persoana care a pus întrebarea a sugerat că vor fi valori statice.

Nu am încercat să răspund la întrebare în timpul seminarului, pentru că știam că ar putea fi cam dificil să rezolv acest lucru.

Dar, recent am început o macro pentru a vedea dacă se poate face acest lucru. Ideea mea a fost să construiesc un șir lung de text care să poată fi lipit. Pentru a forța articolele să apară în două coloane, șirul de text ar trebui să aibă eticheta pentru coloana 1 (Sumă) și apoi o filă, iar valoarea pentru coloana 2. Apoi ar trebui să aveți o retur de transport, eticheta pentru rândul 2, coloana 1, apoi o altă filă, valoarea și așa mai departe.

Știam că Application.WorksheetFunction este o modalitate excelentă de a returna rezultatele funcțiilor Excel către VBA, dar că nu acceptă toate cele peste 400 de funcții Excel. Uneori, dacă VBA are deja o funcție similară (STÂNGA, DREAPTA, MID), atunci Application.WorksheetFunction nu va accepta această funcție. Am declanșat VBA cu Alt + F11, am afișat panoul imediat cu Ctrl + G și apoi am tastat câteva comenzi pentru a mă asigura că toate cele șase funcții ale barei de stare sunt acceptate. Din fericire, toate cele șase au returnat valori care se potriveau cu ceea ce apărea în bara de stare.

Pentru a micșora macro-ul, puteți atribui Application.WorksheetFunction unei variabile:

Set WF = Application.WorksheetFunction

Apoi, mai târziu în macro, puteți consulta pur și simplu WF.Sum (Selection) în loc să tastați Application.WorksheetFunction de mai multe ori.

Care este codul ASCII pentru o filă?

Am început să construiesc șirul de text. Am ales o variabilă MS pentru MyString.

MS = "Sum:" &

Acesta este punctul în care aveam nevoie de un caracter tab. Sunt destul de ciudat să știu câteva caractere ASCII (10 = LineFeed, 13 = Carriage Return, 32 = Space, 65 = A, 90 = Z), dar nu mi-am putut aminti fila. În timp ce eram pe punctul de a mă îndrepta spre Bing pentru a-l căuta, mi-am amintit că ai putea folosi vblf în codul tău pentru o linie de alimentare sau vbcr în codul tău pentru o întoarcere la transport, așa că am tastat vbtab cu litere mici. Am trecut apoi la o nouă linie pentru a permite Excel VBA să scrie cu majuscule cuvintele pe care le-a înțeles. Speram să văd că vbtab ridică un capital și, cu siguranță, linia a devenit cu majuscule, indicând că VBA avea să-mi dea un caracter tab.

Dacă introduceți VBA cu litere mici, când mergeți la o nouă linie, veți vedea toate cuvintele scrise corect ridicând o literă mare undeva în cuvânt. În imaginea de mai jos, vblf, vbcr, vbtab sunt cunoscute de vba și se valorifică după ce treceți la o nouă linie. Cu toate acestea, ceea ce am inventat, vbampersand nu este un lucru cunoscut de VBA, deci nu este valorificat.

În acest moment, a fost vorba de a uni 6 etichete și 6 valori într-un șir lung. Amintiți-vă în codul de mai jos că _ la sfârșitul fiecărei linii înseamnă că linia de cod este continuată pe următoarea linie.

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

După ce am alăturat toate etichetele și valorile, am vrut să-mi admir munca, așa că am afișat rezultatul într-un MsgBox. Am rulat codul și a funcționat frumos:

Am crezut că sunt liber acasă. Dacă aș putea să introduc MS în clipboard, aș putea începe să înregistrez Podcast 1894. Poate MS.Copy ar face trucul?

Din păcate, nu a fost atât de ușor. MS.Copy nu a fost o linie de cod validă.

Așadar, am mers la Google și am căutat „Excel VBA Copy Variable to Clipboard”. Unul dintre rezultatele de top a fost această postare pe forumul de mesaje. În acel post, vechii mei prieteni Juan Pablo și NateO încercau să ajute OP. Sfatul real, totuși, a fost locul în care Juan Pablo a sugerat să folosească un cod de pe site-ul Excel MVP Chip Pearson. Am găsit această pagină care explica cum să introduc variabila în clipboard.

Pentru a adăuga ceva în clipboard, trebuie mai întâi să accesați meniul Instrumente al ferestrei VBA și să alegeți Referințe. Veți vedea inițial câteva referințe verificate implicit. Biblioteca Microsoft Forms 2.0 nu va fi verificată. Trebuie să-l găsiți în lista foarte lungă și să-l adăugați. Din fericire, pentru mine, era pe prima pagină de alegeri, despre locul în care o arată săgeata verde. După ce adăugați bifa lângă referință, aceasta se deplasează în partea de sus.

Codul cipului nu va funcționa dacă nu adăugați referința, așa că nu treceți peste pasul de mai sus!

După ce adăugați referința, terminați macrocomanda folosind codul Chip:

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

Înainte de a înregistra podcast-ul, am făcut un test pentru a mă asigura că funcționează. Destul de sigur, când am rulat macro-ul, apoi am selectat un interval nou și am apăsat Ctrl + V pentru a lipi, clipboard-ul a fost golit într-un interval de 6 rânduri x 2 coloane.

Vai! Am pregătit cartea de titlu PowerPoint pentru episod, am pornit Camtasia Recorder și am înregistrat totul de mai sus. Dar … în timp ce eram pe punctul de a arăta creditele de încheiere, un sentiment plictisitor a venit peste mine. Această macrocomandă lipea statisticile ca valori statice. Ce se întâmplă dacă datele subiacente s-au schimbat? Nu ai vrea să se actualizeze blocul lipit? A fost o lungă pauză în podcast, unde m-am gândit la ce să fac. În cele din urmă, am făcut clic pe pictograma Camtasia Pause Recording și am mers să văd dacă aș putea pune o formulă în șirul MS și dacă ar fi lipită corect. Sigur, a făcut-o. Nici măcar nu am terminat complet macro-ul și nici nu am făcut mai multe teste când am repornit reportofonul și am vorbit despre acest macro. În podcast, am teoretizat că acest lucru nu va funcționa niciodată pentru selecții necontinue, dar în testările ulterioare, funcționează.Iată macrocomanda de lipit ca formule:

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

După postarea videoclipului, vizionatorul obișnuit Mike Fliss a întrebat dacă există o modalitate de a construi formule care să se actualizeze constant pentru a afișa statisticile pentru orice interval selectat. Acest lucru ar necesita o macrocomandă Worksheet_SelectionChange care ar actualiza în mod constant un interval numit pentru a se potrivi cu selecția. Deși acesta este un pic de înșelătorie, forțează o macro să ruleze de fiecare dată când mutați indicatorul de celulă și asta va șterge în mod constant stiva UnDo. Deci, dacă utilizați această macrocomandă, aceasta trebuie adăugată la fiecare panou de cod al foii de lucru în care doriți să funcționeze și va trebui să trăiți fără Anulare pe acele foi de lucru.

Mai întâi, din Excel, faceți clic dreapta pe o filă de foaie și alegeți Vizualizare cod. Apoi, lipiți acest cod în.

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

Reveniți la Excel. Selectați o celulă nouă și tastați formula =SUM(SelectedData). Veți primi inițial o referință circulară. Dar, apoi selectați un alt interval de celule numerice și se va actualiza totalul formulei pe care tocmai ați creat-o.