Copie los valores de Estadísticas rápidas en el portapapeles - Consejos de Excel

Tabla de contenido

¿Qué es el código ASCII de una pestaña?

La pregunta surgió durante un seminario de Excel en Tampa: ¿No sería genial si pudiera copiar las estadísticas de la barra de estado al portapapeles para luego pegarlas en un rango?

Presioné a la persona que hizo la pregunta sobre cómo debería funcionar exactamente la pasta. Por supuesto, no puede pegar las estadísticas inmediatamente, porque tiene un montón de celdas importantes seleccionadas. Tendría que esperar, seleccionar otro rango en blanco de la hoja de cálculo, pegar (como en Ctrl + V) y las estadísticas aparecerían en un rango de 6 filas por 2 columnas. La persona que hizo la pregunta sugirió que serían valores estáticos.

No intenté responder a la pregunta durante el seminario, porque sabía que podría ser un poco complicado lograrlo.

Pero, recientemente comencé una macro para ver si esto se podía hacer. Mi idea era crear una cadena de texto larga que pudiera pegarse. Para forzar que los elementos aparezcan en dos columnas, la cadena de texto tendría que tener la etiqueta de la columna 1 (Suma) y luego una Tab, y el valor de la columna 2. Luego, necesitaría un retorno de carro, la etiqueta de fila 2, columna 1, luego otra pestaña, el valor, y así sucesivamente.

Sabía que Application.WorksheetFunction es una excelente manera de devolver los resultados de las funciones de Excel a VBA, pero que no es compatible con las más de 400 funciones de Excel. A veces, si VBA ya tiene una función similar (LEFT, RIGHT, MID), Application.WorksheetFunction no admitirá esa función. Encendí VBA con Alt + F11, mostré el Panel Inmediato con Ctrl + G y luego escribí algunos comandos para asegurarme de que las seis funciones de la barra de estado fueran compatibles. Afortunadamente, los seis devolvieron valores que coincidían con lo que aparecía en la barra de estado.

Para acortar la macro, puede asignar Application.WorksheetFunction a una variable:

Set WF = Application.WorksheetFunction

Luego, más adelante en la macro, puede simplemente hacer referencia a WF.Sum (Selección) en lugar de escribir Application.WorksheetFunction una y otra vez.

¿Qué es el código ASCII de una pestaña?

Empecé a construir la cadena de texto. Elegí una variable de MS para MyString.

MS = "Sum:" &

Este es el punto en el que necesitaba un carácter de tabulación. Soy lo suficientemente geek como para conocer algunos caracteres ASCII (10 = LineFeed, 13 = Carriage Return, 32 = Space, 65 = A, 90 = Z), pero no podía recordar la Tab. Cuando estaba a punto de dirigirme a Bing para buscarlo, recordé que podía usar vblf en su código para un salto de línea o vbcr en su código para un retorno de carro, así que escribí vbtab en minúsculas. Luego pasé a una nueva línea para permitir que Excel VBA escribiera en mayúscula las palabras que entendía. Esperaba ver que vbtab tomara una mayúscula y, efectivamente, la línea se puso en mayúscula, lo que indica que VBA me iba a dar un carácter de tabulación.

Si escribe su VBA en minúsculas, cuando vaya a una nueva línea, verá que todas las palabras escritas correctamente toman una letra mayúscula en algún lugar de la palabra. En la imagen de abajo, vblf, vbcr, vbtab son conocidos por vba y se capitalizan después de pasar a una nueva línea. Sin embargo, lo que inventé, vbampersand no es algo conocido para VBA, por lo que no se capitaliza.

En este punto, era cuestión de unir 6 etiquetas y 6 valores en una cadena larga. Recuerde en el código de abajo que el _ al final de cada línea significa que la línea de código continúa en la línea siguiente.

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

Después de unir todas las etiquetas y valores, quería admirar mi trabajo, así que mostré el resultado en un MsgBox. Ejecuté el código y funcionó a la perfección:

Pensé que estaba libre en casa. Si pudiera poner MS en el portapapeles, podría comenzar a grabar Podcast 1894. ¿Quizás MS.Copy haría el truco?

Desafortunadamente, no fue tan fácil. MS.Copy no era una línea de código válida.

Entonces, fui a Google y busqué "Excel VBA Copiar variable al portapapeles". Uno de los mejores resultados fue esta publicación en el tablero de mensajes. En ese post, mis viejos amigos Juan Pablo y NateO intentaban ayudar al OP. Sin embargo, la sugerencia real fue donde Juan Pablo sugirió usar algún código del sitio de Excel MVP Chip Pearson. Encontré esta página que explica cómo colocar la variable en el portapapeles.

Para agregar algo al portapapeles, primero debe ir al menú Herramientas de la ventana de VBA y elegir Referencias. Inicialmente verá algunas referencias marcadas de forma predeterminada. No se comprobará la biblioteca de Microsoft Forms 2.0. Debe encontrarlo en la lista muy larga y agregarlo. Afortunadamente, para mí, estaba en la primera página de opciones, sobre dónde lo muestra la flecha verde. Una vez que agrega la marca de verificación junto a la referencia, se mueve hacia la parte superior.

El código de Chip no funcionará si no agrega la referencia, ¡así que no omita el paso anterior!

Una vez que agregue la referencia, termine la macro usando el código 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

Antes de grabar el podcast, hice una prueba para asegurarme de que funcionaba. Efectivamente, cuando ejecuté la macro, luego seleccioné un nuevo rango y presioné Ctrl + V para pegar, el portapapeles se vació en un rango de 6 filas x 2 columnas.

¡Whoo-hoo! Preparé la tarjeta de presentación de PowerPoint para el episodio, encendí Camtasia Recorder y grabé todo lo anterior. Pero… cuando estaba a punto de mostrar los créditos finales, me invadió una sensación de fastidio. Esta macro estaba pegando las estadísticas como valores estáticos. ¿Qué pasa si los datos subyacentes cambian? ¿No le gustaría que el bloque pegado se actualizara? Hubo una larga pausa en el podcast donde consideré qué hacer. Finalmente, hice clic en el ícono de grabación en pausa de Camtasia y fui a ver si podía poner una fórmula dentro de la cadena MS y si se pegaba correctamente. Efectivamente, lo hizo. Ni siquiera terminé completamente la macro ni hice más de una prueba cuando volví a encender la grabadora y hablé sobre esta macro. En el podcast, teoricé que esto nunca funcionaría para selecciones no contiguas, pero en pruebas posteriores, sí funciona.Aquí está la macro para pegar como fórmulas:

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

Después de publicar el video, el espectador habitual Mike Fliss preguntó si hay una manera de crear fórmulas que se actualicen constantemente para mostrar las estadísticas de cualquier rango seleccionado. Esto requeriría una macro Worksheet_SelectionChange que actualizaría constantemente un rango con nombre para que coincida con la selección. Si bien esto es un truco genial, obliga a que se ejecute una macro cada vez que mueve el puntero de la celda, y eso va a borrar constantemente la pila Deshacer. Por lo tanto, si usa esta macro, debe agregarla a cada panel de código de la hoja de trabajo donde desea que funcione, y tendrá que vivir sin Deshacer en esas hojas de trabajo.

Primero, desde Excel, haga clic con el botón derecho en la pestaña de una hoja y elija Ver código. Luego, pega este código en formato.

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

Regrese a Excel. Seleccione una nueva celda y escriba la fórmula =SUM(SelectedData). Inicialmente obtendrá una referencia circular. Pero, luego seleccione otro rango de celdas numéricas y el total de la fórmula que acaba de crear se actualizará.