Copie os valores das estatísticas rápidas para a área de transferência - dicas do Excel

Índice

Qual é o código ASCII para uma guia?

A pergunta surgiu durante um seminário do Excel em Tampa: Não seria legal se você pudesse copiar as estatísticas da barra de status para a área de transferência para depois colar em um intervalo?

Pressionei a pessoa que fez a pergunta sobre como exatamente a pasta deveria funcionar. Claro, você não pode colar as estatísticas imediatamente, porque você tem um monte de células importantes selecionadas. Você teria que esperar, selecionar outro intervalo em branco da planilha, colar (como em Ctrl + V) e as estatísticas apareceriam em um intervalo de 6 linhas por 2 colunas. A pessoa que fez a pergunta sugeriu que seriam valores estáticos.

Não tentei responder à pergunta durante o seminário, porque sabia que seria um pouco complicado fazer isso.

Mas, recentemente comecei uma macro para ver se isso poderia ser feito. Minha ideia era construir uma longa string de texto que pudesse ser colada. Para forçar os itens a aparecerem em duas colunas, a string de texto teria que ter o rótulo para a coluna 1 (Soma) e, em seguida, um Tab, e o valor para a coluna 2. Você então precisaria de um retorno de carro, o rótulo para linha 2, coluna 1, depois outra guia, o valor e assim por diante.

Eu sabia que Application.WorksheetFunction é uma ótima maneira de retornar os resultados das funções do Excel para o VBA, mas que não oferece suporte a todas as mais de 400 funções do Excel. Às vezes, se o VBA já tiver uma função semelhante (LEFT, RIGHT, MID), então Application.WorksheetFunction não suportará essa função. Ativei o VBA com Alt + F11, exibi o Painel Imediato com Ctrl + G e digitei alguns comandos para ter certeza de que todas as seis funções da barra de status eram suportadas. Felizmente, todos os seis valores retornaram que correspondiam ao que estava aparecendo na barra de status.

Para tornar a macro mais curta, você pode atribuir Application.WorksheetFunction a uma variável:

Set WF = Application.WorksheetFunction

Em seguida, posteriormente na macro, você pode simplesmente referir-se a WF.Sum (Seleção) em vez de digitar Application.WorksheetFunction repetidamente.

Qual é o código ASCII para uma guia?

Comecei a construir a string de texto. Eu escolhi uma variável de MS para MyString.

MS = "Sum:" &

Este é o ponto onde eu precisava de um caractere de tabulação. Eu sou geek o suficiente para saber alguns caracteres ASCII (10 = LineFeed, 13 = Carriage Return, 32 = Space, 65 = A, 90 = Z), mas não conseguia me lembrar da guia. Quando eu estava prestes a ir ao Bing pesquisar, lembrei que você poderia usar vblf em seu código para um feed de linha ou vbcr em seu código para um retorno de carro, então digitei vbtab em letras minúsculas. Em seguida, mudei para uma nova linha para permitir que o Excel VBA capitalize as palavras que ele entendeu. Eu esperava ver o vbtab pegar uma letra maiúscula e, com certeza, a linha ficou em maiúscula, indicando que o VBA iria me dar um caractere de tabulação.

Se você digitar seu VBA em letras minúsculas, quando for para uma nova linha, verá que todas as palavras digitadas corretamente pegam uma letra maiúscula em algum lugar da palavra. Na imagem abaixo, vblf, vbcr, vbtab são conhecidos por vba e são capitalizados após a mudança para uma nova linha. No entanto, o que eu inventei, vbampersand, não é conhecido pelo VBA, então não é capitalizado.

Nesse ponto, era uma questão de juntar 6 rótulos e 6 valores em uma longa string. Lembre-se no código a seguir de que _ no final de cada linha significa que a linha de código continua na próxima linha.

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

Depois de juntar todos os rótulos e valores, eu queria admirar meu trabalho, então exibi o resultado em um MsgBox. Executei o código e funcionou perfeitamente:

Eu pensei que estava em casa livre. Se eu pudesse apenas colocar o MS na área de transferência, poderia começar a gravar o Podcast 1894. Talvez o MS.Copy resolvesse o problema?

Infelizmente, não foi assim tão fácil. MS.Copy não era uma linha de código válida.

Então, fui ao Google e procurei por "Excel VBA Copy Variable to Clipboard". Um dos principais resultados foi esta postagem no Message Board. Nesse post, meus velhos amigos Juan Pablo e NateO estavam tentando ajudar o OP. A dica real, porém, foi onde Juan Pablo sugeriu usar algum código do site do Excel MVP Chip Pearson. Encontrei esta página que explica como obter a variável para a área de transferência.

Para adicionar algo à área de transferência, você precisa primeiro ir ao menu Ferramentas da janela do VBA e escolher Referências. Você verá inicialmente algumas referências marcadas por padrão. A biblioteca do Microsoft Forms 2.0 não será verificada. Você precisa encontrá-lo em uma lista muito longa e adicioná-lo. Felizmente, para mim, estava na primeira página de opções, sobre onde a seta verde mostra. Depois de adicionar a marca de seleção ao lado da referência, ela se move para o topo.

O código do chip não funcionará se você não adicionar a referência, portanto, não pule a etapa acima!

Depois de adicionar a referência, termine a macro usando o 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 gravar o podcast, fiz um teste para verificar se estava funcionando. Com certeza, quando executei a macro, selecionei um novo intervalo e pressionei Ctrl + V para colar, a área de transferência foi esvaziada em um intervalo de 6 linhas x 2 colunas.

Uau! Eu preparei o cartão de título do PowerPoint para o episódio, liguei o Camtasia Recorder e gravei tudo acima. Mas … quando eu estava prestes a mostrar os créditos finais, uma sensação incômoda tomou conta de mim. Esta macro estava colando as estatísticas como valores estáticos. E se os dados subjacentes mudassem? Você não gostaria que o bloco colado fosse atualizado? Houve uma longa pausa no podcast onde pensei no que fazer. Finalmente, cliquei no ícone Camtasia Pause Recording e fui ver se eu poderia colocar uma fórmula dentro da string MS e se ela seria colada corretamente. Com certeza, sim. Eu nem mesmo terminei completamente a macro ou fiz mais de um teste quando liguei o gravador novamente e falei sobre esta macro. No podcast, teorizei que isso nunca funcionaria para seleções não contíguas, mas em testes posteriores, funciona.Aqui está a macro para colar 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

Depois de postar o vídeo, o espectador regular Mike Fliss perguntou se há uma maneira de construir as fórmulas que seriam atualizadas constantemente para mostrar as estatísticas para qualquer intervalo selecionado. Isso exigiria uma macro Worksheet_SelectionChange que atualizaria constantemente um intervalo nomeado para corresponder à seleção. Embora seja um truque legal, ele força a execução de uma macro toda vez que você move o ponteiro da célula, e isso limpará constantemente a pilha UnDo. Portanto, se você usar essa macro, ela deverá ser adicionada a todos os painéis de código da planilha onde você deseja que funcione, e você terá que viver sem Desfazer nessas planilhas.

Primeiro, no Excel, clique com o botão direito em uma guia de planilha e escolha Exibir código. Em seguida, cole este código.

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

Volte para o Excel. Selecione uma nova célula e digite a fórmula =SUM(SelectedData). Você obterá inicialmente uma referência circular. Porém, selecione outro intervalo de células numéricas e o total da fórmula que você acabou de criar será atualizado.