Edcronos,

Boa Noite!

A limitação é dada pelos tipos de variáveis dos arrays e pela quantidade de memória disponível na máquina onde os arrays serão utilizados.

Abaixo, um link onde você poderá consultar maiores informações:
http://pt.wingwit.com/Software/spreadsh ... 9LX_rEvkvI

Além do Help VBA sobre Array, não conheço nenhum outro informativo sobre detalhes/limites.
Creio eu que em principio Arrays são limitadas pela capacidade de processamento/armazenamento do equipamento e sua definição de tipo (integer/long/string etc..) bem como o modelo de Array (unitaria/varios elementos etcc..).
Porem uma boa pratica, que auxilia na prevenção de "pilha"; e após ser utilizada qualquer variavel/array deva ser "limpa"/"descarregada" liberando assim a memoria para outras aplicações/incursões

Ola,

sobre limitações de memoria eu já tinha lido,
mas é mais em cima do tipo de excel do que pela memoria física da maquina, 64bit ou 32bit
até agora só testei com 6000 linhas X 40 Colunas, não sei o limite para o tipo variante

como é para trabalho direto com dados da planilha o tipo vai ser sempre variante

Porem uma boa pratica, que auxilia na prevenção de "pilha"; e após ser utilizada qualquer variavel/array deva ser "limpa"/"descarregada" liberando assim a memoria para outras aplicações/incursões

Uso praticamente todas as variaveis em modo publico principalmente as Arrays que trocam informações entre si nas varias SUBs.

as Arrays são dinâmicas somente uso Redim quando não é para pegar direto da planilha
praticamente é:

Sub test()
ColunO = Range("A1:G1000").Value2
Call Espelhar(ColunO, 2)
Call Organiza(ColunO, 3, 10)
Range("A1:G1000").Value2 = ColunO
Call SetorL
End Sub

Sobre esvaziar as Arrays para liberar memoria não consegui nada a respeito
Minhas Arrays são publicas e redefinidas a cada execução das macros
talvez isso tenha liberado o impacto do uso da memoria.

Pensei em usar redim para liberar as Arrays, Ex. Redim array(1)
mas como não tiver problemas a respeito larguei de lado.

como estou reformulando a planilha e criando Subs personalizadas para trabalho com arrays, tenho que pensar nos possíveis problemas e limitações,
mas não achei nenhum exemplo pratico sobre o assunto

tipo :quantas celulas contendo 3 casas decimais posso inserir num array tipo variante?

qualquer dica a respeito é bem vinda.

att.

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

A respeito de variáveis publicas ,
Realmente dentro do meu projeto é essencial pq uma macro se comunica com as outras e uma depende das outra ( talvez não tanto agora que estou aprendendo a trabalhar com ByRef " mas ainda estou engatinhado")

Sobre a velocidade eu já me deparei com esse fato,
por isso mesmo estou transformando tudo em array.
parece ter um interpretador de código entre vba e excel oq torna as rotinas exponencialmente mas lentas.
e com arrays o vba trabalha com os dados diretamente na memoria.

sobre memoria já tinha lido algo, e parece que o vba precisa de uma area de memoria contigua para alojar os dados
mas não sabia o distancia que eu estava de alcançar o limite pratico

como minha planilha e macros trabalham por blocos divisíveis e escalonáveis não creio te problemas em um tempo curto, pelo menos assim espero...

vou ter que dar uma estudada nas suas macros para entender o funcionamento,
As minhas são bem simples ainda...

inverte (direita:esquerda) e (cima:baixo):

Public Sub Espelhar(ByRef ArrayNome As Variant, C_ou1__L_ou2 As Variant, Optional Nun_C_L As Long)
    Dim L As Long, C As Long, x As Long, Pia As Long, Pfa As Long
    Dim Ar2() As Variant
    Pia = LBound(ArrayNome, 2): Pfa = UBound(ArrayNome, 2)
    lia = LBound(ArrayNome, 1): lfa = UBound(ArrayNome, 1)
    x = 0
    Ar2 = ArrayNome
If C_ou1__L_ou2 = "c" Then C_ou1__L_ou2 = 1
If C_ou1__L_ou2 = "l" Then C_ou1__L_ou2 = 2
    If Nun_C_L = 0 Then
    
        If C_ou1__L_ou2 = 1 Then '-------( ColunaS )
            For L = lia To lfa
                x = 0
                For C = Pia + 3 To Pfa
                    ArrayNome(L, C) = Ar2(L, Pfa - x)
                    x = x + 1: Next: Next
        End If
        If C_ou1__L_ou2 = 2 Then '--( LinhaS )
            For L = lia To lfa
                For C = Pia To Pfa
                    ArrayNome(L, C) = Ar2(lfa - x, C)
                Next: x = x + 1: Next
        End If
    Else '------------(   Linha ou coluna especifica    )
        If C_ou1__L_ou2 = 1 Then
            For L = lia To lfa
                ArrayNome(L, Nun_C_L) = Ar2(lfa - x, Nun_C_L)
                x = x + 1: Next
        End If
        If C_ou1__L_ou2 = 2 Then
            For C = Pia + 3 To Pfa
                ArrayNome(Nun_C_L, C) = Ar2(Nun_C_L, Pfa - x)
                x = x + 1: Next
        End If
    End If
End Sub

Meu propósito é caso a minha ideia inicial se prove possível, seria transferir o projeto para outra linguagem e usar um banco de dados e o excel apenas para efeito visual.

creio que com essa minha duvida está respondida.

fernando, não posso matar todas as variáveis de vez

tem variáveis que guarda os valores para a próxima execução da macro

tipo , se eu executo a 1ª vez ela faz uma coisa, na 2ª faz outra, na 3ª outra e assim vai
uma delas é para deslocamento assimétrico dos setores.
enquanto um desloca 3 colunas a cada 2 execuções o outro setor desloca duas linhas a cada 2...

Então, essas variáveis que você precisa em outras rotinas, você não as deve destruir até não precisar mais delas.
Mas acostume-se a sempre destruir aquelas que não são mais necessárias.

E mais, quando vc dominar o ByRef e o ByVal, verá que variáveis globais serão cada vez menos presentes em seus códigos.
Digo, para passar uma variável de uma rotina para outra, usa-se os parâmetros.

É interessante vc sempre verificar o consumo de memória do Excel.
Passo a passo:

- Abra o Gerenciador de Tarefas;
- Inicie seu código com o F8, e execute-o linha a linha;
- Preste atenção ao que cada linha de código faz com o consumo de memória;
- Verá que a matriz afeta a memória. Quanto maior a matriz, maior o salto de utilização de memória. Mas quando a execução termina, o Excel nem sempre volta a seu estado inicial de consumo de memória. Se você destruir os objetos ou variáveis que estava usando, garantirá que o consumo sempre voltará ao estado inicial.
[/list:u:3sqavhfo]

Portanto, mantenho a dica: Sempre que puder (e entenda, sempre que puder, é isso mesmo, sempre que puder), ou seja, depois que já tiver feito todo uso necessário das variáveis e objetos, globais ou não, é importantíssimo limpar a memória.

FF

Existem mil maneiras de preparar Neston. Invente a sua!
http://www.youtube.com/ExpressoExcel