VBA Excel: programas de exemplo. Macros no Excel

Poucas pessoas sabem que a primeira versão do produto popular Microsoft Excel apareceu em 1985. Desde então, ele sofreu várias modificações e demanda de milhões de usuários ao redor do mundo. No entanto, muitos trabalham apenas com um pouco de capacidades desta planilha bits e nem sei como eles poderiam tornar a vida mais fácil para a capacidade de programação Excel.

O que é VBA

Programação em Excel é feito por Basic for Applications linguagem Visual programação que foi originalmente construído na mais famosa planilha da Microsoft.

Para seu mérito especialistas atribuem a relativa facilidade de desenvolvimento. Como mostra a prática, VBA pode dominar o básico, até mesmo os usuários que não têm habilidades de programação profissional. Para VBA características incluem a execução de scripts no ambiente de aplicativos de escritório.

A desvantagem do programa são os problemas associados com a compatibilidade das diferentes versões. Eles são causados pelo fato de que o código VBA do programa refere-se à funcionalidade que está presente na nova versão do produto, mas não no antigo. Há também uma grande desvantagem e excessivamente alta a abertura de código para mudar a face de um estranho. No entanto, Microsoft Office e IBM Lotus Symphony permite aos usuários aplicar as configurações de código de entrada de criptografia e senha para visualizá-lo.

Objectos, colecções, propriedades e métodos

É com estes conceitos que você precisa entender aqueles que estão indo para o trabalho no ambiente VBA. Primeiro de tudo, você deve entender o que é o objeto. No Excel, neste ato como uma folha, um livro, e intervalo de células. Esses objetos têm hierarquia especial, ou seja, obedecer uns aos outros.

O principal deles é a aplicação, o programa Excel correspondente em si. As pastas de trabalho seguido, planilhas e Range. Por exemplo, para se referir a célula A1 de uma folha específica deve apontar a forma, tendo em conta a hierarquia.

Em relação ao conceito de "coleção", este grupo de objetos da mesma classe, em que a gravação é dada ChartObjects. Seus elementos são também objetos.

A próxima coisa – propriedades. Eles são uma característica necessária de qualquer objeto. Por exemplo, para o Range – é um valor ou fórmula.

Métodos – é o comando para mostrar que você quer fazer. Ao escrever código em VBA que eles sejam separados do ponto de objeto. Por exemplo, como será mostrado mais adiante, muito frequentemente ao programar no comando "Excel" utilização de células (1,1) .Select. Isso significa que é necessário para seleccionar uma célula com coordenadas (1,1), isto é, A1.

No entanto, muitas vezes é usado Selection.ClearContents. Sua implementação é limpar o conteúdo da célula selecionada.

Como iniciar

Primeiro de tudo, você quer criar o arquivo e salvá-lo, atribuir um nome e selecione o tipo de «Livro Excel habilitado para macro."

Então, vá para a aplicação VB, que é o suficiente para usar uma combinação de «Alt» chave e «F11». Seguinte:

• na barra de menu na parte superior da janela, clique no ícone ao lado do ícone do Excel;
• Mudule seleccionado de comando;
• manter a clicar no ícone com o disquete;
• escrever, digamos, um esboço de código.

Parece como se segue:

Sub programa ()

'Nosso código

End Sub

Por favor, note que a linha " 'Nosso código" será destacado em uma cor diferente (verde). A razão para esta apóstrofe, entregue no início da cadeia, o que indica que o que se segue é um comentário.

Agora você pode escrever qualquer código e criar para si uma nova ferramenta no VBA Excel (ver os programas de exemplo. Etc.). Naturalmente, aqueles que estão familiarizados com os conceitos básicos do Visual Basic, ele será muito mais fácil. No entanto, mesmo aqueles que não têm, se você quer ser capaz de se sentir confortável muito rapidamente.

Macros no Excel

