✔ VBA Dashboards Specialist®: VBA Excel - Acelere os seus Dashboards, Reports e Scorecards

6 de novembro de 2013

VBA Excel - Acelere os seus Dashboards, Reports e Scorecards

Consultancy in Excel, Access, VBA & PowerPoint projects

DESENVOLVO APLICAÇÕES

Perguntas, Questões e Esclarecimentos

Foi-se o tempo de leveza e felicidade onde ingenuamente podíamos criar as nossas planilhas sem nos preocuparmos com o tamanho final delas.

Apenas inseríamos abas (worksheets) adicionais às nossas planilhas (workbook), sem nos preocuparmos com a performance destas.

Sim, a palavra performance foi inserida no nosso vocabulário quase que abruptamente, devido aos nossos usuários começarem a reclamar que as suas planilhas demoravam para abrir ou travavam ao serem atualizadas.

Nesta época, ainda nos restringíamos a eliminar linhas desnecessárias, links quebrados e assim por diante.

Hoje, mesmo usando a versão do MS Office 2013, sim este tópico foi escrito em 15.10.2013, ainda precisamos exercitar essa 'lição de casa'.

Não podemos nos furtar à responsabilidade que nos cerca. Está sob a nossa responsabilidade propiciarmos uma experiência agradável àqueles que se beneficiam da nossa experiência, por utilizarem os produtos que criamos.

Este post foi desenvolvido visando amadurecer mais rapidamente os desenvolvedores com pouca experiência, ou mesmo aqueles que usam o MS Excel a muito tempo, mais ainda não tiveram chance de serem apresentados a sua arquitetura, compreendendo os planos do pessoal de Redmond, e por isso, não têm ciência de como melhorar os seus resultados.

Definindo o tamanho de cada Aba da Planilha

O código abaixo lhe permitirá saber exatamente quanto cada aba (sheet) tem fisicamente como tamanho. Com tal conhecimento poderá trabalhar nelas para consolidar melhor as informações e perceber o quanto impactam no processamento geral da planilha.

Sub SheetSiz()


Dim a(), Bytes As Double, i As Long, FileNameTmp As String
Dim Sh, Wb As Workbook


Set Wb = ActiveWorkbook

ReDim a(0 To Wb.Sheets.Count, 1 To 2)

' Desliga a atualização da tela

Let Application.ScreenUpdating = False

On Error GoTo exit_


' Coloca os nomes em a(,1) e o tamanho em a(,2)

With CreateObject("Scripting.FileSystemObject")

' Constrói um arquivo temporário

Let FileNameTmp = .GetSpecialFolder(2) & "\" & Wb.Name & ".tmp"

' Salva a planilha
Wb.SaveCopyAs FileNameTmp

' Coloca o nome da Planilha (workbook) e o tamanho em a(0,)

Let a(0, 1) = Wb.Name
  Let a(0, 2) = .GetFile(FileNameTmp).Size

' Coloca o nome e o tamanho de cada aba em a(i,)

For i = 1 To Wb.Sheets.Count
Wb.Sheets(i).Copy
ActiveWorkbook.SaveCopyAs FileNameTmp

  Let a(i, 1) = Wb.Sheets(i).Name

  Let a(i, 2) = .GetFile(FileNameTmp).Size

  Let Bytes = Bytes + a(i, 2)

ActiveWorkbook.Close False
Next

Kill FileNameTmp
End With


' Mostra o nome e o Tamanho da Planilha
Debug.Print a(0, 1), Format(a(0, 2), "# ### ### ##0") & " Bytes."


' Mostra o nome e o tamanho correto de cada Aba.
For i = 1 To UBound(a)

Debug.Print a(i, 1), Format(a(0, 2) * a(i, 2) / Bytes, "# ### ### ##0") & " Bytes."
Next

exit_:

  Let Application.ScreenUpdating = True



If Err Then MsgBox Err.Description, vbCritical, "Erro!"
End Sub

Grave as suas aplicações como XLSB

Entender melhor o conteúdo de cada extensão lhe fará perceber a importância de manter suas aplicações como XLSB.

Xlsx
Este formato é baseado no formato XML, que é padrão desde a versão do Excel 2007/10. Este formato não comporta código VBA (Visual Basic for Applications), ou seja, arquivos com macros nem pensar. Também não é compatível com a versão 4.0 das macros (.xlm).

Xlsm
Formato baseado em XML, e aceita VBA, e macros, desde a versão 2007/10 do Excel. É compatível com a versão 4.0 das macros (.xlm).

Xls
Este formato de arquivo é usado na versão anterior do Excel 97-2003 e é conhecido como formato de arquivo binário (BIFF8).

Xlsb
Este é o formato de arquivo binário (BIFF12) para Excel 2007/10. Este formato é usado para fornecer uma técnica para usuários do Excel para armazenar arquivos extremamente grandes e é mais eficiente ao abrir e ser executado.

Xlam
Este formato de arquivo é baseado em XML e está habilitado para macros e Add-ins desde o Excel 2007/10. Um add-in é um programa adicional que se destina a executar código suplementar. Este formato suporta o uso de projetos VBA e planilhas com macros do Excel 4.0 (. Xlm).

Ods
Este formato de arquivo é conhecido como Open Document Spreadsheet. Você pode salvar arquivos do Excel 2010 para que eles possam ser acessados em aplicativos de planilha que aplicam o formato Open Document Spreadsheet, por exemplo: OpenOffice.org e Google Docs. Pode ser aberto no Excel 2010. Alguma formatação pode ser salva.

Comprima os arquivos XLSX em arquivos ainda menores
Clique no link acima para aprender como são compostos os arquivos XML do Excel. E aprenda a compactar ainda mais os seus arquivos XLSX.

Referenciando a frente
Para conseguir uma clareza maior e evitar erros, implemente as suas fórmulas de modo que elas não façam referência para a frente (para a direita ou para baixo) e para outras fórmulas ou células. Geralmente encaminhar referência não afeta o desempenho de cálculo, exceto em casos extremos para o primeiro cálculo de uma planilha (workbook), onde ele pode levar mais tempo para estabelecer uma seqüência de cálculo sensatos, caso hajam muitas fórmulas que precisem ter o seu cálculo adiada.

Ligações entre as pastas de trabalho
Evite ligações inter-pasta de trabalho quando for possível: elas podem ser lentas, facilmente quebrados, e nem sempre será fácil encontrá-los e corrigi-los.

Utilizar menos planilhas grandes, geralmente, mas não sempre, será melhor do que utilizar muitas planilhas menores. Uma exceção pode ser quando temos muitos cálculos no front-end e raramente forem recalculados em uma pasta de trabalho separada, ou quando você tiver RAM suficiente.

Tente usar referências de célula simples e direta que trabalhem em pastas de trabalho fechadas. Ao fazer isso, evitará recalcular todos os seus workbooks interligados ao recalcular toda a planilha. Além disso, poderá ver os valores da planilha fechada, que é freqüentemente importante para a depuração e auditoria de uma pasta de trabalho.

Se você não puder evitar o uso de pastas de trabalho vinculadas, tente tê-las todas abertos em vez de fechadas, e abra as pastas de trabalho que estiverem ligadas antes de abrir as pastas de trabalho que estiverem ligadas a partir destas (ufa, confuso né...Re-leia...).

Aumente a velocidad e pare com o flickering da tela
É uma dica simples, mas muita gente esquece de colocá-las.