Kopieren Sie die Werte für die Schnellstatistik in die Zwischenablage - Excel-Tipps

Inhaltsverzeichnis

Was ist der ASCII-Code für einen Tab?

Die Frage kam während eines Excel-Seminars in Tampa: Wäre es nicht cool, wenn Sie die Statistiken aus der Statusleiste in die Zwischenablage kopieren könnten, um sie später in einen Bereich einzufügen?

Ich drückte die Person, die die Frage stellte, wie die Paste genau funktionieren sollte. Natürlich können Sie die Statistiken nicht sofort einfügen, da Sie eine Reihe wichtiger Zellen ausgewählt haben. Sie müssten warten, einen anderen leeren Bereich der Tabelle auswählen, die Einfügung (wie in Strg + V) und die Statistik würde in einem Bereich von 6 Zeilen x 2 Spalten angezeigt. Die Person, die die Frage gestellt hat, schlug vor, dass es sich um statische Werte handelt.

Ich habe während des Seminars nicht versucht, die Frage zu beantworten, weil ich wusste, dass es etwas schwierig sein könnte, dies durchzuziehen.

Aber ich habe kürzlich ein Makro gestartet, um zu sehen, ob dies möglich ist. Meine Idee war es, eine lange Textzeichenfolge zu erstellen, die eingefügt werden kann. Um zu erzwingen, dass die Elemente in zwei Spalten angezeigt werden, muss die Textzeichenfolge die Bezeichnung für Spalte 1 (Summe) und dann eine Registerkarte sowie den Wert für Spalte 2 enthalten. Anschließend benötigen Sie einen Wagenrücklauf, die Bezeichnung für Zeile 2, Spalte 1, dann eine weitere Registerkarte, der Wert usw.

Ich wusste, dass Application.WorksheetFunction eine großartige Möglichkeit ist, die Ergebnisse von Excel-Funktionen an VBA zurückzugeben, aber nicht alle über 400 Excel-Funktionen unterstützt. Wenn VBA bereits eine ähnliche Funktion hat (LINKS, RECHTS, MITTEL), unterstützt Application.WorksheetFunction diese Funktion manchmal nicht. Ich habe VBA mit Alt + F11 gestartet, den Sofortbereich mit Strg + G angezeigt und dann einige Befehle eingegeben, um sicherzustellen, dass alle sechs Statusleistenfunktionen unterstützt wurden. Glücklicherweise gaben alle sechs Werte zurück, die mit den Angaben in der Statusleiste übereinstimmten.

Um das Makro zu verkürzen, können Sie einer Variablen Application.WorksheetFunction zuweisen:

Set WF = Application.WorksheetFunction

Später im Makro können Sie dann einfach auf WF.Sum (Auswahl) verweisen, anstatt Application.WorksheetFunction immer wieder einzugeben.

Was ist der ASCII-Code für einen Tab?

Ich fing an, die Textzeichenfolge zu erstellen. Ich habe eine MS-Variable für MyString ausgewählt.

MS = "Sum:" &

Dies ist der Punkt, an dem ich ein Tabulatorzeichen brauchte. Ich bin geeky genug, um ein paar ASCII-Zeichen zu kennen (10 = LineFeed, 13 = Carriage Return, 32 = Space, 65 = A, 90 = Z), aber ich konnte mich nicht an die Registerkarte erinnern. Als ich nach Bing wollte, um nachzuschlagen, fiel mir ein, dass Sie vblf in Ihrem Code für einen Zeilenvorschub oder vbcr in Ihrem Code für einen Wagenrücklauf verwenden konnten, also tippte ich vbtab in Kleinbuchstaben. Ich bin dann zu einer neuen Zeile übergegangen, damit Excel VBA die verstandenen Wörter groß schreiben kann. Ich hatte gehofft, dass das vbtab ein Kapital aufnimmt, und tatsächlich wurde die Zeile groß geschrieben, was darauf hinweist, dass VBA mir einen Tabulatorcharakter geben würde.

Wenn Sie Ihren VBA in Kleinbuchstaben eingeben und zu einer neuen Zeile wechseln, werden alle richtig geschriebenen Wörter irgendwo im Wort einen Großbuchstaben aufnehmen. In der Abbildung unten ist bekannt, dass vblf, vbcr, vbtab vba sind und nach dem Wechsel zu einer neuen Zeile groß geschrieben werden. Die Sache, die ich erfunden habe, vbampersand, ist VBA jedoch nicht bekannt, daher wird sie nicht groß geschrieben.

Zu diesem Zeitpunkt ging es darum, 6 Beschriftungen und 6 Werte zu einer langen Zeichenfolge zusammenzufügen. Denken Sie im folgenden Code daran, dass das _ am Ende jeder Zeile bedeutet, dass die Codezeile in der nächsten Zeile fortgesetzt wird.

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

