Kopier hurtigstatistikkverdiene til utklippstavlen - Excel-tips

Innholdsfortegnelse

Hva er ASCII-koden for en fane?

Spørsmålet kom under et Excel-seminar i Tampa: Ville det ikke vært kult om du kunne kopiere statistikken fra statuslinjen til utklippstavlen for senere liming til et område?

Jeg presset personen som stilte spørsmålet om nøyaktig hvordan pastaen skulle fungere. Selvfølgelig kan du ikke lime inn statistikken umiddelbart, fordi du har valgt en haug med viktige celler. Du må vente, velge et annet tomt område i regnearket, lime (som i Ctrl + V) og statistikken vises i et 6-rad med 2-kolonne-område. Personen som stilte spørsmålet foreslo at de ville være statiske verdier.

Jeg prøvde ikke å svare på spørsmålet under seminaret, fordi jeg visste at det kunne være litt vanskelig å trekke dette av.

Men jeg startet nylig en makro for å se om dette kunne gjøres. Tanken min var å bygge en lang tekststreng som kunne limes inn. For å tvinge elementene til å vises i to kolonner, må tekststrengen ha etiketten for kolonne 1 (Sum) og deretter en Tab, og verdien for kolonne 2. Du trenger da en vognretur, etiketten for rad 2, kolonne 1, deretter en annen fane, verdien og så videre.

Jeg visste at Application.WorksheetFunction er en fin måte å returnere resultatene av Excel-funksjoner til VBA, men at den ikke støtter alle 400+ Excel-funksjoner. Noen ganger, hvis VBA allerede har en lignende funksjon (VENSTRE, HØYRE, MIDT), støtter ikke Application.WorksheetFunction den funksjonen. Jeg fyrte opp VBA med Alt + F11, viste Umiddelbar rute med Ctrl + G, og skrev deretter inn noen kommandoer for å sikre at alle seks statuslinjefunksjonene ble støttet. Heldigvis returnerte alle seks verdiene som samsvarte med det som dukket opp i statuslinjen.

For å gjøre makroen kortere, kan du tilordne Application.WorksheetFunction til en variabel:

Set WF = Application.WorksheetFunction

Senere i makroen kan du ganske enkelt referere til WF.Sum (Selection) i stedet for å skrive Application.WorksheetFunction om og om igjen.

Hva er ASCII-koden for en fane?

Jeg begynte å bygge tekststrengen. Jeg valgte en variabel MS for MyString.

MS = "Sum:" &

Dette er punktet der jeg trengte et fanetegn. Jeg er geeky nok til å kjenne noen ASCII-tegn (10 = LineFeed, 13 = Carriage Return, 32 = Space, 65 = A, 90 = Z), men jeg kunne ikke huske fanen. Da jeg var på vei til Bing for å slå opp det, husket jeg at du kunne bruke vblf i koden din for linjemating eller vbcr i koden din for vognretur, så jeg skrev vbtab med små bokstaver. Jeg flyttet deretter til en ny linje for å la Excel VBA bruke store ord på ordene den forsto. Jeg håpet å se at vbtab plukket opp en kapital, og helt sikkert ble linjen kapitalisert, noe som indikerer at VBA skulle gi meg en tabulatortegn.

Hvis du skriver inn VBA med små bokstaver, når du går til en ny linje, vil du se alle de riktig stavede ordene plukke opp en stor bokstav et eller annet sted i ordet. På bildet nedenfor er vblf, vbcr, vbtab kjent for vba og blir kapitalisert etter å ha flyttet til en ny linje. Imidlertid, det jeg gjorde opp, vbampersand er ikke noe kjent for VBA, så det blir ikke kapitalisert.

På dette tidspunktet handlet det om å koble 6 etiketter og 6 verdier til en lang streng. Husk i koden under at _ på slutten av hver linje betyr at kodelinjen fortsetter på neste linje.

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

