VBA Advanced Excel - Evitando que aplicações Excel fiquem carregados no memória.

| Blog Office VBA | Blog Excel | Blog Access |

Caso não nos resguardemos bem ao escrevermos as nossas aplicações com o MS Excel, podemos incorrer no erro, muito comum, de permitir que processos fiquem soltos e perdidos na memória RAM do nosso sistema operacional.

Ao construirmos aplicações que acessam e manipulam objetos e recursos de outro aplicativos, dependendo do nosso nível de conhecimento, podemos tornar tais automações tecnicamente complexas, pois às vezes envolverão diversas interfaces entre a aplicação cliente, e a aplicação servidor, além do próprio sistema operacional.

Já estamos bem acostumados a usar o MS Office como servidor de automações. E ele é excelente nisso, pois além de oferecer recursos avançados como editor de textos, planilha eletrônica, modelos gráficos, apresentações, etc... que complementam vários tipos de soluções, ainda consegue encapsular complexos aplicativos de automação, tornando os processos mais simples para nós, desenvolvedores. E um alívio e tanto para os usuários que não precisam dedicar centenas de horas no ano para processos repetitivos, que imploram por erros.

Uma vez que praticamente todos os  recursos estejam disponíveis programaticamente não há simplesmente como comparar MS Office a outros pacotes concorrentes. Encontramos sim, um enorme abismo que o diferencia e distancia das demais suites. É nítido o que a programação lhe agrega, acrescentando-lhe ainda mais facilidade e valor. 

Também não é por acaso, que de todos os softwares do pacote MS Office, o MS Excel seja o mais usado como servidor de automação, especialmente por ser um produto versátil (repositório de dados, motor de cálculos, gerador de gráficos, Dashboards e Scorecards, etc...), usado de ponta a ponta no mercado, em praticamente todos os modelos de negócio.

Cuidado...

A automação é fácil de ser implementada, mas, precisa receber especial atenção ao fato de que ao abrirmos um aplicativo, o mesmo deve ser fechado a posteriori. Neste artigo, que é aplicável em qualquer outro módulo da suite, usaremos o MS Excel:

Manual

A automação funcionará de modo similar a quando um usuário aciona uma aplicação MS Excel, abre uma pasta de trabalho, escolhe uma planilha, e começa a ler e preencher as  células. Com estes passos simples e muito comuns um processo do MS Excel é iniciado no Windows e, quando um usuário fecha-o, o processo é finalizado.

Automatizado

Num cenário automatizado porém, é muito comum o aplicativo servidor ser executado nos bastidores, de modo invisível ao usuário que, na maioria das vezes, sequer imagina que o MS Excel (ou seja lá qual for o aplicativo) está sendo executado. Como o usuário não tem a janela do MS Excel ativa para poder fechar, quem deve fazer isto é a própria aplicação que o abriu, caso contrário o processo ficará pendurado no Windows. Mas há um cenário ainda mais perturbador para o Cliente e conseqüentemente para o desenvolvedor: Deixar a pasta de trabalho do usuário bloqueada de forma que não consiga acessá-la nem abrindo o MS Excel manualmente, nem por outro processo de automação.

Este é um erro muito comum nos fóruns mundo a fora: "Minha planilha [neste caso] está travada, não consigo editar nada!". O usuário diz que fechou todos os programas (e de fato fechou), abriu seu arquivo do MS Excel e recebeu esta mensagem de erro.

Mas, quando solicitamos que ele abra o seu WTM (Windows Task Manager) indo para a guia processos, e classificando-a por nome, estes invariavelmente encontram listadas repetidas vezes o arquivo "EXCEL.EXE". Os processos são finalizados e, momentâneamente, o problema está resolvido. 

Mais tarde, porém, o problema volta a se repetir. Por quê? Algum aplicativo que usa o MS Excel como servidor de automação falha em fechá-lo 'completamente'. Geralmente ocorre algum erro em tempo de execução no código depois que a aplicação foi instanciada, deixando-a pendurada na memória.

Como evitar...

Antes que o cenário constrangedor descrito acima ocorra trazendo vitupério para todos os desenvolvedores da plataforma VBA, pois dirão: "O VBA não é estável!". 

Recomendo uma das seguintes simples práticas, por prioridade:

a) DEIXE A APLICAÇÃO VISÍVEL: Se o objetivo da automação for preparar um arquivo exibindo o resultado final ao usuário, nada mais óbvio que torná-lo visível, certo? Mas, algumas vezes o desenvolvedor não faz isto: ele abre o MS Excel, executa as rotinas de preenchimento, salva, fecha e, por fim, executa o comando Shell para abrir o arquivo em uma nova instância do MS Excel.

