Kopieer de Quick Stats-waarden naar het klembord - Excel-tips

Inhoudsopgave

Wat is de ASCII-code voor een tabblad?

De vraag kwam tijdens een Excel-seminar in Tampa: zou het niet cool zijn als je de statistieken van de statusbalk naar het klembord zou kunnen kopiëren om ze later in een bereik te plakken?

Ik drukte de persoon die de vraag stelde op hoe de pasta precies zou moeten werken. Je kunt de statistieken natuurlijk niet meteen plakken, omdat je een heleboel belangrijke cellen hebt geselecteerd. U zou moeten wachten, een ander leeg bereik van de spreadsheet selecteren, de plak (zoals in Ctrl + V) en de statistieken zouden verschijnen in een bereik van 6 rijen bij 2 kolommen. De persoon die de vraag stelde, suggereerde dat het statische waarden zouden zijn.

Ik heb tijdens het seminar niet geprobeerd de vraag te beantwoorden, omdat ik wist dat het een beetje lastig zou zijn om dit voor elkaar te krijgen.

Maar ik ben onlangs een macro begonnen om te zien of dit kon worden gedaan. Mijn idee was om een lange tekenreeks te maken die kon worden geplakt. Om ervoor te zorgen dat de items in twee kolommen verschijnen, moet de tekstreeks het label hebben voor kolom 1 (Som) en vervolgens een Tab, en de waarde voor kolom 2. Je hebt dan een regelterugloop nodig, het label voor rij 2, kolom 1, dan nog een tabblad, de waarde, enzovoort.

Ik wist dat Application.WorksheetFunction een geweldige manier is om de resultaten van Excel-functies terug te sturen naar VBA, maar dat het niet alle 400+ Excel-functies ondersteunt. Soms, als VBA al een vergelijkbare functie heeft (LINKS, RECHTS, MIDDEN), ondersteunt Application.WorksheetFunction die functie niet. Ik startte VBA met Alt + F11, gaf het directe venster weer met Ctrl + G en typte vervolgens enkele opdrachten om ervoor te zorgen dat alle zes statusbalkfuncties werden ondersteund. Gelukkig hebben alle zes waarden geretourneerd die overeenkwamen met wat er in de statusbalk werd weergegeven.

Om de macro korter te maken, kunt u Application.WorksheetFunction aan een variabele toewijzen:

Set WF = Application.WorksheetFunction

Vervolgens kunt u later in de macro eenvoudig verwijzen naar WF.Sum (Selectie) in plaats van Application.WorksheetFunction steeds opnieuw uit te typen.

Wat is de ASCII-code voor een tabblad?

Ik begon de tekstreeks te bouwen. Ik koos een MS-variabele voor MyString.

MS = "Sum:" &

Dit is het punt waarop ik een tab-teken nodig had. Ik ben gek genoeg om een paar ASCII-tekens te kennen (10 = LineFeed, 13 = Carriage Return, 32 = spatie, 65 = A, 90 = Z), maar ik kon me de Tab niet herinneren. Toen ik op het punt stond naar Bing te gaan om het op te zoeken, herinnerde ik me dat je vblf in je code kon gebruiken voor een linefeed of vbcr in je code voor een regelterugloop, dus typte ik vbtab in kleine letters. Ik ging toen naar een nieuwe regel om Excel VBA toe te staan de woorden die het begreep met een hoofdletter te schrijven. Ik hoopte dat de vbtab een hoofdletter zou opnemen, en ja hoor, de regel werd een hoofdletter, wat aangeeft dat VBA me een tab-teken zou geven.

Als u uw VBA in kleine letters typt en u naar een nieuwe regel gaat, ziet u dat alle correct gespelde woorden ergens in het woord een hoofdletter oppakken. In de onderstaande afbeelding zijn vblf, vbcr, vbtab bekend bij vba en krijgen ze een hoofdletter nadat ze naar een nieuwe regel zijn verplaatst. Het ding dat ik heb verzonnen, vbampersand, is echter niet bekend bij VBA, dus het krijgt geen hoofdletter.

Op dit punt was het een kwestie van 6 labels en 6 waarden samenvoegen tot één lange reeks. Onthoud in de onderstaande code dat de _ aan het einde van elke regel betekent dat de regel code wordt voortgezet op de volgende regel.

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