Por trás deste nome esconde programas escritos em linguagem Visual Basic para Aplicativos. Assim, a programação para Excel – é criar uma macro para o código desejado. Com esta capacidade, Microsoft planilha auto-desenvolve, adaptando-se às exigências de um determinado usuário. Tendo lidado com a forma de criar módulos para escrever macros, é possível prosseguir com exemplos concretos de programa VBA Excel. É melhor começar com os códigos mais básicas.

EXEMPLO 1

Tarefa: Escreva um programa que irá copiar o valor do conteúdo de uma célula e, em seguida, escrever para outro.

Para fazer isso:

• abra a guia "Exibir";
• Mova o ícone "macros";
• agitar para o "Macro Record";
• preencher o formulário aberto.

Para simplificar, no campo "Nome de macro" deixar "Makros1" e na "tecla de atalho" está inserido, por exemplo, hh (isso significa que você pode executar o programa exemplo será «Ctrl + h» equipa Blitz). Pressione Enter.

Agora que você começou a gravar a macro, certifique-se o conteúdo de uma célula para outra. Voltar para o ícone original. Clique no botão "Gravar Macro". Essa ação marca a conclusão de applets.

Seguinte:

• novamente se movendo para a string "Macros";
• é selecionado na lista de "Macro 1";
• clique em "Executar" (as mesmas começa acção lançado teclas de atalho «Ctrl + hh»).

Como resultado, a ação que foi realizada durante a gravação da macro.

Faz sentido para ver como o código se parece. Para fazer isso, voltar para a cadeia "Macros" e clique em "Editar" ou "Enter". Como resultado, eles se encontram no ambiente VBA. Na verdade, o próprio código está localizado entre as linhas de macro Makros1 Sub () e End Sub.

Se a cópia foi executada, por exemplo, a partir de uma célula A1 na célula C1, uma das linhas de código será parecido com Range ( "C1"). Selecione. Na tradução, parece que "Range (" C1 "). Select", em outras palavras, faz uma transição para VBA Excel, na célula C1.

Uma parte ativa do código completa ActiveSheet.Paste equipe. Significa gravação conteúdos da célula seleccionada (neste caso, A1) na célula C1 seleccionado.

EXEMPLO 2

ciclos VBA ajudar a criar diferentes macros no Excel.

ciclos VBA ajudar a criar diferentes macros. Assume-se que há uma função y = x + x ³ + 3x ² – cos (x). Você deseja criar uma macro para seus gráficos. Isso pode ser feito apenas usando ciclos VBA.

Para o valor inicial e final de funções argumentos utilizam x1 = x2 = 0 e 10. Além disso, é necessário introduzir uma constante – o valor para o passo muda o argumento e um valor inicial para o contador.

Todos os exemplos de macros VBA Excel são criados usando o mesmo procedimento tal como estabelecido acima. Neste caso particular, o código se parece com:

Sub Programm ()

x1 = 1

x2 = 10

transar = 0,1

i = 1

Não Enquanto x1 <x2 (circular será executado até que a expressão é verdadeira x1 <x2)

y = x1 + x1 ^ 2 + 3 * x1 ^ 3 – Cos (x1)

As células (i, 1) .Value = x1 (valor x1 escrito na memória com as coordenadas (i, 1))

As células (i, 2) .Value = y (valor Y foi escrito na célula com as coordenadas (i, 2))

i = i + 1 (contagem válida);

x1 = x1 + transar (argumento é alterado para o tamanho do passo);

laço

End Sub.

Como resultado desta macro run "Excel" obter duas colunas, a primeira das quais estão gravados os valores para x, ea segunda – a y.

Em seguida, agendar capaz de construir sobre eles, o padrão para "Excel".

EXEMPLO 3

Para implementar ciclos em VBA Excel 2010, bem como em outras versões, juntamente com o Do já reduzido Enquanto o design utilizado.

