Ok cheguei neste tópico e me sinto em casa! eheheh
1) Usar variáveis públicas eu evito ao máximo, salvo casos em que eu enxergue que pública é melhor para a situação. E se as arrays forem públicas, você ainda assim pode limpá-las da memória... Pode e deve.
2) Já esbarrei no limite de uso de array, mas ele varia conforme variáveis que eu não saberia verbalizar. Estou trabalhando com planilhas com mais de 200 colunas e 400k linhas. São fontes de dados *cuspidas pelo SAP. Concordo que o limite se dê pela qtd de memória, mas não o disponível pela máquina. O Excel 32bits fecha quando o uso de memória passa de 1GB. O Excel 64bits, diz que aguenta até 2GB. Quando uma array variante bidimensional não aguenta o *estupro de muitos dados, o erro é: Memória insuficiente. E isso as vezes acontecem bem antes de eu chegar no limite de 1GB. Por isso não consigo dizer as variáveis que limitam a array. O que já tive como conferir é, quanto menos colunas tiver, mais linhas você consegue. E vice-versa.
3) Quanto a limpeza de memória, eu criei uma rotina que está abaixo. Eu penso que como todo bom programador, tudo que se instancia ou inicia, deve ser detruido da memória quando o uso não é mais necessário. Então eu uso uma rotina super legal que remove qquer coisa da memória, criada pelo meu grande amigo Jefferson Dantas, que não frequenta este forum. Eu dei uma boa melhorada e ela mata tudo que vc manda como parametro, numa chamada só.
'---------------------------------------------------------------------------------------
' Modulo : xlApplication / Módulo
' Rotina : RemoveObjectsFromMemory() / Sub
' Autor : Jefferson Dantas (jefferdantas@gmail.com)
' Data : 07/11/2012 - 16:42
' Revisão : Fernando Fernandes (fernando.fernandes@outlook.com.br)
' Data : 07/01/2013 (mdy)
' Proposta : Remove crap from memory
'---------------------------------------------------------------------------------------
Public Sub RemoveObjectsFromMemory(ParamArray Objects() As Variant)
On Error Resume Next 'Resume next necessario em caso de erro
Dim Counter As Integer
For Counter = 0 To UBound(Objects) Step 1
Select Case TypeName(Objects(Counter))
Case "Boolean"
Objects(Counter) = False
Case "Variant"
If VBA.IsArray(Objects(Counter)) Then Erase Objects(Counter)
Objects(Counter) = Empty
Case "String"
Objects(Counter) = vbNullString
Case "Worksheet"
Set Objects(Counter) = Nothing
Case "Workbook"
Objects(Counter).Close SaveChanges:=False
Set Objects(Counter) = Nothing
Case "Database", "Recordset2", "Recordset"
Objects(Counter).Close
Set Objects(Counter) = Nothing
Case Else
Set Objects(Counter) = Nothing
'Suggestion:
If VBA.IsObject(Objects(Counter)) Then
Set Objects(Counter) = Nothing
Else
Objects(Counter) = Empty
End If
End Select
Next Counter
On Error GoTo 0
End Sub
Para usar, um exemplinho bobo:
Sub ExemploBobo()
Dim wbk As Excel.Workbook
Dim wsh As Excel.Worksheet
Dim rng As Excel.Range
Set wbk = Workbooks.Add
Set wsh = wbk.Worksheets(1)
Set rng = wsh.Range("A1")
'faça qquer coisa com estes objetos
'ao final, ao invés de fazer isso:
Set rng = Nothing
Set wsh = Nothing
Set wbk = Nothing
'você faz isso:
Call RemoveObjectsFromMemory(wbk, wsh, rng)
'só um cuidado, eu preparei o RemoveObjectsFromMemory para fechar um workbook, sem salvar.
'ou seja, se vc quer que ele seja salvo, salve antes de chamar essa *faxineira de memória
End Sub
4) Usar matriz é um risco quando sua massa de dados for gigantesca. Minha sugestão é, se você for trabalhar com matrizes/arrays, tenha uma planilha temporária que sempre será preenchida e limpa ao final do processo, use esta planilha para remover linhas e/ou colunas que nào são necessárias para o processamento, e somente então você carrega este intervalo numa array. Garantindo assim mais segurança de que a matriz vai aguentar a *tóra de dados.
5) Matrizes/Arrays são estupidamente mais rápidas do que o Cells(). E oferecem vantagens absurdas no quesito desempenho. Mas têm que ser bem aproveitadas.
Eu criei um modelinho básico que mostra a diferença em performance de uma array contra o cells(). Pode ser encontrado aqui: http://1drv.ms/1qI4ziv
6) Para facilitar o uso de matriz/arrays, com dados vindo de ou indo para uma planilha, eu cruei duas outras rotinas muito loucas. Sabe aquela limitação de pegar os dados de um intevalo para uma matriz tipo variant, mas pq seu intervalo dessa vez tem uma célula só, e então o VBE entende que não é matriz, é um string, e nestes casos, vc queria uma matriz, e não tem matriz, e dá tipos incompatíveis? Então, isso é resolvido abaixo.
E mais, pra quem não fala inglês: GetArrayFromRange = PegarMatrizDoIntervalo e DropArray = Derrubar Matriz.
Public Enum CellProperty
Value = 0
Formula = 1
FormulaR1C1 = 2
End Enum
'---------------------------------------------------------------------------------------
' Modulo....: xlArrays / Módulo
' Rotina....: GetArrayFromRange() / Function
' Autor.....: Fernando Reis
' Contato...: fernando.fernandes@outlook.com.br
' Data......: 12/19/2012 (mdy)
' Empresa...: Mondial Tecnologia em Informática LTDA.
' Descrição.: This routine creates an array from a given range of 1 or more cells.
'---------------------------------------------------------------------------------------
Public Function GetArrayFromRange(ByRef rng As Range, Optional WhichProperty As CellProperty = CellProperty.Value) As Variant
On Error GoTo TreatError
Dim arrArray(1 To 1, 1 To 1) As Variant
If rng.Cells.Count = 1 Then
Select Case WhichProperty
Case CellProperty.Value
arrArray(1, 1) = rng.Value
GetArrayFromRange = arrArray
Case CellProperty.Formula
arrArray(1, 1) = rng.Formula
GetArrayFromRange = arrArray
Case CellProperty.FormulaR1C1
arrArray(1, 1) = rng.FormulaR1C1
GetArrayFromRange = arrArray
End Select
Else
Select Case WhichProperty
Case CellProperty.Value
GetArrayFromRange = rng.Value
Case CellProperty.Formula
GetArrayFromRange = rng.Formula
Case CellProperty.FormulaR1C1
GetArrayFromRange = rng.FormulaR1C1
End Select
End If
On Error GoTo 0
Exit Function
TreatError:
' Call xlExceptions.TreatError(VBA.Err.Description, VBA.Err.Number, "xlArrays.GetArrayFromRange()", Erl, True)
End Function
'---------------------------------------------------------------------------------------
' Rotina....: DropArray() / Sub
' Contato...: fernando.fernandes@outlook.com.br
' Autor.....: Fernando Fernandes
' Data......: 02/18/2013 (mdy)
' Empresa...: www.Planilhando.Com.Br
' Descrição.: Routine that drops a whole array into a worksheet, given the worksheet and a start range
'---------------------------------------------------------------------------------------
Public Sub DropArray(ByRef wsh As Excel.Worksheet, ByVal lRow As Long, ByVal lCol As Long, ByRef arr As Variant)
On Error GoTo TreatError
With wsh
If LBound(arr, 1) = 1 And LBound(arr, 2) = 1 Then
.Range(.Cells(lRow, lCol), .Cells(lRow + UBound(arr, 1) - 1, lCol + UBound(arr, 2) - 1)).Value = arr
ElseIf LBound(arr, 1) = 0 And LBound(arr, 2) = 0 Then
.Range(.Cells(lRow, lCol), .Cells(lRow + UBound(arr, 1), lCol + UBound(arr, 2))).Value = arr
End If
End With
On Error GoTo 0
Exit Sub
TreatError:
'Call xlExceptions.TreatError(VBA.Err.Description, VBA.Err.Number, "xlArrays.DropArray()", Erl, True)
End Sub
Isso tudo faz parte do meu modelo de projeto que propus no outro tópico.
Eu quero poder juntar o maior número de rotinas úteis, tão genéricas que tornam-se reutilizáveis em quaisquer situações.
Bom, falei demais. Qualquer coisa, estou por aqui para participar.
(p.s.: não falamos de dicionários, que são muito mais rápidos que matrizes, e possuem métodos super interessantes para encontrarmos coisas numa matriz/array sem ter que ficar *varrendo ela o tempo todo. Tem muita coisa legal !
Abraço!
FF