VBA - Iniciante ao Ninja - Série intermediária

1. 01 - VBA Lesson 8 - Introdução de conteúdo intermediária: Bem-vindo à próxima fase de se tornar um ninja musta do Visual Basic. Meu nome é Andrew Derek e serei seu instrutor. Se você é novo no VBA, sugiro que comece com o meu começo, claro, porque estamos prestes a entrar em alguns tópicos que exigirão algum conhecimento prévio. Basta consultar a minha página de perfil de compartilhamento de habilidades para encontrar o link para o curso para iniciantes. Para aqueles de vocês que estão prontos, mantenha-se confortável abrir o Excel em seu computador. E vamos começar. Começarei afirmando que a estrutura do conteúdo intermediário mudará ligeiramente do curso para iniciantes. Comece, claro, com estruturado de uma forma para cobrir o básico, juntamente com algum conteúdo adicional em cada lição para garantir uma boa compreensão ampla dos fundamentos VB I. Com os fundamentos cobertos, o conteúdo intermediário será abrangido por vários cursos. Como você pode imaginar, há muito para cobrir. Embora eu recomendo que você aprenda e entenda todos os tópicos abordados, essa abordagem oferece a capacidade de aprender as coisas que você deseja seletivamente. Isso pode ser porque você está confrontado com um problema particular no local de trabalho ou por curiosidade ou desejo de domínio. Como no início, é claro, continuarei a abordar os erros comuns que os desenvolvedores do VB I cometem e quais os métodos de melhores práticas deveriam raspar o resultado desejado. Em última análise, quero ajudar a tornar seu código eficiente, robusto e fornecer aos seus usos finais uma experiência atraente. Neste curso, vamos cobrir funções mais críticas da pasta de trabalho, tinha um Criar arquivo caixas de diálogo. Como fazer o código executado em eventos específicos, vai falar sobre lidar com conjuntos de dados. E vou apresentá-los a uma das funções mais poderosas do VBA. As matrizes também abrangerão várias formas de lidar com datas e começarão a aprofundar os formulários do usuário e a experiência do usuário. Agora vamos começar. Vejo você no próximo vídeo. 2. 02 - VBA Lesson 9 - Mais funções de livro: Bem-vindo à Lição nove, mais funções de pasta de trabalho. No mundo real, você precisará conhecer essas funções críticas da pasta de trabalho do VBA. Nesta lição, você aprenderá sobre como abrir, fechar e salvar pastas de trabalho de forma programática, criando um caminho de arquivo completo quando for conhecido pela lógica de negócios baseada em cenários e suposições. Você aprenderá a lidar com mensagens pop-up quando abrir programaticamente uma pasta de trabalho e escrever código para abrir pastas de trabalho protegidas por senha. Você aprenderá a carregar arquivos enormes instantaneamente. E explicarei os argumentos opcionais para o comando aberto da pasta de trabalho. Vamos seguir em frente. Vamos considerar este cenário. Você é solicitado a escrever uma macro que precisa de dados de uma fonte externa. Os dados são produzidos automaticamente em uma hora específica todos os dias e são depositados em um diretório específico. Então isso é um conhecido, isso é uma constante que não vai mudar. O arquivo será sempre depositado nessa pasta todos os dias. A queda que é gerada automaticamente tem uma convenção de nomenclatura específica e que é ano, mês, dia e ano sendo ano de caracteres completos ano dois caracteres mês para dia de caracteres no ponto de conversão de moeda escolar csv. E esse tipo de convenção de nomenclatura na verdade é bastante comum com esses trabalhos automáticos que criam arquivos. E isso é para evitar que a Queda seja substituída pelo próximo arquivo morre. E há, na verdade, os usos que usam esses arquivos. Então isso significa um par de coisas. Isso significa que não podemos editar o arquivo ou excluir o arquivo. Não podemos movê-lo e não devemos manter o acompanhamento em muito tempo, caso isso bloqueie outros fora do arquivo. E por último, a equipe em que você está também requer uma cópia da queda para seus próprios propósitos. Então precisamos fazer uma cópia desse arquivo. Então, vamos rapidamente dar uma olhada na pergunta de arquivamento. Vou trazer os meus documentos aqui. Está bem. Então este é o arquivo. Vou abrir isso rapidamente. Ok, aqui vamos nós. Como podemos ver, é um arquivo bastante simples. São só três colunas. Temos encontros com um país e temos valores. São valores de conversão de moeda. Você também pode notar que o tipo de arquivo é um CSV ou um valor separado por vírgula. Os arquivos CSV são, na verdade, apenas arquivos de texto. No entanto, eles podem ser abertos no Excel porque ele arquivo CSV basicamente armazena uma tabela de dados. Vou fechar rapidamente isso e abri-lo no Bloco de Notas apenas para mostrar que você não salvou. Eu trago isso de volta aqui, clique com o botão direito do mouse aberto com. Então é assim que o arquivo se parece, abrindo-o no bloco de notas. Então, na primeira fila contém nossos rebatedores. E como você pode ver, o valor h é separado por uma vírgula todo o caminho. Vou fechar isso. Então o primeiro problema que precisamos pensar é que o nome do arquivo muda todos os dias. No entanto, fornecemos informações suficientes para poder criar dinamicamente esse nome de arquivo e abrir o arquivo, desde que ele tenha sido criado automaticamente na data e hora específicas. Normalmente no local de trabalho com esses tipos de trabalhos, as quedas são depositadas em uma unidade de compartilhamento de rede. No entanto, neste caso, ele vai estar apenas na minha unidade de dizer local na minha pasta de documentos. Mas isso é bom. O conceito ainda é o mesmo. Então eu vou começar com uma constante porque sabemos que o arquivo vai aparecer em um diretório todos os dias em um diretório específico e esse diretório não vai mudar. Então essa é apenas a minha pasta de documentos. Como eu disse antes, eles geralmente estão em unidades compartilhadas de rede, mas o conceito é o mesmo. Assim, o propósito de usar variáveis para criar um nome de arquivo é para que possamos concatenar essas strings juntos para criar o caminho completo e o nome do arquivo para que possamos abri-lo. Como tal, é importante considerar como será o bode expiatório quando você concatenar todas essas cordas juntas. O que quero dizer com isso é olhar para fora para esses diretórios barras invertidas e certifique-se de que você realmente tem barras invertidas onde eles devem ser um. Então a próxima constante que eu quero criar é essa parte do nome do arquivo, porque podemos criar dinamicamente isso com base na data atual. Vou usar isto de forma constante, porque esta parte não vai mudar. Também. Vamos copiar isso. Tudo bem, então vamos agora definir nossa sub-rotina. Só estou chamando de arquivo de dados abertos. E vamos pensar sobre algumas das variáveis que vamos precisar. Então nós definitivamente vamos precisar de um objeto de pasta de trabalho, especificamente a pasta de trabalho que vamos abrir. Nós também vamos precisar da pasta de trabalho atual porque podemos estar tirando dados da abertura unidirecional e depositando neste. Além disso, vamos precisar dos objetos de planilha para a pasta de trabalho que vamos abrir e a pasta de trabalho na qual vamos gravar os dados. Então agora vamos trabalhar em como vamos criar o nome do arquivo, que esperamos que ele tenha esse formato de data. Então o ano em caracteres completos, mês em caracteres, dia em caracteres. Como podemos gerar isso para que possamos criar um caminho de arquivo completo e nome de arquivo. Só vou arrastar a janela imediata por um segundo. Vba tem algumas funções embutidas que podem nos retornar a data e hora atuais. Normalmente, use a função now. Então deixe-me interrogar essa função. Como você pode ver, a função NOW retornou dados e o carimbo de data/hora atual. Não está exatamente no mesmo formato que fizemos. No entanto, você pode realmente usar isso e coagir isso no formato que precisamos. E deixa-me mostrar-te como se faz isso. Então vou deletar essa linha. Então. Podemos usar a função de formato. E como você pode ver, ele está nos pedindo para fornecer um documento que é expressão. Então, a coisa que eu quero fornecer é a expressão agora, que vai retornar a data que acabamos de ver antes. E o próximo argumento é o formato. Então, isso é muito semelhante à estrutura de formatação de células do Excel. Podemos basicamente especificar o formato que queremos que a altura seja retornado. Então eu, é por isso, por que, por que, por que m, m d d, mas perto criados e aperte enter. Você verá que ele nos forneceu a data no formato que exigimos. Então, o ano, o mês e o dia, você só arrasta isso para baixo. Ok, então agora temos informações suficientes para abrir a pasta de trabalho que queremos abrir. Então, a maneira que abrimos uma pasta de trabalho é com, primeiro lugar, a coleção de pastas de trabalho. E a coleção de pastas de trabalho tem um método filho. Frio aberto. E então você pode fornecer um monte de argumentos. Mas nós somos realmente capazes de abrir a pasta de trabalho e, ao mesmo tempo, colocar esse objeto em um objeto que definimos aqui. E vamos usar isso em uma declaração definida. Apenas perto como parênteses, então não está chateado com a gente. Então, essencialmente, a sintaxe disso é que estamos definindo o objeto e com a especificação de quais objetos estamos falando. E esse é o que vamos abrir. Então agora vamos fornecer o nome do arquivo, que será o caminho completo do arquivo, a data e o nome do arquivo. E usamos cadeias de caracteres de concatenação comercial. E agora eu preciso da data, então momento. E agora eu preciso do resto do nome do arquivo. Agora, como você pode ver, o nome do arquivo é o primeiro documento e há muitos outros argumentos depois disso, e os argumentos que depois disso estão entre colchetes. O que isso significa é que eles são realmente argumentos opcionais, e eu vou entrar no que esses argumentos chave são mais tarde na lição. Então o que eu quero fazer é pegar essa pasta de trabalho que acabamos de abrir, e eu quero citar minha própria cópia disso. Então agora que eu tenho meu objeto pasta de trabalho, eu posso usar um dos métodos desse objeto para salvar e salvar como. E eu vou colocá-lo no mesmo lugar. Então, no mesmo diretório que acabamos de pegá-lo, normalmente, quero dizer, você provavelmente teria isso em um diretório diferente, mas apenas para uma demonstração. Então vou usar isso de novo. E eu vou salvar o arquivo em um formato diferente daquele que abrimos. Então isso é um CSV com esta convenção de nomenclatura. Então eu mudarei o nome e a convenção e o tipo de queda. Então, primeiro de tudo, eu quero o encontro ao contrário. Então di, di mês, mês, ano, ano, ano, ano. E agora também precisamos especificar o formato do arquivo. E eu posso fazer isso digitando o padrão da pasta de trabalho do Excel ou a pasta de trabalho do Excel normal. Vou usar o padrão. E isso é tudo. Então vamos fazer isso para ver como vamos. Se adicionar isso de repente aconteceu, se eu acertar FI mais uma vez. Está bem. E eu produzo acabado. Então vamos falar sobre o outono explorar para ver o que está acontecendo. Então podemos ver que temos SI cair aqui, o que é ótimo, que é o formato e o nome do arquivo que designamos. Vamos abrir isso para dar uma olhada. Já está aberta. Esquecemos de fechá-la. Lá vamos nós. Então, nós abrimos o arquivo e nós mudamos esse formato de arquivo para um livro XLS x whip. Deixe-me apenas abrir também a conversão de moeda c é v, para que possamos compará-los lado a lado. É perfeito. Então eu posso ir em frente e fechá-los. Há mais uma coisa que eu quero fazer é fechar a pasta de trabalho após site, ele muda falso. Então vamos fazer isso de novo e ver o que vai acontecer. Se tivermos uma pasta de trabalho que já existe com este nome de arquivo que ganhou um salvar como sobre o topo dele. Então eu abri. Vamos dar uma olhada. Sim, é o CSV. E agora vamos salvar como. Então, ele nos forneceu uma caixa de mensagem de erro. E ele está nos perguntando, Nós queremos sobrescrever nota lateral por enquanto, que nos deu uma área de depuração porque não foi capaz de salvar, que vai acabar nisso e fechá-la novamente. Então, obviamente, isso não é ideal em termos de automação VBA porque esses pop-ups manterão a execução de sua macro e exigirão intervenção do usuário para descartá-los. Felizmente, somos capazes de suprimir esses tipos de pop-ups. E a maneira que fazemos isso é definindo a propriedade display que pertence ao objeto aplicativo. O objeto de aplicativo é a instância do Excel, que é o pai do objeto de coleção de pastas de trabalho. Então, se eu digitar aplicativo e, em seguida, exibir alertas, você pode dizer que o método pop-up lá é igual a false. O que isso significa é que, enquanto o código está sendo executado, ele irá suprimir a exibição dos alertas. E ele selecionará automaticamente a opção padrão. Mas lembre-se de sempre defini-lo como verdadeiro depois de terminar de correr. Então vamos tentar isso novamente agora para ver se temos suprimido essa caixa pop-up. Então, como você pode ver, terminamos de executar o código e não vimos um pop-up, o que é ótimo. E se eu apenas abrir o File Explorer, podemos ver que temos um novo arquivo, então ele é salvo no topo da queda existente que estava lá. O que é ótimo. Então vou voltar para o nosso código. Ok, então agora vamos passar por alguns dos argumentos adicionais que podemos enviar a pasta de trabalho aberta e pastas de trabalho Salvar como comando. Agora eu não vou passar por todos eles e tipo de passar por aqueles que eu acho que são críticos para saber, o que pode ser útil para suprimir ainda mais os alertas que são mostrados durante um comando open ou save. Tudo isso pode ser útil na automação em geral. Então, primeiro de tudo, depois do método aberto, temos nossos parênteses de abertura e então temos nossos argumentos para os quais podemos enviá-lo. O primeiro é obviamente o nome do arquivo, e isso é obrigatório. Você precisa especificar o que, qual livro você gostaria de abrir. E separamos os argumentos por vírgulas. Então, como você pode ver, enquanto eu começo a digitar vírgulas, o argumento a que estamos nos referindo torna-se negrito no intellisense. Então isso é um dado que precisamos separar nossos argumentos com vírgulas. Então, digamos, por exemplo, que eu queria definir o argumento de carga corrompida. Uma maneira de fazer isso seria entrar em coma até que a carga corrupta seja ousada, que está lá. Ou o que eu poderia fazer é dizer, primeiro de tudo, separado com uma vírgula e então realmente ensinou o argumento de carga corrupta. Em seguida, com dois pontos e um igual, e você verá que agora o argumento de carga corrompida está em negrito no IntelliSense, que significa que qualquer coisa que eu digite aqui é para o argumento corrupto load. Então você pode realmente especificar a ordem dos argumentos, ou você pode simplesmente usar a opção vírgula para especificar qual argumento você gostaria de fornecer. Então o primeiro é atualizar links. Agora, se você abriu uma pasta de trabalho no local de trabalho e ela contém links para fontes externas, seja outra pasta de trabalho ou um conjunto de dados. Você pode ver essa mensagem pop-up dizendo, você gostaria de atualizar os links para esses dados? Agora, obviamente, estou abrindo uma pasta de trabalho programaticamente e ela contém links. Ele vai exibir essa mensagem, que vai segurar minha macro e eu não quero fazer isso. Então você pode realmente especificar false, o que impedirá que essa caixa de mensagem apareça. O próximo argumento é somente leitura. O que isso faz é que especifica que abrimos a pasta de trabalho no modo somente leitura, o que é bastante auto-explicativo. E isso pode ser útil especificamente para o cenário que acabamos de incorporar que não queremos editar o arquivo que estamos abrindo. Nós só queremos pegar dados e fechá-los rapidamente. E também pode evitar o bloqueio de queda, o que pode ser um incômodo no ambiente de trabalho com vários usuários usando o mesmo arquivo. O próximo é o formato. E isso é usado quando estamos abrindo um arquivo de texto como um CSV. Mas queremos especificar que o delimitador de arquivo ou o caractere que especifica a separação do campo h é diferente de uma vírgula. Então pode ser uma aba, pode ser um espaço, pode ser um ponto-e-vírgula, ou pode ser um personagem acostumado. E esta é uma lista enumerada, que significa que você precisa fornecer um número e você pode realmente apenas Google qual número você precisa especificar para o topo da abertura defeituosa, mas eu vou apenas passar por eles rapidamente. Então um é para tabulação separada delimitador é um delimitador separado por vírgula, que é o padrão. Três é para o espaço, quatro é para ponto-e-vírgula, cinco é nada, e seis é o caractere personalizado, que você precisará especificar a queda delimitador. A próxima é a senha. Então, novamente, no local de trabalho que pode ter aberto uma pasta de trabalho que pede uma senha quando você está abrindo a pasta de trabalho. E, obviamente, este é outro ponto de falha para automação porque ele está solicitando que o usuário especifique uma senha. Agora você pode realmente fornecer essa senha. Vamos salvar a senha é apenas senha. Você pode realmente especificar isso como uma string neste argumento. Portanto, se a sua pasta de trabalho contém uma senha, é uma boa idéia apenas especificar isso em uma constante, semelhante à forma como usamos as constantes para o caminho do arquivo e para o nome do arquivo, modo que você não precisa apenas falar isso em Toda vez que você abre ou fecha a pasta de trabalho, a próxima que eu quero falar é delimitada. Então, se especificamos que é um delimitador personalizado no argumento de formato, precisamos especificar o que é. Então pode ser falta, pode ser uma aspas duplas ou pode ser qualquer coisa. É apenas o caractere usado nesse arquivo que especifica que é um delimitador para o próximo campo. E o próximo sobre o qual quero falar é uma carga corrupta. Portanto, isso é extremamente útil. E no local de trabalho cai um comumente armazenado em servidores de arquivos, mesmo sua própria pasta Meus documentos. E se o seu local de trabalho tem muitos usos e seu lidar com arquivos grandes do Excel, isso pode obviamente causar problemas. Na minha experiência, vi pastas de trabalho demorarem de dois a cinco minutos para abrir. E isso é devido a algumas coisas. Em primeiro lugar, é devido à largura de banda da rede. Portanto, se você tem muitos usuários em sua rede, não há largura de banda suficiente para fornecer o arquivo rapidamente. Mas o principal culpado pelo carregamento lento de arquivos que não sejam largura de banda na rede é a formatação desnecessária. Notei que alguns usuários gostam de preencher a formatação em uma tabela inteira ou até mesmo na última linha da pasta de trabalho. E isso não só torna a pasta de trabalho incrivelmente grande, ele torna a abertura da pasta de trabalho muito lenta porque o Excel precisa concluir um monte de tarefas antes de realmente apresentar uma abertura do arquivo. Então, o que pode corromper carga fazer para nós na instância em que temos pasta de trabalho que leva muito tempo para abrir, o que eu vou fazer é especificar que é apenas radar, mas eu também vou especificar dados de extração do Excel no argumento de carga corrompida. E o que isso faz, ele irá ignorar toda a formatação e abriremos a queda o mais rápido possível a partir da fonte do arquivo, que significa que tão rápido quanto puder ser lido da rede, ele será aberto. E ele irá ignorar toda a formatação e apenas abri-lo para que você possa rapidamente saltar lá, dados extraídos e saltar para fora. Ok, então eu vou deletar isso agora. E agora vamos falar sobre os argumentos que podemos enviar para o comando Salvar como. Então o primeiro é o nome do arquivo, o que é bastante óbvio. É apenas o nome do arquivo que especificamos que queremos salvar o arquivo como. E o próximo é o formato de arquivo, que cobrimos rapidamente. Existem inúmeras opções disponíveis aqui. Então, se você tem um requisito específico para que um arquivo seja salvo em um formato específico, então basta pesquisar a enumeração de formato de arquivo XML e você verá uma lista completa dos formatos de arquivo para os quais você pode exportar o arquivo. Os principais aqui, porém, pasta de trabalho do Excel para falha, que é XLS X, você também pode usar XML CSV. Outro realmente comum, pelo menos na minha experiência, é folha de Excel XL, que é para compatibilidade com versões anteriores com o Excel 2003 de volta para pastas de trabalho 2007. Então esse é apenas o formato de arquivo dot XLS. E o próximo que quero falar é sobre a resolução de conflitos. Então isso é o que acontece se tivermos vários usos no mesmo arquivo. E especifica quais alterações devem ser priorizadas para serem salvas. Portanto, o padrão é a resolução do Excel USA, que não é ideal em automação porque pede ao usuário para resolver os conflitos de salvamento. A outra opção é alterações de sessão local XL. Então, o que isso significa é que o Excel priorizará as alterações de usos locais sobre quaisquer alterações de usos remotos. E o outro é alterações de sessão do Excel. Então esse é o oposto do que acabamos de cobrir. O que significa que o Excel priorizará as outras alterações de usos sobre as nossas nesta sessão local. E isso resume os principais argumentos que eu queria abordar nesta lição. Então isso termina ouvir nove. Nesta lição que você aprendeu sobre abrir, fechar e salvar pastas de trabalho programaticamente, eu mostrei a você como criar um caminho de arquivo completo para um arquivo com base na lógica de negócios e suposições, aprendemos a suprimir durante a execução de código e como abrir uma pasta de trabalho protegida por senha. Eu mostrei a você como carregar rapidamente arquivos que normalmente levam muito tempo para carregar. E eu expliquei os principais argumentos opcionais para o comando dot open de pastas de trabalho. Obrigado por assistir, e te vejo no próximo vídeo. 3. 03 - Lição 10 - Caixas de diálogo de arquivo: Bem-vindo à lição dez, Caixas de diálogo Arquivo. Uma caixa de diálogo Arquivo é um recurso interno de aplicativos do Office. Em poucas palavras, é basicamente uma janela que apresenta ao usuário uma visão do sistema de arquivos para que o usuário possa selecionar um arquivo ou diretório para o propósito especificado pelo aplicativo que o esfriou. Um exemplo de uma caixa de diálogo de arquivo é a janela que você verá ao selecionar Arquivo e abrir a partir de qualquer aplicativo do Office. Nesta lição, eu vou ensinar a você como criar caixas de diálogo Arquivo. Vou explicar as várias propriedades que somos capazes de sentar para personalizar nossa caixa de diálogo arquivo. Vou mostrar-lhe como lidar com a resposta devolvida dos EUA. O conteúdo adicional que cobriremos inclui com instruções, a função DIR VBA embutida e a função LAN VBA incorporada. Vamos dar um pontapé. Caixas de diálogo de arquivo é algo que eu costumo usar ao criar aplicativos VBA. O objetivo disso é basicamente solicitar ao usuário que localize um arquivo ou pasta. Apenas um par de cenários onde você pode usar. Um poderia ser pedir ao usuário para localizar um arquivo do qual você estará extraindo dados ou onde o usuário deseja salvar um arquivo, como e onde seus usos realmente até sua própria imaginação usando o cenário na última lição como um exemplo, que abre e salva uma cópia de uma pasta de trabalho externa, você pode tentar localizar automaticamente o arquivo como sabemos o diretório em que ele deveria estar e a convenção de nomenclatura desse nome de arquivo. E se não tiver êxito , peça ao utilizador para localizar o ficheiro em falta. Então vamos tentar alcançar exatamente isso. Olhando para a rotina da última lição, queremos tentar abrir o arquivo usando a lógica que já definimos. E se isso falhar, apresente ao usuário um seletor de arquivos. Existem algumas maneiras de fazer isso se o arquivo não existir e o VBA tentar abri-lo, isso forçaria o VBA a um erro de tempo de execução. Nesse caso, precisaríamos escrever um manipulador de erros para que o VBA saiba o que fazer quando esse erro específico for encontrado. No entanto, estaremos cobrindo o tratamento de erros em uma sessão de rede. E também, eu sinto que há uma maneira melhor de conseguir isso, vez de apenas tentar abrir um arquivo que pode ou não existir, que causaria uma era. Nós poderíamos primeiro testar para ver se o arquivo realmente existe antes mesmo de tentar abri-lo. Dessa forma, estamos impedindo que uma era de tempo de execução ocorra e a necessidade de atender a essa época. Então, em resumo, o que estamos tentando fazer é tentar abrir o arquivo automaticamente. E se esse arquivo não existir, então solicitaremos ao usuário que selecione o arquivo para abrir. Agora vamos falar sobre como determinar se um arquivo existe ou não. Vba tem uma função que podemos usar para conseguir isso e chama-se D. Deixe-me trazer a Janela Imediata. Então, a função DIR, vamos testar para ver se existe um diretório. E se ele existir, ele vai retornar o nome do arquivo como uma string. E se ele não existir, ele vai retornar uma string de comprimento 0. Então, o arquivo que usamos no primeiro exemplo, eu acabei de copiar o caminho do arquivo e eu vou apenas fornecer isso como uma string. E sabemos que existe porque usamos isso na última lição. Então, se apertarmos Enter, podemos ver que ele nos forneceu de volta o nome do arquivo. Agora, se eu fosse apenas para mudar um personagem aqui. Eu sei que este arquivo não existe, e se eu pressionar enter, você verá que ele nos forneceu de volta uma string de comprimento 0. Portanto, somos capazes de usar a função DIR para determinar se o arquivo existe ou não. Porque se ele existir, ele vai retornar uma string que é maior que 0, ou o comprimento dessa string é maior que 0. E se ele não existir, ele vai retornar uma string que tem o comprimento de 0. Então, tire a janela imediata do caminho. Vamos começar. Então, a fim de validar o que a função DIR retorna, eu vou apenas usar uma instrução if básica. Espere um minuto. Vou trazer a janela imediata de volta. Vou apontar isso por um segundo, e vou mostrar a declaração de comprimento também. Então, se eu envolver esta função dentro de outra função que nos diz o comprimento de uma string, e essa função é chamada Lin. Contou 31. Então isso significa que dentro desta string há 31 caracteres. Agora, se eu mudar isso para que ele não exista, o arquivo não existe. Ele nos deu um comprimento de 0 caracteres. Então, dentro dessa declaração se, somos capazes de usar esta função len e envolto dentro da função len é a função. E se o arquivo não existir, nós vamos obter 0. E se o arquivo existir, teremos algo maior que 0. Então vou tirar isso do caminho de novo. Descomente isso. Se, então, se o comprimento de D e o caminho é o, ok, então é assim que escrevemos esta declaração se. Então a primeira função lá é o comprimento, que é isso, tudo dentro desses suportes. E se acertarmos o tempero aqui, ele nos mostrará a expressão no meio. E a expressão no meio é esta. Então, se o comprimento do que é retornado de D, se esse comprimento é maior que 0, então podemos ir em frente e abrir a pasta de trabalho. Então, vou copiar esta cópia. Então devo dizer. E eu vou colocar isso aqui. Caso contrário, faça alguma coisa. Então, o que queremos fazer se o arquivo não existir? Queremos solicitar que o usuário selecione o arquivo. Então, para fazer isso, vamos precisar de uma caixa de diálogo de arquivo. Vamos precisar de uma variável para isso. Então é, está em cima, que é pote de escritório ou está embutido no Office, eu diria. Eles vão a caixa de diálogo Arquivo. Agora, este é apenas um objeto genérico que é piscar. Portanto, precisamos definir esse objeto. Então vou fazer isso agora. Agora, a caixa de diálogo Arquivo é um filho do objeto de aplicativo. E como eu mencionei na última lição, aplicação é aparente da coleção de pastas de trabalho e os objetos de pastas de trabalho. Então eu preciso de aplicação, eu derramei isso incorretamente. Diálogo de arquivo de ponto. Existem alguns tipos de diálogo de arquivo diferentes. E, felizmente, diz-nos quais temos à nossa disposição. Então queremos um seletor de arquivos. Mas os outros tipos que temos lá, há um seletor de arquivos, uh, pika de pasta, e diálogo aberto e uma caixa de diálogo Salvar como. Mas eu quero o seletor de arquivos. Assim, uma vez que o código tenha executado esta linha, ele tem colocar este objeto em branco em uma janela de diálogo de arquivo que pertence ao aplicativo. E no topo dessa caixa de diálogo de arquivo está um diálogo de arquivo, arquivo pika. Há algumas outras propriedades que eu quero sentar. Então, por exemplo, eu não quero permitir que o usuário selecione mais de um arquivo. Não quero que eles selecionem um arquivo que não seja o tipo certo de arquivo. E eu também quero ajudar o usuário começando a procurar em uma pasta específica. Então, para fazer isso, precisamos sentar mais propriedades desse objeto de diálogo de arquivo. E para fazer isso eu vou usar uma instrução width. Então, basicamente, uma declaração com permite que você se sente várias propriedades ao mesmo tempo. Então, vou preparar isso. E semelhante a uma instrução if, a instrução width precisa de uma instrução end. Agora, dentro desta declaração de largura, podemos sentar várias propriedades ao mesmo tempo. Outra coisa também é que dentro destes com declarações, não precisamos declarar novamente qual variável que está falando. Assim, por exemplo, se eu quiser definir um filtro, que filtra que tipo de arquivos podem ser selecionados por esta caixa de diálogo Arquivo. Não preciso primeiro dizer o nome do objeto e depois a propriedade. Eu posso apenas começar com a propriedade ou o ponto para a primeira propriedade que eu sempre corrigir quando eu estou sentado uma caixa de diálogo de arquivo é que eu limpei os filtros. Então eu sinto que esta é uma propriedade filho do objeto de diálogo de arquivo, e clear é um método filho da propriedade filters. E então a razão pela qual eu faço isso é porque os filtros para esses objetos são armazenados em cache. Significa que se eu sentar um monte de filtros e, em seguida, eu oculto essa caixa de diálogo e, em seguida, eu mostrei novamente os filtros da última vez que eu mostrei a caixa de diálogo presente no novo. Portanto, é sempre uma boa prática limpar os filtros. E o próximo a adicionar é adicionar um filtro para que possamos selecionar um arquivo CSV. Então, novamente, estamos usando a propriedade filters. Mas desta vez eu quero adicionar um. Então, há o método add. Agora ele nos diz o que precisamos fornecer. Então precisamos fornecer uma descrição como uma string e extensão uma string e uma posição. Portanto, a descrição é bastante simples. É uma string que você pode digitar apenas para descrever o que você permite que eles selecionem. As extensões são as extensões de arquivo ou os caracteres que aparecem após o ponto. E a posição é se você tem mais de um, qual posição nessa lista deve este item B? Então vamos começar com arquivos CSV, a descrição e a extensão. Vou usar um curinga aqui. Vou explicar o porquê em um segundo. Então o que um caractere curinga faz é dizer ao Windows que aceito qualquer caractere antes desse caractere. Então o que ele vai fazer é procurar a patente de qualquer personagem ou qualquer caractere, em seguida, ponto CSV, que significa que ele vai filtrar tudo que não é um arquivo CSV. Eu também quero impedir que o usuário seja capaz de selecionar vários arquivos, mesmo que eles possam teoricamente selecionar vários arquivos nesta instância, eu não quero que eles, eu só quero permitir que eles selecionem um arquivo. Então, isso é. Falso de seleção múltipla. Então eu estou definindo para falso. E para ajudar o usuário, quero ter certeza de que eles não precisam caçar por muito tempo. Então eu tenho um palpite educado de que ele vai estar na minha pasta de documentos. Portanto, eu quero que eles comecem a procurar nessa pasta. E para fazer isso, eu posso dizer nome de arquivo inicial igual a e eu vou apenas fornecer essa constante. Então o que isso vai fazer é abrir a janela e vai estar nessa pasta. E assim a última propriedade seria mostrar pontos, que vamos apresentar a caixa de diálogo de arquivo para o usuário. Então, há uma pegadinha aqui. A captura é que o formulário que é exibido, que é a caixa de diálogo de arquivo, é um formulário modal. Então o que isso significa é que quando o formulário é exibido e presente na tela, isso significa que nenhum código pode ser executado. Mas está tudo bem. Podemos usar isso a nosso favor. Então o que eu vou fazer é colocar essa propriedade de show de pontos dentro de uma declaração if. Então o que isso significa é que quando ele avalia esta linha, ele realmente vai mostrar a caixa de diálogo de arquivo para o usuário, mas não foram continuar porque não pode, é modal. Portanto, nenhum código está sendo executado em segundo plano. No entanto, uma vez que eles selecionaram um arquivo e o calor, ok, desde que ele passe esta linha, isso significa que eles não só viram o formulário, eles selecionaram um hit caído OK. E se Mostrar é igual a falso, isso significa que eles realmente apertaram Cancelar e descartaram essa caixa de diálogo de arquivo. Então, temos duas condições aqui. Temos se eles selecionaram um arquivo ou quinto hit OK. E se eles cancelaram fora da caixa de diálogo de arquivo, se eles selecionaram um folic e ir em frente e arpanet como fizemos aqui em cima. Então eu vou apenas copiar esta linha. Provavelmente é mais fácil superar isso de novo, porque eu vou mudar, mas tudo bem. E eu vou remover todas as coisas dentro dos colchetes de argumentos abertos. Agora, o que eu quero fornecer? Quero fornecer os itens que eles selecionaram. Então, isso é pontos selecionados itens. Agora, como a caixa de diálogo de fonte potencialmente pode permitir várias seleções, este objeto aqui vai esta propriedade é uma lista, portanto, eu preciso fornecer o índice. E por causa do no permitiu que eles selecionassem um, o índice será apenas um. Mas se houvesse, por exemplo, múltiplo, você teria que, em seguida, percorrer por eles e executar uma ação sobre eles um por um. Então, isso é bom. Se o show é verdade, significa que eles viram, eles bateram bem. Portanto, eu quero que você abra o arquivo se selecionado. Caso contrário, se eles apertaram escape ou cancelar, eu quero apresentar uma caixa de mensagem. Não está se divertindo com o teclado Mac. E também quero sair do submarino. Então Exit Sub é um pouco diferente do N sub. N sub sendo o ponto final de toda a rotina. E Exit Sub é só um ponto onde você pode chegar a isso, vamos dizer, ok, eu já tive o suficiente. Vou sair do submarino graciosamente. Então isso significa que se excedemos esta declaração if, isso significa que ou o arquivo existe e nós abrimos, ou o arquivo foi selecionado e nós também o abrimos. Não seremos capazes de chegar a este ponto por qualquer outro meio, porque saímos do submarino antes que ele possa chegar a essa linha de código. Então isso significa que podemos executar as mesmas ações que estávamos fazendo antes, que foi salvar erro na versão. E feche a pasta de trabalho e, em seguida, ative novamente os alertas de exibição. Ok, então estamos quase prontos para testar isso. O que eu vou primeiro fazer no entanto, é que eu vou saltar para a minha pasta Documentos e eu vou apenas mudar o nome do arquivo para que ele definitivamente não existe. Então eu só vou adicionar um no início. Eu também posso fazer isso por este também. De qualquer forma. Então vamos testar este código. Vou passar por ele usando F8. Então desativei os alertas e o arquivo não existe porque mudei o nome do arquivo. Então agora ele vai saltar para este lado da declaração if, a outra condição, ele vai sentar aquele objeto. Agora vamos dizer que o objeto foi definido. É uma caixa de diálogo Arquivo e é uma caixa de diálogo de arquivos, um seletor de arquivos. Então, a próxima linha está entrando dentro da instrução width. Então vamos limpar os filtros. Ainda não vimos a janela porque não atingimos o show dos pontos. E vamos adicionar o filtro de arquivos CSV. Vamos desligar a seleção múltipla. Vamos definir o caminho do arquivo inicial para a pasta para que ele apareça dentro do meu OneDrive, pasta Meus documentos. E agora depois que eu apertar F8, ele vai nos mostrar a caixa de diálogo de arquivo ou espero. Ok, lá vamos nós. Então, e está dentro da pasta Meus Documentos. Eles entrariam. Esse é o arquivo que acabei de renomear. Então eu vou apenas selecioná-los. Acerte OK. Então agora eu tenho um lado otomano, o índice de itens selecionados de um. E eu vou sentar este objeto aberto WB usando as pastas de trabalho dot open comando, que vai abrir o arquivo que selecionamos na caixa de diálogo arquivo. Então, se acertar F8 mais uma vez. Oh, ok, então o código pulou até o fim, então não vamos mais passar por ele. Mas posso confirmar que ele foi concluído porque temos o arquivo que acabamos de salvá-lo como. Então nós tivemos o porque nós o renomeamos. Lembre-se, colocamos um na frente dele, de volta, no Explorador de Arquivos, mas acabamos de criar que há bom no momento atual aqui. Então ele terminou, mas é apenas uma espécie de fugir de nós. O código foi-se embora de nós. Agora isso é algo que você meio que precisa ter cuidado quando você está depurando e pisando em seu código. Que às vezes o código pode fugir de você e pode simplesmente começar a executar-se a toda velocidade. que obviamente é um ideal, especialmente quando você está depurando, você está tentando garantir que as coisas não estão quebradas antes de executá-lo no ritmo. E a razão pela qual isso acontece é meio desconhecida, mas sabemos as condições que fazem com que isso ocorra. E essas condições quando uma chamada de procedimento remoto acontece, que é abrir essa pasta de trabalho, e ele retorna para este módulo de código para continuar com o código. Então, neste caso, o que aconteceu é que está aberto essa pasta de trabalho, que é enviada de uma chamada de procedimento para abrir essa pasta de trabalho. E então, como está terminado, esse comando, ele voltou direto para este código e ele é executado em ritmo total. Agora o que você pode fazer para contornar isso é colocar o seu código com pontos de interrupção. E a razão pela qual você faz isso é que se ele dispara e faz sua chamada de procedimento remoto e ele volta e executa o ritmo total. Vai parar no próximo ponto de interrupção. Então vamos tentar de novo. Vou apertar F5 e devemos ver imediatamente a caixa de diálogo do arquivo aparecer. Mas então uma vez que selecionamos um arquivo, ele irá parar neste endif ou este endif. E eu bateria bem. Boom, então ele parou no primeiro ponto de interrupção após a linha aberta da pasta de trabalho. E eu posso continuar. Ok, então isso resume este exemplo prático. Então, em resumo, o que fizemos foi, em primeiro lugar, desligar os alertas. Em seguida, testamos para ver se o arquivo existe e estamos fazendo isso usando a função. E isso nos devolverá um nome de arquivo se o arquivo existir nesse diretório, e se não existir, ele não nos devolverá nada. Então, portanto, vamos testar o comprimento da string. Se a função retorna e se o arquivo existe, vamos apenas saltar direto e abrir esse arquivo. Mas se o arquivo não existir, se não pudermos localizar automaticamente, então vamos perguntar ao usuário através de uma caixa de diálogo Arquivo. Em seguida, sentamos várias propriedades ao mesmo tempo. E enquanto estamos dentro desta declaração de largura, também tivemos uma declaração if que aproveitou o fato de que a caixa de dólar de queda mostra isso modalmente, basicamente nos permitindo pausar nesta linha e Branco para o para selecionar um arquivo ou clicar em cancelar e, em seguida, executar o código de acordo. Então, ou abrimos a falha que eles selecionaram ou cancelamos e saímos do submarino. Em seguida, continuar como normal, que é salvar a versão Erin do arquivo e fechar o arquivo sem salvar. Isso encerra esta lição. Em resumo, hoje aprendemos como criar caixas de diálogo Arquivo. Como personalizar nossa caixa de diálogo. Por exemplo, criar uma pasta ou um seletor de arquivos, permitindo que o usuário selecione vários arquivos, fazer com que a janela comece em uma determinada pasta e filtre arquivos por extensão de arquivo. Aprendemos como lidar com a resposta devolvida do final dos EUA que eu apresentei com declarações. Além disso, as funções VBA incorporadas que cobrimos foram a função DIR e a função LEN. Espero que tenha gostado deste vídeo e te vejo no próximo. 4. 04 - VBA Aula 11 - Execute a: Bem-vindo à Lição 11, corra em eventos. No VBA. Somos capazes de capturar alguns eventos que são acionados pelo código do usuário ou outras razões ambientais. Apenas alguns exemplos de alguns eventos. O clique de um botão quando uma planilha é adicionada a uma pasta de trabalho ou quando uma alteração é feita em uma planilha. Capturar eventos pode ser muito útil para garantir que nossos aplicativos VBA operem de uma maneira que pretendemos. Nesta lição, responderemos à pergunta, quais são os eventos? Vamos falar sobre os vários tipos de eventos e escopo de eventos. E vamos fazer alguns exemplos práticos de uso de eventos em nosso código. Nós também estaremos falando sobre a função VBA embutida chamada esquerda. Vamos falar com um pouco mais de detalhes sobre o que realmente são os eventos. Eventos ou simplesmente uma ação ou um gatilho que ocorreu. Um evento pode ser iniciado por um usuário programaticamente ou por data ou hora. Existem vários eventos ou gatilhos que podemos usar no VBA que você é capaz capturar e, em seguida, executar código imediatamente assim que o evento é detectado. No interesse de tornar nossos aplicativos VBA o mais robustos possível. Às vezes, precisamos capturar eventos para que possamos garantir uma experiência consistente do usuário. Minha filosofia em fazer aplicativos VBA consistentes funcionais é controlar um bloqueio tanto quanto possível e, ao mesmo tempo, capturar as ações do usuário e manipulá-las de uma maneira que eu especifique o que isso faz é remover o máximo possível de variância ambiental, o que pode causar problemas inesperados com os aplicativos que criamos. Um exemplo simples disso pode ser se um usuário inexperiente abrir minha pasta de trabalho ou aplicativo do VBA e eles não habilitar macros. Eles podem perceber que o aplicativo que eu construí para ser quebrado, e isso é porque ele não está fazendo o que eles estão esperando que ele faça, acredite ou não, este é um problema razoavelmente comum que eu experimentei no local de trabalho. O que somos capazes de fazer para contornar isso é configurar uma pasta de trabalho em um estado bloqueado padrão e aplicar lógica no evento aberto da pasta de trabalho, o que implica que a macro foi habilitada e, em seguida executar o código que inicializa o e desbloqueia-o. Vou mostrar-lhe exatamente como fazer isso mais tarde na lição. Vamos falar sobre o escopo do evento, semelhante à hierarquia do modelo de objeto da pasta de trabalho como discutido em detalhes no meu curso iniciante, existem vários níveis ou tipos de eventos. Esses são eventos de planilha, eventos de pasta de trabalho, eventos de aplicativo, usar os eventos de formulário, bate-papo , eventos, eventos no tempo e em eventos chave. Basicamente, estes são os eventos que ocorrem nesses níveis discretos da aplicação. Por exemplo, alterar uma célula seria um evento de planilha. Adicionar uma nova planilha a uma pasta de trabalho seria um evento de pasta de trabalho na janela, minimizar, maximizar ou redimensionar. Isso seria um evento de inscrição. Ao clicar de um botão em um formulário de usuário que criamos seria um evento de formulário de usuário na mudança de uma série de gráficos ou o redimensionamento de gráfico seria um evento de gráfico. Quando ocorre um horário específico seria um evento pontual. E quando teclas específicas são pressionadas no teclado seria um OnkeyEvent. Tenha em mente que esta lista não é uma lista exaustiva de eventos. Agora vamos começar a usar eventos em nosso código. Neste exemplo prático, quero ter certeza de que o usuário ativa macros. E para fazer isso, o que eu quero fazer é alterar o estado padrão da pasta de trabalho. O que quero dizer com isso é que quero esconder todas as folhas da pasta de trabalho por uma. E aquele que foi desoculto conterá uma mensagem solicitando ao usuário para ativar macros e reabrir a pasta de trabalho. Quando eles, em seguida, ativar macros e abrir a pasta de trabalho, eu vou pegar o evento da abertura da pasta de trabalho, o que significa que eles definitivamente ativaram macros e, em seguida, mostrar essas folhas ocultas e ocultar a mensagem pedindo a eles para ativar macros. Porque queremos capturar eventos que pertencem à pasta de trabalho. Eu vou entrar no, este objeto de pasta de trabalho. E quero chamar sua atenção para essas caixas suspensas verdadeiras na parte superior do IDE. Então o da esquerda nos mostra o objeto que estamos falando neste caso, queremos falar sobre uma pasta de trabalho, que é a pasta de trabalho. E o outro lado pode nos mostrar todos os eventos que foram capazes de capturar neste objeto pasta de trabalho. Como você pode ver, há alguns. Então o que isso é feito, eu não tenho certeza se isso é visível na tela, mas o que eu vou fazer é apenas copiar e colar. O que isso fez criou automaticamente essa subfloresta. E é um sub privado, privado, que significa que pertence a esse objeto de pasta de trabalho. E não é visível a partir de todas as outras planilhas ou módulos de código menos que nos referimos explicitamente ao objeto deste livro primeiro. Então, este sub será executado automaticamente quando a pasta de trabalho for aberta. Não há nada mais que precisamos fazer para garantir que este sub é executado na pasta de trabalho aberta. Além de, é claro, garantir que as macros estão habilitadas na própria pasta de trabalho. Então, neste reter, o que eu quero fazer é que eu quero percorrer as planilhas e eu quero ocultar uma planilha particular. Então eu vou começar adicionando uma nova folha, e eu vou colocar uma mensagem nessa folha que diz, por favor ative macros. Aqui está a nossa pasta de trabalho. Vou adicionar uma nova folha, e vou chamar esta folha activa. E eu vou e vamos sentar este fundo para branco. Então você não diz essas grades. E também vou esconder tudo por baixo dela. E desse lado, vou garantir que essa célula seja grande o suficiente. E eu vou fazer esse vermelho. E nós vamos apenas torná-lo ousado apenas para que ele se destaca em um pode realmente aumentar o tamanho. E então apenas sim, legal. Então a intenção é que quando eles abriram a pasta de trabalho e eles não ativaram macros ou eles vão ver, é esta folha, a folha de ativação, todos os outros serão escondidos e ele irá dizer claramente o que eles precisam fazer para que esta macro funcione. Então eu vou apenas ir em frente e rapidamente esconder as outras planilhas. Então nós temos este ativado agora, então é isso que eles estarão vendo quando abrirem a pasta de trabalho se eles não habilitarem macros. Então, vamos voltar para o IDE. E vamos trabalhar nesta pasta de trabalho, abrir reter. Pensando nos objetos que precisaremos. Vamos precisar de um objeto de pasta de trabalho, que é a pasta de trabalho que estamos olhando atualmente. Nós também vamos precisar de um objeto de planilha. E o que eu quero estar fazendo é que eu quero percorrer cada uma das folhas na pasta de trabalho e eu quero mostrar ou ocultar a pasta de trabalho dependendo do nome. Então eu sei que este vai ser visível e o resto vai ser escondido. Portanto, eu posso apenas olhar para esta propriedade nome e eu posso determinar se eu quero mostrar ou ocultar essa pasta de trabalho. Então fizemos isso no início, claro, percorrendo cada uma das planilhas. Então eu vou fazer isso rapidamente. Então nós pedimos para loop. Então, isso vai percorrer cada planilha na coleção de tons de pasta de trabalho. Mas antes de continuar, quero explicar algo, pois vamos andar de bicicleta por essas planilhas arbitrariamente. Se houver apenas uma folha visível, que nós temos. E a primeira forma que encontramos neste loop é uma que queremos esconder, que vai quebrar. Não é possível ocultar todas as planilhas na pasta de trabalho que precisam ser pelo menos uma forma visível. Então o que eu vou fazer é eu vou fazer isso em dois loops separados. Primeiro a ter a certeza dos que queremos mostrar, e depois a segunda a esconder os que queremos esconder. Então eu vou estar interrogando o nome da planilha e é ativa folha. Agora, porque este é o que queremos mostrar que queremos negar isso. Só estamos interessados nas planilhas que não têm esse nome. Portanto, eu só vou adicionar 11 coisa, o que não é. Então, ele só vai entrar esta declaração se. Se o nome não é folha ativa, então e vamos nomear argumento para isso e queremos torná-lo XL visível. E se ele desaparecer em você basta apertar o espaço de controle e trazê-lo de volta para cima ponto x0, xo viável. Legal. Agora o próximo loop, ele vai copiar e colar. Vou me livrar disso e não, e vou mudar essa. Faça-o escondido. Então eu vou salvar agora e que vai preservar os estados visíveis de cada uma das folhas e também ecoou uma outra coisa que pode desencadear falhas ou erros é o estado protegido da folha ou da pasta de trabalho. Assim, você pode ter moldado a proteção que só permite que o usuário edite determinadas células. Neste caso, o que você vai querer fazer é que você iria querer desescolher a folha antes de você quente ou mostrá-lo e você fazer isso assim. E é aqui que você também forneceria senha se você incluiu uma, é uma boa idéia se você tem uma senha para armazenar isso como uma constante, certifique-se de que depois de definir a propriedade visible para protegê-la novamente, isto é, se você tiver um forma que está protegida. Mas, neste caso, não temos. Vou deletar isso. O mesmo novamente para a pasta de trabalho. Se você tiver uma pasta de trabalho protegida e ela estiver protegida no momento, você pode fazer isso fora do folato logo antes de entrar no loop completo. Como esta pasta de trabalho não está protegida, vou deletar isso. Agora podemos executar esse código para testar o que acontece quando um usuário abre a pasta de trabalho sem a necessidade de realmente fechá-la e reabri-la. Então vou fazer isso agora. Nós vamos passar se eu oh, então nós vamos tempo de tempo de execução. Variável de objeto ou com variável de bloco não definida. Isto é bom. Fico feliz que recebemos um erro de tempo de execução, então vamos apenas depurá-lo. Está nesta linha aqui. Então, para cada planilha, então não há nada. Sabe o que não fizemos? Não definimos o objeto que é pasta de trabalho. Então eu vou mudar isso rapidamente. Porque esta pasta de trabalho, apenas adicionar esta linha não faz nada porque o código já foi aprovado nesta lei. E então o que eu vou fazer é apenas arrastá-lo de volta aqui para que ele defina esse objeto. E vamos dizer que agora, sim, ok, que funciona quando agora interrogando o nome da planilha e nós só queremos inserir esta instrução if se o nome não é forma ativa e a planilha atual com olhar neste loop é realmente ativar forma. Então ele pula graduação indo. E a folha atual é custdata. Então, ainda assim, queremos que este seja visível. pai um é a saída, que está lá. Sim, ótimo. Ok, indo. Vou esperar até chegarmos ao próximo ciclo. Ok, então agora estamos no segundo ciclo e esse é o que vai esconder os lençóis que queremos escondidos. E o atual é ativar folha. E está escondida. Aquele quarto dado ainda não se e não se escondeu mais. Então, agora, se minimizarmos isso, eu só me certifico de que esse código está terminado de ser executado. Vou minimizar isso e trazer o Excel. Então eu vejo que a mensagem de macro ativar desapareceu. Toda essa folha desapareceu, e mostrou todas as outras folhas que queremos mostrar. O que acontece se quisermos várias folhas escondidas? Podemos adicionar critérios extras ou então se é a esta declaração if que está dentro do loop que se destina a ocultar as formas. Mas isso pode ficar meio fora de controle, particularmente se você tem muitas planilhas na pasta de trabalho, há uma maneira mais fácil. De volta ao Excel. Vou adicionar uma nova planilha e mostrarei como. Então eu adicionei uma nova forma que agora é chamada SHA-2. O que eu faço é usar uma convenção de nomenclatura no nome da folha. Então eu me certifico de que os primeiros sete caracteres estão escondidos um sublinhado, e pode haver inúmeras razões pelas quais você quer folhas escondidas, pode ser que você tenha um conjunto de dados que você deseja ocultar do usuário. Ou pode ser que você tenha um monte de configurações em uma forma que você está lendo, do código. É basicamente qualquer coisa que você não quer a edição. Então vamos em frente e adicionar isso à nossa condição “se”. Deixe-me primeiro mostrar-lhe na janela imediata como vou usar esta convenção de nomes. Então, vamos usar uma função VBA embutida que é chamada esquerda. E o que lift faz é que ele nos retorna um certo número de caracteres do lado esquerdo da corda. E especificamos quantos caracteres queremos do lado esquerdo da string. Então eu vou digitar à esquerda e então você verá os argumentos que fizemos. Então nós temos uma corda e uma corda, eu vou apenas digitá-la. Então essa é a forma que acabamos de adicionar à pasta de trabalho. E eu vou bater vírgula, e é o primeiro sete personagem. Então eu quero que ele retorne os sete primeiros caracteres do nome da folha. E como você pode ver, ele retornou os primeiros sete caracteres que estão escondidos na escola, porque agora nós retornamos os primeiros sete caracteres do nome da planilha. Podemos usar essa lógica em nossa declaração if. Então eu vou apenas saltar de volta para o bloco if LCF porque nós temos a primeira condição if lá e não é um outro, é um outro. Se temos outra condição, queremos testar. À esquerda como acabamos de aprender, da string, que é o nome da planilha. E são os primeiros sete caracteres iguais a sublinhado oculto. Então. E eu também quero fazer a mesma coisa. Outra maneira que podemos alcançar isso é também adicionar uma condição de todos em nossa declaração if. Então eu vou te mostrar rápido ou então podemos copiar isso. Então, podemos realmente adicionar duas condições a essa declaração se. Mas vou deletar essa. Então, agora podemos executar isso e apenas testar para ver se esta planilha desaparece. Está bem? E nós realmente testamos aqui. Então, se selecionarmos a folha, podemos dizer que a propriedade da folha já está escondida, em forma escondida, mas eu vou apenas movê-los para fora do caminho. Sim, a folha que eu criei que estava escondida, dados sublinhados, desapareceu da pasta de trabalho. Quando você cria o teste desse tipo de funcionalidade que é executado em aberto e UNCLOS, é uma boa idéia criar uma rotina que redefina o estado para que você possa testá-lo novamente rapidamente para que você não precise passar manualmente por cada das planilhas e selecione a propriedade visible. Então eu vou fazer isso rapidamente agora. E então eu vou te mostrar o que mais você pode usar nesse outono. E eu vou fazer isso em um novo módulo de código, Inserir Módulo, módulo três. Ok. Então eu estou resfriando redefinir a pasta de trabalho que eu posso carvão a qualquer momento. E isso vai devolvê-lo ao estado em que a pasta de trabalho deve estar quando for aberta. Vou nomear minhas variáveis. Novamente. Vou precisar de dois para os loops. E o primeiro ciclo será para os auditores que queremos mostrar. E o segundo ciclo será quatro itens que queremos ocultos. Então, o que queremos mostrar é aquele que é ativar o Xite porque essa é a única planilha que queremos ativar quando a pasta de trabalho é aberta. E eu também quero adicionar a LCA para os escondidos debaixo da escola. Ok, então agora podemos usar isso para redefinir nossa pasta de trabalho volta ao estado em que deveria estar quando for aberta. Vou testar isso agora com F5. Oh, bom, temos que falar de incompatibilidade. E estou feliz que estamos recebendo esses erros de tempo de execução porque isso realmente nos ajuda a aprender. Então, se acertar depuração, ele nos diz o que está errado. Então, se não planilha 0.9c e o nome do ponto da planilha atual é folha ativa. É uma corda. Ok, você pode ver aqui que eu tenho um menos em vez de um igual. Então, esse é apenas um exemplo de depuração do código e teste do código para garantir que ele esteja funcionando e robusto. Então, aperte F5 novamente. E eu vou voltar para o meu livro aqui. E como você pode ver, apenas esta folha é visível. Então, outra maneira que eu quero usar essa rotina é quando a pasta de trabalho é fechada. Então, digamos que, para um exemplo, um usuário o usa e eles têm macros habilitados, e então todas as planilhas são desocultas, mas eles salvam a pasta de trabalho e, em seguida, usam um verdadeiro vem junto. E eles não têm macros habilitados porque esse usuário já salvou a pasta de trabalho e ele não está oculto as planilhas. Quero ter certeza de que toda vez que a pasta de trabalho é fechada, essas planilhas estão ocultas e que a mensagem que lhes diz para ativar macros esteja visível. Então eu vou apenas copiar este sub-nome e eu vou saltar de volta para o módulo de código da pasta de trabalho. E eu vou apenas olhar através desses eventos para encontrar um que vai pegar o usuário fechando a pasta de trabalho. Então eu sou capaz de definir a propriedade visível forma para o que eu quero que seja. Ele iria antes de fechar. E é criado uma rotina que vai pegar o evento de fechamento da pasta de trabalho e nos permitir executar código antes que a pasta de trabalho realmente fecha. Então o que eu vou fazer é agora eu vou chamar uma macro já construída. Então, em resumo, esta rotina aqui vai pegar o evento do usuário fechando a pasta de trabalho. E antes que a pasta de trabalho realmente feche, ele vai chamar a rotina de pasta de trabalho de redefinição. E a outra coisa que eu quero fazer é também salvar a pasta de trabalho, que irá salvar os estados visíveis de cada uma das planilhas. Então eu vou fazer isso rapidamente. Ok, lá vamos nós. Ok pessoal, isso é tudo por enquanto. Nesta lição, aprendemos sobre quais eventos e riscamos a superfície de como podemos usá-los. Falamos sobre o escopo do evento, IA, em que nível do aplicativo, os eventos que dispararam ou capturaram. E concluímos alguns exemplos práticos de captura em eventos abertos de pasta de trabalho e de fechamento de pasta de trabalho e execução de código assim que o evento é detectado. Também aprendemos sobre a função VBA incorporada chamada lift, que retorna o número especificado de caracteres da esquerda da string. Algumas outras coisas que surgiram nesta lição foram testar seu código. Encontramos alguns erros de tempo de execução. Isso foi uma coisa boa. É melhor para nós descobrir esses erros do que seus usuários. Também abordamos o controle da experiência do usuário, que era inteiramente o que eram os exemplos práticos. E por último, aprendemos sobre como chamar uma macro de dentro de outra macro. Meu nome é Andrew, obrigado por assistir, e te vejo no próximo vídeo. 5. 05 - VBA Lesson 12 - Resolvendo dados: Bem-vinda de volta. Isso é menos de 12 lidando com dados. Percebi que lidar com dados é um título razoavelmente vago e amplo para este tópico porque existem inúmeras maneiras de lidar com dados no VBA. Portanto, este título será tópico recorrente sobre o resto dos meus cursos para conteúdo intermediário e avançado. Nesta lição, nós vamos aprender sobre como importar dados para nossa pasta de trabalho a partir de várias fontes. Aprenderemos sobre como se conectar a bancos de dados externos e executar algumas consultas SQL básicas para buscar dados e devolvê-los ao Excel. Vamos fazer esta lição em duas partes. A primeira parte estaremos importando dados do arquivo CSV que abrimos na lição anterior. E a segunda parte, vamos nos conectar a um banco de dados e extrair todos os dados de uma tabela dentro desse banco de dados. E em ambos os exemplos, estaremos preenchendo uma folha dentro desta pasta de trabalho com os dados extraídos do arquivo ou do banco de dados. Então, neste primeiro exemplo, estaremos construindo um pouco mais de funcionalidade nesta sub-rotina que temos vindo a construir ao longo das últimas lições. E apenas como uma recapitulação, o que esta rotina faz é procurar primeiro um arquivo para ver se ele existe e abri-lo se ele existe, caso contrário, ele pede ao usuário para localizar esse arquivo. Então, o próximo passo é que queremos extrair os dados da planilha que abrimos, preenchê-lo em uma planilha dentro desta pasta de trabalho e, em seguida, continuar da mesma forma. Então vamos dizer a sua própria cópia da pasta de trabalho e vamos fechá-la. Então vamos começar a codificar por aqui. Este é o ponto em que a pasta de trabalho será aberta porque a macro irá abri-lo automaticamente. E se ele não conseguir encontrar esse arquivo, ele pedirá ao usuário para localizar esse arquivo. Então, novamente, se chegarmos a esta cápsula aqui, isso significa que temos a pasta de trabalho aberta. E não só está aberto, como está definido para este objeto aberto WB. A primeira coisa que vou fazer é definir o objeto da pasta de trabalho atual. Então essa é a pasta de trabalho está realmente executando o código. E agora vamos mudar de conversa sobre esta pasta de trabalho atual. Vou definir uma planilha e, neste caso, o que vamos estar fazendo é usar a folha um objeto que está aqui em cima. E é aí que vamos depositar os dados. Agora vou definir o objeto da planilha da pasta de trabalho que abrimos, que é o arquivo CSV. Agora, CSV é um documento muito básico. É só um documento de texto. Um CSV também pode conter apenas uma folha. Portanto, não preciso especificar um nome aqui. Posso apenas fornecer um índice que é um, porque só vai haver uma folha em um documento CSV. Uma vez que o código atinge este ponto aqui, teremos ambas as pastas de trabalho abertas. Nós também terá ambos os objetos de planilha definido. Agora podemos ir em frente e começar a tirar os dados da planilha da pasta de trabalho que acabamos de abrir. Eu acho que a maneira mais rápida de fazer isso é com o uso de intervalos. E isso é porque eu posso simplesmente usar a propriedade de valor de intervalo e que irá conter todas as informações que estão nesse intervalo. E então eu posso atribuir o valor de um intervalo para outro intervalo. Então, para fazer isso, eu vou precisar de um par de objetos de alcance. Um especificando um intervalo da pasta de trabalho que abrimos e outro especificando um intervalo da pasta de trabalho atual. Vamos primeiro definir o intervalo da pasta de trabalho que acabamos de abrir, que é R e G aberto. Há um pequeno truque bacana que podemos empregar aqui para obter o Excel para nos dizer qual é o intervalo usado dessa planilha. Ia, Excel nos dirá todas as colunas e linhas que contêm dados. É importante lembrar que isso só é útil quando estamos falando de uma mesa. Assim, uma folha que contém apenas uma tabela para usar propriedade range con identificar se há mais de uma tabela em uma folha e ele irá apenas incluir ambos por padrão. Então, é tão simples como que nós usando o objeto de planilha e estamos especificando o intervalo usado. Agora eu posso sentar o outro objeto intervalo com base no tamanho deste objeto intervalo. Como estamos mudando valores de um intervalo para outro intervalo, precisamos que esses intervalos sejam do mesmo tamanho. Então deixe-me mostrar-lhe como fazer isso. Então o que estamos fazendo aqui é que estamos sentando um novo objeto de intervalo, que está na pasta de trabalho atual na planilha atual, que é a folha um. E com a definição desse intervalo para ter o mesmo tamanho que o intervalo de objetos abertos. E como estamos fazendo isso é usando a propriedade address do objeto aberto intervalo. A propriedade address nos dirá a célula superior esquerda e a célula inferior direita da tabela. Então, agora que temos ambos de nosso conjunto de objetos de intervalo, podemos simplesmente definir o valor de um para o valor do outro. E é isso. A razão pela qual eu prefiro este método é que ele é extremamente eficiente. Nós não precisávamos gastar tempo descobrindo manualmente o tamanho dos intervalos, porque estamos usando uma função integrada do Excel para fazer isso. Também não estamos usando a interface do usuário, que significa que não estamos selecionando planilhas, selecionando pastas de trabalho ou selecionando intervalos. Nós não estamos usando copiar e colar que copia dados para a área de transferência simplesmente usando as propriedades e os métodos que estão à nossa disposição dentro desses objetos. Então vamos tentar executar isso. Eu só estou indo para F5 através do primeiro pop usando conversão de moeda CSV. Ok. Estamos agora sentado fora objeto de pasta de trabalho atual e estamos definindo o objeto de planilha atual. E eu estava sentado um segundo objeto de planilha que é da pasta de trabalho que acabamos de abrir. Ao usar agora a propriedade range usada desse objeto de planilha para acomodar as dimensões de intervalo. Agora, rapidamente só vamos abrir a janela imediata depois de termos definido esse objeto. E eu vou ver qual é o endereço deste enorme alcance. Então, vou devolver o endereço. Agora ele retornou I1 para ver 805. Então, se eu apenas trazer nossa conversão de moeda, csv, Podemos dizer que o canto superior esquerdo é I1. E devemos dizer que o canto inferior direito é C 805, que temos 805. Então essa é uma maneira muito rápida e fácil de obter o Excel para dizer-lhe quantas linhas e colunas estão em uso nesta planilha. Então, eu só estou pulando de volta aqui, para derrubar a janela dos minutos. Então agora eu estou sentado organizar em uma planilha que está na pasta de trabalho atual para ser exatamente o mesmo tamanho que o intervalo que acabamos de tirar da pasta de trabalho que abrimos. Agora estou tirando o valor do intervalo na pasta de trabalho que acabamos de abrir. E eu estou definindo isso como o valor do intervalo na pasta de trabalho atual. E deixe-me mostrar rapidamente uma pasta de trabalho atual que temos na Folha um. Eu só vou trazer isso à tona. Na folha um. Temos I1 para ver 805. E isso foi instantâneo, o que é objetivamente mais rápido do que usar copiar e colar. Vamos minimizar isso. Agora continue como normal. Só vamos salvar, fechar e acabar com a rotina. Vamos agora passar para o próximo exemplo. Neste exemplo, estaremos conectando a um banco de dados do Microsoft Access e será extrair todos os dados de uma tabela e preenchendo isso em uma nova planilha nesta pasta de trabalho. Antes de continuar, só quero mostrar o banco de dados em excesso. Então eu vou trazer isso aqui. Se você estiver acompanhando esses vídeos, o banco de dados e o arquivo CSV estarão disponíveis para download na seção do projeto deste curso. Então vamos abrir esse banco de dados. Como podem ver, temos duas mesas aqui. Temos a tabela de conversão de moeda e temos um título de teste. Mas agora tudo o que estamos preocupados é com esta tabela aqui, TBL sublinhou conversão de moeda. E se eu abrir essa tabela, você verá que ela contém as mesmas informações que estão no arquivo CSV. Ok, então eu vou fechar isso agora. E vamos começar. Vou criar espaço aqui em baixo. Então vou começar definindo nossa sub-rotina. Só estou chamando de conectar a dois dB. Vamos pensar no que vamos precisar aqui. Então, vamos definitivamente precisar do livro do Outlook e um objeto de planilha porque vamos estar preenchendo dados do banco de dados para esta pasta de trabalho atual em uma planilha. Eu também poderia ir em frente e sentar esse objeto de pasta de trabalho. Então, a fim de se conectar a um banco de dados e extrair dados dele, vamos precisar de duas coisas. Vamos precisar de um conjunto de recordes e vamos precisar de uma ligação. Pense em um conjunto de registros como uma planilha. É essencialmente uma tabela dos registros que retornamos do banco de dados. E a conexão é o vínculo entre o Visual Basic ou Excel e o próprio banco de dados. Como sempre com o VBA, existem inúmeras maneiras de nos conectarmos a um banco de dados. Neste exemplo, vou usar IDEO DB ou objetos x-dot ativos. E isso ocorre porque podemos usar esse método para vários tipos de banco de dados diferentes. Então, a fim de ser capaz de usar um objeto x-dot ativo, nós vamos precisar realmente adicionar essa referência em nossa pasta de trabalho. E para fazer isso, vamos selecionar ferramentas e selecionar referências. E eu vou trazer isso aqui. E vamos precisar procurar a biblioteca de objetos do Microsoft Active X ponto. Então, vou rolar até chegarmos à Microsoft. E você quer selecionar o mais atualizado em sua máquina? Na minha máquina que é 6.1 e bateu bem, uma vez que adicionamos esta biblioteca de referência, agora somos capazes de sentar variáveis desses tipos. Então vamos começar com um objeto conjunto de registros. Vai chamar-lhe o meu RS. Em seguida, vou declarar o objeto de conexão. Agora que eu declarei esses objetos, eu vou colocá-los como novas versões genéricas em branco desses objetos. Assim, o próximo passo será estabelecer uma conexão entre Excel e o banco de dados do Microsoft Access phi, esse objeto de conexão de banco de dados. E para fazer isso, vamos precisar configurar uma cadeia de conexão. Então vou precisar de mais uma variável. Vou somar isso aqui. E agora vou criar uma cadeia de conexão. A cadeia de conexão requer uma sintaxe específica, mas a sintaxe será diferente dependendo do tipo de banco de dados ao qual você está se conectando. Existem alguns ótimos recursos online que podem ajudá-lo a criar uma cadeia de conexão para o banco de dados que você está tentando se conectar na cadeia de conexão. Neste exemplo, precisamos fornecer algumas coisas. A primeira coisa é o provedor e a segunda coisa é a fonte de dados. O provedor apenas diz ao VBA que mecanismo estamos usando para se conectar a este banco de dados? E o provedor que vamos usar neste exemplo é um mecanismo padrão Microsoft ice OLA DB e sua versão 12. E vamos separar os parâmetros do aplicativo com um ponto-e-vírgula. E para criar a fonte de dados, vou apenas reutilizar a constante que usamos no exemplo anterior, que é o link para minha pasta Documentos. E esta última parte é o nome do arquivo do banco de dados. Então, vou pegar isso daqui. Então, com esses dois combinados, é o caminho do arquivo da pasta Meus documentos e o nome do arquivo do banco de dados. Então essa é a nossa fonte de dados. Agora que temos uma cadeia de conexão construída, podemos conectar-se a um banco de dados. Basta notar uma coisa, eu tenho um erro de digitação aqui, eu tenho um N sobre isso e outro sobre aquilo. Então vou mudar isso. E eu vou estar usando o método Open do objeto de conexão. E ele vai nos dizer o que precisamos fornecer quando é uma cadeia de conexão de fornecimento que nós criamos. E se tivermos um nome de usuário e senha, podemos fornecê-los. E nós também podemos fornecer outras opções, mas tudo o que precisamos neste é apenas a cadeia de conexão. Então, depois que esta linha é executada, teremos estabelecido espero uma conexão com o nosso banco de dados. E agora podemos começar a enviar comandos para um banco de dados, a fim de trazer de volta os registros que queremos e os comandos que estaremos enviando o que é chamado SQL ou SQL. SQL principal linguagem de consulta estruturada. Eu vou precisar construir uma instrução SQL, e eu vou fazer isso dentro de uma string objetos. Então eu vou apenas criar um novo objeto e chamá-lo STI SQL. Agora vamos construir nossa instrução SQL. Agora, SQL pode ficar incrivelmente complicado. No entanto, neste caso, tudo o que queremos fazer é retornar todos os registros que estão em uma tabela específica. Então, a fim de fazer isso, usamos Select e vamos usar uma estrela aqui capturada selvagem, que é tudo de. E agora precisamos fornecer o nome da nossa tabela, que foi TBL na conversão de moeda escolar. Ok, agora que temos nossa instrução SQL, podemos ir em frente e abrir no conjunto de registros usando o método Open que vai nos dizer o que precisamos, a fonte, que é a instrução SQL. E precisamos fornecer a conexão, que é a conexão que estabelecemos com o banco de dados. E há outros argumentos que podemos fornecer, mas isso é tudo o que precisamos fornecer por enquanto. Então, a primeira coisa que eu sempre faço depois que eu abrir um registro sit é que eu verificar que nós retornamos registros e os registros sit objeto tem uma propriedade de contagem de registro, no entanto, há uma captura e deixe-me explicar. Então vamos falar sobre as diferenças entre Excel e banco de dados. Excel é muito macro nível em que podemos facilmente referir-se a células individuais dentro de uma tabela. No entanto, um banco de dados é mais focado em conjuntos de dados, significa uma tabela inteira ou conjunto de registros que é retornado de uma consulta. Assim, em um banco de dados há menos foco em células individuais. E embora essa seja uma ótima maneira de lidar com conjuntos de dados em um banco de dados, isso pode ser visto como limitação ao fazer referência a um banco de dados do Excel. Então, para contornar isso, eles inventaram um mecanismo que chamamos de cursor. Então deixe-me trazer o Excel. Então pense em um cursor bloquear isso, é literalmente apenas a célula com atualmente em que indica qual coluna e em que Roe estavam. E somos capazes de navegar por aí usando o cursor. Então, tendo isso em mente, precisamos especificar onde a localização costeira está no objeto de conexão. Então deixe-me fazer isso rapidamente agora. E é usado cliente. Então vamos usar o cursor do Excel, não o cursor do banco de dados. E fazendo isso, somos capazes de obter facilmente uma representação precisa de quantos registros no conjunto de registros. Então eu vou apenas usar a propriedade de contagem de registros dentro de uma instrução if básica. E então a razão pela qual eu estou fazendo isso é se começamos a interagir com um conjunto de registros como se ele tivesse registros, no entanto, ele não tem registros, então isso vai produzir uma era. Então eu só quero executar código quando um registro foi realmente retornado. Então, se entrarmos nesta declaração se com conexão de dados aberta com direto aberto seria sentar e temos registros retornados do banco de dados. Então agora eu posso ir em frente e criar uma nova planilha na qual eu vou preencher os dados. E para criar uma nova planilha, eu preciso usar a coleção de folhas, que pertence ao objeto de pasta de trabalho. Então WB, porque essa é a pasta de trabalho que queremos criar a folha e a coleção de folhas. E há um método que o anúncio para esta linha, vamos apenas adicionar uma folha genérica. No entanto, eu também quero especificar o nome. E eu vou apenas usar a data atual como fizemos em uma lição anterior usando formato. Escolha um sublinhado lá também. Então esta linha vai adicionar uma nova sombra nesse objeto de pasta de trabalho, que é esta pasta de trabalho atual. E ele vai definir a propriedade name ao mesmo tempo que adicionar a nova planilha e ele vai criar uma folha com este nome. Então esse é o dia atual no dia e no mês. Então d, d m, m, e ele vai ter um sublinhado e, em seguida, moeda. Agora que eu criei minha nova forma, eu posso então sentar meu objeto de planilha com base no que acabamos de criar. Só vou copiar isto porque é a propriedade do nome. É exatamente o mesmo. Legal. Assim, em um U em forma de não só querem preencher todos os dados, eu também quero incluir os cabeçalhos de campo. E se você olhar para um conjunto de registros como uma tabela, ele não inclui os cabeçalhos de campo, mas os cabeçalhos de campo estão disponíveis no objeto do conjunto de registros. Então o que eu vou fazer é usar um loop for e loop através das contagens de campos no conjunto de registros. E então nós vamos preencher os nomes dos campos na planilha dentro que para loop. Então eu estou usando a propriedade fields, e essa propriedade tem um método chamado count. Eu só vou precisar definir esta variável x. Defina como junto apenas no caso. E agora eu posso ir em frente e preencher isso na planilha. Quero que esteja na primeira fila porque é para lá que vão os cabeçalhos de campo. E eu vou usar x, que é o nosso ponto dentro do nosso loop for. E agora eu vou pegar a propriedade nome do campo fora do objeto conjunto de registros para a coluna atual em que estamos. Uma coisa que eu quero adicionar aqui é que, ao contrário de uma planilha ou de um objeto de planilha, propriedade desse campo é 0 indexada. O que quero dizer com isso é que o número do índice começa em 0. Enquanto um objeto de planilha, o Índice começa em um, como na linha um, coluna um, etc. Mas a propriedade fields, a indexação começa em 0. Isso significa que eu preciso tirar um do número x porque ele começa em um aqui. Que estamos usando para abordar qual coluna está na planilha. Agora eu quero ter certeza de que o cursor do conjunto de registros está no primeiro registro. Então eu vou apenas usar um método de movimento. Como você pode ver, existem vários tipos diferentes de métodos, mas eu quero dizer mova-se primeiro. Então isso só vai mover o cursor para a direita até o primeiro registro nesse conjunto de registros. E agora que eu também tenho minha cabeça de campo é preenchida, agora eu posso ir em frente e começar a preencher os dados do registro, sentar na worksheet. Para fazer isso, eu vou usar um loop due until, e eu vou fazer loop até o conjunto de registros chegar ao fim. Então vou te mostrar como vou fazer isso. Faça E, o, F, ou fim do arquivo. Agora, porque eu não estou usando um loop de índice aqui, eu vou precisar de outro número ou contador para contar qual linha estamos atualmente em somas vai pegar uma nova variável aqui. E eu vou sentar isso aqui, 22 porque a primeira fila já está tomada com os cabeçalhos e vamos começar na segunda fila. Então, se você pensar sobre isso, este loop vai estar percorrendo os registros ou linhas, e eu vou precisar de outro loop para percorrer os campos ou colunas no meu conjunto de registros. Então eu vou usar um método semelhante ao que usamos aqui. Agora eu posso preencher esta célula atual do conjunto de registros para a célula na worksheet. E eu vou usar y como o rho porque essa é a linha lá, começa às duas. E o índice da coluna é x valor de ponto igual a. E semelhante ao acima, estamos usando x menos um porque campos é 0 índice. Estamos usando o método de valor desta vez. Acabei de encontrar um erro. Então neste loop devemos estar usando nome não valor, porque valor é o conteúdo da célula e nome é o nome do campo. Então eu vou mudar isso. Agora, depois de terminarmos esse loop aqui, isso significa que percorremos todas as colunas na linha atual no conjunto de registros. Então agora precisamos ajustar isso. Precisamos mover para a próxima linha na worksheet e a próxima linha no conjunto de registros. Portanto, vou fazer y igual a y mais um. E eu vou passar para o próximo registro e o conjunto de registros usando o método move próximo. Ok, então acho que estamos prontos para testar isso agora eu vou passar através dele usando F8. Então, basta clicar aqui e parar ocorreu. E se não temos uma flecha imediatamente, significa que compilamos e tudo está bom ou pelo menos é bom por enquanto. Sim. Ok. Isso é bom. Está compilado. Vamos começar a passar por isso. Então nós estamos indo para sentar fora objeto pasta de trabalho. Vamos criar novas versões em branco do conjunto de registros e da conexão com o banco de dados. Estamos construindo uma cadeia de conexão. E eu poderia abrir a janela imediata só para mostrar o que parece estar concluído. Então é isso que parece, um banhado. Então concatenei o caminho e o nome do arquivo. Vamos derrubar isso. Então, espero que agora possamos nos conectar ao nosso banco de dados usando esta cadeia de conexão, dedos cruzados. Bem, isso parecia funcionar. Então agora vamos definir a localização costeira. Então ele define a localização do cursor para o lado do cliente, que significa que somos capazes de obter uma representação precisa de quantos registros foram retornados no conjunto de registros. Eu vou construir a consulta SQL agora. Eu vou abrir esse conjunto de registros dedos cruzados que com escrito instrução SQL corretamente. Sim, isso é bom. Então vamos abrir a janela imediata e podemos interrogar qual é a contagem de recordes. Ok, então nós temos 804 registros naquele conjunto de registros, que é tudo no título. Vamos continuar. Então agora vamos criar uma nova planilha. E podemos dizer que criou um novo aqui hoje à noite. Oh, que é o dia atual aqui na Austrália sob a moeda escolar, o que é excelente. E eu vou definir que objetos para que nós estamos usando o mesmo nome que acabamos criar para que nós definimos objeto planilha. Vamos continuar. Então agora vamos preencher os cabeçalhos de campo na folha. Então eu só vou trazer o Excel para que possamos dizer que ele já fez isso. Então nós temos o lençol aqui, eu vou imã se for pequeno. Então, podemos dizer o nome da folha aqui, e é apenas preenchido os cabeçalhos de campo na folha. Isso é ótimo. Hum, eu também meio que faço isso em tela dividida, então apenas me acompanhe por um momento para que possamos executar o código ao mesmo tempo. Podemos dizer isso, preencher a planilha. Então, de volta ao código agora. Vamos passar para o primeiro disco. Vamos definir a localização do cursor para o primeiro registro e o conjunto de registros. E vamos definir o primeiro rotor dois. E agora vamos percorrer todos os registros desses centros de registro, derrubar isso até chegarmos ao fundo do conjunto de registros. Então, no momento, como você pode ver, não estamos no final do conjunto de registros e essa propriedade é falsa. Então vamos só andar de bicicleta agora. É interessante, mas é porque o valor é lançar para C. Ok, então agora está se movendo. Então você também pode estar pensando, uau, estamos passando por esse conjunto de registros célula por célula, mas isso é razoavelmente eficiente. Se tivéssemos mais colunas e mais linhas ou mais registros, eu provavelmente faria isso de outra maneira, mas não fomos tocados nisso até chegarmos ao conteúdo avançado. Então, no momento, este método é realmente muito bom para o que queremos fazer, que é preencher um pequeno número de linhas e colunas em uma planilha. E como eu disse, é razoavelmente eficiente porque quando não usar a interface do usuário com apenas colocar valores de um lugar para outro. Então o que eu vou fazer é colocar uma caixa de mensagem no final que indica que chegamos ao fundo. Então, havia 805 cada linhas. Para que possamos ajustá-lo a preço total e podemos obter a caixa de mensagem quando estiver pronto. Então agora eu vou bater F5 e pronto. Então, como você pode ver, é bem rápido. E vamos rolar rapidamente por aqui. Vou levar isso um pouco mais adiante. E sim, preencheu todos os registros. Espero que, se eu baixei o controle ainda, eu tinha um 105. Lá vamos nós. Então isso é preenchido todos os registros. Certo, então isso encerra a Lição 12. Em resumo, analisamos a importação de dados de várias fontes externas. Abrimos um arquivo CSV e importamos isso para nossa pasta de trabalho. Também nos conectamos a um banco de dados externo e executamos uma consulta SQL básica para retornar os registros nos quais estávamos interessados. Os novos recursos do VBA que introduzimos foi a biblioteca de objetos de dados x ativos e também o objeto tipo de conjunto de registros. Meu nome é Andrew, obrigado por assistir, e te vejo no próximo vídeo. 6. 06 - VBA Lesson 13 - Arrays: Bem-vindos à Lição 13. Matrizes. Na minha opinião, um aumento da função mais poderosa e útil no VBA. Arrays são tão úteis porque eles nos permitem manter eficientemente enormes quantidades de dados na memória enquanto consomem muito poucos recursos. Isso nos permite realizar tarefas trabalhosas em frações de frações de segundos. Nesta aula, nós vamos aprender sobre meu tópico favorito, arrays. Vamos falar sobre matrizes de dimensão única, e também vamos falar sobre matrizes multidimensionais. Pense em um array como uma tabela virtual. Mesa de medalhão. Pode ter linhas e colunas. Isto é o que queremos dizer quando estamos nos referindo às dimensões de uma matriz. Uma matriz unidimensional contém apenas uma coluna. Portanto, ele só precisa de uma dimensão para se referir a um valor 0 específico, que é o rho. Uma matriz multidimensional contém mais de uma coluna. Isso significa que você precisa de várias dimensões para se referir a um ponto ou valor específico dentro desse conjunto de dados, por exemplo, coluna e qual linha. A vantagem de usar uma matriz ou se é dizer que uma faixa de folha ou registro sit é por causa de sua eficiência. Ele é apenas uma comparação rápida de um objeto de intervalo versus uma matriz. Um objeto de intervalo é uma referência a um local específico em uma planilha. E como organizar variável é uma representação desse local de forma específica, ele tem várias outras propriedades pai e filho. E por isso, a variável no VBA consome mais memória em comparação com uma matriz das mesmas dimensões. Um objeto de intervalo também tem que sustentar uma referência entre o objeto teórico declarado em VBA e o objeto de intervalo físico na folha. Isso significa que há sempre operações inevitáveis de leitura e gravação realizadas entre a representação variável do objeto de intervalo e o objeto de intervalo físico na planilha. Portanto, quando estamos falando conjuntos de dados muito grandes que exigem uma operação intensiva em mão-de-obra. E por isso eu até fiz apenas olhando para cima um único valor dentro do título. O desempenho do objeto de intervalo é muito ruim quando comparado a uma matriz. Por outro lado, uma matriz é apenas um tipo de objeto básico que tem muito poucas propriedades. São literalmente apenas as dimensões certas e o tipo de dados. Isso significa que ele permite que você simplesmente carregá-lo com dados e segurá-lo com o mínimo de confusão. E por causa de seu nitrito simples, ele faz as mesmas tarefas trabalhistas intensas a serem realizadas sem esforço e, portanto, incrivelmente rapidamente. Vamos olhar para dois exemplos práticos hoje, uma matriz unidimensional e uma matriz multidimensional. Vamos saltar para dentro E este exemplo, vou apresentá-lo a surgirem unidimensional e também a função VBA incorporada chamada split. Vou adicionar rapidamente um novo módulo de código. Então estamos no módulo cinco. Agora vamos começar com “split”. Split é uma função VBA embutida que nos permite executar uma operação em uma string, que a divide em vários componentes discretos baseados em um delimitador. Um delimitador é um caractere que denota um ponto de separação. Se você se lembra na última lição, usamos arquivos CSV. Um arquivo CSV tem um coma como um delimitado entre campos. A função split também nos permite definir qual caractere queremos usar como delimitador. Eu vou começar primeiro de tudo, apenas criando uma sub-rotina. Ok, eu só estou chamando de matriz SPL SBA. Ok, então a primeira coisa que vou fazer é declarar nossa variável array. Agora este tipo de dados é uma string. O que isso significa é que tudo dentro desta matriz será armazenado como uma string. Você é realmente capaz de manter vários tipos de dados usando variante. No entanto, raramente uso variante. E a razão é porque eu quero apenas segurá-la da maneira mais básica possível, que é uma corda. Ao usar a guia de dados de variante, você pode encontrar problemas como nós tínhamos formatos de data e esse tipo de coisa. E isso é obviamente indesejável. Então eu sempre seguro que tudo é uma corda. Agora eu também quero declarar uma variável de string, e isso vai conter um pedaço de texto que contém coisas que queremos dividir. Então vamos pensar sobre por que podemos querer dividir algo. Ok, então eu vou voltar para o nosso exemplo anterior aqui. Como você pode ver aqui, temos o caminho completo do arquivo do banco de dados que tínhamos no exemplo anterior. Então eu vou apenas copiar que saltou de volta para o módulo cinco, e eu vou apenas atribuí-lo a esta variável aqui. Digamos, por exemplo, que sabemos que este é um caminho de arquivo completo, mas nós só queremos pegar o nome do arquivo em si. Sabemos que um diretório contém barras invertidas. Portanto, se dividirmos esta string por barras invertidas, Podemos agora pegar o nome do arquivo, que será o último outono nessa divisão. Então vamos fazer isso agora. Então eu estou usando a função split aqui e meu colchete, e a expressão que eu quero enviar a função split é split e o delimitador é barra invertida. Não preciso fornecer mais nada. Assim, uma vez que esta linha é executada será segurando uma matriz em SPL e ele vai conter esta string dividida pelo caractere barra invertida. Então o que eu estou esperando é a primeira linha na minha matriz para segurar C dois-pontos. E o próximo será usos, e o próximo será I e II. O próximo é o OneDrive e assim por diante e assim por diante e assim por diante. Então o que eu vou fazer é passar pelo código e depois de executarmos esta linha, eu vou mostrar a vocês o que acontece na janela do relógio. E despreocupado com este contexto, o contexto atual desta sub-rotina. Então bata bem. Ok, então agora na janela do relógio podemos ver que temos nossa variável aqui. Podemos dizer que no topo é uma corda e tem uma propriedade de 0 a cinco. E array é 0 indexado, o que significa que o primeiro registro é indexado 0. Ao contrário de uma planilha indexada em um, não há linha 0, há apenas uma linha. Então, na matriz, o primeiro item é sempre um 0 para linha e coluna. Mas este é apenas uma matriz dimensional única, portanto, só temos linhas. Então, como você pode ver aqui, se eu acertar o mais, você pode ver que o índice 0 é C dois-pontos, que é exatamente o que eu esperava que fosse. E o próximo é usos, e o próximo é El e DRA. O próximo é um documento do One Drive, blá, blá. Então, como você pode ver aqui, e eu dobro SPL cinco, eu estou segurando o nome de arquivo real em si. Então agora vamos falar sobre como eu vou sobre puxar o nome do arquivo para fora desta matriz. Neste caso, eu sei que o nome do arquivo é o último outono nesta string. Lembre-se quando estávamos olhando para a matriz no relógio, nós vimos que no topo ele dizia corda 0 a cinco. Portanto, sabemos que há seis itens nesta matriz, e nos referimos a essas coisas como limites. Assim, o limite baixo, que significa que é o primeiro item na matriz e o limite superior é o último item na matriz. Então, se eu quiser pegar o último outono, eu me referiria ao limite superior da minha matriz. Então eu vou apenas usar esse limite superior e agora eu vou depurar imprimir isso na Janela Imediata. Então, então eu estou usando os colchetes novamente porque eu estou realmente me referindo a algo dentro da matriz. E agora, na idade, referem-se ao índice. Então, outro é cinco. Então só mostrar 5 primeiro, que é o sexto item porque começa em 0. Então, se eu trazer isso de volta aqui e bater F8, basta trazer isso para cima. Você verá na janela imediata que ele nos deu o nome do arquivo. Agora, e se eu não quiser explicitamente Cy5 porque suas inúmeras pastas, existem inúmeras, mais pastas do que esta ou menos pastas do que temos nesta string atual. Então, o que eu posso fazer então é referido ao limite real. Então esse é um cálculo que me diz qual é o limite inferior ou superior dessa matriz particular. Então, em vez de fornecer um índice, vou fornecer-lhe uma curva em U do que é abundante. E se passarmos por cima disso, dirão que nos diz que o limite superior é cinco. Então, se eu fizer isso e apertar F5 novamente, você verá que ele fez a mesma coisa. Da mesma forma, se eu quiser retornar o limite inferior ou o primeiro item na matriz, que seria C dois-pontos. Eu também posso usar L ligado. Então, se eu fizer isso, você pode ver agora que tem o cólon C na Janela Imediata. Então, esse foi apenas um exemplo muito rápido e fácil de apresentá-lo a arrays ao mesmo tempo apresentá-lo à função split. Então agora vamos passar para matrizes multidimensionais. Este próximo exemplo pode não ser inteiramente prático. No entanto, ele serve como uma boa introdução aos arrays 2D. E, ao mesmo tempo, vamos demonstrar quão eficiente e rápido morrem quando usado como uma mesa em comparação com o uso de uma folha exatamente da mesma forma. Vamos fazer este exemplo em duas partes. A primeira parte, vamos carregar até 5 mil linhas em uma planilha com números aleatórios. E então vamos olhar através dessas 5 mil linhas para um valor específico. Depois disso, também vamos carregar uma matriz 2D com 5 mil linhas de números aleatórios e caçar um valor específico dentro dessa matriz. Vou ficar no módulo cinco aqui. Mas vou começar definindo nossa sub-rotina. Certo, pensando nas variáveis que precisaremos. O primeiro enredo, eu vou carregar uma planilha com 5 mil linhas de números aleatórios. Então eu definitivamente vou precisar da pasta de trabalho e da planilha. Agora eu tenho um lençóis em branco pronto. Isso é frio em forma de três. Então eu vou usar esse neste exemplo. Eu vou estar carregando as 5 mil linhas usando um par de loops para. Portanto, vou precisar de um par de números ou inteiros. Se você já sabe familiar, eu vou estar usando os inteiros x e y como a linha e o ponteiro da coluna em meus loops completos. Então, o primeiro loop para será para a linha. Então está indo da primeira fila para a fila 5 mil. E eu fui da coluna A para Z, então isso é 26. Então isso será de um a 26. Então, neste loop, como estamos em uma linha e como estamos percorrendo a coluna um a 26 na célula atual que passou dentro deste loop usando o ponto y, eu quero imprimir um número aleatório nessa célula, e eu quero que esse número para estar entre 15 mil. Vou falar rapidamente sobre isso em um comentário para explicar como criamos um número aleatório entre certos valores. Então, primeiro de tudo, vamos começar com alguns colchetes. E dentro desses suportes, o que queremos fazer é que queríamos ter o limite superior e o limite inferior mais um. Então, o limite superior é de 5 mil. E agora queremos subtrair o limite inferior, que é um. E então precisamos de mais um. Agora, fora desses colchetes, queremos vezes isso pela função R e D, que é um gerador aleatório. Agora também queremos adicionar o limite inferior novamente, então esse é um. Mas não queremos casas decimais, portanto, precisamos converter isso como um inteiro. Então, se eu agora apenas pegar isso e levá-lo para a janela imediata, eu vou apenas trazer a janela imediata para que eu não tenha que zoom para baixo mais tarde e fazer um ponto de interrogação e, em seguida, interrogar o que esse valor retorna. Deu-nos um número aleatório. E esse número nunca excede 5 mil nunca vai abaixo de um. Então agora vamos usar isso e preencher isso em uma cela. Sons, filmes Identidade Janela Imediata Como fizemos várias vezes e eu ouvi, queremos abordar a folha. Queremos tratar das células dessa folha. Que células queremos. Queremos x, y, porque temos o ponteiro da linha, temos o ponteiro da coluna. Valor de ponto é igual a número aleatório, que é custo como um inteiro. Então, depois de termos escrito todos os valores para as 5 mil linhas e 26 colunas. E eu quero essencialmente fazer a mesma coisa, mas ler os valores e procurar um número específico. E o número que quero procurar é 4.999. Então eu vou copiar isso muito rapidamente. Porque estamos fazendo a mesma coisa. Vamos começar na primeira fila e ir para a fila 5 mil. E em cada linha vamos percorrer a coluna 12, cólon 26. E eu vou me livrar disso por enquanto. Então, para interrogar um valor, eu só vou usar uma declaração if. Se”. Então, se a célula atual em que estamos, se o valor dessa célula é igual a 4,999, loops 4,999, então eu quero fazer algo. Quero substituir o valor dessa célula pelo outono encontrado. E eu também vou apenas depurar imprimir isso na janela imediata. E eu vou, então, incluir também o endereço. E desta vez eu vou copiá-lo. Ok, agora vamos para a nossa matriz. Então eu vou pular de volta as declarações de declaração de Ian Euro. E eu vou declarar nossa matriz. E você vai notar, mesmo que eu esteja falando de inteiros, eu ainda estou me referindo a ele como uma string. E isso é puramente para eficiência, mas também há uma maneira realmente rápida e fácil de comparar strings e inteiros. Então, agora que definimos nossa matriz, podemos agora dimensionar a primeira dimensão ou declaração, que apenas a configura como uma matriz. Agora eu quero fornecer as dimensões dessa matriz. Então, quão grande é a tabela, quantas colunas e quantas linhas? Agora também posso fazer isso na declaração de declaração, mas prefiro sempre fazê-lo separadamente. A maneira que você atribui o tamanho de uma variável de matriz está usando a instrução read deem. E nós ensinamos o nome da variável array. Agora precisamos fornecer os números de coluna e linha. Mas primeiro deixe-me comentar isso e eu explico uma coisa. Então, já sabemos que as matrizes são indexadas de forma diferente para outros tipos de objetos, como células e intervalos e folhas, etc, na medida em que começam em 0, então o 0 indexado. Agora, as propriedades de indexação também são diferentes. Então, por exemplo, quando estamos nos referindo a uma determinada célula em um intervalo ou em uma planilha. Primeiro precisamos da fila. E depois fizemos a coluna. Mas em uma matriz, é na verdade o contrário. Você primeiro aplica a coluna e, em seguida, forneça a linha. E isso pode potencialmente ficar muito confuso, mas na verdade é por uma razão bem direta. E a razão é que enquanto você está executando código, você não pode realmente adicionar colunas em uma matriz. Você só pode adicionar linhas quando você pensa em uma tabela de dados que você está usando em tempo de execução, que seus iranianos de classificação que estão interrogando ou tentando encontrar um objeto. É muito raro que você precise adicionar colunas a esse título, mas talvez seja necessário adicionar linhas a esse título ou adicionar novos registros a essa tabela. E por causa disso, você não pode realmente expandir a propriedade column em uma matriz sem primeiro destruir esse objeto e, em seguida, começar novamente. Então leia a dimensão toda e, em seguida, carregá-la novamente. De qualquer forma. Então vamos definir o valor da coluna e sabemos que há 26. Queremos um para Zed. E porque o array começa em 0, isso significa que precisa ser 25. E sabemos que haverá 5 mil linhas, portanto, porque começa em 0, é 4.999. Então, que definiu o nosso tamanho de matriz para 5 mil linhas e 26 colunas. E então agora que definimos o tamanho da matriz, podemos usar os limites da matriz em loops. Então vou fazer isso agora. Então, a primeira coisa que queremos fazer, que é idêntica ao que fizemos aqui com a folha. Queremos primeiro carregá-lo com números aleatórios, e então queremos percorrer esse array e queremos encontrar 4.999. Então, quatro. E eu só vou usar 0 aqui. Agora. Poderia usar 4,999 porque eu já sei que esse é o limite superior da matriz. No entanto, eu também posso usar o cálculo do limite superior, que está vinculado. E precisamos fornecer o limite superior que estamos procurando. Então esse é o Array Rand. Mas porque temos várias dimensões, precisamos fornecer qual dimensão queremos procurar. E estranhamente este índice aponta em um. Então estamos procurando as linhas neste caso. Assim, as colunas serão uma e as linhas seriam duas. Então estamos procurando o índice de dois. Não me pergunte por que fizeram isso. Foi assim que eles fizeram. E agora podemos percorrer a coluna vai usar você ligado novamente. E eu estou procurando a coluna, então essa é uma. E eu não estou apenas copiando isso aqui em cima. Mas agora vou designar. E eu estou procurando a coluna, primeira coluna, linha. Vou atribuir o valor a isso. Então exatamente o mesmo para o ciclo completo aqui em cima. O que estamos fazendo é percorrer esses dois para loops, passando pelas fileiras e pelas colunas. E estamos atribuindo um valor a cada célula individual dentro de cada linha e cada coluna. E agora também exatamente o mesmo que o exemplo. O que queremos fazer é que queremos percorrer a matriz e queremos procurar um valor específico. Então, novamente, eu só vou copiar isso e colá-lo lá. Vai descer um pouco. Aqui vamos nós. E eu vou ter a mesma declaração se, mas para a matriz. Agora, antes de eu continuar, você deve se lembrar que eu defini o array como uma string, que significa que ele está segurando apenas strings. Então o que isso significa é, digamos que temos o número nove. Se temos um nove, isso é um inteiro real, mas está segurando assim, como uma corda. Então VB olho não vê-lo como um caractere numérico o. O que isso significa é que se eu estou usando um inteiro aqui e eu tenho uma string aqui, estes nunca se compararão. Então o que isso significa é que eu preciso custar o que eu tenho aqui, que é mantido como uma string, como um inteiro. E a maneira que eu sou capaz de custar uma string como um inteiro é largura, o custo como função inteira. Há alguns problemas em fazer isso. Um problema que temos em fazer isso é que o que acontece se essa coluna em particular ou, ou esse valor em particular não contém um número, nós realmente vamos ter uma era. Então vou mostrar-lhe na janela imediata. Digamos que enviamos a função Sea INT um caractere alfa. Ensinam-nos incompatibilidade porque não se pode converter. Isso em um inteiro porque é um caractere alfa. Há inúmeras maneiras de contornar isso usando funções de linha única embutidas muito semelhantes, mas eu não vou entrar nisso agora. Vamos cobrir isso em outra hora. Ok, então exatamente o mesmo que o nosso loop aqui em cima. Quero substituir o valor mantido nessa célula pelo item encontrado. E eu também quero depurar imprimir uma mensagem nas janelas imediatas. Então vou fazer isso agora. Voltando para aquela incompatibilidade ensinada antes. Se eu sentar isso ou declarar uma variável como um inteiro porque estamos segurando inteiros, isso não funcionaria. Eu não seria capaz de enviar uma string. E essa é outra razão pela qual eu uso cordas. Porque basicamente todos os outros tipos de dados podem ser convertidos em uma string e, em seguida, convertê-lo longe de uma string. Mas se você defini-lo como um tipo de dados inteiro, você não pode colocar nada além de inteiros lá, números pares acima de 32,5 mil, porque esse é o limite máximo de um número inteiro. De qualquer forma, seguindo em frente, vamos alterar a impressão de depuração. Vou fazer isto um pouco diferente. Então eu também vou incluir o nome da matriz e o ponto está nesta declaração de impressão. Agora, eu também queria comparar a diferença entre os dois. E uma boa comparação é quanto tempo leva para fazer uma tarefa. Então, quanto tempo leva para completar o exemplo de planilha e quanto tempo leva para completar o exemplo de matriz. Para fazer isso, vou precisar fazer algumas somas de encontros. Então eu vou apenas adicionar uma variável de data. E eu também vou adicionar um par de inteiros. O primeiro estará segurando o número de segundos que leva para completar a primeira parte. E o segundo segurará o número de segundos que leva para fazer o segundo pote. Então, como eu vou conseguir isso é definindo o tempo para uma variável antes de seus pontos e, em seguida comparando esse tempo com o tempo atual depois que nós terminamos isso para possamos ver quanto tempo passou entre esses dois pontos. Então vamos começar colocando a hora atual e data na variável Date. E lembre-se que agora sempre retorna a hora atual, não importa o que aconteça. E depois de terminarmos. E agora podemos usar outra função para contar o número de segundos que levou para concluir esta tarefa e, em seguida, atribuir esse número a esta variável aqui. Vou parar com isso igual. Então a função que você usa para comparar a diferença entre duas dietas é data DEF. E você pode ver que está pedindo intervalo de uma string. Então é horas, minutos, segundos, dias, semanas, meses, anos, e então a primeira data, Então essa é a data mais antiga e a segunda data, que é a hora atual ou a data que você deseja comparar a luz de dia que você gostaria de comparar com a primeira data. E é isso que você precisa fornecer. Então eu quero segundos. E o primeiro dydt é D G dot. E a segunda noite, bem, isso é agora. Então, agora. E nós vamos estar segurando então o número de segundos que levou para chegar daqui até aqui. E agora podemos fazer o mesmo por este. Então vou reutilizar esse estoque porque não precisamos mais disso. Já aguentamos quantos segundos levou. E eu vou pegar a outra variável inteira. E farei exatamente a mesma coisa. Ok, então agora que eu tenho esses dois inteiros, eu vou apenas depurar imprimi-los na Janela Imediata. Vou copiar isso. Enquanto estamos executando isso, Excel pode relatar que ele realmente não está respondendo e isso é porque pode levar muito tempo para fazê-lo. Se você pensar sobre isso, estamos fazendo 5 mil linhas e estamos fazendo 26 colunas. Então isso é como 203.000 mil, sim, 130 mil operações. E então estamos fazendo mais 130 mil operações aqui. E então estamos fazendo mais 130 e mais 130 mil operações porque é o número de linhas vezes o número de colunas. E estamos fazendo uma coisa específica para H1. E o propósito disso era realmente apenas para mostrar a diferença no tempo que leva para extrair valores de uma folha versus puxá-los da memória. E toda vez que nós realmente passar por qualquer um desses loops com fazer algo para a célula onde ler ou escrever para ele. Então estamos lendo a partir dele nesta instância, que é verificar o valor. E também estamos escrevendo para ele. Então, estamos fornecendo um valor. E ainda temos fornecendo um valor aqui quando o valor da célula realmente é igual a 4,999. Mas, ao mesmo tempo, estamos fazendo o mesmo com o array. Na verdade, escrevemos 130 mil vezes aqui. E estamos lendo 130 mil vezes neste. Então eu vou esperar que isso vai levar um tempo para ser concluído, e ele definitivamente vai relatar que o Excel não está respondendo. E isso porque ele tem um valor de tempo limite em seu carvão de procedimento, que significa que ele não pode estar ocupado com tarefas por mais de um certo período de tempo antes de relatar que ele não está respondendo. E depois de executarmos isso, mostraremos uma maneira rápida de evitar que isso aconteça. Mas isso vem a um custo de desempenho. Então você gostaria de usá-lo com moderação. Tudo bem, vamos começar agora. Ponha o cursor aqui em cima. Eu vou para F5 em 321. Ok, nós fizemos os ventiladores do meu computador girarem muito rápido. Tudo bem, então eu vou mover esta janela imediata para cima aqui. E nós vamos dizer, eu vou apenas me livrar deste aqui. Verá que ele encontrou 4.999 em vários lugares e mantendo-os em seus números aleatórios. Então é, você sabe, nem sempre vai ser igual, mas é uma comparação razoável. Então, se encontrado 4,999 nestes endereços no intervalo, e também encontrou 4,999 na matriz neste ponto é. Mas o mais importante, completou 260 mil iterações de leitura e escrita em uma folha em 20 segundos. Mas um completou a mesma quantidade de iterações ou 260 mil para uma matriz em menos de 1 segundo. Então, como você pode imaginar, este tempo soma-se significativamente e essa redução de tempo é extremamente perceptível. Certo, só uma última coisa antes de terminarmos. Então, como esperado, a janela VBA ou Excel inteiramente mostrado não está respondendo enquanto este código estava sendo executado. Então há um y que podemos contornar isso. E isso é que estamos usando um comando chamado fazer eventos. E o que o comando do events realmente faz é que ele permite que o Excel priorize outros eventos na fila, como atualizar a janela, et cetera, et cetera. E isso pode ser muito útil quando você tem outros usos usando uma macro que você criou. E isso pode impedir um fechamento de pânico força porque eles pensam que seu aplicativo congelou. Mas por razões de desempenho, não queremos usar eventos do fazer em cada iteração do loop, queremos apenas usá-lo com moderação. Então, digamos aqui a cada 1000 linhas. Então, se eu precisar colocar eventos do fazer em meus loops, eu uso a função mod, a função MOD matemática para fazer isso para nós. Então eu acabei de colocar uma instrução if aqui que garante que o comando do events só é executado a cada 11000 linhas. Então, basta ir em frente e adicionar esta declaração if aos nossos loops ANOVA. E 11000 pode não ser suficiente porque pode demorar mais do que o valor de tempo limite para fazer cem, dez centenas de linhas. Então, em seu teste, defina isso para um valor razoável. Ele vai adicioná-lo ao Ri Loop também, apenas para ser justo, porque nós estamos realmente adicionando tempo extra para isso. Isso é apodrecer os eventos introduzem tempo extra. Vamos fazer isso de novo. Vou mover isto para cá. Esperemos que não devemos ver o Excel exibindo como não respondendo, mas quem sabe? Vamos dar uma olhada. Ok, está acabado. Esses trazem isso aqui. E não vimos que a exibição do Excel não está respondendo, o que é bom. E também não vimos uma redução de desempenho. Ainda são 20 segundos e 0 segundos, então isso é positivo. E isso me diz que eu acho que fazer, eu faço eventos comandar cada 1000 linhas é um intervalo razoavelmente razoável para realmente fazer esse comando. Ok, então isso resume o segundo exemplo prático nesta lição, a introdução a matrizes 2D ou matrizes multidimensionais, e uma demonstração das diferenças de desempenho entre o uso de apenas intervalos e matrizes, que contém isso dados na memória para fácil acesso de leitura e gravação. Eu só quero fazer um comentário final antes de terminar esta lição. Como você testemunhou no segundo exemplo prático, executar operações iterativas de um intervalo de células adiciona tempo considerável para executar nosso código. E esta é uma armadilha comum que VB eu desenvolvo tantas vezes caem. Na minha experiência, eu fui capaz de otimizar macros de trituração de dados que quer levou minutos ou mesmo horas para correr para mim alguns segundos. E isso é simplesmente usando e manipulando os conjuntos de dados dentro de arrays em vez de em intervalos de células. Obviamente, as operações de planilha, leitura e gravação são o que leva mais tempo. E é inevitável que, a fim de carregar um array ou depositar os dados de volta em uma planilha, você precisa executar leitura e gravações de ou para a planilha. No conteúdo avançado, vou mostrar-lhe como absorver um dado inteiro definido fora da folha em um array com uma única operação de leitura e tinha um despejado todo o array de volta para a folha com um único Raj operação para economizar ainda mais tempo. Então pessoal, isso encerra a lição de hoje. Se ele chegou até aqui, eu quero dizer parabéns, você acabou de aprender o único conceito mais útil no Visual Basic além da dimensão única e matrizes multidimensionais aprendidas nesta lição, você também aprendeu sobre a função split, que permite dividir strings em pequenos componentes usando um caractere especificado como delimitador. Você também aprendeu a usar a função R e D para gerar um número aleatório entre dois números. E por último, você aprendeu a retornar uma diferença entre duas dietas em um intervalo especificado, IA, segundos, minutos, horas, dias, semanas, meses ou anos. Meu nome é Andrew. Obrigado por assistir e te vejo na próxima lição. 7. 07 - VBA Aula 14 - Datas: Bem-vindos à Lição 14. Datas. Eu só queria falar sobre os problemas que você provavelmente vai encontrar com datas durante a codificação e VBA, o que procurar eo que você pode fazer para mitigar esses problemas. Então, hoje vamos falar sobre como o Excel armazena datas, como o VBA armazena datas, e sim, elas são diferentes. Iremos também abordar o grande problema na forma como as datas são implementadas. E vou mostrar-lhe algumas maneiras de resolver estes problemas. É importante entender como o Excel e o VBA realmente armazenaram datas. Porque se você comparar o formato de rolagem de uma data do Excel e uma data do VBA, haverá uma discrepância. Excelência portas datas como um número. É um número sequencial que representa quantos dias passaram desde o primeiro de janeiro de 1900. Assim, como exemplo, o 11 de agosto de 2020 será armazenado como 44.054. E isso porque o 11 de agosto de 2020 é 44.054 dias desde o primeiro de janeiro de 1900. O Vba também armazena datas como um número, e é também um número sequencial, mas expresso como quantos dias se passaram desde 31 de dezembro de 1899. Assim, o 11 de agosto de 2020 é armazenado como 44.055, sendo 44.055 dias após 31 de dezembro de 1899. Todas as datas, horas, minutos e segundos do Excel e do VBA são expressas como um decimal do número de dique. Então, se por exemplo, 0,5 é igual a 12 PM meio-dia, ou 0,25 é igual a seis AM. Portanto, no VBA, o número 1.25 representa o 31 de dezembro de 1899 às 6h. E no Excel, o número 1.25 representa o primeiro de janeiro de 1900 às seis, eu estou armazenando datas desta forma torna mais simples para Excel e VB I para que meta para realizar cálculos em datas. Por exemplo, contando o número de dias entre datas, adicionando datas juntas, etc. A Microsoft vê isso como um conceito muito simples, mas eficaz. Quando o Excel apresenta a data para um usuário em uma planilha ou com VBA na caixa de mensagem Janela Imediata ou usar um formulário, etc., é realizado um cálculo que apresenta os dados em um formato legível pelo usuário. E é aqui que encontramos problemas porque há muitas maneiras de uma data ser expressa. Por exemplo, US Long Date, US short date, European long day, European short date, Julian date, Unix, timestamps, et cetera, et cetera. Todas essas diferentes maneiras de expressar dietas podem confundir ou até mesmo perplexar completamente o cálculo de data embutido usado para fazer uma representação de uma data e armazenar no formato numérico sequencial. Se você usar o Excel como parte de sua vida profissional, você não terá dúvida experimentado isso. Para aqueles nos EUA que lidam com dados onde os dados armazenados em um formato europeu e vice-versa. Indiscutivelmente, o problema é realmente pior para aqueles de nós que não usam o formato de data dos EUA, pois o cálculo embutido parece favorecer formatos de data. Ele também deve, mas nem sempre leva em consideração o formato de data e hora local no seu computador. Aqui está um exemplo deste problema de cálculo de data. O Excel tratou esta data europeia como uma data dos EUA até o 13º dia do mês e, em seguida, é invertida para uma data europeia. Aqui está outro exemplo de algum comportamento inexplicável errático do cálculo de data no Excel. Quando precisamos ler ou gravar dados de ou para planilhas com VBA e realizar nossos próprios cálculos nessas datas. Este é, obviamente, um grande problema. Além disso, se enviarmos uma data formatada para uma célula, ela estará novamente sujeita ao cálculo de data defeituosa do Excel porque o Excel o vê como uma data e ele tem que executar seu cálculo defeituoso nessa data para convertê-lo em seu formato numérico. Então, o que podemos fazer? Simplificando, você precisa controlar o máximo possível. E, a fim de ser capaz de controlar o máximo possível, você precisa estar preparado para que você precisa entender o que você pode enfrentar. Em outras palavras, faça o máximo de análise possível. Se você precisar de entrada do usuário, não permita que eles falem em uma Data. Use um controle de seletor de data para garantir que a data seja precisa, sempre que possível, armazene seus dados como uma string na planilha. Isso impedirá que o Excel seja executado. São cálculos falhos malucos em datas. Ao escrever uma data em uma célula, basta colocar um único apóstrofo antes de sua string de data. Isso impede que o Excel execute um cálculo sobre ele. Se você precisar armazenar dados como datas em uma planilha, que seria necessário se os usuários precisassem filtrar por data ou usar uma data em uma fórmula, use um formato de data não ambíguo. Por exemplo, use caracteres alfa como mês, IA, AN como janeiro. O que isso faz é garantir que o cálculo do Excel da Flórida pode dizer imediatamente qual é o componente dy e qual é o componente mês. Por fim, faça seus próprios cálculos. Se você estiver pegando datas fora de uma folha, não confie nas funções internas básicas. Analise seus dados como o primeiro passo na criação de seu aplicativo. E isso é para que você possa ter uma idéia melhor de quais formatos de data aleatórios o Excel irá jogar em você. Eles têm para executar de forma confiável seus cálculos de data. Aqui está um exemplo da vida real de um cenário que me deparei recentemente e os passos que tomei para garantir que as datas estavam corretas, meu aplicativo teve que importar dados de um CSV e como o Excel abriu o arquivo, ele executou sua falha no campo de data, e este foi o resultado dos cálculos do Excel. Então, meu primeiro passo foi obter o maior tamanho amostral que eu poderia obter e ajudar as datas possíveis que eu poderia ser confrontado com. Dessa forma, eu posso escrever minha própria função que pode lidar com todas essas datas e convertê-lo em uma data real. Então vamos reconstruir essa função agora. Então vamos começar a construir a função. Vou parar adicionando um novo módulo de código. Módulo seis. E eu vou definir uma função. Então eu estou chamando ele é desligado da corda. E o que precisamos para fornecer a função é o dydt como uma string. E quero devolver um encontro. Então o propósito desta função é pegar a data que está em qualquer formato aleatório e retornar isso como um morto real. Então, realizando meu próprio cálculo de data nessa string. Então eu já fiz minha análise sobre o maior tamanho amostral de dados que eu poderia obter. E eu notei que neste exemplo eles eram verdadeiros formatos de data potenciais que eu poderia ver. Eu também sei que este arquivo é criado por um aplicativo ou um aplicativo web. Portanto, eu posso mais ou menos garantir que as dietas estão recebendo a entrada não vai mudar terrivelmente muito porque o arquivo é gerado automaticamente por um pedaço de software. E no tamanho amostral grande que recebi, percebi que havia verdadeiros resultados potenciais de como o Excel apresentará essas datas. Então o que eu normalmente faço é passá-los como comentários no topo para que eu possa trabalhar com eles. Então a primeira coisa que eu notei é que um formato de data tem um espaço e o outro formato de data tem bem, devs para especiarias aqui e outro espaço lá. A coisa que eu notei é que uma das dietas tem um carimbo de 12 horas com ele e pagá-lo no final. E o outro está em um carimbo de 24 horas. Então, agora que eu sei essas coisas, eu posso atender a esses cenários. Então a primeira coisa que quero fazer é lidar com esses espaços duplos. Então, a maneira que eu estou pensando em fazer isso é usar a função Substituir para substituir um espaço duplo por um único espaço. Então, primeiro de tudo, eu vou apenas declarar minha variável, apenas chamando de data de tentativa STR. E agora vou substituir espaços duplos por um único espaço. Mas a função Substituir aqui e a expressão que queremos enviar é a data STI. E o que queremos encontrar são especiarias verdadeiras. E o que queremos substituí-lo por um único tempero. Então eu vou ser capaz de enviar qualquer uma dessas dietas para esta função de substituição, e isso só vai afetar o S1. O próximo passo é eu preciso identificar a diferença entre esses dois. Então, obviamente, temos um que está em 24 horas e o outro não é um timestamp de doze horas e contém um IM OPM. Então o que eu posso fazer é procurar AM ou PM em uma string. E se isso existe, então eu sei que eu tenho esse formato. E se ele não existe, então eu sei que eu tenho esse formato. Então eu posso apenas usar uma declaração simples se para isso. Então eu estou apenas apresentando você para isso em STI ou na função string. Então o que isso faz é que ele vai olhar dentro de uma string para uma string que você define. E se o que você está procurando não existe dentro dessa string, então ele irá retornar a posição nessa string como um inteiro. E se ele não existir na string, ele retornará 0. Então eu quero começar no personagem um. Então você pode começar em qualquer ponto da string. E a corda 1 é o que você está enviando, que será esta. Corda dois é o que procuro. Estou procurando por mim e não preciso fornecer mais nada. Agora, se isso é maior que 0, então nós temos eu estou na cadeia. Então, o que acontece se você tem PM na corda? Bem, podemos usar uma declaração oral e usar a mesma coisa. Ou teremos outra condição também. Então, se entrarmos nesta parte da declaração if, isso significa que temos esse tipo de dieta. E se entrarmos nesta parte da declaração if, temos esta data superior. Então a primeira coisa que queremos fazer agora é dividir esta data por especiarias. E essa foi uma razão pela qual eu me livrei dessas especiarias duplas para começar. Eles pagam que eu vou precisar de uma nova variável, uma variável de matriz. E eu estou chamando de SPL spice, então ele está se dividindo pelo espaço. Vamos SPL tempero é igual a divisão. O que estamos dividindo com a colocação da string template? E vamos dividi-lo pelo espaço. O que isso significa agora é que agora eu tenho um array que deve ter três linhas. primeira linha é essa parte da data, a segunda linha é essa parte da data e a terceira linha é essa parte do dia. Vamos primeiro trabalhar nesta parte do site, que é a data física real. Então agora o que eu posso fazer é que eu posso dividir por caractere barra invertida, que então vai me levar com outra matriz. Essa é essa parte. Esta porção. E esta porção somas vai precisar de outra variável de matriz. E eu vou copiar isso e eu vou colar isso aqui. Agora, eu sei que a porção dydt vai existir na primeira linha, que é inferior 0 nessa matriz. Então eu estou usando split novamente. E o que vou dividir? Vou dividir o espaço SPL. E eu estou usando a linha 0, que é a primeira linha. E o delimitador é barra invertida. Então, agora, vou segurar três filas. A primeira fila é o dia, a segunda linha é o mês, e a terceira linha é o Gaea. Em seguida, vamos lidar com esta parte da data, a hora. E então eu sei que eu tenho dois dois pontos aqui e eu posso separar isso por dois pontos. E outro, este será o alfa e que serão os minutos, e que serão os segundos. Então eu vou fazer a mesma coisa aqui. Forneça alguns suportes. E eu sei que o componente Tom existe na linha dois, que é um. Então agora eu posso começar o acoplamento para dar a data do que nós separamos desta corda. E eu vou estar armazenando isso primeiro em uma variável temporária, que então eu vou atribuir de volta para a variável de função, que será retornado de volta da função. Então vamos definir isso agora. E esse topo variável é um dite. Agora vou apresentá-los a outra função que chamamos de cereal de data. E então o que isso faz é que ele irá converter uma data e nós podemos especificamente fornecer o mês Gaea um inteiro para que nós não temos que confiar no cálculo Yankee excel. Então eu sei que o ano está na matriz de barra invertida SPL, e eu sei que ele está na terceira linha dessa matriz. Então o ano está em SPL, reação real, e está em R3, que é o item dois porque começa em 0. E lembre-se, o mês também está nessa matriz, e isso está na segunda fila. Portanto, de baixo, o fim do dia está na primeira linha. Item 0. Então agora eu vou ter a data correta armazenado como uma data nesta variável. Então, e agora o tempo? Normalmente eu usaria uma função semelhante à data serial, que é chamado de cereal de tempo. No entanto, a função de série de tempo requer um tempo de 24 horas. E infelizmente neste exemplo, nós não temos um tempo de 24 horas, mas isso é realmente OK porque nós temos este componente do corante no tempo e ele tem um eu sou e pm aqui. Eu sei que posso enviar isso de forma confiável para uma variável de data e que ele converta corretamente. E isso porque não tem nossa boneca de dietas europeias aqui. É literalmente só tem tempo com um IM e PM. Então eu vou apenas criar uma variável que vai nos ajudar a lidar com isso agora. Então sucata que é igual. Então normalmente estava chamando isso para fora. Então normalmente eu estaria usando este componente aqui, mas neste caso, eu realmente não preciso fazer isso. Então, vou comentar isso por enquanto. Porque o que usarei dentro desta matriz está no formato de 12 horas. E não posso usar esse serial indiciado. Então, portanto, vou apenas concatenar algumas strings juntos para gerar isso. E eu vou atribuir isso a esta variável de data como espaço Beale. E eu quero a linha dois, que é um, porque eu estou me referindo à matriz que está dividindo a string por especiarias, então essa é a linha um, e este é R2. E então eu estou apenas concatenando cordas aqui. Então o que isto vai devolver é isto aqui. E vai ser atribuindo isso a uma variável de data. Então agora é uma simples questão de adicionar estes juntos porque temos uma fração aqui, que é a fração de um número inteiro que representa o dado e o tempo. E então temos um número inteiro aqui que representa a data real. Então eu posso simplesmente adicionar estes juntos. E teremos um encontro completo. Agora eu vou retornar isso de volta para uma variável de função dois iguais. E eu também funcionarei. Então isso lida com essa data. Agora vamos falar sobre esse encontro. Então eu sei que isso contém um espaço e eu posso dividi-lo por dia e hora. Então vou copiar este porque fizemos exatamente a mesma coisa. Da mesma forma, eu sei que eu tenho barras invertidas no meu componente data, então eu também posso usar isso. E eu sei que tenho um valor de 24 horas aqui. Então eu posso usar minha divisão de cólon agora. E agora posso começar a juntar as minhas dietas. Então, com essa data, semelhante a essa data, eu sei que é dia, mês , ano, então eu posso realmente copiar isso diretamente. E é aqui que começa a diferir do acima. E eles vão usar cereal de tempo, que é semelhante cereal dydt para gerar o valor de tempo. Ok, então a primeira coisa que ele está pedindo é alfa. Alpha está na primeira linha, spiel cólon, e é a primeira linha, significando 0. E o minuto está na segunda fila. E os dados que ele tinha não contêm segundos, então eu vou apenas fornecer para 0. Então agora temos nossa fração de tempo e também temos nossa dieta como um número completo. Então eu posso juntá-los. E posso devolver isso a uma função. E eu também quero sair da função aqui. Certo, então vamos testar isso. Vou colocar um ponto de interrupção no ponto de entrada de uma função. E eu vou copiar esta seqüência de data aqui, o primeiro, e eu vou apenas trazer l Janela Imediata. Agora, ponto de interrogação, pegue a data da string e eu vou enviar essa data. O que isso nos dá? Oh, ok, isso é bom. Isso nos deu uma função sabo não definida. E isso é porque eu escrevi errado isso. Eu não controlava o espaço. Barra invertida. Isso também significa que este não está correto porque eu copiei de cima. Isso funciona? Sim. Está bem. Legal. Está bem. Então vamos dar uma olhada nisso aqui. Se eu pular de volta para a janela imediata, você pode dizer ponto de interrogação data STI. E está retornando a corda, o que está correto. E agora ele substituirá o espaço duplo. Mas, neste caso, não temos que temperar. Então, como as cordas devem ser idênticas, se eu apenas saltar de volta para a Janela Imediata, Will, nós devemos ser capazes de dizer isso. Vamos apagar, apagar essa linha para que possamos compará-los lado a lado. Sim, idêntico. Certo? Agora o que estamos esperando é que isso pule esta declaração se então pulou para baixo para este. O que faz. Ok, isso é bom. Agora estamos nos dividindo pelo espaço. Então, se eu agora adicionar isso para assistir. Contextos atuais são bons. Ok, e na janela do relógio podemos ver que agora temos uma corda dividida por especiarias. Então temos a primeira linha que contém a data real, e temos a segunda linha que contém a hora. Isso é ótimo. Então, de volta para a janela de código. Agora está dividindo por barra invertida. Então assine de novo, o que vou fazer é adicionar um relógio. E eu só estou preocupado com esses contextos que é grande e nenhum dano meu relógio, podemos dizer que SPL barra invertida. Se abrirmos isso, dividimo-nos por barra invertida muda o corante na primeira linha, o mês na segunda fila, e o ano na linha três. Perfeito. Sim, backup para a janela ecoada. Estamos dividindo por cólon. E eu vou adicionar um G9, eu vou adicionar um relógio. E novamente, estão preocupados apenas com esses contextos. E podemos dizer que temos o tempo que eles respondem que o nosso, e nós temos as atas. Esta próxima linha estamos usando a função serial com fornecido no ano, que é 20-20. E nós fornecemos no mês que é julho. E nós também fornecemos no dique, que é o dia 21. Então, se eu passar pelo gramado e passar um pairar aqui, devemos ver é exatamente isso. 21 de julho de 2020. Da mesma forma para esta data e hora carimbo no momento é 12. Eu sou. Mas nós temos a função serial de tempo estava enviando nove como as horas e estava enviando 24 horas e minutos e estava sentado em 0 é os segundos. E como podemos ver, temos 924 eu sou. E sem segundos, o que é ótimo. Então agora eu posso apenas adicionar esses dois juntos, o que vai fazer aqui. Podemos ver se pairamos sobre, dizemos 21 de julho, 20-20 às 924 AM. E, em seguida, vamos atribuir que de volta à variável de função para que possamos retornar isso para a janela imediata, aperte a função de saída. E veremos se ele nos forneceu a data e hora corretas. Está bem. Então eu vou apagar tudo na janela imediata e vamos tentar a outra data. Então eu vou copiar este aqui e acertar o ponto de interrogação e digitá-lo. - Sim. E eu tenho o suporte que uma corda aqui e dentro do quartzo e eu vou apenas colar nisso. E vamos apertar Enter. Ok, então agora estamos novamente de volta ao funcionamento. A primeira coisa que vai fazer é substituir as duas especiarias. Como podemos dizer é que pairamos, passamos por especiarias entre o quente e o pote do tempo. Então agora não temos apenas um espaço porque substituímos dois personagens por um. Agora esperamos que o código para nesta parte da instrução porque na string temos AM ou PM. Isso é ótimo. Sim, vamos dividir por Spice e Alfa saltou para a janela da Vigia. Ainda deve conter aqueles relógios. Adicionamos a matriz SPL spice contém três linhas, o que é ótimo. Nós temos a data, nós temos a hora, e nós temos PM, e eles são todos armazenados como cordas. Em seguida, backup na janela Código, eu vou dividir o primeiro pote por barra invertida. Pode dizer que dividimos essa string por barra invertida. Então, saímos dia, mês e ano. Excelente. Agora eu vou atribuir isso a uma variável de data usando série. Enviamos o ano, enviamos para mês. Nós enviamos no dia. E, no entanto, primeiro de julho de 2020. E esta linha aqui é a linha mais arriscada de toda a nossa rotina. E isso é porque estamos contando com o cálculo do excels para converter essa string nesta variável de data. Mas porque estamos falando apenas de um momento em nível de confiança é extremamente alto porque não temos nenhum formato de data estranho para se preocupar. Então eu vou fazer isso agora se a altura e nós deveríamos ver isso. Sim, 7058 PM. Ótima. Na verdade, transformou isso em um tempo. Então agora vamos apenas adicionar esses dois juntos como fazemos. E eu estou retornando isso de volta para a variável de função e saindo da função. E devemos ver isso aparecer na janela imediata. Você vai. Então esse é apenas um exemplo rápido de como lidei com esse problema. Então o primeiro passo foi analisar meus dados. E eu notei que eu tinha um dos dois formatos de data. E então eu construí essa função que manipulava um desses formatos. Certo, pessoal, isso encerra essa lição. Então, nesta lição, falamos sobre como o Excel armazena datas e como o VB ay armazena datas. Falamos sobre os inúmeros problemas que você pode encontrar com dietas graças ao cálculo dydt do Excel. E eu expliquei como vou prevenir ou minimizar esses problemas. Eu também apresentei a você um par de novos recursos e funções UVB I. A primeira é a função Substituir, que é essencialmente exatamente a mesma que gosta e substituir no Microsoft Word. E eu também apresentei você em string ou um STR, que vai procurar por uma determinada string dentro de outra string. E se ele existir, ele irá retornar um número que representa qual número de caractere a string que você está procurando existe dentro da de uma string. E se ele não existir, ele retornará 0. E por último, nós também olhamos para data e hora cereal. E isso nos permite especificar o que é o dia e o que é o mês, vez de usar o método de cálculo padrão do Excel? Um cara, pessoal, meu nome é Andrew. Obrigado por assistir e te vejo no próximo vídeo. 8. 08 - VBA Aula 15 - Formulários de usuário e experiência de usuário: Bem-vindo à Lição 15, formulários de usuário e Introdução à Experiência do Usuário. Nesta lição, eu só quero apresentar a você a criação de formulários de usuário e fornecer-lhe algumas dicas de experiência de usuário que ajudarão a tornar o uso de seus formulários uma experiência mais atraente. Vamos começar de volta no IDE. Então a primeira coisa que queremos fazer é adicionar um formulário de usuário. Vou clicar com o botão direito do mouse aqui na janela do projeto. E eu vou selecionar inserir e eu vou selecionar formulário EUA, que você pode ver exibiu o uso da janela de design de formulário. E isso é basicamente o que você vê é o que você recebe editor. Então é assim que você desenha suas formas nesta visão aqui. E você vai notar que ele também é trazido para cima uma pequena caixa de ferramentas aqui, que contém todos os controles ou pelo menos alguns dos controles que queremos estar usando. Agora, depois de adicionar o meu formulário, a primeira vez que vamos fazer é eu vou dar-lhe um nome. E para fazer isso, eu só vou trazer a janela de propriedades aqui, e eu vou chamar este endereço de nome de sublinhado f RAM. Então esse é o nome do objeto. Isso é o que vamos estar nos referindo a este formulário como em código, se RM sublinhar nome, endereço. E eu também vou dar uma legenda, que é o que é exibido no canto superior esquerdo da janela. E assim a propriedade da legenda aqui, vai mudar isso. E como podem ver, mudou o texto que está na legenda deste uniforme. Então, o que estamos tentando fazer com este formulário? O que queremos fazer é capturar a entrada do usuário e, em seguida, colocar isso em uma planilha. E em uma lição anterior, eu vou apenas trazer o Excel. Tínhamos a ficha de dados de custo. Eu só vou trazer o fundo disso para que possamos, para que possamos dizer que a folha de dados de custo e que tem o nome, sobrenome, cidade e país. E apenas desconsidere a frase porque essa é a fórmula que construímos que apenas recebe sentença. Mas o que eu quero fazer é criar um formulário que capture nome, sobrenome, cidade e país, e então preencha isso em uma nova linha nessa folha. Então, pensando sobre o que precisaremos, vamos precisar de caixas de texto. E eles vão conter o nome, o sobrenome, a cidade e o país. Nós também vamos precisar de quatro rótulos para rotular cada livro didático. E isso para que o usuário saiba o que inserir uma idade das caixas de texto. Por último, vamos precisar de um botão de salvamento. E o botão citar vai então executar algum código que vai preencher o que eles inseriram na planilha. Então eu vou minimizar isso e nós vamos voltar aqui. Então, onde está a minha caixa de ferramentas aqui? Sim, primeiro as coisas, vou adicionar algumas caixas de texto. Então, como eu vou fazer isso é a partir da caixa de ferramentas, basta clicar nela e depois arrastá-la para o próprio formulário. E eu só vou arrastar para a cruz. Eles não estão alinhados nem nada no momento, tudo bem. Só por agora, também ajuste o tamanho usando um track pad neste vídeo para que a agilidade de massa seja muito baixa. E também queremos adicionar alguns rótulos. Então, vamos trazer estes para o outro lado. E eu vou adicionar alguns rótulos. E eu vou apenas fazer um pequeno trackpad é irritante. Eu realmente apenas apenas o tamanho e, em seguida, copiar e colar dias. Então copiar e colar vê exatamente o mesmo, apenas Control-C, Control-V. o que quero fazer depois de acrescentarmos dias é dar-lhes uma convenção de nomes. Então eu sou tudo sobre nomear convenções. E quando chegarmos ao código, mostrarei porque é muito útil usar uma convenção de nomes. Então vamos começar com caixas de texto. Vou chamá-los de T, X T. E o primeiro foi o primeiro nome. Sou o nome da escola. Talvez até nos dê um rótulo também. Ibl, FirstName, e vou dar uma legenda a isso. Sim. E isso é assim e eu dou uma legenda para o que mais nós temos? Tínhamos primeiro nome, sobrenome, cidade e país. Ótima. Então volte para aqui, cidade TXT e haverá LCD e dê uma legenda também. E país TXT, dar um rótulo. Mais tarde no vídeo, explicarei mais sobre a experiência do usuário, mas quero que pense nisso, certo? Então, quando você está projetando suas formas, pense sobre a viagem do mouse e a viagem dos olhos de uso. Então, no mundo real, você definitivamente deve ver um rótulo à esquerda de uma caixa de texto. Mas quando você pensa sobre como você usá-la, digere essas informações, eles precisam primeiro olhar para o rótulo e depois passar para a própria caixa de texto. Então, tendo isso em mente, é realmente muito melhor. Eu só vou mover estes para baixo para colocar o rótulo diretamente acima da caixa de texto em si. E isso torna mais fácil de usar. É digerir porque eles podem simplesmente olhar para baixo a forma em vez de olhar para a esquerda e depois para a direita e depois para a esquerda e depois para a direita e depois para a esquerda e depois para a direita. Então eu vou fazer isso por todos esses. Traga isso um pouco mais. Eu também posso expandir o tamanho de cada uma dessas caixas de texto lá um pouco pequeno o momento, então eu só poderia fazer um pouco maior. E agora eu posso fazer uma forma menor. E agora vamos adicionar um botão de comando e que é para acionar o código que executa que toma o que está no formulário e preenche na folha. Vamos colocar isso aí em baixo. Novamente. Vou chamar isso de C e B e vou salvar. E eu vou dar uma legenda também. Seguro. Eu poderia apenas fazer que também a largura de caixas de texto l. Trazendo isso de volta. Certo, então agora temos nossa reclamação básica de design de formulário, e também temos convenções de nomenclatura para nossos controles. E isso é muito útil, como vou mostrar agora. Então eu vou apenas trazer esta janela de propriedades para baixo um pouco. Então agora eu estou indo para ir para o módulo de código deste objeto, que é o objeto formulário. Então ele tem seu próprio módulo de código privado dentro dele. Então, se eu apenas ir ver código, e esse é o módulo de código dentro deste formulário. Então a próxima coisa é como atribuir código para o evento sendo o clique do botão cite. Então, há algumas maneiras de fazer isso. A primeira maneira eu vou te mostrar que o jeito ACS. Então volte para o design do formulário. Basta clicar duas vezes sobre isso. E se eu selecionar isso e clicar duas vezes sobre o objeto, ele irá gerar automaticamente um sub, um Private Sub que captura o evento do clique para que possamos executar código com o clique disso. Então, como você pode ver, ele tem o nome do botão de comando e, em seguida, ele também tem o evento depois disso. Então, a outra maneira é usar essas caixas suspensas na parte superior. Como podem ver, temos os controles que estão nesse formulário, que está dentro do objeto uniforme. E se eu apertar CMB salvar no lado esquerdo, ele cria automaticamente clique. Mas também há do lado direito, um monte de outros eventos que podemos pegar também. Então, se eu clicar duas vezes, se eles arrastarem, et cetera, et cetera, vamos deixar com Click. Então, a primeira coisa que queremos fazer é validar o que o usuário inseriu. Não quero inserir linhas em branco em nossa planilha. Isso é realmente muito importante quando você está criando código com base nas informações inseridas pelo usuário que você valida o que o usuário inseriu. Então eu vou fazer isso é eu vou primeiro de tudo olhar para as caixas de texto e eu vou medir o comprimento do valor, que é o que o usuário inseriu, e garantir que eles realmente digitou texto em todos os campos. Então, neste caso, todos os campos neste anterior obrigatório. Então deixe-me mostrar como vou fazer isso. Então eu vou primeiro criar um objeto em branco. E este tipo é apenas um objeto que é um tipo de objeto genérico. Então você pode atribuí-lo praticamente qualquer objeto. E eu vou usar este objeto dentro de um loop for. E já usamos esses tipos de folatos antes. Mas para explicação, o que estamos fazendo é que estamos percorrendo todos os controles no formulário e cada iteração do loop, ele vai atribuir o controle atual para este objeto para que possamos interrogar esse objeto e, em seguida, executar alguma lógica nele. Então vamos parar com isso para loop. Assim, o método de controles do objeto de formulário é uma coleção de controles. Então, apenas especificando controles será uma coleção de controles. Mas você também pode especificar um controle individual, fornecendo-o em índice ou nome. Mas neste caso, eu só quero a coleção de controles. E nós vamos percorrer cada objeto nessa coleção de controles. Agora, dentro desta coleção de controles, ele conterá todos os controles neste formulário. Então ele irá incluir os rótulos, ele irá incluir as caixas de texto, ele irá incluir o botão e qualquer outro controle que você adicionou ao formulário. E isso é quando as convenções de nomenclatura são muito úteis. Então, porque temos nomeado tudo sobre caixas de texto, TXT na escola, e, em seguida, o nome do controle. Sabemos que se o nome do controle começar com TXT, é uma caixa de texto. Então eu vou adicionar o próximo. Então esse é o nosso ciclo. Então agora eu vou interrogar os três primeiros caracteres de cada um dos controles. Estou usando a esquerda como os três primeiros personagens. Então agora eu posso interrogar o comprimento do valor IA, o que o usuário entrou nesse campo. Então, se eles não inseriram nada, então se o tamanho do que eles inseriram no campo é 0. E também quero sair do Sabah. Não quero continuar se chegarmos a este ponto da validação. E o é uma caixa de texto que não contém um pedaço de informação porque todos os campos são obrigatórios. Então, vou fazer um comentário rápido. Elas são verdadeiras. Então eu valido todos os campos. Então agora podemos começar o processo de resolução. Então, pensar sobre o que vai fazer definitivamente vai precisar da pasta de trabalho e do objeto de planilha. Então, vou declará-los. E eu vou colocá-los. E eu só vou sentá-los depois da validação porque eu não quero apenas desperdiçar recursos. E a folha que procuramos são dados de custo. Então agora eu quero inserir as informações na worksheet. Então eu vou contar com o método de contagem de linhas de intervalo usado da própria worksheet. Mas eu vou atribuir isso a uma variável ou um número. Então eu estou usando um top longo aqui. Então, é um número inteiro sem um decimal, mas poderia potencialmente ser maior do que 32,5 mil. E isso porque neste caso, marchamos ao longo do tempo, temos um usuário em mais de 32,5 mil linhas. E não quero limitá-lo a apenas 32,5 mil. Então, neste caso, eu vou usar um longo e eu vou atribuir que usando o método de contagem de linhas de intervalo usado. E assim este método, eles usaram o método de intervalo, ponto ROS contagem de pontos vai nos retornar a linha atual que contém a última peça de informação. Então, neste caso, ele vai nos devolver um seis porque seis é a última contagem de linhas do intervalo usado. Mas porque queremos adicionar os valores para o próximo hard down, eu vou ser apenas adicionando um a que quando uma informação de preenchimento na worksheet. Então vamos fazer isso agora. Já fizemos isso várias vezes antes. Então x mais um. Então esse é o número da linha e a coluna é um. E eu estou indo apenas para copiar e colar esses tópicos seguros e me encontrei um monte de vezes. Mas eu só vou ajustar os números das colunas. E eu também vou ajustar os controles reais eles mesmos. Apelido. CT. Então, depois de ter executado essas linhas, temos salvo esse registro para a worksheet. E agora eu também quero redefinir o formulário. Então eu estou realmente indo apenas para usar este método, que é um loop completo. E eu só vou passar por cada um dos controles que começam com TXT e vamos definir os valores para nada. Então eu vou me livrar disso. Então vamos testar isso agora. Então eu apenas configurei uma janela que podemos ver a planilha por baixo para que possamos dizê-la adicionando à medida que avançamos. E eu trarei isso aqui. E eu vou voltar para o formulário. Feche por enquanto. Jogar. Ok, então primeiro de tudo, vamos testar as validações. Se eu apertar Salvar, eu deveria ver uma caixa de mensagem. Ele diz por favor insira caixas de texto antigas ou valor em todas as caixas de texto. E ele tem, vamos, Eu soletrei valorizando corretamente. E vamos entrar um por um. O John. Sim. E agora devemos ver esta nova linha preenchida. Boom, lá vamos nós. Isso é preenchido uma nova linha nessa planilha. E também limpou todos os valores. E isso é para que se acertarmos salvar várias vezes seguidas, ele não entra em várias linhas do mesmo registro. Certo, feche isso. Agora, trazendo isso de volta para o código. Vamos explicar mais uma vez. Então, em uma etapa de validação, o que estamos fazendo é primeiro pegar a lista completa de controles no formulário. E em cada iteração deste para loop, nós atribuindo o controle atual para este objeto em branco. E então estamos interrogando esse objeto para ver se esquerda do nome do objeto, ou os três primeiros caracteres do nome do objeto é TXT. E então se for, estamos testando o valor, mas estamos testando o comprimento do valor. E se esse valor for 0, então se o tamanho do que é inserido nessa caixa de texto for 0, então vamos apresentar ao usuário uma caixa de mensagem e isso vai dizer, por favor insira uma val lie. Isso altera esse valor em todas as caixas de texto. E assim, em seguida, uma vez que tenhamos concluído as etapas de validação com sentado a pasta de trabalho e o objeto de planilha. Maneira agarrando a contagem de linhas de intervalo usado, que será, que foi seis nota sete. E então estamos preenchendo isso na própria folha. E então estamos usando o mesmo método de loop foreach aqui para limpar os valores dessa caixa de texto para atualizar o formulário e permitir que o usuário insira outra coisa. Agora que temos o básico sob controle, vamos começar a falar sobre algumas dicas de experiência do usuário. Dica número um, manter as coisas simples e Cape as coisas inteligentes. Elimine todos os campos desnecessários no formulário. Use lógica condicional. Então, se você precisar de um campo, mas você só precisa desse campo em determinadas condições, defina seu estado padrão como oculto e mostre-os programaticamente. É muito melhor para nós colocar no esforço escrevendo código para lidar com a complexidade, a fim de manter as coisas simples para o usuário. Dica número dois, semelhante à ponta um, use a lógica condicional o máximo que puder para manter as coisas inteligentes. Use os muitos eventos que estão à sua disposição. Pegar o evento de um usuário inserir algo e, em seguida, fazer algo com base no que ele entrou. Um bom exemplo disso que você pode ter visto que no mundo real está em alguns formulários de captura de endereço, as caixas de combinação de estado preenchidas automaticamente com base no país selecionado. Então, por exemplo, se eu declarar que estou na Austrália no formulário não vai me mostrar estados dos EUA na caixa de combinação de estado. Dica número três. É melhor fazer várias etapas de captura de dados em vários formulários e, em seguida, criar um formulário enorme que sobrecarrega o usuário com a quantidade de dados que ele precisa inserir. Embora o VBA se forme construído para esse conceito moderno, é bastante fácil implementar isso de forma perfeita. Por exemplo, você pode criar várias telas em vários formulários e alternar entre as telas de forma programática, dando a ilusão de um único formulário. Para fazer isso de forma eficaz, você precisará construir uma rotina de transição que leve coisas como a posição da tela em consideração. O que eu quero dizer com isso é pegar as coordenadas x e y que representam onde o formulário é exibido na tela. E então atribua isso para formar dois para que o próximo formulário apareça exatamente no mesmo lugar que o anterior. Ele apenas fornece a ilusão de que múltiplas formas são realmente uma forma que está mudando dinamicamente. Dica número quatro, como indicado no exemplo prático, coloca um campo rótulos acima do campo não alinhados à esquerda ou à direita. O que isso faz é simplificar a compreensão de usos do formulário. Ele também fornece menos pontos de fixação, i, j, os lugares que o usuário precisa olhar no formulário. Dica número cinco, agrupe campos relacionados em grupos lógicos. Se você estiver usando mais de seis campos, agrupe os campos de entrada em seções lógicas. Além disso, pesquisas sugerem que grupos lógicos devem ser orientados em colunas. Dica número seis, tamanho dos campos apropriadamente. Não há nada pior do que inserir texto em um campo que é muito pequeno para mostrar o que eles estão digitando em uma linha. Por outro lado, parece muito estranho inserir uma pequena quantidade de texto em um enorme livro didático. Dica número sete: verifique se o índice de tabulação está correto para todos os controles no formulário. Isso permite que os usuários naveguem facilmente pelo formulário com a tecla Tab. Você pode exibir e definir o índice de tabulação na janela de propriedades de cada controle. Dica número oito. Embora o Excel e o VBA não tornem isso particularmente fácil, o uso de imagens ou ícones pode torná-lo muito mais atraente, de marca e profissional. Mas você realmente precisa ter cuidado aqui. Devido à plataforma de formulários VBA, o uso de imagens como um substituto para outros controles pode ter resultados indesejáveis. E isso porque os controles de imagem são literalmente apenas objetos estáticos e eles não se comportam da mesma maneira que outros controles. Em uma lição avançada letra, vou mostrar-lhe como usar corretamente as imagens como controles. Por exemplo, como obter um efeito sobre o mouse e como fazer com que uma imagem seja pressionada como um controle de botão e outras animações de controle. Então, isso resume esta lição e a primeira série intermediária de iniciante VBA para Ninja. Quero agradecer-te por teres assistido às minhas aulas. Se há algo específico que você gostaria de ver, por favor deixe um comentário. Além disso, se houver algo que você está tendo problemas com Eva com o curso ou seu próprio projeto VBA pessoal, sinta-se à vontade para entrar em contato comigo. Fico sempre feliz em ajudar. A próxima série de vídeos também será conteúdo intermediário. No entanto, vamos mergulhar em mais problemas baseados em cenários que você, sem dúvida, encontrará no mundo real. Meu nome é Andrew. Espero que você tenha gostado deste curso e eu vou vê-lo na próxima série de VBA, iniciante para ninja.