Salin Quick Stats Values ke Clipboard - Excel Tips

Daftar Isi

Apa kode ASCII untuk Tab?

Pertanyaan muncul selama seminar Excel di Tampa: Bukankah lebih keren jika Anda dapat menyalin statistik dari status bar ke clipboard untuk kemudian ditempelkan ke rentang?

Saya menekan orang yang mengajukan pertanyaan tentang bagaimana tepatnya pasta itu bekerja. Tentu saja, Anda tidak dapat langsung menempelkan statistik, karena Anda telah memilih banyak sel penting. Anda harus menunggu, pilih rentang kosong lain dari spreadsheet, tempel (seperti pada Ctrl + V) dan statistik akan muncul dalam rentang 6 baris kali 2 kolom. Orang yang mengajukan pertanyaan menyarankan bahwa nilai-nilai itu akan statis.

Saya tidak mencoba menjawab pertanyaan tersebut selama seminar, karena saya tahu ini mungkin agak sulit untuk dilakukan.

Tapi, saya baru-baru ini memulai makro untuk melihat apakah ini bisa dilakukan. Ide saya adalah membuat string teks panjang yang bisa ditempelkan. Untuk memaksa item muncul dalam dua kolom, string teks harus memiliki label untuk kolom 1 (Sum) dan kemudian Tab, dan nilai untuk kolom 2. Anda akan membutuhkan carriage return, label untuk baris 2, kolom 1, lalu tab lain, nilai, dan seterusnya.

Saya tahu bahwa Application.WorksheetFunction adalah cara terbaik untuk mengembalikan hasil fungsi Excel ke VBA, tetapi tidak mendukung semua 400+ fungsi Excel. Terkadang, jika VBA sudah memiliki fungsi serupa (LEFT, RIGHT, MID), maka Application.WorksheetFunction tidak akan mendukung fungsi itu. Saya menjalankan VBA dengan Alt + F11, menampilkan Panel Langsung dengan Ctrl + G, dan kemudian mengetik beberapa perintah untuk memastikan keenam fungsi bilah status didukung. Untungnya, keenam nilai yang dikembalikan cocok dengan yang muncul di bilah status.

Untuk membuat makro lebih pendek, Anda bisa menetapkan Application.WorksheetFunction ke variabel:

Set WF = Application.WorksheetFunction

Kemudian, nanti di makro, Anda cukup merujuk ke WF.Sum (Pilihan) alih-alih mengetikkan Application.WorksheetFunction berulang kali.

Apa kode ASCII untuk Tab?

Saya mulai membuat string teks. Saya memilih variabel MS untuk MyString.

MS = "Sum:" &

Ini adalah titik di mana saya membutuhkan karakter tab. Saya cukup culun untuk mengetahui beberapa karakter ASCII (10 = LineFeed, 13 = Carriage Return, 32 = Space, 65 = A, 90 = Z), tetapi saya tidak dapat mengingat Tab. Ketika saya hendak menuju Bing untuk mencarinya, saya ingat bahwa Anda dapat menggunakan vblf dalam kode Anda untuk linefeed atau vbcr dalam kode Anda untuk carriage return, jadi saya mengetik vbtab dalam huruf kecil. Saya kemudian pindah ke baris baru untuk memungkinkan Excel VBA menggunakan huruf besar dari kata-kata yang dipahami. Saya berharap untuk melihat vbtab mengambil modal, dan tentu saja, garis menjadi kapital, menunjukkan bahwa VBA akan memberi saya karakter tab.

Jika Anda mengetik VBA dalam huruf kecil, saat Anda pergi ke baris baru, Anda akan melihat semua kata yang dieja dengan benar mengambil huruf kapital di suatu tempat di kata tersebut. Pada gambar di bawah, vblf, vbcr, vbtab dikenal sebagai vba dan dikapitalisasi setelah pindah ke baris baru. Namun, hal yang saya buat, vbampersand bukanlah hal yang diketahui VBA, jadi tidak dikapitalisasi.

Pada titik ini, itu masalah menggabungkan 6 label dan 6 nilai menjadi satu string panjang. Ingatlah pada kode di bawah ini bahwa _ pada akhir setiap baris berarti baris kode tersebut dilanjutkan pada baris berikutnya.

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

Setelah menggabungkan semua label dan nilai bersama, saya ingin mengagumi pekerjaan saya, jadi saya menampilkan hasilnya di MsgBox. Saya menjalankan kodenya, dan itu bekerja dengan baik:

Saya pikir saya bebas dari rumah. Jika saya bisa memasukkan MS ke clipboard, saya bisa mulai merekam Podcast 1894. Mungkin MS.Copy akan melakukan triknya?

Sayangnya, ternyata tidak semudah itu. MS.Copy bukanlah baris kode yang valid.

Jadi, saya pergi ke Google dan mencari "Variabel Salin VBA Excel ke Clipboard". Salah satu hasil teratas adalah posting ini di Papan Pesan. Di postingan itu, teman lama saya Juan Pablo dan NateO berusaha membantu OP. Namun, tip sebenarnya adalah saat Juan Pablo menyarankan untuk menggunakan beberapa kode dari situs Excel MVP Chip Pearson. Saya menemukan halaman ini yang menjelaskan cara memasukkan variabel ke clipboard.

Untuk menambahkan sesuatu ke clipboard, Anda harus terlebih dahulu masuk ke menu Alat jendela VBA dan memilih Referensi. Awalnya Anda akan melihat beberapa referensi dicentang secara default. Perpustakaan Microsoft Forms 2.0 tidak akan diperiksa. Anda perlu menemukannya di daftar yang sangat panjang dan menambahkannya. Untungnya, bagi saya, itu ada di halaman pertama pilihan, tentang di mana panah hijau menunjukkannya. Setelah Anda menambahkan tanda centang di sebelah referensi, itu bergerak ke atas.

Kode chip tidak akan berfungsi jika Anda tidak menambahkan referensi, jadi jangan lewatkan langkah di atas!

Setelah Anda menambahkan referensi, selesaikan makro menggunakan kode 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

Sebelum merekam podcast, saya melakukan tes untuk memastikannya berfungsi. Benar saja, ketika saya menjalankan makro, lalu memilih rentang baru dan menekan Ctrl + V untuk menempel, papan klip dikosongkan menjadi rentang kolom 6 baris x 2.

Whoo-hoo! Saya menyiapkan kartu judul PowerPoint untuk episode tersebut, menyalakan Camtasia Recorder, dan merekam semua yang ada di atas. Tapi… saat saya akan menunjukkan kredit penutup, perasaan mengganggu menyelimuti saya. Makro ini menempelkan statistik sebagai nilai statis. Bagaimana jika data pokok berubah? Tidakkah Anda ingin blok yang ditempel diperbarui? Ada jeda panjang di podcast di mana saya mempertimbangkan apa yang harus dilakukan. Akhirnya, saya mengklik ikon Camtasia Pause Recording dan pergi untuk melihat apakah saya bisa meletakkan formula di dalam string MS dan apakah itu akan ditempel dengan benar. Benar saja, itu berhasil. Saya bahkan tidak menyelesaikan makro sepenuhnya atau melakukan lebih dari satu tes ketika saya menyalakan perekam kembali dan membicarakan tentang makro ini. Di podcast, saya berteori bahwa ini tidak akan pernah berfungsi untuk pilihan yang tidak bersebelahan, tetapi dalam pengujian selanjutnya, itu berhasil.Berikut adalah makro untuk ditempelkan sebagai rumus:

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

Setelah memposting video, penonton reguler Mike Fliss bertanya apakah ada cara untuk membuat formula yang akan terus diperbarui untuk menunjukkan statistik untuk rentang apa pun yang dipilih. Ini akan membutuhkan makro Worksheet_SelectionChange yang akan terus memperbarui rentang bernama agar cocok dengan pilihan. Meskipun ini adalah tipuan yang keren, ini memaksa makro untuk berjalan setiap kali Anda memindahkan penunjuk sel, dan itu akan terus menghapus tumpukan UnDo. Jadi, jika Anda menggunakan makro ini, itu harus ditambahkan ke setiap panel kode lembar kerja tempat Anda ingin bekerja, dan Anda harus hidup tanpa Batalkan pada lembar kerja tersebut.

Pertama, dari Excel, Klik kanan pada tab lembar dan pilih Lihat Kode. Kemudian, tempel kode ini.

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

Beralih kembali ke Excel. Pilih sel baru dan ketikkan rumusnya =SUM(SelectedData). Anda awalnya akan mendapatkan referensi melingkar. Tapi, lalu pilih rentang sel numerik lain dan total rumus yang baru saja Anda buat akan diperbarui.