Skopiuj wartości szybkich statystyk do schowka - porady dotyczące programu Excel

Spisie treści

Jaki jest kod ASCII dla karty?

Pytanie pojawiło się podczas seminarium programu Excel w Tampie: czy nie byłoby fajnie, gdybyś mógł skopiować statystyki z paska stanu do schowka w celu późniejszego wklejenia do zakresu?

Nacisnąłem osobę, która zadała pytanie, jak dokładnie powinna działać pasta. Oczywiście nie możesz od razu wkleić statystyk, ponieważ masz zaznaczonych kilka ważnych komórek. Musiałbyś poczekać, wybrać inny pusty zakres arkusza kalkulacyjnego, wkleić (jak w Ctrl + V) i statystyki pojawią się w zakresie 6 rzędów na 2 kolumny. Osoba, która zadała pytanie, zasugerowała, że będą to wartości statyczne.

Nie próbowałem odpowiadać na to pytanie podczas seminarium, ponieważ wiedziałem, że może to być trochę trudne.

Ale ostatnio uruchomiłem makro, aby zobaczyć, czy można to zrobić. Mój pomysł polegał na zbudowaniu długiego ciągu tekstowego, który można by wkleić. Aby wymusić pojawienie się pozycji w dwóch kolumnach, ciąg tekstowy musiałby mieć etykietę dla kolumny 1 (Suma), a następnie tabulatora i wartość w kolumnie 2. Potrzebny byłby wtedy znak powrotu karetki, etykietę dla wiersz 2, kolumna 1, następnie kolejna karta, wartość i tak dalej.

Wiedziałem, że Application.WorksheetFunction to świetny sposób na zwrócenie wyników funkcji Excela do VBA, ale nie obsługuje wszystkich 400+ funkcji Excela. Czasami, jeśli VBA ma już podobną funkcję (LEFT, RIGHT, MID), wówczas Application.WorksheetFunction nie będzie obsługiwał tej funkcji. Uruchomiłem VBA za pomocą Alt + F11, wyświetliłem okienko natychmiastowe z Ctrl + G, a następnie wpisałem kilka poleceń, aby upewnić się, że wszystkie sześć funkcji paska stanu są obsługiwane. Na szczęście wszystkie sześć zwróciło wartości, które pasowały do tego, co pojawiało się na pasku stanu.

Aby skrócić makro, możesz przypisać Application.WorksheetFunction do zmiennej:

Set WF = Application.WorksheetFunction

Następnie w dalszej części makra możesz po prostu odwołać się do WF.Sum (Selection) zamiast w kółko wpisywać Application.WorksheetFunction.

Jaki jest kod ASCII dla karty?

Zacząłem budować ciąg tekstowy. Wybrałem zmienną MS dla MyString.

MS = "Sum:" &

To jest punkt, w którym potrzebowałem znaku tabulacji. Jestem na tyle zuchwały, że znam kilka znaków ASCII (10 = LineFeed, 13 = Carriage Return, 32 = Space, 65 = A, 90 = Z), ale nie mogłem sobie przypomnieć Tab. Kiedy miałem się udać do Binga, aby to sprawdzić, przypomniałem sobie, że możesz użyć vblf w swoim kodzie do wysuwania wiersza lub vbcr w kodzie do powrotu karetki, więc napisałem vbtab małymi literami. Następnie przeszedłem do nowej linii, aby umożliwić programowi Excel VBA pisanie wielkimi literami zrozumiałych słów. Miałem nadzieję, że vbtab zbierze kapitał i rzeczywiście, wiersz został zapisany wielkimi literami, wskazując, że VBA nada mi znak tabulacji.

Jeśli wpiszesz VBA małymi literami, kiedy przejdziesz do nowego wiersza, zobaczysz, że wszystkie poprawnie napisane słowa pobierają wielką literę gdzieś w słowie. Na poniższym obrazku vblf, vbcr, vbtab są znane jako vba i są wyświetlane wielkimi literami po przejściu do nowego wiersza. Jednak to, co wymyśliłem, vbampersand nie jest znane VBA, więc nie jest kapitalizowane.

W tym momencie chodziło o połączenie 6 etykiet i 6 wartości w jeden długi ciąg. Pamiętaj w poniższym kodzie, że _ na końcu każdego wiersza oznacza, że wiersz kodu jest kontynuowany w następnym wierszu.

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