Considere um programa que cria uma coluna. Em cada célula que vai ser gravada quadrados número de linha correspondentes. Para o uso do projeto permitirá que a gravá-lo muito brevemente, sem o uso de um contador.

Primeiro é necessário criar uma macro, como descrito acima. Em seguida, escrever o próprio código. Acreditamos que estamos interessados nos valores das células 10. O código é como se segue.

Para I = 1 a 10 Próximo

O comando é transferido para a linguagem "humano", como "repetido de 1 a 10 em incrementos de um."

Se a tarefa para receber a coluna com quadrados, por exemplo, todos os inteiros ímpares entre 1 e 11, podemos escrever:

Para i = 1 a 10, passo 1 seguinte.

Aqui, passo – passo. Neste caso, é igual a dois. Por padrão, a ausência da palavra no circuito significa que uma única etapa.

Os resultados precisam de ser armazenados em número de células (I, 1). Então, cada vez que você inicia o ciclo com um aumento no valor de i passo será automaticamente crescer e número da linha. Assim, haverá otimização de código.

Em geral, o código ficaria assim:

Sub programa ()

Para I = 1 a 10, Passo 1 (só pode ser escrita para i = 1 a 10)

As células (i, 1) .Value = i ^ 2 (valor quadrado isto é escrito para dentro da célula (i, 1) i)

Em seguida (num sentido desempenha o papel dos meios de contagem e outro início do ciclo)

End Sub.

Se isso for feito correctamente, incluindo a gravação e a execução de macros (ver. As instruções acima), então é chamado cada vez que um dado tamanho será obtida coluna (neste caso constituído por 10 células).

EXEMPLO 4

Na vida cotidiana, muitas vezes é necessário tomar esta ou aquela decisão dependendo de algumas condições. não pode fazer sem eles em VBA Excel. Exemplos de programas onde o curso posterior do algoritmo é selecionado em vez de inicialmente predeterminado, design mais comumente usado de Se … Então (para casos difíceis) If … Then … End If.

Considere o caso particular. Suponha que você queira criar uma macro para "Excel" para o celular com as coordenadas (1,1) foi gravada:

1 se o argumento é positivo;

0 se o argumento é zero;

1, se o argumento for negativo.

A criação de um tal macro para "Excel" começa de uma forma padrão, através do uso de chaves "quentes" Alt e F11. Além disso escrito o seguinte código:

Sub programa ()

x = As células (1, 1) .Value (Este comando determina que o valor de x coordenadas dos conteúdos das células (1, 1))

Se x> 0, então As células (1, 1) = 1 .Value

Se x = 0, então As células (1, 1) = 0 .Value

Se x <0, As células (1, 1) = -1 .Value

End Sub.

Ela continua a executar uma macro e entrar em "Excel" valor desejado para o argumento.

funções de VBA

Como você deve ter notado, no programa mais famoso aplicativo de planilhas Microsoft não é muito difícil. Especialmente se você aprender a usar funções de VBA. No total, esta linguagem de programação criada especificamente para escrever aplicações em "Excel" e a Palavra, cerca de 160 funções. Eles podem ser divididos em vários grupos grandes. São eles:

• funções matemáticas. Aplicá-las para o argumento do valor do cosseno é obtido, o logaritmo natural, e assim por toda a parte.
• funções financeiras. Devido à sua disponibilidade e uso de programação no Excel, você pode obter ferramentas eficazes para contabilização e liquidação financeira.
• funções de processamento de matriz. Estes incluem Array, IsArray; LBound; UBound.
• VBA Excel funcionar para a linha. Este é um grupo bastante grande. Inclui, por exemplo, a função de espaço para criar uma cadeia com uma série de lacunas igual ao argumento inteiro ou símbolos transferências Asc ao código ANSI. Todos eles são amplamente utilizados e permitem que você trabalhe com cordas no "Excel" para criar aplicações muito mais fácil trabalhar com estas tabelas.
• funções de conversão do tipo de dados. Por exemplo, CVar retorna argumento Expression, convertê-lo para o tipo de dados Variant.
• funções de data. Eles expandir as características padrão de "Excel". Assim, a função WeekdayName retorna o nome (completo ou parcial) do dia da semana pelo seu número. Ainda mais útil é o temporizador. Ele dá o número de segundos decorridos desde a meia-noite para um determinado momento do dia.
• Função para converter um argumento numérico nos diferentes sistemas numéricos. Por exemplo, Out emite a representação octal do número.
• funções de formatação. A mais importante delas é o formato. É devolve uma variante com uma expressão formatado de acordo com as instruções dadas na descrição do formato.
• e assim por diante.

