Copier les valeurs de statistiques rapides dans le presse-papiers - Conseils Excel

Table des matières

Quel est le code ASCII d'un onglet?

La question est venue lors d'un séminaire Excel à Tampa: ne serait-il pas cool si vous pouviez copier les statistiques de la barre d'état dans le presse-papiers pour les coller plus tard dans une plage?

J'ai pressé la personne qui a posé la question sur le fonctionnement exact de la pâte. Bien sûr, vous ne pouvez pas coller les statistiques immédiatement, car vous avez sélectionné un tas de cellules importantes. Vous devrez attendre, sélectionner une autre plage vide de la feuille de calcul, le coller (comme dans Ctrl + V) et les statistiques apparaîtront dans une plage de 6 lignes par 2 colonnes. La personne qui a posé la question a suggéré qu'il s'agirait de valeurs statiques.

Je n'ai pas essayé de répondre à la question pendant le séminaire, car je savais que cela pourrait être un peu difficile de réussir.

Mais, j'ai récemment lancé une macro pour voir si cela pouvait être fait. Mon idée était de créer une longue chaîne de texte qui pourrait être collée. Afin de forcer les éléments à apparaître dans deux colonnes, la chaîne de texte devrait avoir l'étiquette de la colonne 1 (Somme), puis une tabulation, et la valeur de la colonne 2. Vous auriez alors besoin d'un retour chariot, l'étiquette pour ligne 2, colonne 1, puis un autre onglet, la valeur, etc.

Je savais que Application.WorksheetFunction est un excellent moyen de renvoyer les résultats des fonctions Excel à VBA, mais qu'il ne prend pas en charge toutes les 400 fonctions Excel. Parfois, si VBA a déjà une fonction similaire (GAUCHE, DROITE, MID), Application.WorksheetFunction ne prendra pas en charge cette fonction. J'ai lancé VBA avec Alt + F11, affiché le volet immédiat avec Ctrl + G, puis tapé quelques commandes pour m'assurer que les six fonctions de la barre d'état étaient prises en charge. Heureusement, les six ont renvoyé des valeurs correspondant à ce qui apparaissait dans la barre d'état.

Pour raccourcir la macro, vous pouvez affecter Application.WorksheetFunction à une variable:

Set WF = Application.WorksheetFunction

Ensuite, plus loin dans la macro, vous pouvez simplement vous référer à WF.Sum (Selection) au lieu de taper Application.WorksheetFunction encore et encore.

Quel est le code ASCII d'un onglet?

J'ai commencé à créer la chaîne de texte. J'ai choisi une variable de MS pour MyString.

MS = "Sum:" &

C'est le point où j'avais besoin d'un caractère de tabulation. Je suis assez geek pour connaître quelques caractères ASCII (10 = LineFeed, 13 = Carriage Return, 32 = Space, 65 = A, 90 = Z), mais je ne me souvenais pas de l'onglet. Alors que j'allais me rendre sur Bing pour le rechercher, je me suis souvenu que vous pouviez utiliser vblf dans votre code pour un saut de ligne ou vbcr dans votre code pour un retour chariot, j'ai donc tapé vbtab en minuscules. Je suis ensuite passé à une nouvelle ligne pour permettre à Excel VBA de mettre en majuscule les mots qu'il comprenait. J'espérais voir le vbtab prendre une majuscule, et bien sûr, la ligne est devenue en majuscule, indiquant que VBA allait me donner un caractère de tabulation.

Si vous tapez votre VBA en minuscules, lorsque vous passez à une nouvelle ligne, vous verrez tous les mots correctement orthographiés prendre une majuscule quelque part dans le mot. Dans l'image ci-dessous, vblf, vbcr, vbtab sont connus de vba et sont capitalisés après le passage à une nouvelle ligne. Cependant, la chose que j'ai inventée, vbampersand, n'est pas connue de VBA, elle n'est donc pas capitalisée.

À ce stade, il s'agissait de joindre 6 étiquettes et 6 valeurs en une seule longue chaîne. Rappelez-vous dans le code ci-dessous que le _ à la fin de chaque ligne signifie que la ligne de code se poursuit sur la ligne suivante.

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

Après avoir réuni toutes les étiquettes et toutes les valeurs, j'ai voulu admirer mon travail, j'ai donc affiché le résultat dans une MsgBox. J'ai exécuté le code et cela a fonctionné à merveille:

Je pensais que j'étais chez moi libre. Si je pouvais simplement mettre MS dans le presse-papiers, je pourrais commencer à enregistrer le Podcast 1894. Peut-être que MS.Copy ferait l'affaire?

Malheureusement, ce n’était pas si simple. MS.Copy n'était pas une ligne de code valide.

Donc, je suis allé sur Google et j'ai recherché "Excel VBA Copy Variable to Clipboard". L'un des meilleurs résultats a été ce message sur le babillard électronique. Dans ce post, mes vieux amis Juan Pablo et NateO essayaient d'aider l'OP. L'astuce réelle, cependant, était où Juan Pablo a suggéré d'utiliser du code du site d'Excel MVP Chip Pearson. J'ai trouvé cette page qui expliquait comment mettre la variable dans le presse-papiers.

Pour ajouter quelque chose au presse-papiers, vous devez d'abord aller dans le menu Outils de la fenêtre VBA et choisir Références. Vous verrez initialement quelques références vérifiées par défaut. La bibliothèque Microsoft Forms 2.0 ne sera pas vérifiée. Vous devez le trouver dans la très longue liste et l'ajouter. Heureusement, pour moi, c'était sur la première page de choix, à propos de l'endroit où la flèche verte le montre. Une fois que vous avez ajouté la coche à côté de la référence, elle se déplace vers le haut.

Le code de la puce ne fonctionnera pas si vous n'ajoutez pas la référence, alors ne sautez pas l'étape ci-dessus!

Une fois que vous avez ajouté la référence, terminez la macro en utilisant le code de 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

Avant d'enregistrer le podcast, j'ai fait un test pour m'assurer qu'il fonctionnait. Effectivement, lorsque j'ai exécuté la macro, puis sélectionné une nouvelle plage et appuyé sur Ctrl + V pour coller, le presse-papiers a été vidé dans une plage de 6 lignes x 2 colonnes.

Whoo Hoo! J'ai préparé la carte de titre PowerPoint pour l'épisode, allumé Camtasia Recorder et enregistré tout ce qui précède. Mais… alors que j'étais sur le point de montrer le générique de clôture, un sentiment lancinant m'envahit. Cette macro collait les statistiques sous forme de valeurs statiques. Et si les données sous-jacentes changeaient? Ne voudriez-vous pas que le bloc collé se mette à jour? Il y a eu une longue pause dans le podcast où j'ai réfléchi à ce qu'il fallait faire. Enfin, j'ai cliqué sur l'icône Camtasia Pause Recording et je suis allé voir si je pouvais mettre une formule dans la chaîne MS et si elle serait collée correctement. Effectivement, c'est le cas. Je n'ai même pas terminé complètement la macro ou fait plus d'un test lorsque j'ai rallumé l'enregistreur et parlé de cette macro. Dans le podcast, j'ai émis l'hypothèse que cela ne fonctionnerait jamais pour les sélections non contiguës, mais lors de tests ultérieurs, cela fonctionne.Voici la macro à coller sous forme de formules:

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

Après avoir publié la vidéo, le spectateur régulier Mike Fliss a demandé s'il existe un moyen de créer des formules qui seraient constamment mises à jour pour afficher les statistiques pour la plage sélectionnée. Cela nécessiterait une macro Worksheet_SelectionChange qui mettrait constamment à jour une plage nommée pour correspondre à la sélection. Bien que ce soit un peu de supercherie, cela force une macro à s'exécuter chaque fois que vous déplacez le pointeur de cellule, ce qui va constamment effacer la pile d'annulation. Donc, si vous utilisez cette macro, elle doit être ajoutée à chaque volet de code de feuille de calcul où vous voulez qu'elle fonctionne, et vous devrez vivre sans Annuler sur ces feuilles de calcul.

Tout d'abord, à partir d'Excel, cliquez avec le bouton droit sur un onglet de feuille et choisissez Afficher le code. Ensuite, collez ce code dans.

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

Revenez à Excel. Sélectionnez une nouvelle cellule et tapez la formule =SUM(SelectedData). Vous obtiendrez initialement une référence circulaire. Mais, sélectionnez une autre plage de cellules numériques et le total de la formule que vous venez de créer sera mis à jour.