O Melhor do Excel: Descrição de matrizes em procedimentos Sub no VBA.

segunda-feira, 10 de junho de 2013

Descrição de matrizes em procedimentos Sub no VBA.

Este artigo descreve as matrizes em procedimentos Sub no Visual Basic for Applications. Este artigo descreve esses três tipos de matrizes: matrizes gerais, matrizes multidimensionais e matrizes dinâmicas. Este artigo também fornece alguns exemplos de cada tipo de matriz e um exemplo de como preencher uma matriz com os dados da planilha.

Informações gerais sobre procedimentos Sub

Um procedimento Sub é uma série de instruções do Visual Basic entre a instrução Sub e a instrução End Sub. O procedimento Sub executa ações, mas o procedimento Sub não retorna um valor. Um procedimento Sub pode receber argumentos que são passados por um procedimento de chamada. Exemplos de tais argumentos são constantes, variáveis e expressões. Se um procedimento Sub não possuir argumentos, a instrução Sub deve incluir um conjunto vazio de parênteses

Observação Neste documento, o termo que procedimento Sub é usado como sinônimo o termo macro.

Cada macro tem a seguinte estrutura:

Sub MacroName ()
lines of macro code
End Sub

Estas são as regras para nomes de procedimentos Sub:

Você deve usar uma letra como o primeiro caractere.
Você não pode usar um nome que seja mais de 255 caracteres.
Não é possível usar um espaço, um ponto, um ponto de exclamação, um sinal, um e comercial, um sinal de cifrão ou um sinal de número no nome do.
Não é possível usar palavras-chave restrita.

Observação Para exibir uma lista completa de palavras-chave restrita, clique em conteúdo e índice no menu Ajuda no Editor do Visual Basic e, em seguida, digite palavras-chave.

Você pode ter duas macros no mesmo projeto com o mesmo nome, mas você deve seguir estas regras:

As duas macros com o mesmo nome devem estar em diferentes módulos.
Se houver duas macros que tenham o mesmo nome no mesmo projeto, você deve colocar o nome do módulo na frente do nome da macro para chamar uma macro.

Por exemplo, para chamar a macro de teste em um módulo chamado Module2, use a seguinte sintaxe:

Module2.Test

Matrizes gerais

Uma matriz é uma única variável que é declarada de forma que ele pode armazenar múltiplos valores do mesmo tipo de dados. Se você deseja acessar qualquer item na variável de matriz, você deve fornecer o número de índice do elemento e o nome da variável.

Declarando uma variável de matriz

Matrizes são declaradas como variáveis não-matriz, usando a instrução Dim, a instrução estática, a instrução e a instrução Public. Variáveis de matriz não são também conhecidas como variáveis escalares . A diferença entre as variáveis de matriz e variáveis escalares é que você deve especificar o tamanho da variável de matriz quando você declará-la. O tamanho da matriz informa ao Visual Basic for Applications podem ser armazenados como muitos elementos na matriz. No entanto, você pode criar uma matriz que muda de tamanho durante a execução do programa. Uma matriz que muda de tamanho durante a execução do programa é conhecida como uma matriz dinâmica.

Declarando uma matriz fixa

Quando você declara uma matriz, você deve especificar o nome da matriz, o tamanho da matriz e o tipo de dados armazenado na matriz. A seguir está a sintaxe padrão para declarar uma matriz:

Dim <array name> (# of elements - 1) As <data type>

Por exemplo, se você tiver uma matriz que você deve ter 10 valores de número inteiro, criar a matriz com a seguinte instrução:

Dim MyArray(9) As Integer

Observação A dimensão da matriz com 9. O primeiro número de índice da matriz é considerado como zero. Portanto, para acessar o primeiro elemento na matriz, use a seguinte sintaxe:

MsgBox MyArray(0)

O local da linha onde você declarar seu array afeta o escopo da matriz e o tempo de vida da matriz.

Tente este exercício:

Insira um novo módulo no seu projeto.

Adicione o seguinte código no módulo:

Sub Array_Example()
'Create an array that has a total of three elements.
Dim MyArray(2) As Integer 
'Fill in the array with some values.
MyArray(0) = 100
MyArray(1) = 300
MyArray(2) = 500
MsgBox "First element= " & Myarray(0) & _
" Second element= "& MyArray(1) & _
" Third element= " & MyArray(2)
End Sub

Execute a macro Array_Example. Você recebe a seguinte mensagem:

Primeiro elemento = 100 segundo elemento = 300 terceiro elemento = 500
Clique em OK para fechar a mensagem.

Alterar o primeiro número de índice da matriz

Se você não quiser o primeiro número de índice para seus arrays como zero, você pode adicionar uma linha adicional que utiliza a instrução Option Base para seu módulo ou pode dimensão sua matriz usando uma sintaxe ligeiramente diferente.

A declaração Option Base

Você pode adicionar a declaração Option Base na parte superior do módulo para especificar o índice padrão para o primeiro elemento das matrizes no módulo. O valor que você especificar pode ser apenas zero ou um.

Tente este exercício:

Modificar o módulo que contém a macro Array_Example à seguinte:

Option Base 1
Sub Array_Example()
'Create an array that has a total of two elements.
Dim MyArray(2) As Integer 
'Fill in the array with some values.
MyArray(1) = 200
MyArray(2) = 400
MsgBox "First element= " & Myarray(1) & _
" Second element= "& MyArray(2) 
End Sub

Execute a macro Array_Example. Você recebe a seguinte mensagem:
Primeiro elemento = 200 segundo elemento = 400
Clique em OK para fechar a mensagem.

Observação Você deve ter cuidado com o índice padrão para suas matrizes. É um erro comum supor que o tamanho da matriz corresponde ao número especificado na linha onde a matriz é dimensionada.

O índice superior e o índice inferior de uma matriz de dimensionamento

Há outra maneira de sua matriz de dimensão. Você pode usar uma segunda sintaxe que usa dois números para especificar os índices da matriz.

Tente este exercício:

Adicione o seguinte código para o módulo que contém a macro Array_Example:

Observação Deixe a instrução Option Base do exemplo anterior no módulo.

Sub Array_Example_2()
'Create an array with two elements with indexes 3 and 4.
Dim NewArray(3 to 4) As Integer
'Fill in the array with some values.
NewArray(3) = 50
NewArray(4) = 60
MsgBox "First element= " & NewArray(3) & _
   " Second element= " & NewArray(4)
End Sub

Execute a macro Array_Example_2. Você recebe a seguinte a seguinte mensagem:
Primeiro elemento = 50 segundo elemento = 60
Clique em OK para fechar a mensagem.

Determinar os limites de uma matriz

Você pode determinar o limite superior e o limite inferior de uma matriz usando a função UBound e a função LBound, respectivamente. O limite inferior padrão para qualquer matriz é zero ou um, dependendo de como você está usando a declaração Option Base. No entanto, como no exemplo anterior, você pode definir o limite inferior de uma matriz para algo diferente de zero ou um. Se você fizer isso, você deve usar a função LBound e a função UBound para determinar os limites de uma matriz.

Tente este exercício:

Altere a macro Array_Example_2 do exemplo anterior para o seguinte:

Sub Array_Example_3()
'Create an array with three elements with indexes 2 through 4.
Dim NewArray(2 to 4) As Integer
'Fill in the array with some values.
NewArray(2) = 40
NewArray(3) = 50
NewArray(4) = 60
MsgBox "Lowest array index is " & LBound(NewArray) & _
     " and Highest array index is " & UBound(NewArray)
End Sub

Execute a macro Array_Example_3. Você recebe a seguinte mensagem:
Menor índice de matriz é 2 e o maior índice de matriz é 4

Observação A mensagem exibe os números de índice em vez dos valores armazenados na matriz.

Matrizes multidimensionais

No Visual Basic for Applications, arrays podem ter até 60 dimensões. O exemplo mais simples de uma matriz multidimensional é uma matriz bidimensional. Uma matriz bidimensional pode ser tratada como uma tabela de várias colunas em uma planilha.

Declarar uma matriz multidimensional

Para declarar uma matriz multidimensional, use a sintaxe padrão:

Dim <array name>(r, c) As <data type>

Essa sintaxe usa o valor que você definiu com a instrução Option Base como o limite inferior de cada dimensão. (Essa sintaxe usa zero se você não estiver usando Option Base.) Você também pode usar o seguinte:

Dim <array name>(1 to r , 1 to c) As <data type>

Essa sintaxe tem um limite inferior de um para cada dimensão da matriz.

Qualquer sintaxe irá criar uma matriz bidimensional que tenha linhas r e c colunas. Se você usar a primeira sintaxe, o número de elementos em cada dimensão variam de acordo com a configuração de Option Base.

Se você usar a seguinte declaração de exemplo para uma matriz, você pode criar uma matriz de três linhas por duas colunas que é semelhante ao intervalo a1: B3 de uma planilha do Microsoft Excel:

Dim MyArray(1 to 3, 1 to 2) As Integer

Tente este exercício:

Inicie o Excel e, em seguida, abra uma nova pasta de trabalho.
Inicie o Editor do Visual Basic e, em seguida, insira um novo módulo no seu projeto.

Digite o seguinte código para este módulo:

Option Base 1
Sub Multi_Array()
'Create a 3-row-by-2-column array.
Dim MyArray(3, 2) As Integer
MyArray(1,1) = 11    'first row, first column
MyArray(1,2) = 12    'first row, second column
MyArray(2,1) = 21    'second row, first column
MyArray(2,2) = 22    'second row, second column
MyArray(3,1) = 31    'third row, first column
MyArray(3,2) = 32    'third row, second column
Sheet1.Range("A1").Value = MyArray(1,1)
Sheet1.Range("B1").Value = MyArray(1,2)
Sheet1.Range("A2").Value = MyArray(2,1)
Sheet1.Range("B2").Value = MyArray(2,2)
Sheet1.Range("A3").Value = MyArray(3,1)
Sheet1.Range("B3").Value = MyArray(3,2)
End Sub

Execute a macro Multi_Array.
Retorne o foco para o Excel pressionando ALT + F11.

O intervalo a1: B3 será preenchido com o conteúdo da matriz.

Matrizes dinâmicas

Você pode declarar uma matriz, de modo que as dimensões da matriz podem ser aumentadas ou diminuídas enquanto a macro estiver sendo executado. No entanto, o conteúdo da matriz serão perdido quando você redimensionar a matriz.

Declarar uma matriz dinâmica

Para declarar uma matriz como uma matriz dinâmica, use a seguinte sintaxe:

Dim <array name>() As <data type>

Observação Nenhum tamanho for especificado para a matriz quando declarar uma matriz dinâmica.

Tente este exercício:

Insira um novo módulo no seu projeto.

Digite o seguinte código para este módulo:

Option Base 1
Sub Dynamic_Array()
'Create a dynamic array.
Dim  MyArray() As Integer
'Redimension the array to two elements.
ReDim MyArray(2)
'Populate the array elements.
MyArray(1) = 1
MyArray(2) = 2
MsgBox "First element= " & MyArray(1) & _
" Second element= " & MyArray(2)
'Redimension the array to three elements.
'The contents of the array are lost.
ReDim MyArray(3)
MyArray(3) = 3
MsgBox "First element= " & MyArray(1) & _
" Second element= " & MyArray(2) & _
" Third element= " & MyArray(3)
End Sub

Execute a macro Dynamic_Array. Você recebe a seguinte mensagem:

Primeiro elemento = 1 segundo elemento = 2
Clique em OK para fechar a primeira mensagem. Você recebe a seguinte mensagem:

Primeiro elemento = 0 segundo elemento = 0 terceiro elemento = 3
Clique em OK para fechar a mensagem.

A segunda mensagem exibe valores de zero para o primeiro elemento e o segundo elemento porque a segunda instrução ReDim limpa o conteúdo da matriz.

Preservar o conteúdo da matriz com matriz redimensioned

Quando você usa a instrução ReDim para redimensionar uma matriz, o conteúdo da matriz é perdido. Isso é aceitável, desde que você sabe sobre ele, quando você estiver desenvolvendo sua macro. Se você quiser manter o conteúdo de sua matriz, quando você a redimensionar, use a palavra-chave Preserve juntamente com a declaração ReDim.

Tente este exercício:

Altere a segunda instrução ReDim na macro Dynamic_Array para que a macro é semelhante à seguinte:

Option Base 1
Sub Dynamic_Array()
'Create a dynamic array.
Dim  MyArray() As Integer
'Redimension the array to two elements.
ReDim MyArray(2)
'Populate the array elements.
MyArray(1) = 1
MyArray(2) = 2
MsgBox "First element= " & MyArray(1) & _
" Second element= " & MyArray(2)
'Redimension the array to three elements.

'The contents of the array are not lost when you use the Preserve keyword.

ReDim Preserve MyArray(3)
MyArray(3) = 3
MsgBox "First element= " & MyArray(1) & _
" Second element= " & MyArray(2) & _
" Third element= " & MyArray(3)
End Sub

Execute a macro Dynamic_Array. Você recebe a seguinte mensagem:

Primeiro elemento = 1 segundo elemento = 2
Clique em OK para fechar a primeira mensagem. Você recebe a seguinte mensagem:

Primeiro elemento = 1 segundo elemento = 2 terceiro elemento = 3
Clique em OK para fechar a mensagem.

A segunda mensagem exibe os mesmos valores como a primeira mensagem. Esse comportamento ocorre porque a palavra-chave Preserve foi usado com a declaração ReDim.

Observação Quando você usa a palavra-chave Preserve com uma matriz dinâmica, você só pode alterar o limite superior da última dimensão da matriz e não é possível alterar o número de dimensões na matriz.

Preencher uma matriz com os dados da planilha

Utilizando dados de uma planilha e colocá-lo em uma matriz são um uso comum de matrizes no Visual Basic for Applications.Se você preencher uma matriz dos dados da planilha, pode fazer coisas como classificar os dados, executar uma análise numérica nos dados ou exportar os dados.

Variável Variant que contém uma matriz

Se você quiser transferir dados de um intervalo de células em uma planilha para uma matriz, você pode percorrer as células e preencher cada elemento na matriz com o valor da célula toda vez pelo loop, ou você pode atribuir diretamente o intervalo para a matriz. O último método é mais rápido. No entanto, a matriz que você está passando para o intervalo de células é realmente uma variável Variant que contém uma matriz.

Observação Uma variável Variant que contém uma matriz é diferente de uma variável de matriz que contém elementos do tipo variant. Tenha cuidado ao trabalhar com matrizes unidimensionais.

Tente este exercício:

Salvar e fechar qualquer abrir pastas de trabalho e, em seguida, abra uma nova pasta de trabalho.
Digite o seguinte na Sheet1:

R1: 1

R2: 2

R3: 3
Inicie o Editor do Visual Basic e, em seguida, insira um módulo.

Digite o seguinte código no módulo:

Option Base 1
Sub Array_from_sheet_data()
'Create a Variant variable.
Dim MyArray As Variant
'Assign the range A1:A3 to the Variant variable.
MyArray = Sheet1.Range("A1:A3").Value
MsgBox  "Cell A1 is: " & MyArray(1,1) & _
" Cell A2 is: " & MyArray(2,1) & _
" Cell A3 is: " & MyArray(3,1)
End Sub

Execute a macro Array_from_sheet_data. Você recebe a seguinte mensagem:

A célula A1 é 1 A2 de célula 2 célula A3 é 3
Clique em OK para fechar a mensagem.

Observação As referências aos elementos MyArray usam o índice da linha e o índice da coluna, mesmo que os dados são uma única coluna. Os dados de coluna única significa que essa matriz é uma matriz unidimensional. A variável de MyArray não é realmente uma matriz. Em vez disso, a variável de MyArray é um Variant que contém uma matriz. Portanto, você deve usar os índices de linha e os índices de coluna quando você faz referência a esse tipo de matriz.

Pesquisar