O estudo das propriedades destas funções e sua aplicação vai expandir significativamente o escopo de "Excel".

EXEMPLO 5

Vamos tentar resolver problemas mais complexos. Por exemplo:

documento em papel Dan o nível real dos custos do relatório da empresa. exige:

• desenvolver sua porção padrão de planilha "Excel";
• fazer um programa VBA que irá pedir dados brutos para preenchê-lo, para realizar os cálculos necessários e preencher o modelo da célula correspondente.

Considere uma das seguintes soluções.

Criação de um modelo

Todas as ações são realizadas em uma folha padrão em Excel. células livres reservados para inserir dados nas mês, ano, empresas de título para o consumidor, o montante das despesas, o nível do volume de negócios. Como o número de empresas (empresas), em relação ao qual o relatório não é registrada, as células de fazer com base em valores e nome profissional não é reservado com antecedência. Planilha atribuído a um novo nome. Por exemplo, "Օ Report".

variáveis

Para escrever o programa preencherá automaticamente o modelo, selecione a notação. Eles serão usados para as variáveis:

• NN- número da linha atual da tabela;
• TP e TF – planejadas e volume de negócios real;
• SF e SP – custos totais reais e planejadas;
• IP e IF – planejado e nível de custos reais.

Denotamos pelas mesmas letras, mas com um "prefixo» Itog acumulação total para essa coluna. Por exemplo, ItogTP – No que diz respeito à coluna intitulada como "o volume de negócios planejado."

Solução do problema utilizando programação VBA

Usando esta notação, obtemos a fórmula para a variância. Se você quiser realizar o cálculo em% temos (F – P) / P * 100, e na quantidade de – (F – P).

Os resultados desses cálculos pode ser melhor apenas para fazer planilhas "Excel" nas células apropriadas.

Para o resultado e prognóstico de facto obtidos por fórmulas ItogP = ItogP + P e ItogF = ItogF + F.

Para desvios usar = (ItogF – ItogP) / * 100 ItogP se o cálculo é levado a cabo como uma percentagem, e quando o valor total – (ItogF – ItogP).

Os resultados mais uma vez gravado nas células apropriadas, por isso não há necessidade de atribuir-lhes a variáveis.

Antes de começar a criar um programa que pretende guardar o livro, por exemplo, sob o nome "Otchet1.xls".

Pressione o botão "Criar uma tabela de relatórios", você deve pressionar apenas 1 hora depois de entrar a informação do cabeçalho. Você deve saber e outras regras. Em particular, o botão "Adicionar uma linha" deve ser pressionada cada vez que depois de entrar para a tabela de valores para cada atividade. Depois de entrar todos os dados necessários para pressionar o botão "Finish", e depois mudar para "Excel" na janela.

Agora que você sabe como resolver o problema para Excel com macros. A capacidade de usar o VBA Excel (ver programas de exemplo. Acima) e você pode precisar trabalhar no ambiente dos mais populares no momento, o editor de texto "Palavra". Em particular, através da gravação, como mostrado no início deste artigo, ou escrevendo código para criar os botões do menu através do qual muitas das operações sobre o texto pode ser feito pressionando as teclas de plantão ou através do ícone "Ver" e guia "Macros".