Kopiera Quick Stats-värdena till Urklipp - Excel Tips

Innehållsförteckning

Vad är ASCII-koden för en flik?

Frågan kom under ett Excel-seminarium i Tampa: Skulle det inte vara kul om du kunde kopiera statistiken från statusfältet till Urklipp för att senare klistra in i ett intervall?

Jag tryckte på personen som ställde frågan om exakt hur pastan ska fungera. Naturligtvis kan du inte klistra in statistiken omedelbart, för du har valt en massa viktiga celler. Du måste vänta, välj ett annat tomt intervall i kalkylbladet, klistra in (som i Ctrl + V) och statistiken visas i ett 6-radigt med två kolumner. Personen som ställde frågan föreslog att de skulle vara statiska värden.

Jag försökte inte svara på frågan under seminariet, för jag visste att det kan vara lite knepigt att ta bort det här.

Men jag startade nyligen ett makro för att se om detta kunde göras. Min idé var att bygga en lång textsträng som kunde klistras in. För att tvinga artiklarna att visas i två kolumner måste textsträngen ha etiketten för kolumn 1 (Sum) och sedan en flik och värdet för kolumn 2. Du behöver då en vagnretur, etiketten för rad 2, kolumn 1, sedan en annan flik, värdet och så vidare.

Jag visste att Application.WorksheetFunction är ett utmärkt sätt att returnera resultaten av Excel-funktioner till VBA, men att det inte stöder alla 400+ Excel-funktioner. Ibland, om VBA redan har en liknande funktion (VÄNSTER, HÖGER, MID), stöder inte Application.WorksheetFunction den funktionen. Jag avfyrade VBA med Alt + F11, visade den direkta rutan med Ctrl + G och skrev sedan några kommandon för att se till att alla sex statusfältfunktioner stöddes. Lyckligtvis returnerade alla sex värden som matchade vad som visades i statusfältet.

För att göra makrot kortare kan du tilldela Application.WorksheetFunction till en variabel:

Set WF = Application.WorksheetFunction

Senare i makrot kan du helt enkelt hänvisa till WF.Sum (Selection) istället för att skriva ut Application.WorksheetFunction om och om igen.

Vad är ASCII-koden för en flik?

Jag började bygga textsträngen. Jag valde en variabel MS för MyString.

MS = "Sum:" &

Det här är den punkt där jag behövde en flik karaktär. Jag är nördig nog att känna till några ASCII-tecken (10 = LineFeed, 13 = Carriage Return, 32 = Mellanslag, 65 = A, 90 = Z), men jag kunde inte komma ihåg fliken. När jag var på väg till Bing för att leta upp det, kom jag ihåg att du kunde använda vblf i din kod för en linjematning eller vbcr i din kod för en vagnretur, så jag skrev vbtab med gemener. Jag flyttade sedan till en ny rad för att tillåta Excel VBA att använda de ord som den förstod. Jag hoppades på att se vbtab plocka upp ett kapital, och säkert blev raden aktiverad, vilket tyder på att VBA skulle ge mig en flik karaktär.

Om du skriver din VBA med små bokstäver, när du går till en ny rad, ser du alla rätt stavade ord plocka upp en stor bokstav någonstans i ordet. I bilden nedan är vblf, vbcr, vbtab kända för vba och blir kapitaliserade efter att ha flyttat till en ny linje. Men det jag gjorde, vbampersand är inte en känd sak för VBA, så det blir inte aktiverat.

Vid denna tidpunkt handlade det om att sammanfoga 6 etiketter och 6 värden i en lång sträng. Kom ihåg i koden nedan att _ i slutet av varje rad betyder att kodraden fortsätter på nästa rad.

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 att ha sammanfogat alla etiketter och värden ville jag beundra mitt arbete, så jag visade resultatet i en MsgBox. Jag sprang koden och det fungerade vackert:

Jag trodde att jag var hemma fri. Om jag bara kunde få MS till klippbordet kunde jag börja spela in Podcast 1894. Kanske skulle MS.Copy göra tricket?

Tyvärr var det inte så enkelt. MS.Copy var inte en giltig kodrad.

Så jag gick till Google och sökte efter "Excel VBA Copy Variable to Clipboard". Ett av de bästa resultaten var detta inlägg på anslagstavlan. I det inlägget försökte mina gamla vänner Juan Pablo och NateO hjälpa OP. Det faktiska tipset var dock där Juan Pablo föreslog att använda lite kod från webbplatsen för Excel MVP Chip Pearson. Jag hittade den här sidan som förklarade hur man får variabeln till Urklipp.

För att lägga till något i urklipp måste du först gå till VBA-fönstrets verktygsmeny och välja Referenser. Initialt kommer du att se några referenser som standard. Microsoft Forms 2.0-bibliotek kommer inte att kontrolleras. Du måste hitta den i den mycket långa listan och lägga till den. Lyckligtvis, för mig, var det på den första sidan med val, om var den gröna pilen visar det. När du har lagt till bocken bredvid referensen flyttas den till toppen.

Chips kod fungerar inte om du inte lägger till referensen, så hoppa inte över steget ovan!

När du har lagt till referensen avslutar du makrot med Chips kod:

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

Innan jag spelade in podcasten gjorde jag ett test för att se till att det fungerade. Visst nog, när jag körde makrot och sedan valde ett nytt intervall och slog Ctrl + V för att klistra in, tappades urklipp i ett 6 rad x 2 kolumnintervall.

Whoo-hoo! Jag förberedde PowerPoint-titelkortet för avsnittet, aktiverade Camtasia Recorder och spelade in allt ovan. Men … när jag skulle visa de avslutande krediterna kom en gnagande känsla över mig. Detta makro klistrade in statistiken som statiska värden. Vad händer om underliggande data ändras? Vill du inte att det klistrade blocket ska uppdateras? Det var en lång paus i podcasten där jag funderade på vad jag skulle göra. Slutligen klickade jag på ikonen Camtasia Pausinspelning och gick för att se om jag kunde sätta en formel inuti MS-strängen och om den skulle klistras in korrekt. Visst nog, det gjorde det. Jag avslutade inte ens makrot helt eller gjorde mer än ett test när jag slog på inspelaren igen och pratade om detta makro. I podcasten teoretiserade jag att detta aldrig skulle fungera för icke-sammanhängande val, men i senare tester fungerar det.Här är makrot att klistra in 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 att ha lagt upp videon frågade den vanliga tittaren Mike Fliss att det finns ett sätt att bygga formlerna som ständigt uppdateras för att visa statistiken för vilket intervall som väljs. Detta kräver ett Worksheet_SelectionChange-makro som kontinuerligt uppdaterar ett namngivet intervall för att matcha valet. Även om detta är en sval bit av knep, tvingar det ett makro att köras varje gång du flyttar cellpekaren, och det kommer ständigt att rensa UnDo-stacken. Så om du använder det här makrot måste det läggas till i varje kalkylruta där du vill att det ska fungera, och du måste leva utan ångra på dessa kalkylblad.

Först, från Excel, högerklicka på en arkflik och välj Visa kod. Klistra sedan in den här koden i.

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

Byt tillbaka till Excel. Välj en ny cell och skriv formeln =SUM(SelectedData). Inledningsvis får du en cirkulär referens. Men välj sedan ett annat antal numeriska celler och summan av formeln som du just skapade uppdateras.