Neste tutorial, aprenderemos sobre a função Excel VBA
1) O que é Visual Basic no Excel?
2) Como usar o VBA no Excel?
3) Como criar uma função definida pelo usuário?
4) Como escrever Macro?
Como escrever código VBA
O Excel fornece ao usuário uma grande coleção de funções prontas, mais do que o suficiente para satisfazer o usuário médio. Muitos mais podem ser adicionados instalando os vários suplementos disponíveis. A maioria dos cálculos pode ser realizada com o que é fornecido, mas não demorará muito para que você deseje que haja uma função que faça um trabalho específico e você não consiga encontrar nada adequado na lista. Você precisa de um UDF. Uma UDF (Função Definida pelo Usuário) é simplesmente uma função que você mesmo cria com o VBA. UDFs são freqüentemente chamados de "Funções Personalizadas". Uma UDF pode permanecer em um módulo de código anexado a uma pasta de trabalho e, nesse caso, estará sempre disponível quando a pasta de trabalho for aberta. Como alternativa, você pode criar seu próprio suplemento contendo uma ou mais funções que podem ser instaladas no Excel como um suplemento comercial. UDFs também podem ser acessados por módulos de código. Freqüentemente, UDFs são criadas por desenvolvedores para trabalhar exclusivamente dentro do código de um procedimento VBA e o usuário nunca está ciente de sua existência. Como qualquer função, o UDF pode ser tão simples ou tão complexo quanto você desejar. Vamos começar com um fácil …
Uma função para calcular a área de um retângulo
Sim, eu sei que você pode fazer isso na sua cabeça! O conceito é muito simples para que você possa se concentrar na técnica. Suponha que você precise de uma função para calcular a área de um retângulo. Você examina a coleção de funções do Excel, mas não há nenhuma adequada. Este é o cálculo a ser feito:
ÁREA = COMPRIMENTO x LARGURA
Abra uma nova pasta de trabalho e, em seguida, abra o Editor do Visual Basic (Ferramentas> Macro> Editor Visual Basic ou ALT + F11).
Você precisará de um módulo no qual escrever sua função, então escolha Inserir> Módulo. No tipo de módulo vazio: Área de Função e pressione DIGITAR.O Editor do Visual Basic completa a linha para você e adiciona uma linha de função final como se você estivesse criando uma sub-rotina. Até agora, ela se parece com isto …
Área de Função () Função Final
Posicione o cursor entre os colchetes após "Área". Se você já se perguntou para que servem os colchetes, você está prestes a descobrir! Vamos especificar os "argumentos" que nossa função terá (um argumento é uma informação necessária para fazer o cálculo). Modelo Comprimento como o dobro, Largura como o dobro e clique na linha vazia abaixo. Observe que, conforme você digita, uma caixa de rolagem aparece listando todas as coisas apropriadas para o que você está digitando.
Este recurso é chamado Membros da lista automática. Se também não aparecer, está desligado (ligue-o às Ferramentas> Opções> Editor) ou você pode ter cometido um erro de digitação anteriormente. É uma verificação muito útil da sua sintaxe. Encontre o item de que você precisa e clique duas vezes nele para inseri-lo em seu código. Você pode ignorá-lo e apenas digitar se quiser. Seu código agora se parece com isto …
Área de função (comprimento como duplo, largura como duplo) Função final
Declarar o tipo de dados dos argumentos não é obrigatório, mas faz sentido. Você poderia ter digitado Comprimento, Largura e deixou assim, mas alertar o Excel sobre o tipo de dados que esperar ajuda o código a ser executado mais rapidamente e detecta erros na entrada. o Duplo tipo de dados refere-se a número (que pode ser muito grande) e permite frações. Agora, para o cálculo em si. Na linha vazia, primeiro pressione o ABA chave para recuar seu código (tornando-o mais fácil de ler) e digitar Área = Comprimento * Largura. Aqui está o código completo …
Área de função (comprimento como duplo, largura como duplo) Área = comprimento * largura Função final
Você notará outro recurso de ajuda do Editor do Visual Basic aparecer enquanto você digita, Auto Quick Info…
Não é relevante aqui. Seu objetivo é ajudá-lo a escrever funções em VBA, informando quais argumentos são necessários. Você pode testar sua função imediatamente. Alterne para a janela do Excel e insira os valores para Comprimento e Largura em células separadas. Em uma terceira célula, insira sua função como se fosse uma das embutidas. Neste exemplo, a célula A1 contém o comprimento (17) e a célula B1 a largura (6,5). Em C1 eu digitei = área (A1, B1) e a nova função calculou a área (110,5) …
Às vezes, os argumentos de uma função podem ser opcionais. Neste exemplo, poderíamos fazer o Largura argumento opcional. Supondo que o retângulo seja um quadrado com comprimento e largura iguais. Para evitar que o usuário tenha que inserir dois argumentos, poderíamos deixá-los inserir apenas o Comprimento e fazer com que a função usasse esse valor duas vezes (ou seja, multiplique Comprimento x Comprimento). Portanto, a função sabe quando pode fazer isso, devemos incluir um Declaração IF para ajudá-lo a decidir. Altere o código para que fique assim …
Área de função (comprimento como duplo, largura opcional como variante) If IsMissing (largura) Then Area = Length * Length Else Area = Length * Width End If End Function
Observe que o tipo de dados para largura foi alterado para Variante para permitir valores nulos. A função agora permite que o usuário insira apenas um argumento, por exemplo, = área (A1).A instrução IF na função verifica se o argumento Width foi fornecido e calcula de acordo …
Uma função para calcular o consumo de combustível
Gosto de controlar o consumo de combustível do meu carro, por isso, quando compro combustível, anoto a quilometragem e a quantidade de combustível necessária para encher o tanque. Aqui no Reino Unido o combustível é vendido em litros. O milômetro do carro (OK, então é um hodômetro) registra a distância em milhas. E porque sou muito velho e estúpido para mudar, só entendo MPG (milhas por galão). Agora, se você acha que isso é um pouco triste, que tal isso. Quando chego em casa, abro o Excel e insiro os dados em uma planilha que calcula o MPG para mim e mapeia o desempenho do carro. O cálculo é o número de milhas que o carro viajou desde o último abastecimento dividido pelo número de galões de combustível usados …
MPG = (MILHAS ESTE ENCHIMENTO - MILHAS ÚLTIMO ENCHIMENTO) / GALÕES DE COMBUSTÍVEL
mas porque o combustível vem em litros e existem 4.546 litros em um galão…
MPG = (MILHAS ESTE ENCHIMENTO - MILHAS ÚLTIMO ENCHIMENTO) / LITROS DE COMBUSTÍVEL x 4.546
Veja como escrevi a função …
Função MPG (StartMiles As Integer, FinishMiles As Integer, Liters As Single) MPG = (FinishMiles - StartMiles) / Liters * 4.546 End Function
e é assim que fica na planilha …
Nem todas as funções realizam cálculos matemáticos. Aqui está um que fornece informações …
Uma função que dá o nome do dia
Muitas vezes me perguntam se há uma função de data que fornece o dia da semana como texto (por exemplo, segunda-feira). A resposta é não *, mas é muito fácil criar um. (* Adendo: Eu disse não? Verifique a nota abaixo para ver a função esqueci!). O Excel tem a função WEEKDAY, que retorna o dia da semana como um número de 1 a 7. Você pode escolher qual dia é 1 se não gostar do padrão (domingo). No exemplo abaixo, a função retorna "5" que, por acaso, significa "quinta-feira".
Mas não quero ver um número, quero ver "quinta-feira". Eu poderia modificar o cálculo adicionando uma função VLOOKUP que se referisse a uma tabela em algum lugar contendo uma lista de números e uma lista correspondente de nomes de dias. Ou eu poderia ter tudo autocontido com várias instruções IF aninhadas. Muito complicado! A resposta é uma função personalizada …
Função DayName (InputDate As Date) Dim DayNumber As Integer DayNumber = Weekday (InputDate, vbSunday) Select Case DayNumber Case 1 DayName = "Sunday" Case 2 DayName = "Monday" Case 3 DayName = "Tuesday" Case 4 DayName = "Wednesday" Caso 5 DayName = "Quinta-feira" Caso 6 DayName = "Sexta-feira" Caso 7 DayName = "Sábado" Finalizar Selecionar Função Final
Chamei minha função de "DayName" e leva um único argumento, que chamo de "InputDate", que (é claro) tem que ser uma data. É assim que funciona …
- A primeira linha da função declara uma variável que chamei de "DayNumber", que será um inteiro (ou seja, um número inteiro).
- A próxima linha da função atribui um valor a essa variável usando a função WEEKDAY do Excel. O valor será um número entre 1 e 7. Embora o padrão seja 1 = domingo, eu o incluí para maior clareza.
- Finalmente um Declaração do Caso examina o valor da variável e retorna a parte apropriada do texto.
Veja como fica na planilha …
Acessando suas funções personalizadas
Se uma pasta de trabalho tiver um módulo de código VBA anexado a ela que contém funções personalizadas, essas funções podem ser facilmente tratadas na mesma pasta de trabalho, conforme demonstrado nos exemplos acima. Você usa o nome da função como se fosse uma das funções internas do Excel.
Você também pode encontrar as funções listadas no Assistente de Função (às vezes chamado de ferramenta Colar Função). Use o assistente para inserir uma função da maneira normal (Inserir> Função).
Role a lista de categorias de funções para encontrar Usuário definido e selecione-o para ver uma lista de UDFs disponíveis …
Você pode ver que as funções definidas pelo usuário carecem de qualquer descrição além da mensagem inútil "Nenhuma ajuda disponível", mas você pode adicionar uma breve descrição …
Certifique-se de estar na pasta de trabalho que contém as funções. Vamos para Ferramentas> Macro> Macros. Você não verá suas funções listadas aqui, mas o Excel sabe sobre elas! No Nome da Macro na parte superior da caixa de diálogo, digite o nome da função e clique na caixa de diálogo Opções botão. Se o botão estiver acinzentado, você digitou o nome da função incorretamente ou está na pasta de trabalho errada ou ela não existe! Isso abre outra caixa de diálogo na qual você pode inserir uma breve descrição da função. Clique OK para salvar a descrição e (aqui está a parte confusa) clique Cancelar para fechar a caixa de diálogo Macro. Lembre-se de Salvar a pasta de trabalho que contém a função. Da próxima vez que você for ao Assistente de Função, sua UDF terá uma descrição …
Como as macros, as funções definidas pelo usuário podem ser usadas em qualquer outra pasta de trabalho, desde que a pasta de trabalho que as contém esteja aberta. No entanto, não é uma boa prática fazer isso. Inserir a função em uma pasta de trabalho diferente não é simples. Você deve adicionar o nome da pasta de trabalho do host ao nome da função. Isso não é difícil se você confiar no Assistente de Função, mas é difícil de escrever manualmente. O Assistente de Função mostra os nomes completos de quaisquer UDFs em outras pastas de trabalho …
Se você abrir a pasta de trabalho na qual usou a função no momento em que a pasta de trabalho que contém a função for fechada, verá uma mensagem de erro na célula em que usou a função. O Excel se esqueceu disso! Abra a pasta de trabalho do host da função, recalcule e tudo estará bem novamente. Felizmente, existe uma maneira melhor.
Se você deseja escrever funções definidas pelo usuário para uso em mais de uma pasta de trabalho, o melhor método é criar um Excel Adicionar em. Descubra como fazer isso no tutorial Construir um suplemento do Excel.
Termo aditivo
Eu realmente deveria saber melhor! Nunca, jamais, diga nunca! Tendo dito a você que não existe uma função que forneça o nome do dia, agora me lembrei daquela que pode. Veja este exemplo …
A função TEXT retorna o valor de uma célula como texto em um formato de número específico. Então, no exemplo, eu poderia ter escolhido = TEXTO (A1, "ddd") para retornar "qui", = TEXTO (A1, "mmmm") para retornar "setembro" etc. A ajuda do Excel tem mais alguns exemplos de maneiras de usar esta função.
Se você gostou de nossos blogs, compartilhe com seus amigos no Facebook. E você também pode nos seguir no Twitter e no Facebook.
Gostaríamos muito de ouvir de você, diga-nos como podemos melhorar, complementar ou inovar nosso trabalho e torná-lo melhor para você. Escreva-nos em site de email