Po połączeniu wszystkich etykiet i wartości chciałem podziwiać moją pracę, więc wyświetliłem wynik w MsgBox. Uruchomiłem kod i działało pięknie:

Myślałem, że jestem wolny w domu. Gdybym mógł po prostu umieścić MS w schowku, mógłbym zacząć nagrywać Podcast 1894. Może MS.Copy załatwi sprawę?

Niestety nie było to takie proste. MS.Copy nie była prawidłową linią kodu.

Poszedłem więc do Google i wyszukałem „Excel VBA Copy Variable to Clipboard”. Jednym z najlepszych wyników był ten post na tablicy ogłoszeń. W tym poście moi starzy przyjaciele Juan Pablo i NateO próbowali pomóc OP. Właściwa wskazówka dotyczyła jednak miejsca, w którym Juan Pablo zasugerował użycie kodu ze strony Excel MVP Chip Pearson. Znalazłem tę stronę, która wyjaśnia, jak przenieść zmienną do schowka.

Aby dodać coś do schowka, musisz najpierw przejść do menu Narzędzia okna VBA i wybrać Referencje. Początkowo zobaczysz kilka referencji zaznaczonych domyślnie. Biblioteka Microsoft Forms 2.0 nie zostanie sprawdzona. Musisz go znaleźć na bardzo długiej liście i dodać. Na szczęście dla mnie było to na pierwszej stronie wyborów, gdzie pokazuje to zielona strzałka. Po dodaniu znacznika wyboru obok odniesienia przesuwa się ono na górę.

Kod chipa nie będzie działał, jeśli nie dodasz referencji, więc nie pomijaj powyższego kroku!

Po dodaniu referencji zakończ makro, używając kodu Chipa:

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

Przed nagraniem podcastu zrobiłem test, aby upewnić się, że działa. Rzeczywiście, kiedy uruchomiłem makro, a następnie wybrałem nowy zakres i wcisnąłem Ctrl + V, aby wkleić, schowek został opróżniony do zakresu 6 wierszy x 2 kolumny.

Whoo-hoo! Przygotowałem kartę tytułową odcinka w PowerPoint, włączyłem Camtasia Recorder i nagrałem wszystko powyżej. Ale… kiedy już miałem pokazać napisy końcowe, ogarnęło mnie dokuczliwe uczucie. To makro wklejało statystyki jako wartości statyczne. Co się stanie, jeśli zmieniły się podstawowe dane? Czy nie chcesz, aby wklejony blok został zaktualizowany? W podcastie była długa przerwa, podczas której zastanawiałem się, co zrobić. Na koniec kliknąłem ikonę Camtasia Pause Recording i poszedłem sprawdzić, czy mogę umieścić formułę w ciągu MS i czy zostanie ona poprawnie wklejona. Rzeczywiście, tak się stało. Nie skończyłem nawet do końca makra ani nie wykonałem więcej niż jednego testu, gdy ponownie włączyłem rejestrator i mówiłem o tym makrze. W podcastie wysunąłem teorię, że to nigdy nie zadziała dla nieciągłych wyborów, ale w późniejszych testach działa.Oto makro do wklejenia jako formuły:

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

Po opublikowaniu filmu zwykły widz Mike Fliss zapytał, czy istnieje sposób na zbudowanie formuł, które będą stale aktualizowane, aby wyświetlać statystyki dla dowolnego wybranego zakresu. Wymagałoby to makra Worksheet_SelectionChange, które stale aktualizowałoby nazwany zakres w celu dopasowania do zaznaczenia. Chociaż jest to fajna sztuczka, wymusza uruchamianie makra za każdym razem, gdy przesuniesz wskaźnik komórki, a to będzie stale czyścić stos UnDo. Tak więc, jeśli używasz tego makra, musisz je dodać do każdego okienka kodu arkusza roboczego, w którym chcesz, aby działało, i będziesz musiał żyć bez Cofnij w tych arkuszach.

Najpierw w programie Excel kliknij prawym przyciskiem myszy kartę arkusza i wybierz opcję Wyświetl kod. Następnie wklej ten kod w.

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

Wróć do programu Excel. Wybierz nową komórkę i wpisz formułę =SUM(SelectedData). Początkowo otrzymasz cykliczne odniesienie. Ale następnie wybierz inny zakres komórek numerycznych, a suma właśnie utworzonej formuły zostanie zaktualizowana.