Nadat ik alle labels en waarden bij elkaar had gebracht, wilde ik mijn werk bewonderen, dus ik toonde het resultaat in een MsgBox. Ik heb de code uitgevoerd en het werkte prachtig:

Ik dacht dat ik vrij thuis was. Als ik MS gewoon op het klembord kon krijgen, zou ik kunnen beginnen met het opnemen van Podcast 1894. Misschien zou MS.Copy het lukken?

Helaas was het niet zo eenvoudig. MS.Copy was geen geldige regel code.

Dus ging ik naar Google en zocht naar "Excel VBA Copy Variable to Clipboard". Een van de beste resultaten was dit bericht op het prikbord. In die post probeerden mijn oude vrienden Juan Pablo en NateO de OP te helpen. De echte tip was echter dat Juan Pablo voorstelde om wat code van de site van Excel MVP Chip Pearson te gebruiken. Ik vond deze pagina waarin werd uitgelegd hoe je de variabele op het klembord kunt krijgen.

Om iets aan het klembord toe te voegen, moet u eerst naar het menu Extra van het VBA-venster gaan en Verwijzingen kiezen. U zult in eerste instantie standaard enkele referenties aangevinkt zien. Microsoft Forms 2.0 Library wordt niet gecontroleerd. U moet het in de zeer lange lijst vinden en toevoegen. Gelukkig stond het voor mij op de eerste pagina met keuzes, ongeveer waar de groene pijl het aangeeft. Zodra u het vinkje naast de referentie plaatst, wordt deze naar boven verplaatst.

De chipcode werkt niet als u de referentie niet toevoegt, dus sla de bovenstaande stap niet over!

Nadat u de referentie hebt toegevoegd, voltooit u de macro met behulp van de code van 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

Voordat ik de podcast opnam, heb ik een test gedaan om er zeker van te zijn dat deze werkte. En ja hoor, toen ik de macro uitvoerde, een nieuw bereik selecteerde en op Ctrl + V drukte om te plakken, werd het klembord geleegd in een bereik van 6 rijen x 2 kolommen.

Joepie! Ik heb de PowerPoint-titelkaart voor de aflevering voorbereid, Camtasia Recorder ingeschakeld en alles hierboven opgenomen. Maar… toen ik op het punt stond de aftiteling te laten zien, kreeg ik een zeurderig gevoel. Deze macro plakte de statistieken als statische waarden. Wat als de onderliggende gegevens veranderen? Zou je niet willen dat het geplakte blok wordt bijgewerkt? Er viel een lange pauze in de podcast waarin ik nadacht over wat ik moest doen. Ten slotte klikte ik op het pictogram Camtasia Pause Recording en ging kijken of ik een formule in de MS-reeks kon plaatsen en of deze correct zou worden geplakt. En ja hoor, dat deed het. Ik heb de macro niet eens helemaal afgemaakt of meer dan één test gedaan toen ik de recorder weer aanzette en over deze macro sprak. In de podcast theoretiseerde ik dat dit nooit zou werken voor niet-aaneengesloten selecties, maar bij latere tests werkt het wel.Hier is de macro die u als formules kunt plakken:

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

Na het plaatsen van de video, vroeg de reguliere kijker Mike Fliss of er een manier is om de formules samen te stellen die constant worden bijgewerkt om de statistieken weer te geven voor het geselecteerde bereik. Dit zou een Worksheet_SelectionChange-macro vereisen die constant een benoemd bereik zou bijwerken om overeen te komen met de selectie. Hoewel dit een coole truc is, dwingt het een macro om elke keer dat je de celaanwijzer beweegt te draaien, en dat zal constant de UnDo-stapel wissen. Dus als u deze macro gebruikt, moet deze worden toegevoegd aan elk werkbladcodepaneel waar u wilt dat het werkt, en u zult moeten leven zonder ongedaan maken op die werkbladen.

Klik eerst vanuit Excel met de rechtermuisknop op een bladtab en kies Bekijk code. Plak deze code vervolgens in.

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

Schakel terug naar Excel. Selecteer een nieuwe cel en typ de formule =SUM(SelectedData). U krijgt in eerste instantie een kringverwijzing. Maar selecteer vervolgens een ander bereik van numerieke cellen en het totaal van de formule die u zojuist hebt gemaakt, wordt bijgewerkt.