Visual Basic em Português

Os ciclos nas folhas de cálculo são bastante comuns quando se usa VBA. O objectivo é percorrer uma lista de valores e executar determinada acção ou validação enquanto este percorre todas as linhas ou colunas.

A execução de um ciclo simples pode ser feito da seguinte forma:

Isto irá escrever em todas as células da coluna A, da linha 1 à linha 100, um número sequencial. Mas este é um exemplo simples onde definimos onde começa e onde termina.

Para se fazer um ciclo numa lista de dados já existente, devemos sempre saber onde começar e onde terminar. Não tem lógica percorrer todas as linhas de uma folha de cálculo se estão apenas a ser utilizadas 20 ou 30.

Para se determinar a última linha usada em uma lista, devemos utilizar o seguinte método:

Isto é semelhante a seleccionar a ultima linha da coluna A e pressionar as teclas CTRL + UP ARROW, que fará saltar a selecção para a primeira célula com dados.

Só assim podemos saber com exactidão, qual a última linha utilizada, pois mesmo que existam linhas em branco na lista, todas as linhas serão percorridas.

No entanto, algumas considerações:

1. Caso existam várias colunas na lista, devemos definir a coluna que tem mais dados;
2. A variável utilizada para a última linha deverá ser do tipo Long e não Integer, pois uma variável do tipo Integer suporta apenas números até  32,768, sendo este inferior ao total de linhas disponível na folha de calculo.

Depois, é só utilizar a variável para limitar o ciclo:

Isto irá listar o valor de todas as células na coluna A. Para verificar qual a última coluna utilizada, o método é semelhante:

Mas existem outros métodos de verificar a última linha ou coluna utilizada, usando, por exemplo, o método SpecialCells:

No entanto, existem vários registos de que este método não é fiável, e basta apagar algumas linhas para verificar que não o é, e por isso não é recomendado.

Existe ainda uma especial atenção para ciclos onde são eliminadas linhas ou colunas, onde o ciclo deverá ser efectuado do fim para o principio, ou seja, da última linha/colunas para a primeira. Deste modo, um ciclo para eliminar linhas, por exemplo, deverá ser feito da seguinte forma:
 

Estes são alguns exemplos simples de como fazer ciclos em células e como verificar qual a última linha ou coluna utilizada.

Já está disponível a MSDN Magazine de Outubro de 2009. Nesta edição podem-se ler alguns artigos como: Visual Studio 2010 Tools for SharePoint Development Generating Documents from SharePoint Using Open XML Content Controls Profiling the .NET Garbage-Collected Heap Data Binding in ASP.NET AJAX 4.0 Profiling the .NET Garbage-Collected Heap Entre muitos outros!

Para os leitores de VB.NET, um artigo sobre Collection and Array Initializers In Visual Basic 2010, de Adrian Spotty Bowles , onde são abordadas as inicializações de arrays e colecções, em Visual Basic 2010, com diversas demonstrações, explicando ainda os novos sintaxes.

É muito importante quando se desenvolve alguma macro, que o tempo de execução não seja exagerado, e até porque, as folhas de calculo têm normalmente diversos cálculos, objectos, formatações, etc, o que pode tornar as acções mais lentas.

Existem no entanto pequenos “truques” que permitem melhorar reduzir o tempo de execução das macros e melhorar o aspecto visual quando estas correm.

Application.ScreenUpdating

Esta propriedade define se o Excel actualiza ou não a folha de cálculo. Por defeito está definida como verdadeira (True), mas podemos defini-la como falsa (False).

Por exemplo, se queremos apagar algumas linhas ou colunas individualmente, sempre que damos uma ordem para eliminar, a folha é actualizada, aumentando o tempo de execução. Se definirmos Application.ScreenUpdating = False, só quando voltarmos a definir Application.ScreenUpdating = True, é que a folha é actualizada, reduzindo significativamente o tempo de execução e o aspecto geral da macro.

No seguinte exemplo, só após o código terminar, a folha será actualizada:

Application.Calculation

A propriedade Calculation permite definir de que forma são feitos os cálculos. Sempre que escrevemos um valor numa célula, o Excel actualiza todos os cálculos, em todas as células, o que poderá levar algum tempo. Por defeito esta propriedade está definida como automática (xlCalculationAutomatic) mas podemos no entanto definir para não ser executada, ou seja, para manual (xlCalculationManual).

Por exemplo, e para testarmos esta opção, se tivermos na colunas A, B, C, D, E e F todas as células com uma formula para gerar um número aleatório - =Rand() -, sempre que escrevermos um valor numa célula, irão ser actualizadas todas as formulas. No seguinte exemplo será desabilitado enquanto o ciclo estiver a ser executado, e poderá testar com e sem o desabilitar dos cálculos automáticos, para ver a diferença.

    Sub WriteValues()

        Dim x As Integer

        Application.Calculation = xlCalculationManual

        For x = 1 To 100
            Cells(x, "g").Value = x
        Next

        Application.Calculation = xlCalculationAutomatic


    End Sub