Etter å ha samlet alle etikettene og verdiene, ønsket jeg å beundre arbeidet mitt, så jeg viste resultatet i en MsgBox. Jeg kjørte koden, og den fungerte vakkert:

Jeg trodde at jeg var fri hjemme. Hvis jeg bare kunne få MS videre til utklippstavlen, kunne jeg begynne å ta opp Podcast 1894. Kanskje MS.Copy ville gjøre susen?

Dessverre var det ikke så lett. MS.Copy var ikke en gyldig kodelinje.

Så jeg dro til Google og søkte etter "Excel VBA Copy Variable to Clipboard". Et av de beste resultatene var dette innlegget på Message Board. I det innlegget prøvde mine gamle venner Juan Pablo og NateO å hjelpe OP. Selve tipset var imidlertid hvor Juan Pablo foreslo å bruke litt kode fra nettstedet til Excel MVP Chip Pearson. Jeg fant denne siden som forklarte hvordan du fikk variabelen til utklippstavlen.

For å legge til noe på utklippstavlen, må du først gå til VBA-vinduets Verktøy-meny og velge Referanser. Du vil i utgangspunktet se noen referanser som er sjekket som standard. Microsoft Forms 2.0-bibliotek vil ikke bli sjekket. Du må finne den på den veldig lange listen og legge den til. Heldigvis, for meg, var det på den første siden av valg, om hvor den grønne pilen viser den. Når du legger til haken ved siden av referansen, flyttes den til toppen.

Chips kode fungerer ikke hvis du ikke legger til referansen, så ikke hopp over trinnet ovenfor!

Når du har lagt til referansen, fullfører du makroen ved å bruke Chips kode:

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

Før jeg tok opp podcasten, gjorde jeg en test for å forsikre meg om at den fungerte. Sikkert nok, da jeg kjørte makroen, og deretter valgte et nytt område og traff Ctrl + V for å lime inn, ble utklippstavlen tømt i et 6 rad x 2 kolonneområde.

Whoo-hoo! Jeg forberedte PowerPoint-tittelkortet for episoden, slå på Camtasia Recorder og spilte inn alt ovenfor. Men … da jeg var i ferd med å vise sluttkredittene, kom en gnagende følelse over meg. Denne makroen limte inn statistikken som statiske verdier. Hva om de underliggende dataene endret seg? Vil du ikke at den limte blokken skal oppdateres? Det var en lang pause i podcasten der jeg vurderte hva jeg skulle gjøre. Til slutt klikket jeg på Camtasia Pause Recording-ikonet og gikk for å se om jeg kunne legge en formel i MS-strengen og om den ville bli limt inn riktig. Visst nok gjorde det det. Jeg fullførte ikke en gang makroen helt eller gjorde mer enn en test da jeg slo på opptakeren og snakket om denne makroen. I podcasten teoretiserte jeg at dette aldri ville fungere for ikke-sammenhengende valg, men i senere tester fungerer det.Her er makroen som skal limes inn som formler:

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

Etter å ha lagt ut videoen spurte den vanlige seeren Mike Fliss at det er en måte å lage formlene som kontinuerlig vil oppdateres for å vise statistikken for det valgte området. Dette vil kreve en Worksheet_SelectionChange-makro som kontinuerlig oppdaterer et navngitt område for å matche valget. Selv om dette er litt lurt, tvinger det en makro til å kjøre hver gang du beveger cellepekeren, og det vil hele tiden fjerne UnDo-stakken. Så hvis du bruker denne makroen, må den legges til i hvert regnearkekodefelt der du vil at den skal fungere, og du må leve uten å angre på disse regnearkene.

Først, fra Excel, høyreklikker du på en arkfane og velger Vis kode. Lim deretter inn denne koden.

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

Bytt tilbake til Excel. Velg en ny celle og skriv inn formelen =SUM(SelectedData). Du vil først få en sirkulær referanse. Men velg deretter et annet utvalg av numeriske celler, og den totale formelen du nettopp opprettet vil oppdatere.