Copia i valori delle statistiche rapide negli Appunti - Suggerimenti per Excel

Sommario

Qual è il codice ASCII per una scheda?

La domanda è arrivata durante un seminario su Excel a Tampa: non sarebbe bello se potessi copiare le statistiche dalla barra di stato negli appunti per incollarle successivamente in un intervallo?

Ho pressato la persona che ha posto la domanda su come dovrebbe funzionare esattamente la pasta. Ovviamente, non puoi incollare immediatamente le statistiche, perché hai selezionato un gruppo di celle importanti. Dovresti aspettare, selezionare un altro intervallo vuoto del foglio di calcolo, l'incolla (come in Ctrl + V) e le statistiche appariranno in un intervallo di 6 righe per 2 colonne. La persona che ha posto la domanda ha suggerito che sarebbero stati valori statici.

Non ho provato a rispondere alla domanda durante il seminario, perché sapevo che poteva essere un po 'complicato ottenere questo risultato.

Ma di recente ho avviato una macro per vedere se questo poteva essere fatto. La mia idea era quella di creare una lunga stringa di testo che potesse essere incollata. Per forzare la visualizzazione degli elementi in due colonne, la stringa di testo dovrebbe avere l'etichetta per la colonna 1 (Somma), quindi una Tabulazione e il valore per la colonna 2. Avrai quindi bisogno di un ritorno a capo, l'etichetta riga 2, colonna 1, quindi un'altra scheda, il valore e così via.

Sapevo che Application.WorksheetFunction è un ottimo modo per restituire i risultati delle funzioni di Excel a VBA, ma che non supporta tutte le oltre 400 funzioni di Excel. A volte, se VBA ha già una funzione simile (LEFT, RIGHT, MID), Application.WorksheetFunction non supporterà quella funzione. Ho attivato VBA con Alt + F11, visualizzato il riquadro immediato con Ctrl + G, quindi ho digitato alcuni comandi per assicurarmi che tutte e sei le funzioni della barra di stato fossero supportate. Fortunatamente, tutti e sei i valori hanno restituito che corrispondevano a ciò che veniva visualizzato nella barra di stato.

Per abbreviare la macro, puoi assegnare Application.WorksheetFunction a una variabile:

Set WF = Application.WorksheetFunction

Quindi, più avanti nella macro, puoi semplicemente fare riferimento a WF.Sum (Selezione) invece di digitare Application.WorksheetFunction più e più volte.

Qual è il codice ASCII per una scheda?

Ho iniziato a costruire la stringa di testo. Ho scelto una variabile di MS per MyString.

MS = "Sum:" &

Questo è il punto in cui avevo bisogno di un carattere di tabulazione. Sono abbastanza geniale da conoscere alcuni caratteri ASCII (10 = LineFeed, 13 = Carriage Return, 32 = Space, 65 = A, 90 = Z), ma non riuscivo a ricordare la scheda. Mentre stavo per andare a Bing per cercarlo, mi sono ricordato che potresti usare vblf nel tuo codice per un avanzamento riga o vbcr nel tuo codice per un ritorno a capo, quindi ho digitato vbtab in minuscolo. Sono quindi passato a una nuova riga per consentire a Excel VBA di scrivere in maiuscolo le parole che comprendeva. Speravo di vedere vbtab prendere una maiuscola e, abbastanza sicuro, la linea è diventata maiuscola, indicando che VBA mi avrebbe dato un carattere di tabulazione.

Se digiti il tuo VBA in minuscolo, quando vai su una nuova riga, vedrai tutte le parole digitate correttamente prendere una lettera maiuscola da qualche parte nella parola. Nell'immagine seguente, vblf, vbcr, vbtab sono noti a vba e vengono scritti in maiuscolo dopo essere passati a una nuova riga. Tuttavia, la cosa che ho inventato, vbampersand non è una cosa nota a VBA, quindi non viene capitalizzata.

A questo punto, si trattava di unire 6 etichette e 6 valori in una lunga stringa. Ricorda nel codice sottostante che il _ alla fine di ogni riga significa che la riga di codice continua sulla riga successiva.

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

