Kopier hurtigstatsværdierne til udklipsholderen - Excel-tip

Indholdsfortegnelse

Hvad er ASCII-koden for en fane?

Spørgsmålet kom under et Excel-seminar i Tampa: Ville det ikke være sejt, hvis du kunne kopiere statistikken fra statuslinjen til udklipsholderen til senere indsættelse i et interval?

Jeg pressede den person, der stillede spørgsmålet om, hvordan pastaen skulle fungere. Selvfølgelig kan du ikke indsætte statistikken med det samme, fordi du har valgt en masse vigtige celler. Du bliver nødt til at vente, vælge et andet tomt område i regnearket, indsætte (som i Ctrl + V) og statistikkerne vises i et 6-række med 2-søjleinterval. Den person, der stillede spørgsmålet, foreslog, at de ville være statiske værdier.

Jeg forsøgte ikke at besvare spørgsmålet under seminaret, fordi jeg vidste, at det kunne være lidt vanskeligt at trække dette ud.

Men jeg startede for nylig en makro for at se, om dette kunne gøres. Min idé var at opbygge en lang tekststreng, der kunne indsættes. For at tvinge elementerne til at blive vist i to kolonner, skal tekststrengen have etiketten for kolonne 1 (Sum) og derefter en fane og værdien for kolonne 2. Du skal derefter have en vognretur, etiketten for række 2, kolonne 1, derefter en anden fane, værdien osv.

Jeg vidste, at Application.WorksheetFunction er en fantastisk måde at returnere resultaterne af Excel-funktioner til VBA, men at det ikke understøtter alle 400+ Excel-funktioner. Nogle gange, hvis VBA allerede har en lignende funktion (VENSTRE, HØJRE, MIDT), understøtter Application.WorksheetFunction ikke denne funktion. Jeg fyrede VBA op med Alt + F11, viste den øjeblikkelige rude med Ctrl + G og skrev derefter nogle kommandoer for at sikre, at alle seks statuslinjefunktioner blev understøttet. Heldigvis returnerede alle seks værdier, der matchede det, der var vist i statuslinjen.

For at gøre makroen kortere kan du tildele Application.WorksheetFunction til en variabel:

Set WF = Application.WorksheetFunction

Derefter senere i makroen kan du blot henvise til WF.Sum (Selection) i stedet for at skrive Application.WorksheetFunction igen og igen.

Hvad er ASCII-koden for en fane?

Jeg begyndte at opbygge tekststrengen. Jeg valgte en variabel af MS til MyString.

MS = "Sum:" &

Dette er det punkt, hvor jeg havde brug for et faneblad. Jeg er nørd nok til at kende et par ASCII-tegn (10 = LineFeed, 13 = Carriage Return, 32 = Mellemrum, 65 = A, 90 = Z), men jeg kunne ikke huske fanen. Da jeg var ved at gå til Bing for at slå det op, huskede jeg, at du kunne bruge vblf i din kode til linefeed eller vbcr i din kode for en vognretur, så jeg skrev vbtab med små bogstaver. Derefter flyttede jeg til en ny linje for at tillade Excel VBA at kapitalisere de ord, den forstod. Jeg håbede på at se vbtab afhente en kapital, og helt sikkert blev linjen aktiveret, hvilket indikerer, at VBA ville give mig et faneblad.

Hvis du skriver din VBA med små bogstaver, når du går til en ny linje, vil du se alle de korrekt stavede ord samle et stort bogstav et eller andet sted i ordet. På billedet nedenfor er vblf, vbcr, vbtab kendt for vba og bliver kapitaliseret efter at have flyttet til en ny linje. Den ting, som jeg lavede, vbampersand, er imidlertid ikke en kendt ting for VBA, så den bliver ikke aktiveret.

På dette tidspunkt var det et spørgsmål om at forbinde 6 etiketter og 6 værdier til en lang streng. Husk i koden nedenfor, at _ i slutningen af hver linje betyder, at kodelinjen fortsættes på den næste 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