O recomendado é deixar a instância visível, fazendo isso logo no início do código. Deste modo, caso ocorra algum erro durante a execução do código, o processo não ficará perdido no Windows, pois o usuário terá domínio da janela.

Sub MySub1()

Dim xlApp As Excel.Application

Dim xlWkb As Excel.Workbook

On Error GoTo ErrHandler

"Instancia o Excel

Set xlApp = New Excel.Application

"Deixa o Excel visível para o usuário

xlApp.Visible = True

"Seu código aqui...

ExitHere:

Exit Sub

ErrHandler:

MsgBox Err.Description & vbCrLf & Err.Number & vbCrLf & Err.Source, vbCritical, "MySub"

Resume ExitHere

End Sub

b) CONTROLE A APLICAÇÃO E FAÇA A FINALIZAÇÃO DO TRATAMENTO DO ERRO: Também é comum alguns colocarem um comando para finalizar o MS Excel (tal como Quit) no tratamento de erro. Porém, se o erro ocorrer por outro motivo que não a automação e o MS Excel não estiver instanciado no momento, este tratamento gerará outro erro: Object variable or With block variable not set. Para que esse erro não ocorra, o mais correto é, antes de fazer o Quit, verificar se o MS Excel foi instanciado:

Sub MySub2()

Dim xlApp As Excel.Application

Dim xlWkb As Excel.Workbook

Dim blnIsOpen As Boolean

On Error GoTo ErrHandler

"Instancia o Excel

Set xlApp = New Excel.Application

blnIsOpen = True

"Seu código aqui...

"Finaliza a aplicação

xlApp.Quit

blnIsOpen = False

ExitHere:

Exit Sub

ErrHandler:

"Verifica se o Excel está instanciado

If blnIsOpen = True Then

    xlApp.Quit

End If

MsgBox Err.Description & vbCrLf & Err.Number & vbCrLf & Err.Source, vbCritical, "MySub"

Resume ExitHere

End Sub

Checando...

Com o código abaixo poderá testar se uma instância do MS Excel está aberta e caso esteja, poderá evocá-la:

Dim ExcelApplication As Object

   Dim TimeoutTime As Long

   On Error Resume Next

   Set ExcelApplication = GetObject(, "Excel.Application")

   On Error GoTo 0

   If ExcelApplication Is Nothing Then

       Shell "Excel.exe"

       Let TimeoutTime = Timer + 5

       On Error Resume Next

       Do

           DoEvents

           Err.Reset

           Set ExcelApplication = GetObject(, "Excel.Application")

       Loop Until Not ExcelApplication Is Nothing Or Timer > TimeoutTime

       On Error GoTo 0

   End If

   If ExcelApplication Is Nothing Then

       MsgBox "Unable to launch Excel."

   Else

       ' Do something with the Excel instance...

   End If

Inspired in: 

Luiz Cláudio C Rocha

CreateObject Function

Tags: VBA, Office, automation, Excel, Clean, memory,automação, Advanced

Excel Tips - Deixe sua planilhas calcularem mais rapidamente - Make MS Excel calculate faster

| Blog Office VBA | Blog Excel | Blog Access |

Acredito que algumas características poderiam estar inerentemente contidas no MS Excel. Uma delas, das que considero mais relevante, passa por nossa constante preocupação em relação a performance.

Nossos Dashboards, Scorecards, e Reports merecem uma performance adequada à expectativa dos nossos usuários. 

Sempre nos esmeramos em manter interfaces leves, limpas. Mas se estas forem lentas tiram todo o crédito dos nossos esforços.

Quando nossas planilhas ficam muito lentas, especialmente aquelas que existem há muito tempo, pensamos logo na possibilidade de existirem muitas linhas vazias, ou más conexões as bases de dados.

Seguem alguns passos simples que o ajudarão a manter as suas planilhas ágeis e prontas:

1) Abra a sua planilha e escolha a aba que contenha o maior número de fórmulas ou dados nela.

2) Enquanto mantém pressionada a tecla [CTRL], pressione também a tecla [END]

3) Cheque a célula para onde o cursor foi movido, certifique-se de que seja a última célula com alguma ocorrência no conteúdo.

4) Caso o cursor tenha parado bem além de última célula com algum valor ou fórmula, isso quer dizer que o MS Excel está se esforçando inutilmente para vir até este ponto toda vez que procura atualizar a sua planilha. Pois ele tentará considerar essa dependência inexistente.