Application.EnableEvents

Com explicado num artigo anterior, existem diversos eventos que estão disponíveis no Excel. Esses eventos podem executar operações demoradas e isso pode não ser desejado. Por defeito esta propriedade está definida como verdadeira (True) mas podemos desactivar quando queremos que os eventos não aconteçam.

Imagine que no evento Change, que é executado sempre que algum valor seja alterado, temos um ciclo demorado.

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim x As Integer

        For x = 0 To 1000
            Debug.Print(x)
        Next
    End Sub

Com a opção Application.EnableEvents = True, sempre que escrever um valor em qualquer célula, irá correr também o código anterior.

Definindo esta propriedade como falsa, Application.EnableEvents = False, fará com que apenas se execute o nosso código e não os restantes eventos do documento.

    Sub Run()

        Application.EnableEvents = False

        For x = 1 To 100
            Cells(x, "g").Value = x
        Next

        Application.EnableEvents = True

    End Sub

Isoladamente ou em conjunto, estas três propriedades permitem melhorar significativamente o tempo de execução de uma macro e com isso tornar a nossa folha de cálculo mais atractiva.

NOTA: é preciso ter algum cuidado com a utilização destas propriedades, e garantir que são sempre repostas para as definições originais, correndo-se o risco de a folha não trabalhar como está planeado. Se colocarmos, por exemplo, a propriedade Application.EnableEvents = False e não voltarmos a colocar a True, todos os eventos ficaram congelados.

Para breve mais dicas de como melhorar o código em VBA!

Os próximos artigos vão mostrar como se iniciar em VBA – Microsoft Excel – e algumas melhorias, dicas e sugestões que podem ser feitas para melhorar o código.

A execução de macros em Excel, está normalmente associada a um botão ou um objecto, no entanto existem diversas formas de executar macros, que podem simplificar bastante a automação na folha de calculo. Este artigo pretende mostrar alguns exemplos simples de alguns dos eventos mais importantes e mais utilizados em VBA.

NOTA: Neste artigo será usado o Microsoft Excel 2010 mas o mesmo se aplica a versões anteriores com o Microsoft Excel 2003 ou o Microsoft Excel 2007.

Quando entramos no editor de VBA, o VBE (Visual Basic Editor), existem alguns objectos no explorador do projecto: um objecto por cada folha disponível (Sheet), e um objecto global (ThisWorkBook).

Objecto Sheet

No objecto Sheet, temos os eventos relativos apenas para essa folha, como por exemplo:

• Change: Quando é efectuada alguma alteração em alguma célula;
• SelectionChange: Quando é alterada a selecção;
• BeforeRightClick: Antes de clicar com o botão direito;
• Calculate: Quando é efectuada uma operação que obrigue a cálculos;

Existem mais disponíveis mas estes são talvez os mais utilizados. Por exemplo, o BeforeRightClick permite-nos criar um menu personalizado quando clicamos com o botão direito do rato, o Calculate permite-nos correr um código quando existem cálculos, etc, etc.

Por exemplo, se queremos executar qualquer código quando alteramos um valor numa determinada área, podemos utilizar o evento Change. Deste modo verificamos se a nossa área – range - coincide com a célula alterada, utilizando a função Intersect(), e executamos o nosso código.

    Private Sub Worksheet_Change(ByVal Target As Range)

        ' Verifica se a célula modificada está entre o range B2:B10
        If Not Intersect(Range("B2:B10"), Target) Is Nothing Then
            MsgBox("Executar código!")
        End If

    End Sub

Objecto ThisWorkBook

No objecto ThisWorkBook estão os eventos comuns a todas as folhas e os eventos gerais, como por exemplo:

• Workbook_Open: Quando o documento é aberto
• Workbook_BeforeClose: Quando o documento é fechado (antes de fechar)
• Workbook_BeforeSave: Quando o documento vai ser gravado
• Workbook_NewSheet: Quando é adicionada uma nova folha
• Workbook_SheetChange: Quando é alterado qualquer célula em qualquer folha
• Workbook_SheetActivate: Quando uma folha é activada

Estes são apenas alguns dos eventos que estão disponíveis e que podem ser utilizados.

Por exemplo, se queremos saber qual a folha – Worksheet – que foi seleccionada, podemos fazer:

    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
        Dim sheet As Worksheet
        sheet = Sh

        MsgBox(sheet.Name)
    End Sub

Ou ainda, evitar que o documento seja fechado:

    Private Sub Workbook_BeforeClose(ByVal Cancel As Boolean)
        Dim msg As String
        msg = "Deseja fechar o documento ?"

        ' Cancela o encerramento do documento
        If MsgBox(msg, vbYesNo) <> vbYes Then
            Cancel = True
        End If

    End Sub

Como podem ver, existem vários eventos que podemos utilizar para melhorar os nossas folhas de cálculo, através do VBA, e com bastantes aplicações.

Para breve mais dicas de como melhorar o código em VBA!