Efter at have samlet alle etiketterne og værdierne, ville jeg beundre mit arbejde, så jeg viste resultatet i en MsgBox. Jeg kørte koden, og den fungerede smukt:

Jeg troede, at jeg var hjemme fri. Hvis jeg bare kunne få MS videre til udklipsholderen, kunne jeg begynde at optage Podcast 1894. Måske ville MS.Copy gøre tricket?

Desværre var det ikke så let. MS.Copy var ikke en gyldig kodelinje.

Så jeg gik til Google og søgte efter "Excel VBA Copy Variable to Clipboard". Et af de bedste resultater var dette indlæg på opslagstavlen. I dette indlæg forsøgte mine gamle venner Juan Pablo og NateO at hjælpe OP. Det egentlige tip var dog, hvor Juan Pablo foreslog at bruge noget kode fra webstedet for Excel MVP Chip Pearson. Jeg fandt denne side, der forklarede, hvordan man fik variablen til udklipsholderen.

For at tilføje noget til udklipsholderen skal du først gå til VBA-vinduets menu Værktøjer og vælge Referencer. Du ser oprindeligt et par referencer kontrolleret som standard. Microsoft Forms 2.0-bibliotek kontrolleres ikke. Du skal finde den på den meget lange liste og tilføje den. Heldigvis var det for mig på den første valgside, hvor den grønne pil viser det. Når du har tilføjet fluebenet ud for referencen, flyttes det til toppen.

Chips kode fungerer ikke, hvis du ikke tilføjer referencen, så spring ikke ovenstående trin over!

Når du har tilføjet referencen, skal du afslutte makroen ved hjælp af 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 optog podcasten, lavede jeg en test for at sikre, at den fungerede. Sikker nok, da jeg kørte makroen og derefter valgte et nyt interval og ramte Ctrl + V for at indsætte, blev udklipsholderen tømt i et 6 række x 2 kolonneområde.

Whoo-hoo! Jeg forberedte PowerPoint-titelkortet til episoden, tændte for Camtasia-optageren og optagede alt ovenfor. Men … da jeg var ved at vise afsluttende kreditter, kom en nagende følelse over mig. Denne makro indsatte statistikken som statiske værdier. Hvad hvis de underliggende data blev ændret? Vil du ikke have, at den indsatte blok skal opdateres? Der var en lang pause i podcasten, hvor jeg overvejede, hvad jeg skulle gøre. Endelig klikkede jeg på ikonet for Camtasia Pause-optagelse og gik for at se, om jeg kunne placere en formel inde i MS-strengen, og om den ville blive indsat korrekt. Sikker nok gjorde det det. Jeg afsluttede ikke engang makroen helt eller lavede mere end en test, da jeg tændte optageren igen og talte om denne makro. I podcasten teoretiserede jeg, at dette aldrig ville fungere for ikke-sammenhængende valg, men i senere test fungerer det.Her er makroen, der skal indsættes 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

Efter udstationering af videoen spurgte den faste seer Mike Fliss, at der er en måde at opbygge de formler, der konstant opdateres for at vise statistikken for det valgte interval. Dette kræver en Worksheet_SelectionChange-makro, der konstant opdaterer et navngivet område for at matche valget. Selvom dette er en smule trick, tvinger det en makro til at køre, hver gang du flytter cellemarkøren, og det vil konstant rydde UnDo-stakken. Så hvis du bruger denne makro, skal den føjes til hver regnearkekodrude, hvor du vil have den til at fungere, og du bliver nødt til at leve uden fortryd på disse regneark.

Først skal du fra Excel højreklikke på en arkfane og vælge Vis kode. Indsæt derefter denne kode i.

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

Skift tilbage til Excel. Vælg en ny celle, og skriv formlen =SUM(SelectedData). Du får oprindeligt en cirkulær reference. Men vælg derefter en anden række numeriske celler, og den samlede formel, du lige har oprettet, opdateres.