Dopo aver unito tutte le etichette e i valori insieme, volevo ammirare il mio lavoro, quindi ho visualizzato il risultato in un MsgBox. Ho eseguito il codice e ha funzionato magnificamente:

Pensavo di essere a casa libera. Se solo potessi inserire MS negli appunti, potrei iniziare a registrare il Podcast 1894. Forse MS.Copy farebbe il trucco?

Purtroppo non è stato così facile. MS.Copy non era una riga di codice valida.

Quindi, sono andato su Google e ho cercato "Excel VBA Copia variabile negli Appunti". Uno dei risultati migliori è stato questo post nella bacheca. In quel post, i miei vecchi amici Juan Pablo e NateO stavano cercando di aiutare l'OP. Il vero suggerimento, tuttavia, è stato dove Juan Pablo ha suggerito di utilizzare del codice dal sito di Excel MVP Chip Pearson. Ho trovato questa pagina che spiega come ottenere la variabile negli appunti.

Per aggiungere qualcosa agli appunti, devi prima andare al menu Strumenti della finestra VBA e scegliere Riferimenti. Inizialmente vedrai alcuni riferimenti controllati per impostazione predefinita. La libreria Microsoft Forms 2.0 non verrà controllata. Devi trovarlo nell'elenco molto lungo e aggiungerlo. Fortunatamente, per me, era sulla prima pagina delle scelte, su dove lo mostra la freccia verde. Dopo aver aggiunto il segno di spunta accanto al riferimento, si sposta in alto.

Il codice di Chip non funzionerà se non aggiungi il riferimento, quindi non saltare il passaggio precedente!

Una volta aggiunto il riferimento, completa la macro utilizzando il codice di 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

Prima di registrare il podcast, ho fatto un test per assicurarmi che funzionasse. Abbastanza sicuro, quando ho eseguito la macro, quindi ho selezionato un nuovo intervallo e ho premuto Ctrl + V per incollare, gli appunti sono stati svuotati in un intervallo di 6 righe x 2 colonne.

Whoo-hoo! Ho preparato la scheda del titolo PowerPoint per l'episodio, ho acceso Camtasia Recorder e ho registrato tutto quanto sopra. Ma … mentre stavo per mostrare i titoli di coda, mi è venuta una sensazione fastidiosa. Questa macro incollava le statistiche come valori statici. Cosa succede se i dati sottostanti cambiano? Non vorresti aggiornare il blocco incollato? C'è stata una lunga pausa nel podcast in cui ho considerato cosa fare. Infine, ho fatto clic sull'icona Camtasia Pause Recording e sono andato a vedere se potevo inserire una formula all'interno della stringa MS e se sarebbe stata incollata correttamente. Abbastanza sicuro, lo ha fatto. Non ho nemmeno finito completamente la macro o fatto più di un test quando ho riacceso il registratore e ho parlato di questa macro. Nel podcast, ho teorizzato che questo non avrebbe mai funzionato per selezioni non contigue, ma nei test successivi, funziona.Ecco la macro da incollare come formule:

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

Dopo aver pubblicato il video, il normale spettatore Mike Fliss ha chiesto se esiste un modo per creare le formule che si aggiornerebbero costantemente per mostrare le statistiche per qualsiasi intervallo selezionato. Ciò richiederebbe una macro Worksheet_SelectionChange che aggiorna costantemente un intervallo denominato in modo che corrisponda alla selezione. Anche se questo è un bel trucco, forza l'esecuzione di una macro ogni volta che sposti il puntatore della cella, e questo cancellerà costantemente lo stack UnDo. Quindi, se usi questa macro, deve essere aggiunta a ogni riquadro del codice del foglio di lavoro in cui vuoi che funzioni e dovrai vivere senza Annulla su quei fogli di lavoro.

Innanzitutto, da Excel, fai clic con il pulsante destro del mouse su una scheda del foglio e scegli Visualizza codice. Quindi, incolla questo codice.

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

Torna a Excel. Seleziona una nuova cella e digita la formula =SUM(SelectedData). Inizialmente otterrai un riferimento circolare. Ma poi seleziona un altro intervallo di celle numeriche e il totale della formula che hai appena creato verrà aggiornato.