5) Agora vem a parte mais divertida: Selecione todas as linhas e colunas a partir da última célula com conteúdo real e delete todas.

6) Repita os passos 2 e 5 em cada uma das abas da sua planilha.

7) Salve a planilha

Reference:

Inspiration:

Tags: Tips, Excel, row, cell, delete, faster, quick, clean, trick, calculate

VBA Excel - Porque as planilhas aumentam inexplicavelmente

| Blog Office VBA | Blog Excel | Blog Access |

Possivelmente já percebeu como suas planilhas inflam sem qualquer aparente explicação, como se tivesse comido vários quilos de açúcar. De uma hora prá outra o que tinha apenas uns parcos 530 KB de peso, passa a exibir exuberantes 2.350 KB. O que aconteceu? Será que nossa planilhas atacam a geladeira durante a madrugada?

Com o passar do tempo, sem que necessariamente tenhamos acrescentado algum conteúdo relevante às nossas planilhas persistem em aumentar.

Linhas inofensivas:
É muito comum que somente abramos o nosso arquivo, efetuando pequenas alterações em certas células, além de vez ou outra inserirmos algumas linhas em branco, apenas para posicionarmos algumas informações.O que talvez não percebamos é que estas inofensivas linhas em branco não somem, antes são salvas, ocupando espaço desnecessário. Lógico que isso é muito comum devido a grande manipulação de dados que efetuamos diariamente. Nunca paramos prá pensar em tamanho no nosso dia-a-dia. Somente quando não temos espaço, ou quando o administrador da rede diz-nos que nosso espaço no Public está lotado é que pensamos no motivo de tão poucas planilhas ocuparem tanto espaço.

Mandando suas planilhas para o SPA:

O MS Excel não consegue distinguir as linhas que estão em branco ou vazias e acaba por gravar todas as ocorrências em branco como conteúdo das nossas planilhas. Além disso, caso formatemos uma coluna inteira até a linha 1 milhão (mesmo que sem querer), ao fechar a planilhas teremos gravado aquela coluna em um milhão de linhas, apenas porque esquecemos da formatação ali.

O tamanho das planilhas é uma preocupação constante nas nossas aplicações. A não ser que realmente precisemos ter ocorrências extensas, deveríamos automatizar a deleção das linhas excedentes.

Como faremos isso?

Várias sugestões de código: 

http://inanyplace.blogspot.com/

Técnicas de exclusão de linhas podem ser colocadas ao fecharmos e/ou gravarmos nossas planilhas...Isso as fariam efetuar atividades 'aeróbicas' constantemente, queimando suas calorias extras...

Deletar linhas e efetuar as tais tarefas aeróbicas podem tornar o fechamento das planilhas muito lento dependendo do tamanho.

Referências: 

Tags: VBA, excel, clean, enxugando, planilha, workbook, size

VBA Excel – Eficiência para deletar milhões de linhas

Termo de Responsabilidade 

Caso necessite deletar aquelas planilhas com milhares de linhas em branco poderá usar a funcionalidade abaixo (método primitivo, mais funcional).

Function EliminateThousandBlankLines(StarLine as Long)

' Author: André Luiz Bernardes.

'    Date: 05.02.2009[/color]

Let nRow = StartLine

    Do While ActiveSheet.Cells(nRow, 1) <> ""

        If ActiveSheet.Cells(nRow, 1).Value <> strUserName Then

            ActiveSheet.Rows(nRow).EntireRow.Delete

        Else

            Let nRow = nRow + 1

        End If

    Loop

End Function

Referências: 

Tags: VBA, Excel, blank, delete, row, line, clean

VBA Excel - Limpando cabeçalhos sem uso no Excel

Desenvolvi esta solução a partir de várias soluções que poderão consultar depois.

6º exemplo:

Sub RemovePageHeaders()

    Application.ScreenUpdating = False

    Dim objRange As Range

    Set objRange = Cells.Find("HeaderText")

    While objRange <> ""

        objRange.Offset(1, 0).Rows(1).EntireRow.Delete

        objRange.Rows(1).EntireRow.Delete

        Set objRange = Cells.Find("HeaderText")

    Wend

    MsgBox ("Removido todos os cabeçalhos")

End Sub

Tags: Excel, Delete, clean, remove, header, Linha, Plan, Planilhas, Row,  rows, worksheet, lines, UDF

André Luiz Bernardes

A&A® - In Any Place.