Nachdem ich alle Labels und Werte zusammengefügt hatte, wollte ich meine Arbeit bewundern und zeigte das Ergebnis in einer MsgBox an. Ich habe den Code ausgeführt und er hat wunderbar funktioniert:

Ich dachte, ich wäre frei zu Hause. Wenn ich nur MS in die Zwischenablage bringen könnte, könnte ich mit der Aufnahme von Podcast 1894 beginnen. Vielleicht würde MS.Copy den Trick machen?

Leider war es nicht so einfach. MS.Copy war keine gültige Codezeile.

Also ging ich zu Google und suchte nach "Excel VBA-Variable in Zwischenablage kopieren". Eines der Top-Ergebnisse war dieser Beitrag im Message Board. In diesem Beitrag versuchten meine alten Freunde Juan Pablo und NateO, dem OP zu helfen. Der eigentliche Tipp war jedoch, wo Juan Pablo vorschlug, Code von der Website von Excel MVP Chip Pearson zu verwenden. Ich habe diese Seite gefunden, auf der erklärt wurde, wie die Variable in die Zwischenablage verschoben wird.

Um der Zwischenablage etwas hinzuzufügen, müssen Sie zuerst das Menü Extras des VBA-Fensters aufrufen und Referenzen auswählen. Zunächst werden einige Referenzen standardmäßig überprüft. Die Microsoft Forms 2.0-Bibliothek wird nicht überprüft. Sie müssen es in der sehr langen Liste finden und hinzufügen. Zum Glück war es für mich auf der ersten Seite der Auswahl, wo der grüne Pfeil es zeigt. Sobald Sie das Häkchen neben der Referenz hinzufügen, wird es nach oben verschoben.

Der Code von Chip funktioniert nicht, wenn Sie die Referenz nicht hinzufügen. Überspringen Sie also nicht den obigen Schritt!

Wenn Sie die Referenz hinzugefügt haben, beenden Sie das Makro mit dem Code von 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

Vor der Aufnahme des Podcasts habe ich einen Test durchgeführt, um sicherzustellen, dass er funktioniert. Sicher genug, als ich das Makro ausführte, dann einen neuen Bereich auswählte und Strg + V zum Einfügen drückte, wurde die Zwischenablage in einen Bereich von 6 Zeilen x 2 Spalten geleert.

Whoo-hoo! Ich habe die PowerPoint-Titelkarte für die Episode vorbereitet, Camtasia Recorder eingeschaltet und alles oben aufgenommen. Aber … als ich den Abspann zeigen wollte, überkam mich ein quälendes Gefühl. Dieses Makro fügte die Statistiken als statische Werte ein. Was ist, wenn sich die zugrunde liegenden Daten geändert haben? Möchten Sie nicht, dass der eingefügte Block aktualisiert wird? Es gab eine lange Pause im Podcast, in der ich überlegte, was ich tun sollte. Schließlich klickte ich auf das Camtasia Pause Recording-Symbol und prüfte, ob ich eine Formel in die MS-Zeichenfolge einfügen konnte und ob sie korrekt eingefügt werden würde. Sicher genug. Ich habe das Makro nicht einmal vollständig fertiggestellt oder mehr als einen Test durchgeführt, als ich den Rekorder wieder eingeschaltet und über dieses Makro gesprochen habe. Im Podcast habe ich theoretisiert, dass dies bei nicht zusammenhängenden Auswahlen niemals funktionieren würde, aber bei späteren Tests funktioniert es.Hier ist das Makro, das als Formeln eingefügt werden soll:

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

Nach dem Posten des Videos fragte der normale Betrachter Mike Fliss, ob es eine Möglichkeit gibt, die Formeln zu erstellen, die ständig aktualisiert werden, um die Statistiken für den ausgewählten Bereich anzuzeigen. Dies würde ein Worksheet_SelectionChange-Makro erfordern, das einen benannten Bereich ständig aktualisiert, um der Auswahl zu entsprechen. Dies ist zwar ein cooler Trick, erzwingt jedoch, dass jedes Mal, wenn Sie den Zellenzeiger bewegen, ein Makro ausgeführt wird, wodurch der UnDo-Stapel ständig gelöscht wird. Wenn Sie dieses Makro verwenden, muss es zu jedem Arbeitsblatt-Codebereich hinzugefügt werden, in dem es funktionieren soll, und Sie müssen auf diesen Arbeitsblättern ohne Rückgängig leben.

Klicken Sie zunächst in Excel mit der rechten Maustaste auf eine Blattregisterkarte und wählen Sie Code anzeigen. Fügen Sie dann diesen Code ein.

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

Wechseln Sie zurück zu Excel. Wählen Sie eine neue Zelle aus und geben Sie die Formel ein =SUM(SelectedData). Sie erhalten zunächst einen Zirkelverweis. Wählen Sie dann jedoch einen anderen Bereich numerischer Zellen aus, und die Summe der soeben erstellten Formel wird aktualisiert.